2 Replies Latest reply on Sep 3, 2012 11:20 AM by rmutsaers

    Using ExecuteSQL results to create new records




      I'm creating an app where i need to get some information based on search criteria and based on the result create some records for each result.



      ExecuteSQL("select city, count(customerID) from customers group by city" ;"";"")


      this results in a field that has something like


      Amsterdam, 10

      Rotterdam, 15

      Groningen, 5

      Breda, 12


      Now for each of these result rows i want to create a record in a summary table

      so i need to loop through the result rows.


      How can i do that in a FM Pro script.


      In regular SQL this would be something like 'INSERT INTO summary SELECT city, COUNT(customerID) FROM customers GROUP BY city'

      But how do you do this in FMPro.




        • 1. Re: Using ExecuteSQL results to create new records

          Hi there.

          I've done something similiar with an execute SQL statement.

          I generated my list into a variable ($list).  I selected student IDs, SQL statement, I simply genereated this list of student IDs that met my criteria.

          Then I went to that new table and looped through, creating a new record for each row in $list. I used a counter variable $i and at the end of the new record creation, I added 1 to $i to count the number of loops, and then exited the loop if $i was greater than the ValueCount ($list)  The $i counter variable was also used to extract each row from the $list variable:


          Something like this:


          SetVariable $List:: Value (ExecuteSQL statement)

          SetVariable $i:: Value:1

          Go to Layout (newtable)



          SetField(newtable::field Value: GetValue($List;$i)

          SetVariable: $i  Value $i+1

          Exit Loop If $i > ValueCount($List)



          Im' sure there are better ways, but I've used this now and its extremely helpful.

          • 2. Re: Using ExecuteSQL results to create new records

            Works perfectly. Trick was indeed to treat the result as a series of values and when i have an individual record i just replace the field separator ( " , ") in my example with a ¶ to create another 'subset' of values where i can retrieve each field using another GetValue function.


            For now i'm saved ....

            Thanks a bunch