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

    Using ExecuteSQL results to create new records

    rmutsaers

      Hi,

       

      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.

      e.g

       

      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.

       

      Thanks

      Rick

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

          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)

          Loop

          newRecord/request

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

          SetVariable: $i  Value $i+1

          Exit Loop If $i > ValueCount($List)

          EndLoop

           

          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
            rmutsaers

            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