Find within a ind

[The Mad Jammer]

Here's a fun one. I have a database of cases that are referred for action. One of the fields is text field with the names of the persons that are handling the case associted to a value list. There is another calculated text field that contians the values OPEN, CLOSED, DUE, LATE or the field is empty. The values are established by some mathematics based on dates and elapsed time.

My customer wants to see reports for each individual that she can give to the individual. Basically its a listing each status code for each person. Hence the dropdown for the name and the status. This was easy to accomplish.

But one that isn't so easy is to get a report for an individual of all the records that do not have CLOSED in the status field. I have tried this approch.

Perform a find on all the CLOSED status records, show the omitted records, then perform another find on this group using the name as the find parameter.

This unfortunately isn't working to my expectation, meaning that I'm getting incorrect results. Here's the script

GO TO LAYOUT[X]

Enter Browse Mode[]

Show All Records

Enter Find Mode[]

Insert Calculated Result [c_Status_Code; "CLOSED"]

Perform Find[]

Show Omitted

If [not IsEmpty(g_Refer_To_Name)]

Set Field ["Refer_To", g_Refer_To_Name]

Perform Find[]

End If

The second find is nested in the if statment in case a name was not chosen. What I get is the entire set of records with the CLOSED Status instead of the converse.

The g_ fields are globals that I use to set up the criteria for name and status and each have a value list associated with them.

How can I construct a find procedure that will return a set of records by name and not CLOSED status?

Thanks

The Mad Jammer.

[BobWeaver]

I would create a calculated field called "NotClosed" with this formula:

not Position(c_Status_Code,"Closed",1,1)

Then search this field for a value of 1, and your name field for the person's name.

[Reed]

Another way to do this is to make two find requests in your perform find:

The first request has the persons name

The second request has CLOSED in the status field with the omit box checked.

This will return all records with that person's name except those that are marked closed.

Dana

[The Mad Jammer]

Thanks for the info.

Bob, I like your idea. I'll give that a try.

Dana's idea is fine except that I'm scripting the find.

Thanks again.

The Mad Jammer

[The Mad Jammer]

OOPS,

Bob, the only problem I encountered is that the c_Status_Code can be blank, meaning that there is a grace period before the item is flagged as DUE. As a result, those items have a blank in the new calculated field as well. I suppose I should simply set the c_Status_Code to some initial value at the outset which will force the 1 in the new calculated field.

The Mad Jammer

[-Queue-]

You can script it dynamically, also.

Enter Find Mode [ ]

Set Field [Refer_To, g_Refer_To_Name]

New Record/Request

Set Field [c_Status_Code, "closed"]

Omit Record

Perform Find [ ]

[Reed]

You can still script the find the way I described.... any perform find script step can have any number of find requests.

[-Queue-]

Negative. This is dynamic find criteria. If the name were constant, then it would work.

[The Mad Jammer]

Thanks --Queue-- and Reed,

In the end I implemented --Queue--'s script. This got 'em all.

Thanks again.

The Mad Jammer