Duplicating a single record in FileMaker is simple. 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.

For 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. 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.

I chose to use FileMaker Pro 12’s native SQL to build arrays with field/value pairs. 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. However, once I constructed the, um, pseudo-arrays I could loop through the array values and set my new records.

Although 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. 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. 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). If field names change or you want to add more fields, the SQL needs to be updated. Also, in regards to your own fields, you need to be aware of any reserved names which will cause the SQL to fail.

Download example file Tickets.fmp12.

The Related Table Query

To begin with, I set a variable with the original ticket number so I could message the user after the script finished. Then I used SQL to select the fields for each related table, and set my array. The example from the time table shows this format. I concatenated the field name and the value from the field. For number, date, and time fields I found that I needed to use the double pipe concatenation symbol instead of the plus sign; using the plus sign for these non-text field types caused some strange behaviors, usually stripping out the value.

In order to later parse out the values from my pseudo-array I added “^” as the field separator. I picked this character I didn’t anticipate it to exist in any field, unlike commas. You may notice the row separator uses a tilde or ~ symbol, instead of a pilcrow or return symbol which is the default separator. The reason for this is explained below in the script parameter section for the subscript the creates the new records.

Let([
query = "
SELECT
'" & GetFieldName ( TIME_ENTRY::NameFirst ) & "|'+namefirst,
'" & GetFieldName ( TIME_ENTRY::NameLast ) & "|'+namelast,
'" & GetFieldName ( TIME_ENTRY::TimeBegin ) & "|'||timebegin,
'" & GetFieldName ( TIME_ENTRY::TimeEnd ) & "|'||timeend
FROM time_entry
WHERE id_ticket = ?";
result = ExecuteSQL( query ; "^" ; "~" ; TICKET::ID )
];
result
)

With my result sets in hand, I duplicated the ticket with the “Duplicate Record/Request” script step. Since I used Get (UUID ) for my primary table ID I had to uncheck the “Do not replace existing value of field (if any)” check box, otherwise the UUID would also get duplicated.

Alternate Duplicate Record Option

If you do not use the “Duplicate Record/Request” you can perform the same action using New Record/Request. First, you would need another SQL statement, grabbing only select items from the record to be duplicated.

Let([
query = "
SELECT
'" & GetFieldName ( TICKET::TicketDescription ) & "|'+ticketdescription,
'" & GetFieldName ( TICKET::TicketName ) & "|'+ticketname,
'" & GetFieldName ( TICKET::ID_Customer ) & "|'+id_customer
FROM ticket
WHERE id = ?";

result = ExecuteSQL( query ; "¶" ; "" ; TICKET::ID )
];
result
)

In order to set the fields you loop through the SQL result, adding pipe separators between the fields (or another separator that later can be switched). With my field/value pair I could use the “Set Field by Name” script step.

Set Variable [$itemCount; Value: ValueCount( $ticketResult )]
Loop
  Exit Loop If [Let ( $i = $i + 1 ; If ( $i > $itemCount ; Let ( $i = "" ; True ) ) ) ]
  Set Variable [$row; Value: GetValue( $ticketResult ; $i )]
  Set Variable [$field; Value: GetValue( Substitute( $row ; "|" ; "¶" ) ; 1 ) ]
  Set Variable [$row; Value: GetValue( Substitute( $row ; "|" ; "¶" ) ; 2 )]
  Set Field By Name[ $field; $value]
End Loop
Commit Records/Requests []

After Parent Record duplicated

Next, I set variables with my new ID and ticket number. The new ID would be used in the subsequent related records. I called a subscript for each related table, passing in the ticket ID, the layout of the related table, and the SQL result for that related table’s values, which is spelled out in the example file. In the subscript I used a return delimited set of values for the script parameters. To address the point raised above with the tilde separator, if I had used the default return delimiter between rows in my ExecuteSQL my script parameter method would strip out everything but the first row or record. To prevent this I used the tilde, and then in the subscript added back the pilcrow/return character as the row separator. To validate the values in my Data Viewer as I tested the scripts I have this in two actions.

Set Variable [$SQL_Lines; Value: GetValue( Get( ScriptParameter ); 3)]
Set Variable [$SQL_Result; Value: Substitute( $SQL_Lines ; "~" ; "¶" )]

If you use a different method of passing multiple parameters, you just adapt the SQL and subscript to handle this. My goal with the example file is to keep it simple with no custom functions.

The subscript, “Duplicate_RelatedTable”, goes to the relevant layout, then uses a nested loop to create the record and set the values. Since the ticket’s time entry portal might contain multiple values, I needed to create a new record for each one, and set the relevant values from the previous parent record. Since I used the same Foreign key naming convention this let me use Set Field by Name for the Ticket ID in the related tables.

Go to Layout [$Layout]
Set Variable [$itemCount; Value: ValueCount( $SQL_Result )]
Loop
  Exit Loop If [Let ( $i = $i + 1 ; If ( $i > $itemCount ; Let ( $i = "" ; True ) ) ) ]
  Set Variable [$row; Value: GetValue( $ SQL_Result; $i )]
  New Record/Request
  Set Variable [$ForeignID; Get ( LayoutTableName ) & "::ID_Ticket" ]
  Set Field By Name[$ForeignID; $TicketID // from script parameter ]
  Set Variable[ $record; Value: Substitute( $row ; "^" ; "¶" ) ]
  Loop
    Exit Loop If [Let ( $j = $j + 1 ; If ( $j > ValueCount( $record ) ; Let ( $j = "" ; True ) ) )]
    Set Variable[ $data; GetValue( $record ; $j ) ]
    Set Variable [$field; Value: GetValue( Substitute( $data ; "|" ; "¶" ) ; 1 ) ]
    Set Variable [$row; Value: GetValue( Substitute( $data ; "|" ; "¶" ) ; 2 )]
    Set Field By Name[$field; $value]
  End Loop
Commit Records/Requests []
End Loop

The counter is handled within the Exit Loop condition. As there are multiple loops I wanted to make sure I cleared out all the values of the counter variable after each section within the loop completed.

With all this in place, the user only needs to click on the “duplicate record” button, and the script runs. Once done they see a message as to which ticket number was duplicated and the new ticket number. Success!