Eric,
Good question, and one every ebase user NEEDS to understand as much as
possible (although admittedly most database work can be done with simple
queries). Finding is probably the most important aspect of databases
and one that confuses a lot of people. While I don't have any answers
that will make the logic work for you, I'll take this opportunity to
throw in my experience/understanding with Filemaker Finds in the hopes
that it spurs on further discussion. This topic is too important to
pass up. I just hope I don't thoroughly confuse anyone.
Queries that are entered into separate screens are looked at this way:
find all "screens" (records, actually) that look either like the first
screen OR like the second screen. Queries that include all the search
criteria on one screen find records that satisfy all the criteria, an
AND query; that is, find records that satisfy x criteria AND y criteria
together. But beware of syntax. If you want to find people who live in
zip code 03301 AND 03101, you need to enter them as a logical OR. This
is because what you're really doing is finding records that have zip
codes of either 03301 OR 03101. Follow me?
Put another way, logical AND requests narrow the found set by making the
search criteria increasingly strict. The more characteristics you
include in the single query, the fewer results you will get (fewer and
fewer people will satisfy all the criteria). Logical OR requests expand
the found set. That is, find records that satisfy more and more
characteristics.
Omit changes the logic just a little. Filemaker handles multiple finds
in the order they are entered. That is, it first executes the first
request, then executes the second, etc. But the Omit option only
operates on the records found thus far in the query. If you throw an
Omit in the middle of the query, it will operate only on the found set
that precedes the omit step, not on the criteria that come after the
Omit.
For example, I have people in my database in Concord and Manchester,
NH. Some of those records in both cities are marked "y" for future
solicitation in the Contact Flag section of the Data Entry screen. If I
query for records as follows: Concord <<new find request>> omit "y"
<<new find request>> Manchester, I get a found set of those in Concord
not marked "y" and all records in Manchester, whether marked "y" or
not. If I query for records in this way: Concord <<new find request>>
Manchester <<new find request>> Omit "y", I get records in both cities
not marked "y". If I want records in Manchester not marked "y" and
Concord marked "y", I enter it as Manchester <<new find request>> Omit
"y" <<new find request>> Concord AND "y", where the second step has both
Concord and "y" entered on one find request.
In other words, Omit acts like a NOT statement for all find requests
that precede it.
Want even more confusion? This scenario came up in the list 6 months
ago. Let's say you want to find someone who has given to an annual
appeal for the last three years. Ebase allows you to track these
contributions using source codes, so the easy solution is to find people
whose payment records contain the three source codes used for each of
those appeals. Easy enough, right?
Well, how do you find records that satisfy multiple different criteria
in the same field? Filemaker doesn't allow this directly, because you
can't add two different criteria into one field on the same logical AND
request. You could find for one of the source codes, somehow mark those
records, find for another source code and mark those records in another
way, then find for both marks AND the third source code. Sound
cumbersome? It is.
But... it isn't necessarily more cumbersome than other approaches. In
one sense, the Filemaker approach is very visual, and so long as you
look at it that way, it can make sense.
OK, enough for now. I'm curious how others will respond. I'm also very
curious about other difficult queries and how people have solved them.
Carl
Eric Johnson wrote:
>
> Ok, FMPro/ebase users ...
>
> If I want to find all the people who have addresses in Denver or Boulder in
> ebase, I start a FIND, put Denver in the city field, create a new request,
> and put Boulder in the city field. When I click FIND, they all come up just
> fine. In this case, the requests are related by the logical operator
> 'OR' -- that is, anyone who lives in Denver OR Boulder will be in the found
> set. Pseudo-code for these criteria in a query some typical database might
> look like:
>
> city="Denver" OR city="Boulder"
>
> (no, this isn't FMPro scripting ... just a visual way for me to understand
> the query)
>
> HOWEVER,
>
> If I want to find all of the people in Denver who don't live in zip 80202, I
> do two requests again. Namely, I start a FIND, put Denver in the city
> field, get a new request, put 80202 in the zip field, click OMIT, and
> execute the query by clicking FIND. Pseudo-code for these criteria might
> look like:
>
> city="Denver" AND zip<>"80202"
>
> In this case, the two requests are related by the logical operator 'AND' ...
>
> Does anyone else find this aspect of FMPro a little confusing? When do
> multiple requests relate to one another by AND, and when do they relate to
> one another by OR?? Is it solely a matter of whether OMIT is checked?
>
> (Here's wishing FMPro could speak the more standardized Structured Query
> Language [SQL] common to so many other database packages ...)
>
> -- Eric Johnson
> Colorado Environmental Coalition
>
> ---------------
> Eric S. Johnson
> Colorado Environmental Coalition
> 1536 Wynkoop #5C
> Denver, CO 80202
> 303-534-7066
>
> VISIT CEC ON THE www AT http://www.ourcolorado.org
>
> ------------------
> Reminder to each recipient: To change your list account preferences, go to
> http://email.sparklist.com/scripts/lyris.pl?enter=support and enter the email
>address you used to subscribe to the ebase support list:: [EMAIL PROTECTED]
>
> To unsubscribe send a blank email to [EMAIL PROTECTED]
> ---------------------------------------------------------------------
> ebase - Relationship Management for Nonprofits, http://www.ebase.org
> ---------------------------------------------------------------------
--
Carl Paulsen
New Hampshire Rivers Council
54 Portsmouth Street
Concord, NH 03301
603-228-6472
603-228-0423 Fax
[EMAIL PROTECTED]
------------------
Reminder to each recipient: To change your list account preferences, go to
http://email.sparklist.com/scripts/lyris.pl?enter=support and enter the email address
you used to subscribe to the ebase support list:: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
---------------------------------------------------------------------
ebase - Relationship Management for Nonprofits, http://www.ebase.org
---------------------------------------------------------------------
