10 Replies Latest reply on Jun 24, 2014 3:55 AM by wimdecorte

    Import Script Step

    user22756

      I want to sync a FM table ('A') with a MySQL table ('B') by using a script.

       

      Table Occ. A has primary key 'recordid'.

      Table Occ. B has primary key 'id' and also stores 'recordid' as a INT field.

       

      This is what my script looks like (translated from Dutch)

      Freeze Window
      Go To Layout ["_A" (A)]
      Execute Search
      Import Records ["myDb.fmp12"; Adjust Matching Records ;Mac Roman]
      Go To Layout [original layout]
      

       

      I compare my two TO's based upon 'recordid', since that is matching in both tables.

      For some reason, I always get a new record in my MySQL table.

       

      What am I missing here??

        • 1. Re: Import Script Step
          wimdecorte

          You say that you want to get data from MySQL but the import script step there has a FM file as the source.  That's strange; are you using ESS in that FM file?

           

          You can use the "from ODBC data source" with the Import script step and use the MySQL database directly.  That should be faster because you would be cutting out the ESS layer.

           

          How may records are involved?

          • 2. Re: Import Script Step
            elmystica

            I might have put it A little unclear, but the import is intended to go from FM towards MySQL. For the moment there are around 400 records with around 17 fields involved

            • 3. Re: Import Script Step
              jameshoty

              I am trying to understand your situation. You want to insert record from A (a table in .fmp12) into B (a table in mySQL, external data source). Is it correct?

              • 4. Re: Import Script Step
                user22756

                Yes, that is what I'm trying to achieve

                • 5. Re: Import Script Step
                  jameshoty

                  And you have a table occ. B (in your .fmp12 file)? Can you describe your table occ. A & B? Their field definition and relationship (screen capture will be good). If you are writing to mySQL, isn't it you should be using SQL statement to update than import record script steps?

                  • 6. Re: Import Script Step
                    user22756

                    For a screenshot: https://www.dropbox.com/s/8zvu12bcqarr5rx/Screenshot%202014-06-23%2009.16.14.png

                     

                    Yes, I do have a table occ B in my file.

                     

                    They are more or less the same:

                    Table A (FMP Table Occ.):

                    recordid: Primary key

                    the rest are just Text and number fields

                    Screenshot on fields: https://www.dropbox.com/s/x7ii6kbkffdhrie/Screenshot%202014-06-23%2009.21.07.png

                     

                    Table B (MySQL Table Occ.):

                    id: Primary key

                    recordid: int (11)

                    and the rest are also just basicly Text and number fields

                    Screenshot on fields: https://www.dropbox.com/s/e6kfpzp4bxwgdnd/Screenshot%202014-06-23%2009.21.46.png

                    • 7. Re: Import Script Step
                      user22756

                      Is it possible that the issue might by caused by a numberfield to a varcharfield?

                       

                       

                      The import normally has the advantage to be able to see if a record only needs to be updated, or if a new record has to be created.

                      So using SQL would mean I have to write a script for each update record (a script trigger for onSaveRecord)?

                      And Insert for new records?

                      • 8. Re: Import Script Step
                        wimdecorte

                        Import with matching is not very fast; how often do you need to do this?

                        On the existing records, do you really update fields or are you just using the mechanism to add new records?

                         

                         

                        You don't need a script for each record; one script can handle it all.  And you don't need to do it onSaveRecord, that would depend on the workflow and how crucial it is to have the new records created in MySQL immediately or if a delay is ok.  If a delay is ok,  you can write a sync script that remembers when it was last run, then find the new records in FM and use the "Insert SQL" script step to create those new records in MySQL

                         

                        If you also update existing records then you search on the modification timestamp of your records instead of the creation timestamp and do the same thing.

                        1 of 1 people found this helpful
                        • 9. Re: Import Script Step
                          user22756

                          Hmmm, interesting ...!

                           

                          The updates wouldn't come very often on the products, but would on clientsdata, etc. We would perform both updates and create new ones. Pricechanges, stock, ...

                           

                          I could indeed make a FM-table to log the SQL-actions.

                          Either make a script that runs every 2 hours, looks for last update in that table, get al the updated records, update MySQL, get all new ones, insert MySQL and update table.

                           

                          In PHP I would have my solution already made, but how do you "loop" through all records?

                          Find a set of records, go to first records, start loop, go to next, exit on last?

                           

                          I'm starting to like scripting more and more.

                          Thanks for the help!

                          • 10. Re: Import Script Step
                            wimdecorte

                            Michel Beyaert wrote:

                             

                            how do you "loop" through all records?

                            Find a set of records, go to first records, start loop, go to next, exit on last?

                             

                             

                            A couple of different ways.  What you describe is one way.

                             

                            You can also loop through records without visiting each record, using GetNthRecord() instead of "go to record".

                            You can also use the ExecuteSQL() function (not the "Execute SQL" script step) to collect all the relevant data in a variable and then loop through the variable's content instead of looping through records.

                            If you can avoid actually having to visit the record you'll get better performance.

                             

                            If you are going to run this sync routine a server-side script then performance is probably not going to be a major concern.