?
Solved

FIND logical order for multiple AND/OR criteria

Posted on 2011-10-17
6
Medium Priority
?
430 Views
Last Modified: 2012-06-27
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
Comment
Question by:MarkJulie
  • 3
  • 3
6 Comments
 
LVL 25

Expert Comment

by:Will Loving
ID: 36985573
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

by:MarkJulie
ID: 36995508
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 25

Expert Comment

by:Will Loving
ID: 36995775
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:MarkJulie
ID: 37002876
I got a partial solution, but I await your further comments.
0
 
LVL 25

Accepted Solution

by:
Will Loving earned 2000 total points
ID: 37003556
> 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

by:MarkJulie
ID: 37004457
Thanks for the additional teaching to save noob questions in the future for me and others.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question