Finding records where a field *doesn't* exist

[itkyle]

Here are the details:

The dept. I'm working with processes claims (data stored in table {CLAIMS}). All open claims should have an open diary attached to them (stored in table {FILEFOLD}). The FILEFOLD table allows and stores different types of entries; D for Diary, L for Letter, N for Note, F for Form. I have the report laid out; my problem is finding the claims that lack an open diary without returning all of the other codes as well (e.g., if I select claims where FF_TYP<>&quot;D&quot;, of course, I'll get all of the other FILEFOLD types). If I say FF_TYP=&quot;D&quot; and FF_STATUS<>&quot;O&quot; (Open), it won't return claims where there never was a diary.

Hope I've explained this clearly enough; if not, I'll be happy to provide any information you gurus may need. :0)

Thanks.

Kyle

 

[jdemmi]

You said you want records that LACK AN OPEN DIARY, so am I correct in that you want to retrieve records in which FF_STATUS does not equal &quot;O&quot; and FF_TYP equals &quot;D&quot;?

 

[dgillz]

Use the IsNull() function to determine records that exist in one file but do not exist in another. Put a record selection formula of IsNull({Filefold.diary}) in your report and you will get these records only.

Let me know if you have any questions. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[itkyle]

jdemmi:
I can't use FF_TYP=&quot;D&quot; and FF_STATUS<>&quot;O&quot;, because I need to find records where there has been no diary created; therefore, these will not have &quot;D&quot; in the FF_TYP field.

dgillz:
I can't use the IsNull function, because FILEFOLD.DIARY is not a field; the field is FF_TYP, which can consist of 4 different entries. If there's a diary, the record shows &quot;D&quot;, if a note, &quot;N&quot;, if a letter, &quot;L&quot;, a form, &quot;F&quot;.

Thanks for the suggestions. Any other ideas?

Kyle

 

[dgillz]

Ok, create a formula field. If {FF_TYP}=&quot;D&quot; then 1 else 0. Then summarize the value of this field by ? Claim, or whatever it needs to be summarized by.

Then in your group selection formula, pick only those groups that do not contain a &quot;D&quot; by entering the formula:

Sum({@formulafield},{claim})=0 Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com