a) "Everyone who has donated more than or equal to $200 to us within the last 3 years, minus those who have died, moved, or have been flagged as "do not solicit," organizations, businesses".
b) "Everyone who has donated less than $200 to us within the last 3 years, minus those who have died, moved, or have been flagged as "do not solicit," organizations, businesses".
What makes these searches tricky is that they're based on information in both the paymnts_.10x file and the names_.10x file. To make it even tougher, they're based on cumulative data in paymnts_.10x that is not being tracked in a specific field in the database (such as $YTD, which tracks total contributions and dues for each record in the names_.10x file for the current fiscal year).
There are two approaches I use when doing such a search. One is to create a summary export, then use that exported file to create labels, reports, etc. This requires a fair amount of Filemaker expertise. If you had that level of expertise, then you probably wouldn't be asking the question. The second approach is to create a new field in the names_.10x file and use it as a temporary "bucket" to hold information for searching and reporting. That's the approach we'll take here.
First, we'll need to do some "setup" work. These steps only need to be done once, *not* each time you do a search.
A1) Let's create the new field. From the HOME screen, select File->Define Fields. Create a new number field. If you're running single user, you can just name the field "_Number1". If you're running multi-user, then you should create one field for each user (eg. "_JackNumber1" and "_BarbNumber1"). This way, you're not clobbering each other's field.
A2) Next, get into Layout Mode and move to one of the 3 query screens. Place these fields onto any or all of the query screens. While you're on this screen, place the field named "Status" there as well (if that's the field you're using to track whether someone is deceased or has moved).
A3) Still in Layout Mode, move to one of the Custom Screens (let's say Custom #2) and place these fields onto that screen as well. Hit Ctrl-1 to get back to the HOME screen in Browse Mode.
A4) From the HOME screen, hit the "View Payments" button to get to the "View Payments" screen in the paymnts_.10x file.
A5) Hit the "Pmt Entry" button to get to the "Payment Entry" screen.
A6) Get into Layout Mode. Add the field(s) from step A1 to this screen (off to the side is fine). Note: Since you're now in the paymnts_.10x file, you won't find these fields in your field list. You'll need to change the field list so that it's drawing from the relationship named "Names" instead of from 'Current file ("paymnts_.10x")'.
A7) Hit Ctrl-1 to get back to the HOME screen in Browse Mode.
That's it for the setup. Now let's do the query....
We'll take the following approach for our query.
- First, we'll initialize the _Number1 field to zero for all records in names_.10x.
- Next, we'll find all donations that fall within our time frame.
- Then, we'll sort the found set of paymnts_.10x records by Record Number.
- Finally, we'll replace the _Number1 field in the names_.10x file with the dollar amount for that record in the found set of payments.
- Once we have this value plugged into the _Number1 field in the names_.10x file, we can search that field like any other field.
Here we go....
- To initialize the _Number1 field....
B1) From the HOME screen, select the Custom Screen that you used in step A1 above.
B2) Hit the "Find All" button.
B3) Place your cursor into your _Number1 field.
B4) Enter the number "0" into the field.
B5) Select Scripts->FMPro REPLACE Command.
B6) Replace all occurences of the _Number1 field with the number 0.
- To find all donations that fall within our time frame...NOTE: I assume that by "donation" you mean "Dues" or "Contribution" payments...
C1) From the HOME screen, hit the "Output" tab.
C2) Hit the "Payment Query Screen" button at the bottom-left of the Output Options screen.
C3) In the Post Date field, enter ">=08/27/98" (without the quotes).
C4) In the Payment For field, enter "Dues".
C5) Hit the "Add New Request" button.
C6) In the Post Date field, enter ">=08/27/98" (without the quotes).
C7) In the Payment For field, enter "Contribution".
C8) Hit the Continue button in the status area (left side) of the screen.
- Now we need to sort by Record Number...
D1) Select "Scripts->Sort". The "Sort Records" dialog box should appear.
D2) Hit the "Clear All" button so that we can build our sort from scratch.
D3) Find the field named "Record Number" in the left column and double-click it so that it appears in the right column.
D4) Hit the "Sort" button.
- Finally, we'll replace the _Number1 field in the names_.10x file with the dollar amount for that record in the found set of payments.
E1) You should still be on the "Payment Entry" screen, with the right found set of payments sorted by Record Number. Place your cursor into the Names::_Number1 field (you should have placed that field here in step A6 above).
E2) Select Scripts->FMPro REPLACE Command.
E3) Replace all occurences of the Names::_Number1 field with the following calculation:
GetSummary ( Summary Amount, Record Number)
At this point, the values in the _Number1 field in the names_.10x file represent the total of all donations for that particular names_.10x record for your given timeframe. This field can now be searched like any other field.
So, to satisfy your first query, do the following...
F1) From the HOME screen, hit the Query tab.
F2) Get to the query screen that contains the _Number1 field that you placed there in step A2 above.
F3) Enter ">=200" (without the quotes) into the _Number1 field.
F4) Hit the "Add New Request" button.
F5) Place an "N" in the Solicit field and click "Omit" under the rolodex".
F6) Hit the "Add New Request" button.
F7) Place "Died" in the Status field and click "Omit" under the rolodex".
F8) Hit the "Add New Request" button.
F9) Place "Moved" in the Status field and click "Omit" under the rolodex".
F10) Hit the "Add New Request" button.
F11) Place "Org" in the Record Type field and click "Omit" under the rolodex".
F12) Hit the "Add New Request" button.
F13) Place "Bus" in the Record Type field and click "Omit" under the rolodex".
F14) Hit the "Continue" button in the status area.
You should now have the found set you requested in query 1.
To get query 2, do steps F1 through F14, with the following change: In step F3, enter "<200" in the _Number1 field. This is assuming that you want everyone who has donated *something* but less than $200 in the past 3 years.
One of our ebase users, the Environmental League of Massachussetts, is having trouble conducting finds in the database along specific criteria. They are listed below.
Can ebase users on this list lend a hand to suggest ways that ELM can conduct these searches? Answer one or all. In your answer, be sure to include any assumptions about how data is stored in order for it to be found again.
Whey you reply, please include Jessica Champness and Jeremy Martin at ELM in your distribution. Their emails are included here. Thanks in advance.
Marshall
On 8/27/01 1:59 PM, "Jeremy Marin" <[EMAIL PROTECTED]> wrote:
- Marshall,
- That's great!!
- Here are some queries we run (or at least try to) on a regular basis:
- 1. Everyone who has donated more than or equal to $200 to us within the last 3 years, minus those who have died, moved, or have been flagged as "do not solicit," organizations, businesses.
- 2. Everyone who has donated less than $200 to us within the last 3 years, minus those who have died, moved, or have been flagged as "do not solicit," organizations, businesses.
- 3. Everyone who has given to us in the past, but not since 11/30/2000, minus deceased, moved, do not solicit, organizations, businesses.
- 4. Everyone who is "event responsive."
- 5. Everyone who has given more than $200 more than once.
- 6. Everyone who has donated within the last three years, all marked as "free," all businesses, organizations, and foundations.
---------------------------------------------------------------------
Jack Noll - ebase consultant and wildlife advocate
Check out a *great* program at
www.beardogs.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
---------------------------------------------------------------------
