2 Replies Latest reply on Jul 20, 2012 12:21 PM by BryanN

    Data Import/ Foreign Key Calculated



      Data Import/ Foreign Key Calculated


      So I'm nearing the completation of our database and lo and behold, the data coming out of our accounting system is messing me up. To make things short, I'm bringing in 2 tables worth of data (field names in bullets):


      Table1: Customers

      • PK Customer ID (serialized)
      • Customer ID (Non serialized uppercase name)
      • Customer's Full proper name
      • Descriptions, addresses, etc.
      Table 2: Jobs
      • PK Job ID (serialized)
      • FK (Blank - no data)
      • Customer ID (Non serialized uppercase name) like Table 1
      • Job name, desc, etc.
      As of now, the customer table is small (600 records) so it was easy to just apply a 1-600 serialization in excel before I imported those. No problem.
      However the jobs are a challenge.  There are over 6,000 records.  Unfortunately, the accounting system doesn't let you see a numerical Customer ID to use as a FK, instead it has an all uppcase text ID instead.
      My question is this:  Is there a way to (in Filemaker, Numbers or Excel) have the app look to see if the Customer ID (Non serialized uppercase name) from Jobs Table matches on Customers table then if they do, enter in the appropirate Customer ID (serialized) into my Jobs FK field?
      Just trying to save time instead of going through 6,000+ records to serialize each job manually.

        • 1. Re: Data Import/ Foreign Key Calculated

          The trick is to use different occurrences of the Customers table to define two relationships--one based on the name and the other based on the ID number. You can then use the name based relationship to copy over the correct Customer ID value.

          If you don't know how to create duplicate table occurrences:

          In Manage | Database | relationships, make a new table occurrence of Customers by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as CustomersByName.

          We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

          Now you can create these relationships:

          Jobs::FK = Customers::PK Customer ID

          Jobs::Customer ID = CustomersByName::Customer ID

          Duing Import, be sure to enable auto-enter options so that PK Job ID automatically assigns serial numbers to each imported Jobs record. (You could also have done this for your PK Customer ID)

          Immediately after you import your Jobs records, your found set will be all 6,000 records. Click into the Jobs::FK field and use Replace Field Contents from the Records menu with this calculation to copy over the needed ID number from the Customers table:

          CustomersByName::PK Customer ID

          • 2. Re: Data Import/ Foreign Key Calculated

            Dude, you are awesome.  I knew it could be done... I didn't think of doing another table occurance.  Well done!