The example from the time table shows this format. Then I used SQL to select the fields for each related table, and set my array. To begin with, I set a variable with the original ticket number so I could message the user after the script finished. Also, in regards to your own fields, you need to be aware of any reserved names which will cause the SQL to fail.ĭownload example file Tickets.fmp12. If field names change or you want to add more fields, the SQL needs to be updated. For the sake of this example, I elected to keep the syntax in the SQL statement fairly simple (no custom functions, although the GetFieldName function accounts for any Table Occurences name changes). Any changes to fields then need to be made inside the SQL statement, although with a little more programming my solution could conceivably adopt the field storage option. However, once I constructed the, um, pseudo-arrays I could loop through the array values and set my new records.Īlthough it’s quite possible to set up an interface where you list all fields in related tables that need to be duplicated, I chose to set my field names within the SQL statement. Unlike a language like PHP, which has a built in logic for handling arrays, in FileMaker these have to be constructed using certain text-character separators, and therefore are not iron-clad arrays. I chose to use FileMaker Pro 12’s native SQL to build arrays with field/value pairs. Rather than create the related records from scratch each time, we needed a fast and simple method to duplicate key elements from each related table, and at the same time retaining the relation information from the newly created ticket or parent table. However, what if you are faced with duplicating not just a record, but all related child records? I wanted to accomplish this without modifying the graph, and without looping through records to build my data sets.įor my customer’s development project, I had a table of tickets with associated time, material, and other related records where sometimes only minor changes to a big ticket were needed. Duplicating a single record in FileMaker is simple.

Filemaker pro 12 vs access