6 Replies Latest reply on Oct 2, 2014 7:48 AM by lamine_d

    Looping in a portal and insert data into a table in SQL

    lamine_d

      Hi there,

      I have a portal in FileMaker 13 and would like to transfer its data to an SQL table using ODBC.

      I have the following script, it does work but only for the first record, it does not go to th next records in the portal.

      I have passed the values of the Portal as Parameters in the script (List(SelectedCustomProperties::CustomPropertyId;SelectedCustomProperties::CustomPropertyName;SelectedCustomProperties::CustomPropertyDescription)

      Go to Object [Object Name: "SendToSQL"]

      Go to Portal Row [Select;First]

      Loop

      Go to Layout["SQLTable Layout"] -> this layout is actually the table from SQL but coonected through ODBC

      New Record/Request

      Set Field(SQLTable::PropertyId;GeValue(Get(ScriptParameter);1;)]

      Set Field(SQLTable::PropertyName;GeValue(Get(ScriptParameter);2;)]

      Set Field(SQLTable::PropertyDescription;GeValue(Get(ScriptParameter);3;)]

      Go to Portal Row [Select;Next;Exit after last]

      End Loop

       

      Am I missing anything?

       

      Your help is much appreciated.

      Regards

      Lamine

        • 1. Re: Looping in a portal and insert data into a table in SQL
          DavidZakary

          Your script looks like you're leaving the layout with the portal on it. You won't be able to grab values from the portal.

           

          Instead of walking the portal rows, why not use go to related records to isolate the related records on a layout based on the same TO as the portal, collect the data into variables (or method of your choice), go to your SQL table and create your records.

          • 2. Re: Looping in a portal and insert data into a table in SQL
            lamine_d

            Thank you for your prompt reply.

            Could you please give me a rough idea by script,

            Thank you.

            • 3. Re: Looping in a portal and insert data into a table in SQL
              wimdecorte

              lamine_d wrote:

               

              (List(SelectedCustomProperties::CustomPropertyId;SelectedCustomProperties::CustomPropertyName;SelectedCustomProperties::CustomPropertyDescription)

               

               

              This does not create an array, or a 3 column list, but one long list with first all values from CustomPropertyId, followed by the values from CustomPropertyName and then the values from CustomPropertyDescription

               

              So when you do this:

               

               

                   Set Field(SQLTable::PropertyId;GeValue(Get(ScriptParameter);1;)]

                   Set Field(SQLTable::PropertyName;GeValue(Get(ScriptParameter);2;)]

                   Set Field(SQLTable::PropertyDescription;GeValue(Get(ScriptParameter);3;)]

               

              GetValue 1 will always get you the first value from CustomPropertyId

              GetValue 2 will always get you the 2nd value from CustomPropertyId

              GetValue 3 will always get you the third value from CustomPropertyId

               

              It will do that for all records in your loop.

               

              So what you need is 3 lists, one for each field

              and your GetValue() call has to call the nth value from each list, n being the iterator in your loop.

               




              • 4. Re: Looping in a portal and insert data into a table in SQL
                beverly

                +1 on this. GTRR is your friend.

                 

                -- sent from myPhone --

                Beverly Voth

                --

                • 5. Re: Looping in a portal and insert data into a table in SQL
                  ch0c0halic

                  Sounds like you have a related set of records showing in a portal and you want that set of records imported into an SQL version of that related table. If this is correct then its relatively easy.

                   

                  Create a Layout for each TO (Table Occurrence) used in the script, 3 total. You already have the first one, main table, and probably the second, portal records, and maybe the third, SQL table.

                  Use a Go To Related Records from the main table to the related portal records. This should result in a found set on a different layout showing the related records from the portal.

                  Change layouts to the one attached to the SQL table. (Not strictly required but it helps to see the results).

                  Perform an import from the TO of the related table to the TO of the SQL table.

                   

                  So in three script steps you have transferred the portals related records into the SQL table.

                   

                  GTRR showing on layout of the related table.

                  Change Layout to SQL table

                  Import from TO of related table to SQL TO

                  • 6. Re: Looping in a portal and insert data into a table in SQL
                    lamine_d

                    Thank you all for your help.

                    I managed to get it to work