CODEWITHSUNDEEP
mysqldatabaseif-statementcrystal-reportsrecords
NWcis
NWcis

Reputation: 59

Crystal Report Record Filtering 3 different ways per record CR10

I am using a command query in crystal reports to pull the information I need. The query results look like so:

Amt      Date      Acc     Unit     Setup     Owner?     Owner
5.00  2016-03-01    1       A        A          Y          B
5.00  2016-03-01    2       A        B          N          null
5.00  2016-03-01    3       A        null       null       null

My flow of logic is: If the Owner? = "Y" then get the owner, else if the owner? = "N" or isnull, then get the setup, else if the setup is null, then get the unit.

In record selection I have tried an if-else-if statement to help filter each record, looking like this to just get the "B" units.:

If({Command.Owner? = "Y") Then {Command.Owner = "B")
Else if ({Command.owner? = "N" or isnull({Command.owner}) Then
     {Command.setup = "B")
Else if ({Command.owner? = "N" or isnull({Command.owner}) AND {command.setup} <> "B" Then
 {Command.unit} = "B"

Basically, what this is doing is checking if the Owner? is Y and then getting the records where the Owner is B, if that is N or null, then get the records where the setup is B, if both those are untrue then get the units that are B.

But it doesn't get every record. I want to be able to get all the records that are with this. Like

Amt      Date      Acc     Unit     Setup     Owner?     Owner
5.00  2016-03-01    1       A        A          Y          B
5.00  2016-03-01    2       A        B          N          null
5.00  2016-03-01    3       B        null       null       null

Would all be valid returns, but the record selection only grabs records that match the first criteria and then ignores the rest.

I have tried the same if-else-if with AND statements instead of Else, but that returns nothing. I have also done the same with OR, which returns nothing.

Is there a way to search every record, and if it matches one of those criteria then pull it and display it?

Upvotes: 0

Views: 82

Answers (1)

Cyndi Baker
Cyndi Baker

Reputation: 679

Try this (edited for null sequencing)?

If (isnull({Command.owner}) or {Command.owner?} = "N") and not isnull({Command.setup} and {Command.Owner?} <> "Y") then {Command.setup} = "B"
Else if {Command.Owner?} = "Y" Then {Command.owner} = "B"
Else {Command.unit} = "B"

Upvotes: 1

Related Questions