1 2 Previous Next 20 Replies Latest reply on May 25, 2016 11:09 AM by BowdenData

    Create Records from SQL Query

    hangry

      I have Tables A, B and C.

       

      I would like to be able to run a query that selects fields from Tables A and B and for each row in the query results create a record in Table C.

       

      I know how to write the select query, and if I was doing this in .Net I would create a dataset from the query results and loop through and create a record in Table C for each row in the dataset.

       

      Can someone tell me how to approach this in Filemaker?

       

      Thanks

        • 1. Re: Create Records from SQL Query
          Mike_Mitchell

          Basically the same way. You create a list of results as a text object, then loop over them and create the new records. The preferred way to do it is through a portal (so it processes as a transaction), but you can do it from a layout based on the table you want (in your example, table C). So a script might look something like this:

           

          Set Variable [ $result ; Value: ExecuteSQL ( yourQuery ; "|" ; "¶" ) ]

          Go to Layout [ layoutBasedOnTableC ]

          Loop

               Set Variable [ $curRow ; Value: GetValue ( $result ; 1 ) ]

               Set Variable [ $curValues ; Value: Substitute ( $curRow ; "|" ; "¶" ) ]

               New Record / Request

               Set Field [ firstField ; Value: GetValue ( $curValues ; 1 ) ]

               Set Field [ secondField ; Value: GetValue ( $curValues ; 2 ) ]

               Set Field [ thirdField ; Value: GetValue ( $curValues ; 3 ) ]

               ...      // Repeat as necessary for the number of fields involved

               Set Variable [ $result ; Value: RightValues ( $result ; ValueCount ( $result ) - 1 ) ]

               Exit Loop If [ ValueCount ( $result ) < 1 ]

          End Loop

           

          HTH

           

          Mike

          1 of 1 people found this helpful
          • 2. Re: Create Records from SQL Query
            erolst

            I would do it essentially the same way as Mike, but not fiddle with the result variable:

             

            Set Variable [ $result ; Value: ExecuteSQL ( yourQuery ; "|" ; ¶ ) ]

            Go to Layout [ layoutBasedOnTableC ]

            Set Variable [ $recordCount ; ValueCount ( $result ) ]

            Loop

              Exit Loop if [ Let ( $counter = $counter + 1 ; $counter > $recordCount ) ]

              Set Variable [ $curRow ; Value: GetValue ( $result ; $counter ) ]

              Set Variable [ $curValues ; Value: Substitute ( $curRow ; "|" ; ¶ ) ]

              New Record / Request

              Set Field [ TableC::firstField ; Value: GetValue ( $curValues ; 1 ) ]

              Set Field [ TableC::secondField ; Value: GetValue ( $curValues ; 2 ) ]

              Set Field [ TableC::thirdField ; Value: GetValue ( $curValues ; 3 ) ]

              # [ For lots of fields, use a list of field names, an inner loop and Set Field by Name[] ]

            End Loop

             

            and if you have FM Advanced, maybe create a simple Custom Function where you define the array field delimiter; this way you don't have to remember it … and can write more explicit code:

             

            Set Variable [ $result ; Value: ExecuteSQL ( yourQuery ; ArrayFieldDelimiter ; ¶ ) ]

            Set Variable [ $curValues ; Value: Substitute ( $curRow ; ArrayFieldDelimiter ; ¶ ) ]

            • 3. Re: Create Records from SQL Query
              Mike_Mitchell

              If you need to preserve the result, then using a counter is preferred. OTOH, if not, truncating it as you go will be a little faster (especially if you have a lot of results).

              • 4. Re: Create Records from SQL Query
                erolst

                Mike_Mitchell wrote:

                truncating it as you go will be a little faster (especially if you have a lot of results).

                I would have expected the exact opposite – especially if you have lots of results (and a large variable).

                • 5. Re: Create Records from SQL Query
                  Mike_Mitchell

                  Not in my experience.

                  • 6. Re: Create Records from SQL Query
                    taylorsharpe

                    FYI, what you are really asking for is the ability for FileMaker to maintain temporary tables in memory to be searched.  This is commonly done on bit SQL servers and it would be nice if we could do that in FileMaker.  It would be great even if we could say do a SQL search on a virtual array.  But alas, that is not supported in the SQL we have right now other than creating a table and writing the data back to that table in the looping examples you have above.  I miss being able to use virtual tables for searching and it can make things a lot faster.  But it also gets into memory management and such things as creating virtual tables, but managing when to get rid of them out of memory, etc.  And that is probably why FileMaker has not done something like that so far.  I use a lot of SQL beyond the Select statement and maybe someday FileMaker will support calculation functions in ExecuteSQL beyond just the Select statement. 

                     

                    Mike and erolst both approach the same problem differently and will work.  erolst is probably the more traditional FileMaker way of doing things.  But the method Mike shows you is all in variables and is MUCH faster than writing things back to a table.  Also, doing things in arrays avoids issues of two people on the same system trying to write results back to the same table.  Mike's way is more multi-user friendly.  But erolst's method actually saves the data somewhere if you want to make further use of it or you want to permanently save that information. 

                    • 7. Re: Create Records from SQL Query
                      siplus

                      Besides the method illustrated (loop on result) you could retrieve the sql into a global, save to a text file in temp path and import the file.

                      • 8. Re: Create Records from SQL Query
                        Mike_Mitchell

                        That works. Or, you could use a virtual list in the same way.

                        • 9. Re: Create Records from SQL Query
                          Mike_Mitchell

                          Actually, there's a reason for it. The GetValue function starts at the top and works down. GetValue ( $var ; 1 ) means it only has to traverse one row. GetValue ( $var ; 27 ) means traversing 27 rows. So, as you might imagine, GetValue ( $var ; 43907 ) would be considerably slower.   

                          • 10. Re: Create Records from SQL Query
                            erolst

                            Taylor Sharpe wrote:

                            Mike and erolst both approach the same problem differently and will work. erolst is probably the more traditional FileMaker way of doing things. But the method Mike shows you is all in variables and is MUCH faster than writing things back to a table. Also, doing things in arrays avoids issues of two people on the same system trying to write results back to the same table. Mike's way is more multi-user friendly. But erolst's method actually saves the data somewhere if you want to make further use of it or you want to permanently save that information.

                             

                            Taylor –

                             

                            Mike's and my code is virtually (pardon the pun) identical, except for the method to control the loop.

                            • 11. Re: Create Records from SQL Query
                              BowdenData

                              A little off the original topic, but related to the looping. Geoff Coffey put out a custom function some time ago called "pop" that took the first value in a list (for processing) and then removed that value from the list all in one call to the CF. Just modularizing what Mike does in his script.

                               

                              The reason I bring this up is that Geoff said in his notes that this method would be slower than traversing the whole list (like Erolst) as you got into larger lists. I believe that Geoff tested it and said that for lists less than a few thousand values, it would not be an issue.

                               

                              Geoff had/has a reputation of of being pretty smart when it came to things like this, so I believe that either method would be viable. One would certainly not be much faster than the other.

                               

                              Doug

                               

                              Sent from my iPhone

                              • 12. Re: Create Records from SQL Query
                                Mike_Mitchell

                                Sounds like some benchmarking is in order ...

                                • 13. Re: Create Records from SQL Query
                                  siplus

                                  it's not just traversing the list, speed also depends on how many "Set field" you have inside the loop, after the New Record step. Import records might win IMHO, but as Mike said, some benchmarking might help, I'm all for it and I'll build something ASAP if given the source tables.

                                  • 14. Re: Create Records from SQL Query
                                    hangry

                                    Thank you very much for your help. It worked great.

                                    1 2 Previous Next