1 2 Previous Next 16 Replies Latest reply on Aug 10, 2015 1:56 PM by john_wolff

    FileMaker Table to MySQL via ESS

    MattLeach

      So for the first time 'm delving into the world of ESS and have hit a logical road block.

       

      I've read the FileMaker ESS Guides which have aided me in setting up an External SQL Source which I now have in my relationship graph. What I'm at a loss at is how to take data from a FileMaker table and place it into the ESS.

       

      We have a FileMaker table that contains customer information. We need to export customers who are on a paid subscription to the MySQL table. My through was to setup a server side script that will run every 30 or 60 minutes that will update the MySQL table with updated information. Some clients may drop off, some may be added, however I do not see how to script this through either an import or an export.

       

      I thought about exporting the data to csv file, then import into the ESS table but that kind of defeats the purpose of having the ESS.

       

      What would be the best approach to accomplish this goal?

       

      Not sure if it makes a difference but currently running FMS13 on Win Server 2008 (upgrade to FMS14 coming)

        • 1. Re: FileMaker Table to MySQL via ESS

          Hi Matt,

           

          There are several alternative ways but I'd tend to avoid importing if there's any chance of fields being added or deleted in either table as that will surely screw up any established import maps.

           

          I've used the Dictionary CF's from SixFriedRice to build a local variable (PassParameter) from the data in the FM table then switch to the ESS table and use the GetParameter CF to write that data from the local variable to the mySQL table.

           

          That can be scripted and run whenever you have new data for a paid subscription. For a Subscription that is no longer paid up, you merely need to delete that record from the mySQL table, or change its status via the above CF's.

           

          Of course you must use the unique record ids, which will be common to both tables, to find the record in the ESS table that you need to modify.  If its not there then create a new record to hold that data.

           

          Should you want to avoid using those CFs then create local vars for each data element.

           

          I hope this helps, but get back if you need any more help.

           

          John

          • 2. Re: FileMaker Table to MySQL via ESS
            MattLeach

            I've used the Dictionary CF's from SixFriedRice to build a local variable (PassParameter) from the data in the FM table then switch to the ESS table and use the GetParameter CF to write that data from the local variable to the mySQL table.

             

            Im not sure I understand how that process works. Do you happen to have a link to examples or even a sample script i could view to help understand this technique?

            • 3. Re: FileMaker Table to MySQL via ESS
              rrrichie

              With ESS you can just set/create the records directly.  I would loop through the clients, cause the mySQL database might modify the record, and you get a 501 error.  Then you can revert the record and try again later.

               

               

              Make sure you set the required fields based on the rules set in mySQL...

              • 4. Re: FileMaker Table to MySQL via ESS

                Hi Matt,

                 

                Here are a couple of ScreenShots from the scripts that we use for updating the product table of our web site.

                 

                The first one creates the variable $product using the PassParameter CF. It is pulling the data from one record in our Products table into the parameter names of the ESS table.Screen Shot 2015-08-08 at 12.17.49 pm.png

                 

                The next screen shot shows the script steps of an Update script called to write that data into the ESS table. That script has $Product as the Script parameter.:

                Screen Shot 2015-08-08 at 12.18.57 pm.png

                 

                Before running that script we have searched the ESS table for a matching product ID. If none found then a new record is created in the ESS table. If a matching record is found then we overwrite the data in that record.

                 

                Once you get the CFs installed you can see how the data is packaged as Name - Value pairs in the Data Viewer.

                 

                If you need further details then send me a private email so that I can send you a pdf of the script. It has many other steps which aren't relevant to your immediate enquiry.

                 

                Regards,

                 

                John

                • 5. Re: FileMaker Table to MySQL via ESS
                  MattLeach

                  Thanks John

                   

                  I found the blog for the CF on the SixFriedRice website so i will do some testing this weekend with it. So in essence i would just need to loop through the records that need to be added to the ESS database setting the variables and creating the new record correct?

                  • 6. Re: FileMaker Table to MySQL via ESS
                    john_wolff

                    Hi  Matt,

                     

                    For adding new records to the ESS table -- YES!! For modifying an existing record, you have to isolate that record then write the data.

                     

                    Cheers,

                     

                    John

                    • 7. Re: FileMaker Table to MySQL via ESS
                      beverly

                      Yes, and no, John! If you have a relationship between the MySQL and FM tables (T.O.s) then you can set field based on the relationship. If you allow creation of related records, set field will INSERT (create) any new records not in MySQL otherwise it will UPDATE (edit) any existing record in MySQL.

                       

                      as  rrrichie stated, you *must* be mindful of MySQL dependancies (with other tables) and NOT NULL or auto-enter fields.

                       

                      beverly

                      1 of 1 people found this helpful
                      • 8. Re: FileMaker Table to MySQL via ESS
                        john_wolff

                        Hi Beverley,

                         

                        Matt was originally considering an import into the mySQL table. Which indicated to me that he had not considered creating a relationship. Your comment raises the question over whether or not it's advisable to have that relationship.

                         

                        Perhaps he could have . . . and I gather you would have recommended that approach ahead of any other, stemming from your expertise with SQL which is far more than I will ever have!

                         

                        Regards,

                         

                        John

                        • 9. Re: FileMaker Table to MySQL via ESS
                          wimdecorte

                          One other alternative is not use use ESS but just the Execute SQL script step to INSERT and UPDATE data directly through the ODBC connection.  Can be done server-side.

                          • 10. Re: FileMaker Table to MySQL via ESS
                            beverly

                            + 1!

                            On Aug 9, 2015, at 8:40 AM, wimdecorte:

                             

                            One other alternative is not use use ESS but just the Execute SQL script step to INSERT and UPDATE data directly through the ODBC connection.  Can be done server-side.

                             

                            • 11. Re: FileMaker Table to MySQL via ESS
                              MattLeach

                              Currently there is no relationship to the MySQL Database.

                               

                              The premise is to have all clients with an active subscription added to the MySQL database. There is a date field in the clients table that tracks when subscription is valid through. If that date is greater than today, it goes to MySQL. With John's help I've been able to get everyone currently on subscription into the MySQL database.

                               

                              Going forward, what would be the ideal way to keep the data is MySQL up to date. There will be people who go on and come off of subscription as often as every hour. My original thought was to basically delete all records in MySQL database and re-import only what is currently on subscription. My concern is making sure those that have gone off of subscription have been removed from the MySQL database.

                              • 12. Re: FileMaker Table to MySQL via ESS
                                beverly

                                Matt, that's where the Execute SQL (script step, not the function) is most helpful. You can perform queries to: INSERT, UPDATE or DELETE (with permissions of course!). I like to make calculations from my records and have the SQL statements in these fields as appropriate. Then loop through the records, adding, editing or deleting with the queries.

                                 

                                It works very well.

                                 

                                The problem with delete and reimport can be timing and other factors.

                                 

                                As for "frequency", you'd need to get more information from others to see if this can be a script that the FMS can schedule. Is this script step currently available to Server? The Script Reference says "partial". My guess is that means without dialog

                                 

                                You'd certainly want ways to error trap that don't halt the progress. If a record already exists in MySQL, then it's an UPDATE not an INSERT. If the record doesn't exist in MySQL, then DELETE will fail for that record. Etc. There is a function 'Get(LastODBCError)' that can help as you step through each record to "sync".

                                 

                                Plug-ins may also help here.

                                 

                                beverly

                                • 13. Re: FileMaker Table to MySQL via ESS
                                  john_wolff

                                  Hi Matt,

                                   

                                  You've now got alternative suggestions to my earlier one. Which you choose will perhaps be governed by the maximum number of records you need to update, insert or delete each hour, and the time that it takes for each loop to complete.

                                   

                                  In our case, we'd have a maximum of 20 records to create or update with each loop taking 6 seconds over our slow internet connection. (Much of that is taken up with the transfer of a base64 encoded image of 480 x 480 pixels.)  At the most, we'd only be running that script a few times each day, say up to 5, with mostly having fewer than 5 records to update, with none ever being deleted. We find our procedure acceptable with that number of transactions. If we had dozens of changes to make every hour of the working day we'd need to be finding a much faster way.

                                   

                                  Regards,

                                   

                                  John

                                  • 14. Re: FileMaker Table to MySQL via ESS
                                    MattLeach

                                    I'd say at minimum we're looking at 4000 records.

                                    1 2 Previous Next