10 Replies Latest reply on Jun 13, 2013 7:54 AM by Mike_Mitchell

    Mysql scripted import

    Hudi

      Hi

      What I'm trying to do: import via odbc from a mysql database. In filemaker I have a table with id's that match id's in a mysql table. I wish to script an import that will update the matching records in the found set without a dialogue.

       

      The Problem: In the script's field mapping dialogue box, the source file has no fields. its only when I run the script that the source fields show up and I can map them correctly. I need the id's to have the double arrow and the rest of the fieds to match by name. This needs to happen without the user seeing it, and I defiently can't ask them to start mapping fields.

       

      What I've tried/done: I've made sure that the field names are identical so that I can set the 'arrange by' option to "matching names". this still does not solve my probelm of designating the match field as it still does not show up in the script.

       

      Any thoughts on this?

       

      Thanks

       

      Screen shot 2013-04-15 at 12.16.59 PM.png

        • 1. Re: Mysql scripted import
          Mike_Mitchell

          Do the import manually, then create the Import script step. The most recently used field mapping is saved as part of the script step (if you check the appropriate checkbox); should do what you need.

           

          Mike

          • 2. Re: Mysql scripted import
            taylorsharpe

            Mike has the best answer.  But you might also create a Table Occurrence of the MySQL table and have a relationship between the ID's.  It makes it very easy to see differences or import from that table since the fields are already defined in FileMaker. 

            1 of 1 people found this helpful
            • 3. Re: Mysql scripted import
              Hudi

              What is the more "standard" way of doing this type of thing?

               

              Importing straight into the FM tables seems a bit unstable and is very difficult to troubleshoot if I need to constantly replace the script step every time there is a change. On the other hand, creating an import table is also not ideal, as I have about 10 tables that would each need an additional import table.

               

              Thanks

              • 4. Re: Mysql scripted import
                Mike_Mitchell

                If the source schema change frequently, Taylor's suggestion of an ESS occurrence with matching relationship is probably best. You can re-sync an ESS table more easily than you can update a script to compensate for changes in the source data.

                 

                Mike

                • 5. Re: Mysql scripted import
                  Hudi

                  Hey Mike,

                  A

                   

                  As you can see I'm deep into this ODBC import debacle...:)

                   

                  I did what you recommended in the 'correct answer' and did the import manually and then wrote the script. It worked yesterday when I wrote it. However today it no longer maps correctly. It reverted back to 'last order' when yesterday it was 'matching names'.

                   

                  A word about the script:

                   

                  Its a sript that jumps through about 15 tables and imports from a corresponding ODBC table. Since they each have a unique id in the mysql database and the same in FM, only the newly added (since the last import) ones get imported.

                  could it be that the first import in the script is affecting the later ones?

                   

                  Thanks again for all your help

                  • 6. Re: Mysql scripted import
                    Mike_Mitchell

                    Yes, it's very possible. Likely, even.

                     

                    I suggest you split your scripts up, one import per script, and then call them. It's better modularity anyway, and you can have better control over the imports that way.

                    • 7. Re: Mysql scripted import
                      Hudi

                      What would be the benefit/difference if instead of running 1 script with many odbc imports I run many scripts with 3,4 or 7 odbc imports. Wouldn't the field mapping still be affected anyway?

                       

                      Is there no way to "fix" the field mapping option (matching names)?

                       

                      thanks again

                      • 8. Re: Mysql scripted import
                        Mike_Mitchell

                        First benefit: When you run the manual import, then save the script, it sticks properly and doesn't gum up the other imports.

                         

                        Second benefit: What happens if, at some point in the future, you want to run just part of that import? Instead of duplicating the script and then removing the parts you want, you can write a second script and just call the individual imports you want to run.

                         

                        The difference? Maintainability. If at some point you need to make a change to one of those imports, you only have to make the change in one place. Otherwise,  you end up having to hunt for all the places that import is executed and duplicate the change everywhere.

                         

                        Make sense?

                        • 9. Re: Mysql scripted import
                          Hudi

                          First benefit: When you run the manual import, then save the script, it sticks properly and doesn't gum up the other imports.

                           

                          Thats great. Just what I was looking for.

                           

                          I rely heavily on script parameters based on a checkbox field to give the user the ability to import only what is necessary:

                           

                          If[patterncount(Get(scriptparameter);"accounts")>0]

                            Go to layout 'Accounts'

                            Import Records ODBC

                          End If

                          #

                          If[patterncount(Get(scriptparameter);"Contacts")>0]

                            Go to layout 'Contacts'

                            Import Records ODBC

                          End If

                          #

                           

                           

                          So what you're saying is this:

                          If[patterncount(Get(scriptparameter);"accounts")>0]

                            Perform Script [ Import Accounts]

                          End If

                          #

                          If[patterncount(Get(scriptparameter);"Contacts")>0]

                            Perform Script [ Import Contacts]

                          End If

                          #

                           

                          The import scripts contain the two script steps it replaced.

                           

                          Sorry for the back-and-forth but I've been at this for awhile and its still not smooth..

                           

                           

                          Thanks

                          • 10. Re: Mysql scripted import
                            Mike_Mitchell

                            Yup. That would be the idea.

                            1 of 1 people found this helpful