Storing data from another table
I'm using FileMaker Pro 8 on an iMac running OS X Version 10.5.8
We're a non-profit and we're linking several donations (in "Donations") to one donor (in "Master')
I've had some experience on relational databases, but not to the extent that I've defined Filemaker relationships. I have a little experience getting data from one table into my "master" list. We have formerly manually given our donors a "renewal level" to ask for higher amounts next time, but as a person who has used data bases and mailmerge, that seems silly to me. I've copied and modified a case statement to automatically tie the renewal level to the donor's most recent donation. However, I've just realized that it's not always the last donation I want to use. It is the last renewal donation. (Like most nonprofits, we're trying to squeeze out every dollar we can from our caring, loyal donors. :-). Plus some people have specified our organization to receive memorial donations, sometime donors give in honor of someone they admire for the work they've done in our program area, etc.
Our donations are coded to indicate whether they are renewal donations or additional donations. The Donations data base has the most recent donation first, whether through sorting or that's just the way Filemaker works, I don't know.
So I have been able to pull the most recent donation into the donor's Master record and I'm fine with seeing that. But now I want the Master to contain the data for the most recent renewal as well.
Using FileMaker help, I've been tryng to figure this out, and I think maybe I need to do a lookup with an if function in it or someting like that, but my stabbing around in the dark like this would take way, way longer than I have.
So, in a nutshell, I want a "RenewalDonation" in my Master table which will link to the most recent "DonationAmount" in my Donations table for which the "Type" starts with "Dues" (We have two "Types" for renewal donations -- "Dues:new" and "Dues:renew")
Thanks for your help!