![]() To prevent this I used the tilde, and then in the subscript added back the pilcrow/return character as the row separator. 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. In the subscript I used a return delimited set of values for the script parameters. 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. The new ID would be used in the subsequent related records. Next, I set variables with my new ID and ticket number. With my field/value pair I could use the “Set Field by Name” script step. 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). Result = ExecuteSQL( query "¶" "" TICKET::ID ) '" & GetFieldName ( TICKET::ID_Customer ) & "|'+id_customer '" & GetFieldName ( TICKET::TicketName ) & "|'+ticketname, '" & GetFieldName ( TICKET::TicketDescription ) & "|'+ticketdescription, First, you would need another SQL statement, grabbing only select items from the record to be duplicated. If you do not use the “Duplicate Record/Request” you can perform the same action using New Record/Request. 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. With my result sets in hand, I duplicated the ticket with the “Duplicate Record/Request” script step. Result = ExecuteSQL( query "^" "~" TICKET::ID ) '" & GetFieldName ( TIME_ENTRY::TimeEnd ) & "|'||timeend '" & GetFieldName ( TIME_ENTRY::TimeBegin ) & "|'||timebegin, '" & GetFieldName ( TIME_ENTRY::NameLast ) & "|'+namelast, '" & GetFieldName ( TIME_ENTRY::NameFirst ) & "|'+namefirst, The reason for this is explained below in the script parameter section for the subscript the creates the new records. ![]() You may notice the row separator uses a tilde or ~ symbol, instead of a pilcrow or return symbol which is the default separator. I picked this character I didn’t anticipate it to exist in any field, unlike commas. In order to later parse out the values from my pseudo-array I added “^” as the field separator. 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. I concatenated the field name and the value from the field. 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. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |