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.
So do you set this execute SQL statement to a variable with the Set Varialbe command?
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 )
Can you check out the beginning of my script to see if I'm on the right track before the loop?
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'"; ""; "" )
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.
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!
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!)
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.
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.
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!