1 of 1 people found this helpful
Do you know how to create a script in FileMaker? A script could use two nested loops, one to loop through the records and one to loop through the comma separated values in the project_code field, creating additional new records from any listed codes added after the first and then removing those extra codes from the original record.
Your example only shows two codes, is that always the case? If the number of codes were always two, you might be able to use an extra table and manually export/import this data to generate the needed records. But if there can be large numbers of listed codes, this won't be very practical.
Thank you for the reply.
Yes, I am beginning to understand FileMaker scripting. Would you recommend using the script to write to new fields or even new fields in a new table? Being very novice, I fear munging the table. To more clearly state, the goal is to move each project_code to a new row while also writing the project_id to that row. The record_id is easy enough to auto increment.
Yes, the project_code field always has only two 5 digit numbers. Always comma separated.
As long as the project_code field has exactly two comma separated numbers, this script will do the job.
The sort step sorts the records by record_id -- important for the logic of where the 'next' record will be relative to the duplicate.
Sample file attached.
gisguy.fmp12.zip 68.0 K
I fear munging the table.
This is what back up copies are for. Make a copy of your file just before you try any thing that modifies large numbers of records if one is not made for you automatically (as can be scheduled by hosting from server). If you munge the table, you can replace the current field with the back up, or import the data from just this one table from the back up to the current file.
backup, Backup, BACKUP! before doing anything that changes data on a massive scale.
Thank you David.
This worked perfectly.
And will be useful in my continuing to build understanding of FileMaker and scripting.
And thanks to all whom responded. Appreciated!