5 Replies Latest reply on Nov 17, 2009 5:52 AM by ninja

    Storing data from another table



      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!

        • 1. Re: Storing data from another table

          Howdy SarahC,

          Welcome to the forum.


          One way to approach this is using a filtering relationship.

          You already have MASTER:: DonorID --< DONATIONS:: DonorID and you get the last donation from that.


          If you additionally have:

                  MASTER:: DonorID ---< DONATIONS2:: DonorID

          AND  MASTER::grenewal ---< DONATIONS2::Renewal code


          you would only view renewal donation through additional relationship.  Then you can pull the last of the related records and it will, by definition through the relationship, be the last renewal donation.


          Note1:  DONATIONS2 is another table occurence of your DONATIONS table.

          Note2:  MASTER::grenewal would be a new global field set to the value you use in your DONATIONS table to mark that it is a renewal donation.  Since the global will lose it's value on closing FMP, use a SetField [Master::grenewal ; Value] step in your opening script to set it correctly on opening FMP.


          Does this filtering relationship make sense as I've descibed it?

          • 2. Re: Storing data from another table



            Sounds like it would work, but I need more information.


            I don't have to actually create a new table?  Just saying Donations2 will create a second relationship to "Donations"?  What do you mean it's another "table occurence."  (I'm used to relating actual files on a hard disk, and I'm not really sure WHAT's going on underneath FileMaker.)


            Is "Renewal Code" a field? The description of the relationship? Or is it the actual string that indicates a renewal (i.e. starts with "Dues") and BTW if so, can I just say " = "Dues" so it will match both "Dues:new" and "Dues:renew"?


            The new field I would add to the Master, "grenewal" -- I get that I define that as a global numeric field.  But the "opening script" reference has me totally flumoxxed. 




            BTW, thank you very much.

            • 3. Re: Storing data from another table

              I don't have to actually create a new table?  Just saying Donations2 will create a second relationship to "Donations"?  What do you mean it's another "table occurence." 

              Table Occurrences are the boxes in your Relationship graph. To create a "Donations 2" TO, select it in the graph and then click the button with two overlapping green plus signs in the lower left corner. It will point to the same Donations table, but now you can set up a new group of key fields to relate the records in a new way.


              To match up multiple pairs of fields in one relationship between your TO's, drag from one field at a time to it's matching field in the other TO until they've all been included or link up the first pair and then double-click the relationship line to pop up a dialog where you can add/change your pairs of matching key fields.


              Is "Renewal Code" a field?

              Yes. This has to be a field you defined in the Donations table. (Click on the Fields tab while still in Manage | Database. )


              An "opening script" is a script you select to run when the file is opened. You can set this up by selecting File Options from the File menu. In the case of this global field, you may not need to do this, though its not a bad idea. Global fields will retain their value if the file is not being shared over a network or if you make the change from the "host" computer. (The computer that first opens the database is the "host".)


              Hope that helps clarify things a bit.

              • 4. Re: Storing data from another table

                This sounds like it can put me on the road.  I'm going on vacation for about a week and will report back as soon after that as I can find time to try this out.



                • 5. Re: Storing data from another table



                  "but not to the extent that I've defined Filemaker relationships."


                  I missed that little phrase in the original post...sorry for being cryptic, it wasn't intentional.