Crystal Reports - Boolean Formula Help

[davismar]

I need some assistance in a boolean formula.
Here is the info:

1. tblAccounts.AccountType
I have my select expert formula showing only:
{tblAccounts.AccountType} in ["CSA Customer", "CSA/Data"]

2. tblAssignedAccountGroups.AccountGroup [String =50]
I only want account groups that contain:
"Aspire"
"UX5000"
"SV8100"
"ISeries"
"Zultys"
but DO NOT contain "TopGun" or "TopGunRemoteSite"

Thanks in advance for the help!

 

[briangriffin]

I think this is what you're looking for:

{tblAccounts.AccountType} in ["CSA Customer", "CSA/Data"]

and

tblAssignedAccountGroups.AccountGroup like ["*Aspire*","*UX5000*","*SV81000*","*ISeries*","*Zultys*"]

and

not (tblAssignedAccountGroups.AccountGroup like "*TopGun*")


Any record that has TopGunRemoteSite also has TopGun, so that is probably not needed. So if the account group is "SomethingAspire" it would be included, but "SomethingAspireTopGun" would not.

 

[davismar]

{tblAccounts.AccountType} in ["CSA Customer", "CSA/Data"]and
{tblAssignedAccountGroups.AccountGroup} like ["*Aspire*","*UX5000*","*SV8100*","*ISeries*","*Zultys*"]
and not {tblAssignedAccountGroups.AccountGroup} like ["*TopGun*"]

Thanks Brian -

So this is what I came up with in formula editor - and now I get the following message:

A boolean is required here on the last line where it begins:
and not {tblAssignedAccountGroups.AccountGroup} and {tblAssignedAccountGroups.AccountGroup} is highlighted.

 

[pmax9999]

You have missed the brackets. Amend the last line to look to:

and not({tblAssignedAccountGroups.AccountGroup} like ["*TopGun*"])



Cheers
Pete

 

[davismar]

Appeciate the help. That worked as far as the error message. But now the data still doesn't appear to be correct.

For example:
I have a customer called "Gabriel"
They are in the following Account Groups:
Aspire
TopGun

After adding the above formula, Gabriel Group appears on the report, but they should be "excluded" because they are a "TopGun".

Any ideas?

 

[pmax9999]

So what you are saying is that a customer can be in more than 1 group, and that they should be excluded if any of the groups contain the word text "TopGun", regardless of what other groups they may be in.

On that basis, and assuming you have the report grouped on customer, I would create a formula like this:

// {@TopGunTest}
If {tblAssignedAccountGroups.AccountGroup} like ["*TopGun*"]
Then 1
Else 0

Then, remove the "TopGun" test from the selection formula, and in the Group Selection (Report => Selection Formulas => Group), add:

Maximum({@TopGunTest}, {table.customer}) = 0

This will exclude any customer who has a "group" record containing the text "TopGun"


Cheers
Pete

 

[davismar]

So here is my Group Selection formula:

CODE --> {tblAssignedAccountGroups.AccountGroup}
Maximum({@TopGunTest},{tblAccounts.AccountName}) = 0

OR

Error Message - the remaining text does not appear to be part of the formula.

 

[pmax9999]

You need to delete everything after the end of the line of code I gave you, ie, the "OR" needs to be removed.