Weird Search - Multiple values in field ... AHHH

[chrisl]

Howdy Group:

Hoping someone can assist me on this. Here is what I need to do:

I have a database with about 700 fields and i am trying to archive using search from about 6 of the fields in question

1. Date Entered (year)

2. Status Field

3. Date of an event

4. Date of a 2nd Event

5. Date of a 3rd Event

6. Recv Category

Here is the scenario of what I need to do:

I need to find all recs that have a Recv of "0"

that also have a status other than Hold, Pending or Challenge(from about 10 other status' entered) that also have null values in the three date field mentioned above.

From that point this found set will be broken down by the first date field mentioned above (year).

These found records will idicate a years worth of records that can be archived.

I have tried using requests in this and when the data comes back from the search, i get very odd sets of numbers. The results should be something like:

A record with no date in the three event dates + recv as "0" and results field with anything entered by the users other than Challenge, Pending and Hold.

Please assist. Nothing seems to be correct.

NietWolf

[Gogargirl]

My brain gets so addled trying to work out multiple finds that I usually try doing it differently. Here's one way. It's not very elegant and would be a pain to modify if your search criteria change frequently, but it does work! Create a text calculation field:

If( IsEmpty(date1 ) and IsEmpty(date2) and IsEmpty(date3) and Recv = 0 and not(status = "challenge") and not(status = "hold") and not(status = "pending"),"Yes","No")

Then all you need to do is search for the Yeses. I'm not sure what you intended to do with the Date_Entered field. You could either add a year or range of years to the search or leave it and sort your results by year.

[chrisl]

Alison:

Sorry to be a pest about this, but are yousuggesting to add this in as a script? I am not as up on some of this as i should be. Any further assistance would be geat!

Nitewolf

[kenneth2k1]

Hello:

What Allison is explaining is defining a field as a calculation to search. Do this by going to File/Define Fields. Create a field (any name you want) and specify it as a calculation field. Once you save this, FM should open the Specify Calculation window. Input the calculation as Allison has described. From looking at it, it looks like it evaluates the fields, and returns either "yes" or "no" depending on whether it meets the criteria. Then she explains that you search just this calculation field for either "yes" or "no."

She is right, if you are going to change your find requests often you will have to change the calculation of this field. I don't think it would be that much of a hassle. However, the alternative would be to script, which could take just as long to change.

Just in case you're wondering, you can manually perform multiple finds on one field like this:

Enter Find Mode. Enter your first search criteria in the fields you want. Then, select Requests/Add New Request from the menu bar. You can now enter additional information in the same field. I believe you can do this as many times as you need to.

Hope this helps clarify.

Ken

[chrisl]

Ken:

Thanks for the insight. Going to try this one later tonight. Will let you know if all works well.

thx to everyone for this.

Nitewolf

[Gogargirl]

Thanks Ken for explaining what I meant whilst I was putting the kids to bed/cooking dinner etc.! You're right about the Add New Request feature, but working out the search criteria for multiple requests can sometimes get really complicated. This one isn't too bad, and the requests would be:

1st request, "=" in date1, date2 and date3 and "0" in Recv

2nd request, "pending" in status, Omit box checked

3rd request, "challenge" in status, Omit box checked

4th request, "hold" in status, Omit box checked

Nitewolf, if you want to use a script for this (maybe because you want to print, automate the export or whatever) it's going to be:

Set error capture On

Enter find Mode (don't check 'pause' or 'restore requests')

Insert Text(date1, "=")

Insert Text(date2, "=")

Insert Text(date3, "=")

Insert Text(Recv, "0")

NewRecord/Request

Insert Text(status,"challenge")

Omit Record

NewRecord/Request

Insert Text(status,"hold")

Omit Record

New Record/Request

Insert Text(status,"pending")

Omit Record

Perform find (don't check 'restore requests')

If (Status(CurrentError) = 401

Show Message ("I've got nothing to archive. Time for a beer!")

Else

- Another message or actions here -

End If

Set Error Capture (Off)

The 401 error message happens when FM finds no records that match your criteria. HTH!

Alison