10 Replies Latest reply on May 24, 2012 5:47 AM by MichaelHemberger1003

    Need Script to Loop thru  a table and check each field

    MichaelHemberger1003

      Title

      Need Script to Loop thru  a table and check each field

      Post

      Can anyone help me script the following:

      Loop thru a table with all fields in table on one layout and store result in a different table

      Starting with the first field of the first record in the main looping table:

       

      (1) Check the field(numeric) for a value greater than 0

      If the field value is greater than 0

      Create a new record in a different table

      copy the field value that was greater than zero into the new record in the different table

      copy the fields name also into the new record in the different table

      Now get the next field (looping table) after this one and copy it's value also into the new record.

      end if

       

      Check to see if t there are no more fields in the first record of the looping table.

      If there are more fields then loop back to (1)

      If there ar NO more fields then get then next record, unless were out of records in the looping table

      If there are more records then get the next record and start looping the all the fields.

        • 1. Re: Need Script to Loop thru  a table and check each field
          Dillik

          Hopefully you can take care of the part where the script goes through a loop and increments a counter script variable until some Exit Loop If criterion is met; if not, I expect there are others here who will explain it in patient detail.  I just wanted to point out the ExecuteSQL function: it was introduced in FMP12, and it's helpful in some unexpected ways; one of them would be to get a dynamic list of all fields in the table you're interested in, using the native table FileMaker_Fields.  Here's a sample formula:

          ExecuteSQL ( "select FieldName from FileMaker_Fields where TableName = 'My Table Occurrence'"; ""; "" )

          Where I put 'My Table Occurrence', you could put your own table occurrence name (with single quotes, which help in the event of names with spaces in them).  If you wish to use a base table name instead (as defined in the first pane of the Manage Database dialog), you can do so using BaseTableName in place of the term TableName.

          Anyway, this should let you establish a variable containing a list of all fields.  (If you don't have FMP12, you could still make a similar list, but it would be hand-typed and hard-coded into a variable instead of dynamically generated via ExecuteSQL.)  Your script loop can perform a GetValue on this list to do something with each field, one at a time, depending on the value of your counter script variable.  You could use that value to check the field contents (using Evaluate), and you can use it to populate the Field Name field in that other table you want to auto-create records in.

          • 2. Re: Need Script to Loop thru  a table and check each field
            MichaelHemberger1003

            So do you set this execute SQL statement to a variable with the Set Varialbe command?

            • 3. Re: Need Script to Loop thru  a table and check each field
              Dillik

              Yes, exactly.  Much of the functionality of the script will come from using Set Variable to assign different variables.  You might have variables as follows (feel free to name them as you wish):

              $AllFieldNames -- set equal to the ExecuteSQL formula above, once, outside the start of the loop

              $Counter -- set it once to 1 outside the start of the loop, then increment it once per pass, and check it with Exit Loop If

              $ThisFieldName -- set it each pass using GetValue ( $AllFieldNames; $Counter )

              $ThisFieldContents -- set it each pass using Evaluate, such as Evaluate ( "My Table Occurrence::" & $ThisFieldName )

              • 4. Re: Need Script to Loop thru  a table and check each field
                MichaelHemberger1003

                Totally Excellent!

                Can you check out the beginning of my script to see if I'm on the right track before the loop?

                New Window[Style:Document]

                Freez Window

                Go to Layout["InspectionTest"(Student_Info_Main)]

                View As [View As Form]

                Go To Record/Request/Page [First]

                Set Variable [$AllFieldNames;Value:ExecuteSQL ( "select FieldName from FileMaker_Fields where TableName = 'Inspection_Invoice'"; ""; "" )

                 

                • 5. Re: Need Script to Loop thru  a table and check each field
                  Dillik

                  It seems passable, but you've opened a window to a layout based on the table occurrence Student_Info_Main, and yet you've set $AllFieldNames to all field names from the Inspection_Invoice table occurrence.  Did you mean for them to be different table occurrences?  You might have a context problem when each pass of your loop tries to set $ThisFieldContents if it isn't on an Inspection_Invoice layout at that step of the script (each pass).

                  Also, don't forget to initialize your $Counter before the loop starts.

                  • 6. Re: Need Script to Loop thru  a table and check each field
                    MichaelHemberger1003

                    Your right about that Student_Info_Main......

                    That was a holdover when I was first trying to figure this out.

                    I'll be changing that.

                    And I didn't forget about the counter. Just didn't show it.

                    Ok.. thanks so much!

                    I'm going to try it all out!

                    • 7. Re: Need Script to Loop thru  a table and check each field
                      Dillik

                      Good luck!  Remember you can always use Command-period on Mac or [insert helpful keyboard shortcut here] on Windows to stop a rogue script if it's looping out of control.  (Remember your Exit Loop If: it should be checking whether your $Counter is greater than or equal to the ValueCount of $AllFieldNames!)

                      • 8. Re: Need Script to Loop thru  a table and check each field
                        MichaelHemberger1003

                        I'm having one problem yet.

                        The Set Field command (and yes I'm using commit after) isn't passing the value (im using one of the $ variables) into the field.

                        I ran the debugger and the variable definetly has a value, but as I watch it in the debugger, I can see that the value doesn't pass over into the field.

                        • 9. Re: Need Script to Loop thru  a table and check each field
                          Dillik

                          I'm not completely sure why you're doing certain things here, like exiting the loop when the counter is 1, or getting two field names at once, but I'll assume you know what you're doing there for debug purposes.

                          Anyway, the set field problem is because you're using the repetition area when you should be using the "calculated result" area one dialog back.  The Set Field step isn't laid out exactly like the Set Variable step.

                          • 10. Re: Need Script to Loop thru  a table and check each field
                            MichaelHemberger1003

                            its my inexperience with the Set Field command.

                            Yea I was doing some odd things for the debug like setting the counter to 1 , so it would just run once for testing purposes.

                            Thanks again as it now works and your suggestion were so excellent. You really saved my bacon on this one!