Checking multiple formulas

[Patrick69]

I'm trying to make an existing report more "readable". It is a validation report that checks 62 different conditions (62 different formula fields) and will display whatever doesnt pass. Currently it will show Every record, even when all conditions are me.

I've tried to make a formula like:

({@ENTER-DEPT-TIME} = "" AND
{@LEAVE-DEPT-TIME} = "" AND
{@LEAVE-PROC-TIME} = "" AND
{@ENTER-PROC-TIME} = "" AND
{@ADMIT-RECOVERY-TIME} = "" AND
{@CHECKS ALL 8 TIME FIELDS} = "" AND
.
.
.


And have it print only the records that fails one of these conditions (field is false). While it "seems to work" I get discrepencies between the original report.

I am using Crystal reports 7, is there a cleaner way for me to do this?

 

[synapsevampire]

Hard to say since you didn't demonstrate what's in the referenced formulas, nor describe what "even when all conditions are me" means.

Then you reference true, when the formulas aren't true/false, rather you look for ""

If you wish to eliminate rows from a report, use the Report->Edit Selection Formula->Record and place something like:

(
{table.field} <> <condition
)
and
(
{table.field} <> <condition
)
etc.

-k

 

[Patrick69]

Sorry I'll give more detail.

The formulas all check various potential problems (end time before stop time, wrong resource assigned, ect), but they will all return "" if the condition is not met.

I created the formula field that should check if all the formula's (@NO ERRORS) are "" (so true/false return) and then in selection expert I had it only print records where @NO ERRORS = false (so in one of the formulas an error was detected.

This is a report that was created by my predessesor. I am really not liking having 60+ seperate formula fields, I am about 5 minutes away from re-writing the report and doing the error checking in single formula field, I just worry about the limitatins in Crystal 7 and its ability to handle a large formula.

 

[synapsevampire]

The formulas don't check problems, they reference data, and posting the data and the data types is what should be done, not a users description of the experience.

Your approach will likely not work well as the query probably won't be passed to the database. Check this by using the Database->Show SQL Query, whatever is in the record selection should be reflected in the query.

I suggest that you rewrite it anyway, and again, if you want technical assistance, state what is in the formulas.

Note that successful posts tend to always include basic technical information:

Crystal version (1st question asked by any tech support person)
Database/connectivity used
Example data
Expected output

Tends to clarify the requirements.

-k

 

[Patrick69]

Sorry I am just not explaining properly (monday)

Crystal version 7
Database used BTRIEVE
Example data (one of the 62 formulas):
----------------
IF ({Post Anes Type.ANES START TIME} > {Post Case.START TIME}) AND ({Post Anes Type.ANES START DATE} = {Post Case.START DATE})
THEN "INCISION BEFORE ANESTHESIA"
ELSE "";
-----------------
62 comparisons each in its own formula field like this one all checking data from various fields and tables. All will return "" if the condition is not met.

I created a formula is supposed to check that all of these formula fields = "" (true/false result possible). My selection will only display the records that have failed one of the conditions in the 62 formula fields.

In the end I am just looking if there is a better way syntax wise to do :

({@ENTER-DEPT-TIME} = "" AND
{@LEAVE-DEPT-TIME} = "" AND
{@LEAVE-PROC-TIME} = "" AND
{@ENTER-PROC-TIME} = "" AND
{@ADMIT-RECOVERY-TIME} = "" AND
{@CHECKS ALL 8 TIME FIELDS} = "" AND
.
.
)

thanks for your quick responses.

 

[synapsevampire]

Yeah, that was my point, doing so in this fashion probably won't pass any criteria to the database, rather all rows will be returned to Crystal, and then the processing will begin.

Place your criteria in the Report->Edit Selection Formula->Record

Anyway, if your looking to learn if ANY of the conditions return "", then use OR, not AND. AND states that all of them must return ""

Again, I'd move all of the criteria to the record selection, and use something like:

(
{Post Anes Type.ANES START TIME} > {Post Case.START TIME}
AND
Post Anes Type.ANES START DATE} = {Post Case.START DATE}
)
OR
(
<next formula>
)
OR
etc...

You should see a boost in performance as a result.

-k

 

[Patrick69]

Thanks, I'm gonna give it a try.

I just worry about the limitations in crystal 7, wasted 2 hours only to find it didn't support loops.

And will it let me write a 200+ line formula
? Time to find out : )

 

[synapsevampire]

Yeah, it should allow for 200 lines, I've been close to that back in the day...

btw, I think that CR 7 supports loops, here's an example:

http://support.businessobjects.com/communityCS/TechnicalPapers/scr_displayparam.pdf

However a loop won't really help you here.

-k

 

[Patrick69]

No, I tried every conceivable syntax for loops and to no avail.

One of the users here confirmed that it was not available till 8x

http://www.tek-tips.com/viewthread.cfm?qid=1158515&page=5

Which kind of boggle the mind, but I'm trapped with this program for at least the short term so I'll have to make do.