Formula for multiple records selection criteria in Crystal

[peaches9531]

Hello all. I am a newer user to Crystal Reports as my employer recently went to this platform from a SQL reporting program, so I apologize if I am missing a simple solution. I am trying to write a report that will search several fields and pull data from both fields and return a response if either one or both are true. I can get my reports to work if I use each code separately but when I try and combine them it stops working. I have also tried changing the status in Formula editor to default values for null to prevent the formula from breaking if a null value is returned.

({syinvolv.relship} = "Adult Offender" and
{lwccirc.ccode} = "CONFI" and
{lwmain.agency} = "AC" and
(currentdatetime - 1) <= {lwmain.ocurdt1})
or
({syinvolv.relship} = "Adult Offender" and
{wfstat.status} = "CONFIN" and
{lwmain.agency} = "AC" and
(currentdatetime - 1) <= {lwmain.ocurdt1})

I have also tried to write both of these as formulas and insert the formula into the record selection. Both of these return non-numerical data from other tables so I am not sure how to write an if, then, else statement. I have also tried using not is null with the formulas.

Thanks for any assistance.

 

[lbass]

I'm wondering about your joins. Can you please copy your SQL Query into the post? Just go to Database->Show SQL Query and copy that.

-LB

 

[peaches9531]

SELECT "lwmain"."agency", "wfmain"."rkeyval", "nmmain"."last", "nmmain"."first", "nmmain"."middle", "nmmain"."birthd", "nmmain"."suffix", "syinvolv"."relship", "tblaw"."desc", "lwoffs"."statute", "lwmain"."address", "lwmain"."locatn", "wfstat"."status", "wfstat"."date"
FROM {oj ((("admin"."wfstat" "wfstat" INNER JOIN "admin"."wfmain" "wfmain" ON "wfstat"."number"="wfmain"."number") INNER JOIN (("admin"."lwoffs" "lwoffs" INNER JOIN "admin"."lwmain" "lwmain" ON "lwoffs"."number"="lwmain"."number") RIGHT OUTER JOIN "admin"."tblaw" "tblaw" ON "lwoffs"."statute"="tblaw"."abbr") ON "wfmain"."rkeyval"="lwmain"."number") RIGHT OUTER JOIN "admin"."syinvolv" "syinvolv" ON "lwmain"."number"="syinvolv"."rec") RIGHT OUTER JOIN "admin"."nmmain" "nmmain" ON "syinvolv"."relrec"="nmmain"."number"}
WHERE "wfstat"."date">={ts '2021-02-17 06:40:31'} AND "syinvolv"."relship"='Adult Offender' AND "lwmain"."agency"='AC' AND "wfstat"."status"='CONFIN'
ORDER BY "nmmain"."last", "lwoffs"."statute", "wfmain"."rkeyval"

 

[lbass]

It looks to me like you need to reconsider your joins for one thing, keeping these things in mind:

1) a right outer join will return matching records from each of the two tables and also records from the right-hand table with no matches in the left hand table, e.g., your link between tblaw and lwoffs will return any matches and also any rows in tblaw without matches in lwoffs.

2) If you select on a field from a right (or left)-joined table, the join will in effect become an equal join, so in the case of tblaw and lwoffs, only matching records would result. You have a select on an syinvolv field, so that outer join will revert to an equal join between syinvolv and Lwmain.

One table that you want to select on, lwccirc, is not even in your table list for some reason.

I have NEVER had occasion to use a right outer join. Ordinarily there is a main table that contains all records and in your case you have three tables that are "main" tables so I'm unsure which should be your lead table. Then you would add other tables and if a table might not have a record, if would be left-joined to the main table. Any tables then linked to a table that is left-joined, would also have to be left joined.

If you get the joins wrong, you will not get the correct dataset, so I would start by troubleshooting your joins. I can't really direct you more specifically without knowing how the tables relate to each other.

-LB