3 Replies Latest reply on Feb 16, 2015 8:43 AM by philmodjunk

    Importing, Moving Multiple Records Between Tables Script

    kat012002

      Title

      Importing, Moving Multiple Records Between Tables Script

      Post

      Hello All,

      I have a file with shipment information that I need to import into separate tables, a shipment name/id table and a shipment line items table. The problem is it is formatted in a way that the shipment name/id is located in column 2, row 1. The shipment line item information starts at row 9 and but the number of subsequent lines vary with each shipment.

      I’m not sure how to go about writing a script to create a new shipment in the shipment table using the value from column 2/row 1, then create child records in the shipment line items table with the values starting at row 9.

      I have written a couple executeSQL calculations that gather the shipment name and line item information I need but I can’t figure out how to use the data from the executeSQL calc to create the shipment line items records.

      Is there a better way to do this?

      Any help and/or suggestion would be very much appreciated!

        • 1. Re: Importing, Moving Multiple Records Between Tables Script
          philmodjunk

          Don't quite see how ExecuteSQL can contribute to the solution here. Since FileMaker assumes that each row of the spreadsheet is a different record and each column is a field, You'll need to import this data into some kind of staging table with a script to loop through the imported records sending data about the shipment as a whole to records in the shipment table and line items data to the line items table.

          • 2. Re: Importing, Moving Multiple Records Between Tables Script
            kat012002

            Thank you for the response! I am going to do as you suggested and loop through the records with a script. I am fairly new to scripting and have been trying to figure out the best way to import all the reports that are generated by Amazon using a staging table.

            Don't quite see how ExecuteSQL can contribute to the solution here.

            I guess I don't fully understand the executeSQL script step. I found it easy to write a query to gather the information I needed from the reports but I'm guessing by your response there is no way to use the data from the query to create new records in a different table. Is that correct?

            • 3. Re: Importing, Moving Multiple Records Between Tables Script
              philmodjunk

              There's an Execute SQL script step used to send data to an ODBC data source.

              There's an ExecuteSQL() function you might use inside a calculation, but it, as you describe, is only used with SELECT queries to "read" data from a FileMaker Table, not to create new records.