In thinking about this, I probably didn't explain my problem clearly enough.
The overview is that I want to collect all members who contributed to 2 separate efforts. The efforts are delineated by different source codes. So source code #1 yields 160 records and source code #2 yields 100 records.
Re-reading your original message, I see that you described it
correctly the first time. The AND/OR logic is misunderstood so often I
think we all read it to be the way we expected it to be.
So, I'll want to collect both source codes and then only select those folks who have BOTH source codes in their payment records. The result should be less than 160, and most likely less than 100 since it is unlikely that everyone who has source code #2 also has source code #1.
You're right, the AND search (for BOTH source codes), will narrow
the results of your search.
So, while your suggestion does result in collecting both source codes separately, it doesn't get me only those people who have BOTH in their payment record. In essence, doing the additional request gets me all those records that satisfy source code #1 OR source code #2....but doesn't pull those records that satisfy source code #1 AND source code #2 (on a per member basis).
Another way of putting it is to conduct a find on a found set. That is, collect everyone with source code #1 (160 records) and then on those 160, find those who also have a source code #2 in their payment record....Any finds I do once finding those 160 seems to initiate a find from scratch....
First, note that your search needs to be within the names file.
No payment record will have both source codes attached. You're looking
for people who have multiple payments records, at least one with each
source code attached.
You'll need to search with a single find request (That's how
FileMaker does the AND logic search). That single request will need to
have a way to enter multiple source codes.
Here's a method to do that, if you are comfortable working in
FileMaker's Layout mode. It's based on a post from Jack Noll last
month. Thanks, Jack, I thought this was brilliant at the time and
filed it away.
1) MAKE SURE THAT YOU HAVE A GOOD BACKUP OF ALL OF YOUR EBASE FILES.
2) From the HOME screen, hit the Custom tab to get to Custom Screen #1.
3) Get into Layout Mode.
4) Place the field named "User Field 1" somewhere onto
the layout. (or some field that isn't currently used. Create a new one
if you like.)
5) Get back into Browse Mode.
6) Hit the "Find All" button so that you're working with all records in the names_.10x file.
7) Place your cursor into the "User Field 1" field.
If there's anything at all in the field, delete it so that the field
is totally blank.
- Select Scripts->FMPro Replace Command.
- Replace the "User Field 1" contents with ""
for all records in your database.
8) Go to the Payment Entry screen in the payments file.
9) Get into Layout Mode.
10) Place the field named "User Field 1" from the
relationship named "Names" somewhere onto the layout.
11) Hit the Output tab.
12) Hit the "Payment Query Screen" button.
13) In the Source Code field, enter one of the source codes
you're looking for. In the member status field, enter Current to make
sure you get only members.
14) Hit the Continue button at the far left of the screen to
perform the search. You will then be on the Payment Entry screen with
a found set of payments, all of which have one of the source
codes.
15) Get back into Browse Mode.
16) Place your cursor into "User Field 1".
17) Select Scripts->FMPro Replace Command.
18) Select "Replace with calculated result".
19) Enter the following calculation into the calculation dialog box
Names::User Field 1 & Source Code & ","
20) Hit the Replace button. This has copied the source code on
these payments into User Field 1 in the related names records
only.
Repeat steps 11-20 for each of the source codes you want to
search for.
22) Hit the HOME button.
23) Hit the Custom tab to get to Custom Screen #1.
24) Hit the Find button to get into Find Mode.
25) In "User Field 1", enter the source codes you want
to search for. Separate each of the source codes with a space;
the order is insignificant. For example, if you want to find all
people who had at least one payment with each source code on my test
files, you'd enter...
"M1B0200CME
M1A0401XXP"
...without the quotes. Hit the "Find" button on
the far left side of the screen to perform the search.
You should now have the correct found set.
Once you've done this entire sequence once and have User Field 1
on both layouts, you can repeat it anytime you wish repeating steps
11-25.
--
--
Dave Shaw H4 Consulting
tel: 206-954-7526 fax: 206-625-1338
------------------
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
---------------------------------------------------------------------
--
Dave Shaw H4 Consulting
tel: 206-954-7526 fax: 206-625-1338
