12 Replies Latest reply on Mar 30, 2010 11:58 AM by mrvodka

    Creating new records from SQL data

    laneystewart

      Title

      Creating new records from SQL data

      Post

      I am in education and we have a web service that is backed by MySql where students file forms.  I have created a ODBC connection to that location and get the data in FM 10. I need to create a new record in my FM database when a new record is created in the MySQL database where we get web forms submitted.  It works fine when the database is open b/c I put in a OnCommit Script Trigger, but it does not work when the database is not open to a client.  The database is on a FMAdvanced 10 server where it is open all the time, but does not always have a client in the database.  Has anyone dealt with this or have any ideas?  I do not want to have to make sure that someone opens the database everyday to get the work to be created so I do not want to use an open script.  I have not used timers before, but maybe that would work.  I also need to export the data collected to a .txt file.  Any ideas there?

       

        • 1. Re: Creating new records from SQL data
          mrvodka

          You could just perform an ODBC import. If you have FMS then you can use a server side script to update only the new records every so often. If not then you could use Install OnTimer or a dedicated "bot" machine to do it.

          • 2. Re: Creating new records from SQL data
            laneystewart

            k, I set up a script on the server that imports every 2 hours.  Now I need to export some of that data to a .txt file.  I am working on a MAC but need the export readable by a program that is PC only.  When I export naming it with a .txt and open in a PC environment with Notepad I get it like 

             

             

             

            The boxes at the carriage return are screwing up the program that I need to get it into.  Any idea on how to get it to NOT put the box in at the carriage return???  If I open in WordPad, it looks fine, and if I do a save as it goes into my other program fine.  I need to not have human intervention on this file so that it an be scheduled to upload overnight.  If anyone has ever had this trouble and have a fix for it, let me now.

             

             

            • 3. Re: Creating new records from SQL data
              IDM

              Hi laneystewart,

               

              I worked in SQL Server for 6 years before moving to FileMaker. There's a red boxed x below almost as if an image was to show up there. Please repost if possible, I'd like to help.

               

              What program are you using to get the data into?

              IDM

              • 4. Re: Creating new records from SQL data
                mrvodka

                You export as XML and use a XSLT.

                 

                See this thread.

                 

                http://fmforums.com/forum/showtopic.php?tid/137360#137425

                • 5. Re: Creating new records from SQL data
                  laneystewart

                  am trying to get the data into Hyland's OnBase software, a document Management system.  I have created an electronic form that incorporates the data.  It is very particular about how the data is formated to be able to pick it up.  I can open the filemaker export in wither word from my MAC or wordpad from a PC and then save it, making NO changes and it will import fine.  Of course I want to not  have to touch the file at all.  Here is the re-post of the screen shot I have.  I hope this time it works.. If not, it has the data separated by a tab but where the carriage return is there is a rectangular box and not on the next line.

                   

                  • 6. Re: Creating new records from SQL data
                    mrvodka

                    I cant see any of your attachments. You could gather all the data into one field and Export the field contents as an option.

                     

                    Did you try the XML output?

                    • 7. Re: Creating new records from SQL data
                      laneystewart

                      I did just try the xml output and got an error of XML parsing error: Invalid document structure.  Maybe I did it wrong and I will be looking into it.  I am also going to try to combine all the fields into one with a carriage return at the end and see what I get as output since it is the carriage return that is the problem I am guessing.  

                      • 8. Re: Creating new records from SQL data
                        IDM

                        Hi laneystewart,

                         

                        Have you tried editing the sql script so that carriage returns are removed from the output file?

                         

                        Try the following to replace your carriage returns with a blank. You can update your table and set a column to replace carriage returns using CHAR(13):

                         

                        Replace (Column , CHAR(13),")

                        • 9. Re: Creating new records from SQL data
                          laneystewart

                          The output file in from Filemaker and has to go into OnBase.  I have figured out how to create the import on a schedule from the MySQL database, but now need to figure out how to get a plain .txt file from Filemaker to import into OnBase software.  The .txt file needs to have a new line for each file that I am importing and when Filemaker creates the output it does not format as a new line in Notepad, just a funny box character instead.  In Wordpad, I get a new line, but it will only import into OnBase after I save it as plain text in Wordpad.  I have never worked with uncoded text files before or xml, so much of this is new to me.

                          • 10. Re: Creating new records from SQL data
                            mrvodka

                            Copy the text in Fenton's post and save it as a XSL. Then when you export the data choose XML as the output. It will prompt you to choose a style sheet and grammar. Use the FMPDSORESULT grammar and choose the XSL file that you saved earlier. Once done you can open up your exported XML and it should show you results that you want.

                            • 11. Re: Creating new records from SQL data
                              laneystewart

                              I have the .xsl saved and used it in the export, but Filemaker will not even let me finish the export and I get a error reading:

                               

                              XML parsing error:

                              Invalid document structure

                               

                              Line Number: 1

                              Column Number: 1

                               

                              Maybe b/c I am on a MAC and not a PC???  I saved the .xsl from my PC but am trying to export from a MAC.  I do not have access to FM from a PC........

                               

                              UGH... maybe I will just convert the files myself everyday until I can get this figured out........

                              • 12. Re: Creating new records from SQL data
                                mrvodka

                                I dunno. Fenton was on a mac. It should work I believe.