Solved

# FIND logical order for multiple AND/OR criteria

Posted on 2011-10-17
394 Views
I need to query my db for several items. I regularly use Constrain, Extend, and Omit in isolated cases, but I need help with the order and syntax to combine AND/OR for complex searches.
Query:
Procedure A or B [ProcA OR ProcB] (from two different fields that either have a date or are NULL)
AND Location [MyTown] (from a checkbox list of locations)
AND Time 1/1/2010...12/31/2010 (from a date field)
AND [1 OR 3] from {1,2,3,4,5} (from a radio button field where I really want NOT [2 OR 4 OR 5]).

Thanks,

Mark
0
Question by:MarkJulie

LVL 24

Expert Comment

Hi Mark - It sounds like this will involve several similar Find requests or Find followed by a Constrain. The general rule with the order of Find Requests, is to always Find what you want first and then use Omit or Constrain afterwards. (I use Extend sometimes, but very rarely compared to Omit or Constrain). There are probably many ways to get what you want but here's one possible order based on what you've described:

First Find request (FIND):

Procedure A [ProcA ] (from two different fields that either have a date or are NULL)
AND Location [MyTown] (from a checkbox list of locations)
AND Time 1/1/2010...12/31/2010 (from a date field)

Second Find request (FIND):

Procedure B [ProcB ] (from two different fields that either have a date or are NULL)
AND Location [MyTown] (from a checkbox list of locations)
AND Time 1/1/2010...12/31/2010 (from a date field)

Third Find request (OMIT):

[2] from {1,2,3,4,5} (from a radio button field

Fourth Find request (OMIT):

[4] from {1,2,3,4,5} (from a radio button field

Fifith Find request (OMIT):

[5] from {1,2,3,4,5} (from a radio button field

Requests 3-5 could also be done as a Constrain after the initial find, either Omitting 2, 4, and 5 or Finding the two you want, 1 and 3.
0

Author Comment

Willmcn,

I tried the steps you suggested, but I am misusing the results of the two Find requests. I get 67 records when I set criteria for the first Find, Duplicate Request and change from ProcA=* to ProcB=* leaving the date range 1/1/2010...12/31/2010 and Location = MyTown in both requests.

After setting criteria for each Request, I run Find and get 67 records.

At this point, I run Modify Last Find (emptying Date, Procedure, and Location criteria) changing to Omit=2 from list {1,2,3,4,5} and Constrain Found Set.

Instead of getting <=67 records, I get 5009.

What do I need to do differently?

For further clarification, does adding additional requests accumulate into one large OR Find?

What is the difference between Add New Request and Extend Found Set for my situation?

This may take several rounds. Apologies for being offline several days of the week and slow response on my end.
0

LVL 24

Expert Comment

Don't use Modify Last Find, but do Constrain Found Set [] including the two Omit Find Requests. this keeps your find within the found set. (I have to go out for a bit but will respond more fully when I return).
0

Author Comment

0

LVL 24

Accepted Solution

> What do I need to do differently?

As noted previously, do not use "Modify Last Find" but instead use a single "Constrain Found Set [ Restore ]" step that contains the three Omit Records Requests as noted above.

> For further clarification, does adding additional requests accumulate into one large OR Find?

In general, doing multiple requests is effectively one large OR Find. It is also critical that if you do large Find with multiple requests, that any "Omit Records" requests come AFTER all the "Find Records" requests. I'm not sure why the particular steps you got led to more records if the last script step was a Constrain Found Set but leaving out the Modify Last Find and simply storing the Omit Records Requests with the Constrain Found Set step should give you what you are looking for.

> What is the difference between Add New Request and Extend Found Set for my situation?

In this situation, I'm not sure there is any difference between the two. The Extend Found set simply lets you add the results of a new Find (which can include many Find Requests) to the existing Found Set. If in your initial Find you can include those Find Requests (presumably before any omits) then it should have a similar effect.

The difference might be if you had a group of requests that included both Find and Omit Records that you performed initially, but then you wanted to Extend that found set with a search criteria and did NOT want to have any Omits applied to it. In that instance, if the Omit Records requests in the first Find would have affected those additional records, using a second script step to Extend the Found set would be correct.

One thing to note: I've been assuming that you are scripting this Find since it is multiple steps. That's why I've made reference to the "Constrain Found Set [ Restore ]" script step. If you are simply performing this manually, then you should do the following:

Perform the initial two Request Find as you described by creating the first Find Request and then duplicating it
Go back into Find Mode instead of using Modify Last Find
Create the three Omit Records Find Requests
Perform a Constrain Found Set
0

Author Closing Comment

Thanks for the additional teaching to save noob questions in the future for me and others.
0

## Featured Post

### Suggested Solutions

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
how to add IIS SMTP to handle application/Scanner relays into office 365.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…