3 Replies Latest reply on May 14, 2011 2:04 PM by davidanders

    Importing question

    BonnieHammond

      Title

      Importing question

      Post

      I am creating a database for a nonprofit organization, so what I want to do is set up a Contacts table and a Donations table. I have an Excel spreadsheet of my Contacts that I can import, that part is easy.  I also have an Excel spreadsheet that lists donations and contains the following fields: donor name, donation date, and donation amount. I want to import this data into my Donations table. My question is how do I make Filemaker match the imported donations with the donors in the Contact list during the importing process?

      thanks in advance for anything that points me in the right direction!

        • 1. Re: Importing question
          LaRetta_1

          Hi Bonnie,

          You cannot make them match during the import but you can establish a match once they are both in their separate tables.  Here is how:

          You should set up three fields automatically in every table you create:  the unique ID, a creation timestamp and a modification timestamp so get them in place first.

          In your Contacts table:

          1) Create field (type is number) called ContactID.  This is the Contacts table's primary key and in Options auto-enter tab, select 'serial' and 'on creation' and 'start with 1' and 'increment by 1.'  Create your other fields, CreatedOn (timestamp auto-enter creation timestamp), ModifiedOn (timestamp auto-enter modification timestamp), ContactName and all the other Contact fields you will need.

          2) Import your Contacts Excel data into it.

          In your Donations table:

          1) Do the same, creating the unique ID called DonationsID, CreatedOn, ModifiedOn and the remaining fields for your Donations.

          2) Also create a ContactID number field but don't put an auto-enter on it.  You will put the ContactID number from your other table in here when we match it in the next part.

          3) Import your Donations Excel data into here.

          Now a temporary match:

          1) In the File > Manage > Database Relationships tab point at the contact name in Contacts table and drag and connect to the donor name in the Donations table.  This temporary connection is required to establish how the data is related.  We will then switch the relation from names (which are dangerous to use) to the keys we established in in both tables 1) above.

          2) To see if we have established a relationship, go to Donations and go to Layout mode (View > Layout) and Insert > Field and select your Contacts::ContactID and place it on the Donations layout.  Go to Table view and see if there is data in this field.  If there is then you have a relationship (although we still cannot full trust it until we verify in next step).  If no data appears then we need to stop and work on making the names match.

          3) On the Donations layout in Browse mode, View > Find and type only an asterisk into the ContactID field and hit enter.  See how many records are produced from your total record set.  If all donations have a parent Contact (if all records are found) then continue to setting the foreign key section. If there are some names which do not match (but most do) then you will need to determine how to match them (I would be happy to assist you through this).

          Set the foreign key in Donations:

          1) We now have all of the data related (although the connection is very shaky because it is based on names and that isn't stable at all because people can change their names or something can be mis-spelled) so we can change the relationship from weak-connected names to proper and stable relationship keys.

          2) One Contact can have many Donations so you want to put the ContactID in the Donations table.  Go to a layout based upon Donations.

          3) Show All Records then back up your database here for protection (File > Save A Copy As 'copy of current file').

          4) Place cursor in Donations::ContactID field and select Records > Replace Field Contents.  Specify 'Replace with Calculated Result' and in the calculaton box, find and double-click your ContactID from your Contacts table to insert it into the calc box.  Click OK and then REPLACE.

          5) This will insert the Contacts::ContactID through your donations records so now go to your graph and change the connection from between names to: Contacts::ContactID = Donations::ContactID

          You are in business, Bonnie.  Let me know if you get stuck anywhere.  And once you have good connection on proper keys, I highly suggest that you split your names now into proper fields and we can help you through that as well.  :^)

          • 2. Re: Importing question
            BonnieHammond

            LaRetta,

            Thank you so much for such a detailed and easy to follow solution! It worked like a charm. I really really appreciate your help with this. I've been puzzling over this for days now, and my brain was really starting to hurt. Thank you!!

            Bonnie

            Save a Forgotten Equine horse rescue
            Seattle WA

            • 3. Re: Importing question
              davidanders

              Bonnie,

              You may fiind it useful to check out the free template here  http://filemakerdonations.com/

              David Anders

              The Computer Guy, Seattle