Is Not one of of...... 1

[robmason10]

I have a report which selects data where:

not ({clgnames.short} in ["CLOSED", "GRP09", "GRP10", "GRP12", "GRP14", "GRP15", "GRP16", "GRP17", "GRP18", "GRP19", "GRP20", "GRP21", "GRP24", "GRP26", "GRP34", "IFMDFJJ", "IFMDFJU", "MIFAJCO", "MIFAJFD", "MIFAJPCO", "MIFAJPFD", "MIFASTMG", "MIFAUCO", "MIFAUFD", "PWM", "PWM (B4)"])

I simply want to add the condition 'but {clgnames.short} is in 'Active'

Having a mental block.

Thanks in advance

 

[Reebo99]

remove
not ({clgnames.short} in ["CLOSED", "GRP09", "GRP10", "GRP12", "GRP14", "GRP15", "GRP16", "GRP17", "GRP18", "GRP19", "GRP20", "GRP21", "GRP24", "GRP26", "GRP34", "IFMDFJJ", "IFMDFJU", "MIFAJCO", "MIFAJFD", "MIFAJPCO", "MIFAJPFD", "MIFASTMG", "MIFAUCO", "MIFAUFD", "PWM", "PWM (B4)"])

and simpy add

{clgnames.short} = 'Active'

Reebo
Scotland (Sunny with a Smile)

 

[Naith]

Perhaps I'm missing something here, but if you just use

UCase({clgnames.short}) = 'ACTIVE'

you satisfy your request, and don't even need the NOT statement.


Naith

 

[robmason10]

Sorry - badly explained...{clgnames.short} can have mutiple entries for each detail i.e.

Active
MIFAJCO
PWM

In the above scenarion I would NOT want to select the client but if I had:

Active
AN OTHER

then I would want it to select.

Cheers

 

[Naith]

You must be talking about two different fields. One field cannot logically have a value of "Active" and "MIFAJCO" for the same record.

If you are talking about two different fields, then:

not ({Field.One} in ["all","values","here"])
and {Field.Two} = "Active"

Naith

 

[lbass]

If it is one field and there can be multiple entries per client (and therefore multiple detail rows), you need to be careful using the "not" operator. In your example, let's say you want "active" and not ({table.field} in ["MIFAJCO","PWM"].

Client CLGNamesShort
001 Active
001 MIFAJCO
001 PWM

The first record would be selected using your criteria and the client would still appear. If you want to filter CLIENTS using these criteria, you should not use the criteria in the record select statement, but instead create a formula like {@criteria}:

if {CLGNames.Short} = "Active" then 1000 else
if {CLGNames.Short} in ["MIFAJCO","PWM"] then 1 else 0

Place this in the details section and suppress. Then go to report->edit selection criteria->group and enter:

sum({criteria},{table.clientID}) > 0 and
remainder(sum({@criteria},{table.clientID}),1000) = 0

This will select only those clients who are "Active" and who do not have "MIFAJCO" or "PWM" as a result in any row.

-LB

 

[Naith]

If the case was as LB's example, you could group by Client, and apply a Group Selection of

Count({CLGNames.Short},{Client}) = 1 and
{CLGNames.Short} = 'Active'

Naith

 

[lbass]

Naith,

I think the issue is (and I should have used several examples) that the rows could be like the following with the following results using the suggested formula:

Client 001 (Exclude)
001 Active
001 ABCDE
001 MIFAJCO
001 PWM

Client 002 (Include)
002 Active
002 FGHIJK
002 LMNOPQ

Client 003 (Exclude)
003 FGHIJK

Client 004 (Exclude)
004 FGHIJK
004 MIFAJCO

In other words, the client could have other records that would not exclude them in addition to "Active."

-LB

 

[Naith]

Aha! Excellent elucidation, LB.

 

[robmason10]

LB - you are correct in your deduction - sorry for the bad examples - and I like your solution...seesm like a good idea to me however 'Thus summery / running total fields could not be created'.

@Criteria:
if {CLGNames.Short} = "Active" then 1000 else
if {CLGNames.Short} in ["CLOSED", "GRP09", "GRP10", "GRP12", "GRP14", "GRP15", "GRP16", "GRP17", "GRP18", "GRP19", "GRP20", "GRP21", "GRP24", "GRP26", "GRP34", "IFMDFJJ", "IFMDFJU", "MIFAJCO", "MIFAJFD", "MIFAJPCO", "MIFAJPFD", "MIFASTMG", "MIFAUCO", "MIFAUFD", "PWM", "PWM (B4)"] then 1 else 0

Group selection:
sum({criteria},{clients.matter_no}) > 0 and
remainder(sum({@Criteria},{clients.matter_no}),1000) = 0

Any thoughts?

Thanks again

 

[lbass]

You have to group on {clients.matter_no}, if you haven't already. I'm guessing that's what the glitch is. Let me know...

-LB

 

[robmason10]

If I could buy you a beer I would! Have a little purple star instead!