Report Process FIND issue

Jhstephenson

I have a process that prepares a query for a report. My report works fine with the following:

FIND CaseMaster WHERE CaseMaster.DateFiled>=PromptData.DateBeginning AND CaseMaster.DateFiled<=PromptData.DateEnding ORDER BY CaseMaster.CaseTypeDescription,CaseMaster.DateFiled IN BATCHES OF 100000
DISPLAY DOCUMENT 'Cases Filed By Type - Detail'

PromptData is a BO I use to hold parameters for report.

I want to add another criteria to the FIND operation that will check to see if I have asked for a particular Case Type; if I have only select that Case Type, otherwise give me all case types, just like it does above.

So, I added to the FIND the following:

FIND CaseMaster WHERE (CaseMaster.DateFiled>=PromptData.DateBeginning AND CaseMaster.DateFiled<=PromptData.DateEnding AND (PromptData.CaseType IS UNDEFINED OR CaseMaster.CaseType=PromptData.CaseType) ORDER BY CaseMaster.CaseTypeDescription,CaseMaster.DateFiled IN BATCHES OF 100000
DISPLAY DOCUMENT 'Cases Filed By Type - Detail'

Shouldn't that check to see if the date range criteria is met and then look to see if one of the CaseType criteria is met?

A side issue: I really don't like having to specify IN BATCHES OF 100000. But, when I specify IN BATCHES OF 1, or just leave 'IN BATCHES OF...' off all together, I get nothing. What am I doing wrong there?

Jim

Jaymer

your standard and/or logic is wrong

D1 = the 1st date test
D2 = the 2nd

The AND are all going to happen 1st, then the lonely OR at the END

you need (D1 and D2) AND (all or one)

sorry, have to run out.

ok, back.
i missed a Paren in your example - but you have a syntax error - mismatched Parens

PointsWell

FIND CaseMaster WHERE (CaseMaster.DateFiled>=PromptData.DateBeginning AND CaseMaster.DateFiled<=PromptData.DateEnding AND (PromptData.CaseType IS UNDEFINED OR CaseMaster.CaseType=PromptData.CaseType) ORDER BY CaseMaster.CaseTypeDescription,CaseMaster.DateFiled IN BATCHES OF 100000 
DISPLAY DOCUMENT 'Cases Filed By Type - Detail'

From a quick eyeball of your second query it looks like you have a typo in the sub clause

(PromptData.CaseType IS UNDEFINED OR CaseMaster.CaseType=PromptData.CaseType)

PromptData.CaseType IS UNDEFINED is unrelated on its own to CaseMaster, I'm assuming is should be CaseMaster.CaseType IS UNDEFINED

And as Jaymer said you've got a missing parenthesis before the ORDER BY.

Also, do all of your DateFiled fields have a value within them - I have issues with searching on NULL date values in my data and so have set an active attribute based on whether or not the Date is NULL (mine are ActiveFrom and ActiveTo dates - from has to be completed, To may be completed).