1 2 3 Previous Next 30 Replies Latest reply on May 16, 2012 3:06 PM by technet_admin

    Using SQL to create records in FMP and get resulting ID

    worldcloud

      I have used several SQL plug-ins, with similar results. My issue is understanding which SQL functions that FileMaker supports and/or the syntax. In mySQL, I have been able to write an statement to create a new record and return the value of my key field. I have created a beast of a workaround with FileMaker, but I use the MAX function which is too expensive on larger data sets. It takes almost 5 seconds to create a record In a 200,000 table. A new record script step is practically instant.

       

      I would like to perform something like INSERT INTO PHONES WHERE ContactID = 5 WITH RESULT PhoneID

       

       

      I totally 'made up' that last part, but knowing what syntax to get the 'PhoneID' field is my problem. PhoneID is an auto-entered serial number and no ESS is being used in this example. 100% FileMaker...

       

      Ideas?

       

       

      Thanks,

       

       

      Joe

        • 1. Re: Using SQL to create records in FMP and get resulting ID
          beverly

          1. 100% Filemaker

               a. using plug-in? if so which one(s)?

               b. using web publishing? if so with what web app and is it using ODBC?

               c. using Filemaker as ODBC source to ??

           

           

          2. INSERT wouldn't use WHERE (unless with a select to get the values)

               INSERT INTO tableName (

                    col1

                    , col2

                    , col3

               ) VALUES (

                    'abc'

                    , 'def'

                    , 'ghi'

               )

           

               or

           

               INSERT INTO tableName (

                    col1

                    , col2

                    , col3

               ) SELECT col4, col5, col6 FROM tableName2 WHERE ...

           

          3. are you looking or the Filemaker/xDBC equivalent of SQL's (which would follow either above INSERT:

               SELECT @@IDENTITY

               or

               SELECT SCOPE_IDENTITY()

               or

               SELECT IDENT_CURRENT('tablename')

               or

               SELECT LAST_INSERT_ID()

           

          4. if it's just FileMaker do you have access to the key field?

           

          5. why are you trying to use SQL when FileMaker functions and script might do better?

           

          Beverly

          • 2. Re: Using SQL to create records in FMP and get resulting ID
            comment

            worldcloud wrote:

             

            My issue is understanding which SQL functions that FileMaker supports and/or the syntax

             

            I believe this is included in the FileMaker ODBC and JDBC Guide installed with the application.

            • 3. Re: Using SQL to create records in FMP and get resulting ID
              worldcloud

              Please read the post before replying. The post clearly stated that I have been using a SQL plug. Actually, I've used about six plug-ins because they have the same issues as the underlying issue is FileMaker's handling of SQL. I have read the odbc/jdbc guide but it says little about the optional SQL parameters which FileMakermay or may not support.

               

              I an very interested in real feedback, but just quoting the manual about how to form a SQL is not the issue...

              • 4. Re: Using SQL to create records in FMP and get resulting ID
                Stephen Huston

                Hi Joe,

                 

                I've read your initial post a couple of times, and am still uncertain about your phrase "100% FileMaker...". Does that mean you want to use the new ExecuteSQL function instead of a plug-in?

                 

                If so, the new FM function only supports the SQL "SELECT" statements. Other interactivity via SQL still requires a plug-in. This limits the new function's use pretty much to outputting calculation results, not data creation or modification, except where that is done via traditional FM steps using the ExectureSQL calculated result.

                • 5. Re: Using SQL to create records in FMP and get resulting ID
                  jbante

                  I don't know if there's some actual FileMaker SQL answer to your "WITH RESULT", but you might try inserting a temporary key that you can use to SELECT the ID from the record after INSERTing it.

                   

                  Set Variable [$temp = Get ( UUID )]

                  INSERT INTO table(temp) VALUES ($temp);

                  SELECT id FROM table WHERE temp = $temp;

                  UPDATE table SET temp = NULL WHERE temp = $temp

                  • 6. Re: Using SQL to create records in FMP and get resulting ID
                    worldcloud

                    100% FileMaker, meaning that I am not using ESS and/or any other SQL source other than FileMaker. This method works great and does a wonderful job at reducing the cluster in my graph, but in order to determine the key, I use the MAX function which is very expensive. It seems stupid for FileMaker I run through every value in the ID field each time it creates a record.

                    • 7. Re: Using SQL to create records in FMP and get resulting ID
                      worldcloud

                      I have considered using UUIDs, which would resolve the problem. On one solution in particular they track physical assets with the ID values which have been serilazed with an auto-enter value, but at least the UUID gives me the abilibity to poll the database for that number.

                       

                       

                      I wasn't found of using UUIDs but as a temp value, I think this is the best option. I will certain tweak my code and see what happens.

                       

                      Thanks....

                      • 8. Re: Using SQL to create records in FMP and get resulting ID
                        timwhisenant

                        Hi Joe,

                         

                         

                         

                        You said you “use the max function”. What about using an “order by” creation date desc clause. The first record should then be the last one created. Would this not be less “expensive”?

                         

                        I realize that means a second statement, SQL query. But with the tools available, it's an alternative.

                         

                        Just a thought,

                         

                        Tim

                         

                        Message was edited by: timwhisenant

                        • 9. Re: Using SQL to create records in FMP and get resulting ID
                          jormond

                          If you are using the Max function, you are using a relationship, yes?  Why not just use the Last () function?  It's faster and only needs to look at one record.

                          • 10. Re: Using SQL to create records in FMP and get resulting ID
                            comment

                            worldcloud wrote:

                             

                            Please read the post before replying. The post clearly stated that I have been using a SQL plug.

                             

                            AFAIK, all the SQL plugins work within the SQL statements and constructs that Filemaker supports - and these are listed in Chapter 7 of the guide I mentioned. There's no need to be rude.

                            • 11. Re: Using SQL to create records in FMP and get resulting ID

                              worldcloud wrote:

                               

                              Please read the post before replying.  I an very interested in real feedback, but just quoting the manual about how to form a SQL is not the issue...

                               

                              Well here is some REAL FEEDBACK for you, Joe.  As a professional developer, I recommend services to my clients all the time.  You have just come OFF my list of recommendations.  You are a real gem.  NOT.

                              • 12. Re: Using SQL to create records in FMP and get resulting ID
                                worldcloud

                                No relationship, using MAX via the SQL. The concept in this method is to be 'context free' and reduce the total number of TOs in my graph..

                                • 13. Re: Using SQL to create records in FMP and get resulting ID
                                  worldcloud

                                  LaRetta,

                                   

                                  I'm not sure why you are offended. I asked a question and clearly stated that I was using a plug-in to access FileMaker via SQL, and then Beverly starts asking me if I'm using web publishing or ODBC and then she simply prints the Knowlegebase text about forming SQL statements. I have been using SQL in FileMaker for the past six years and the question had nothing to do with the formation of SQL statements. Beverly responds to every post I submit and has yet to ever answer the question which was asked.

                                   

                                  The one time I mentioned my client's name, she called them directly and took work from them, so if you want to talk about a 'gem' I think your anger is misplaced. Most of the people on this board are interested in improving the quality of their code and we all know that there are many techniques which are not covered in the FileMaker documentation is an insult to those of us who do this for a living to say 'go read the manual'.

                                   

                                  jbante clearly understood my question and made a good suggestion which was clearly not mentioned in the FileMaker manual. I have also had conversations with two other FileMaker developers which have struggled with this same issue. I value the input of those wh are truly interested in any topic, but those who just post without adding any input and/or asking realted questions devalue this service. When the same developer is calling my clients, I have even less patience.

                                   

                                  Sorry to offend... the frustration was not aimed at you...

                                  • 14. Re: Using SQL to create records in FMP and get resulting ID
                                    worldcloud

                                    I tried that, but SQL is still having to 'evaluate' the entire table and on a 100,000 record table it takes about three seconds to get a result on a local Gigabit network. Since this script is used to create new records, three seconds is a lifetime.

                                     

                                    Apparently, mySQL, msSQL, and Oracle all support additional SQL arguments which can return the result of any column, which is how many PHP solution handle this same issue of 'knowing' the value of the 'ID' field.

                                     

                                    I realize that FileMaker has very limited support for SQL, but now that more users are using SQL (with the native SQL functionality), we can only hope that FileMaker will add support for INSERT, UPDATE, as well as additional arguements within these statements.

                                    1 2 3 Previous Next