5 Replies Latest reply on Oct 17, 2012 5:50 PM by StephenSkoutas

    the script in recurring excel import wipes out existing data.

    StephenSkoutas

      Title

      the script in recurring excel import wipes out existing data.

      Post

           I have a couple of other posts going here that are all working toward a goal of having an excel spreadsheet that serves as a data source for a FileMaker Pro database.  The excel source needs to be updated regularly, and the recurring import needs to be run regularly to keep the database current.

           All is now working well, in that the import works smoothly and produces desired results.  I have a result that I understand, but I would like to change if possible.

           After importing into FileMaker, I have a layout that includes a related field from another table called "classification."  I want to be able to run the recurring excel import (which brings in product data) and then create more data based on what has been provided.  One necessary element is "classifiction" which links to a related table containing US Customs Classifications and duty / quota information.  It is in the layout as a pop-up and works fine.  

           The problem is that every time I run  the script from the recurring import, the classifications are wiped out.  That is because the script (image attached) has a line that commands "delete all records [no dialog]"

           Any way around this?  The goal is to be able to run the recurring import, classify, and then run another recurring import at a later time without wiping out the classifications.

           Thank you.

      script.jpg

        • 1. Re: the script in recurring excel import wipes out existing data.

               The simplest method would be to delete the line "Delete all records"...

               It sounds as if this table is based on gov classes and not data in the import. Thus the table could be permanent and if needed you could link to it.

               Also, creating a file just for the import and cleanup is a useful technique. When done you could then import that.

          • 2. Re: the script in recurring excel import wipes out existing data.
            philmodjunk

                 If there is a unique Identifier value that uniquely identifies each row in your file of imported data, you may be able to set up an "import matching values" type of import where existing records are updated from the import and rows that do not match an existing record are added as new records. In this scenario, you can leave the classifications field out of the list of mapped columns to fields in the Import Records step so that data in it remains unmodified by the import.

            • 3. Re: the script in recurring excel import wipes out existing data.
              StephenSkoutas

                   Phil, you hit the nail on the head.  I did the "import matching records" solution late last night.  It worked, and I'll detail it more in this forum when I get home tonight for other's benefit.  Now I need to write a script to automate it.

              • 4. Re: the script in recurring excel import wipes out existing data.
                StephenSkoutas

                     OK, for those that are interested, as I said below PhilModJunk's point was the solution I implemented.  I maintain the multiple vendor's product spec data in an Excel spreadsheet.  

                       
                •           I want the spreadsheet to include the serial number (which is my 5 digit product number), and I want that number to range 10000-99999.  Therefore I include a calculation cell on every vendors' blank "template" spreadsheet that says "=ROW(Ax+9999)" (this takes the first row, 2, and assigns it item number 10001, and each successive row is one serial number higher.
                •      
                •           Vendor fills in the rest of the data.
                •      
                •           I import copy and paste the vendor daily data into one master Excel spreadsheet.
                •      
                •           I import that spreadsheet to FileMaker Pro using this           
                                   
                  •                     file > import records > file...
                  •                
                  •                     in the import options, I map only one field using update matching records in found set (remove mapping relationship on all others)
                  •                
                  •                     the matching is serial number (the ROW calc in Excel) <-> item number (the Primary Key in FileMaker Pro)
                  •                
                  •                     select "add remaining data as new records"
                  •                
                  •                     import
                  •           
                       

                     Everything works well, and after the first import I can assign US Customs classification without concern that my next import will overright my new data.

                     Now, as I mentioned above, I need to script this.  Thanks for your help, Phil and Jack.

                • 5. Re: the script in recurring excel import wipes out existing data.
                  StephenSkoutas
                       

                  I map only one field using update matching records in found set (remove mapping relationship on all others)

                        

                  Correcting myself on one point - above.  I've changed it so that I map all of the other fields that are in the spreadsheet to their counterpart in the table, but I only use the matching records check on the serial / item number cell <-> field.  This way, updates in the spreadsheet (should an item's specs be changed) populate into the database.