4 Replies Latest reply on May 16, 2013 6:55 AM by philmodjunk

    Importing into a new table w/ appropriate linking key

    LeahRiley

      Title

      Importing into a new table w/ appropriate linking key

      Post

           I have a Customers table with a set of serial numbers (primary keys) attached to each entry.  I have a second table called Orders that are linked to the customers through the primary key.  It is a one to many relationship.

           I want to import new orders into my Orders table.  At the moment, the only way for me to import and link is to make a column in Excel and write the customer key the orders are going to be related to in every row.  I need to do this each time I import.  

           Is there any way, using scripts, for example, to import my orders and have it auto relate (aka, auto populate the key) to the Customers table?  I already know which Customer it needs to link to, I just need to do it manually....

            I import lots and lots of data so I need it to be as seamless as possible.

        • 1. Re: Importing into a new table w/ appropriate linking key
          LeahRiley

               Note that each time I import I only import for a single customer.  I (so far) never have an instance where I import multiple customer's orders at the same time.  I think this is an important point.

          • 2. Re: Importing into a new table w/ appropriate linking key
            philmodjunk

                 FileMaker can't read minds. How can you, just by looking at the data in your spreadsheet, tell what customer should be linked to the rows of data that you are importing into Orders?

                 If there is some kind of identifying info in at least one cell of your spreadsheet, there are ways to make this work.

            • 3. Re: Importing into a new table w/ appropriate linking key
              LeahRiley

                   PhilModJunk:

                   For example, I have a layout where I am looking at the information of a single Customer (Customer 1).  I'd like to put an import button, so when I import the data, it will put customer 1 in the Orders table.  I would choose the appropriate file.

                   My Excel files are generated by a different program and I'd like to not have to edit the files with the customer # every time I want to import the data.

                   If I am currently selecting a record (Customer), is it possible to use that information when importing my Orders?  Is it possible to use a script that will fill in the customer # with whatever record is active after importing?

              • 4. Re: Importing into a new table w/ appropriate linking key
                philmodjunk

                     Ok, that we can work with.

                     Manually, you could copy the primary key from your customer table to the clipboard, go to a layout based on orders and import your data. Immediately after import, your newly imported records make up the current found set of records and you can paste the value from your clipboard into the foreign key field that matches to the customer table's primary key. Then you use Replace Field Contents to put this same value into the same field of all the other newly imported orders records.

                     A script can make this an automatic process by capturing the current customer ID in a variable, importing the records to orders and then using Replace Field Contents to update them with the correct Customer ID.