You can use import record to copy blocks of records from one table to another in one step. In the process, the import step can split the repeating fields into individual records. Thus, you can copy the data in one or many records all in one step.
You should never copy data to the clipboard in order to move it somewhere else. That destroys any data the user might have copied to the clipboard previously. Instead, set a variable to the value of the field in place of copy and then use set field to move the data from the variable to the target field in place of paste.
Jeez... how can I interpret what you write here... You should know, I'm a user since 2.1. So, this file dates back to 1993 or so. Layout is like this:
Productname - product number - quantity
raw material 1 - raw material name 1 - percentage 1 - RM quantity 1
raw material 2 - raw material name 2 - percentage 2 - RM quantity 2
And I want to go to a file like this with one hit at a button:
Product number - product name - quantity - raw material 1 - raw material name 1 - RM quantity 1
Product number - product name - quantity - raw material 2 - raw material name 2 - RM quantity 2
I'm not sure I understand you if you say: copy blocks of records from one table to another in one step. It's the structure of both files that confuses me I guess, or FM made something nice up in the mean time.
Oh, and file #1 is one record, file #2: each line is a record.
And that's exactly how I understood things to be. Since FileMaker 3.0, Import records comes with an option to split repeating fields in the source table into individual records in the target table. Presumably, this was done as a way to make it easy to phase out the use of repeating fields by replacing them with a related table of individual records. But it will do what you describe needing to do in this thread.
The process works both manually and via a script.
Let's say you want to copy the data from one record in your recipe table into multiple records in the log table, one record for each set of parallel repetitions in your repeating fields. (First target record get's the first repetition of each of the repeating fields, the second target record gets the second repetition of the same fields...)
First, isolate that one record in a found set of a single record. You can perform a find or show all records, omit the record you want to copy from and then show omitted only.
Then, (if manually importing), go to a layout based on the target table and select import records. Select the very same file that you already have open if both source and target tables are in the same file. Use the field mapping dialog to align the fields from which you want to copy data in the source table to fields in the target table. When you click the import button, a small funky dialog appears asking you two questions. One asks if you want to split the data in repeating fields into separate records. Select this option and import your data.
The result will be that the data from the one record in your recipe table has been copied into up to 30 individual records in your log table.
The number of steps is much shorter than your current script and the above steps are nearly the same for either a manual or scripted use of Import Records to copy over the data. (In a script, you don't have to change to a layout based on the target table.)
Yeah, just found it, sorry I let you write another reaction, we just crossed each other.
I was just trying, following the script, connecting all the variables and found the 'write away as seperate records' option, ran the script and here it is...
The only thing I need to do now is to get rid of the empty records now.
Many many thanks.
A script can perform a find and delete them. But when I use this method (have inherited a DB where student attendance data is stored in repeating fields and have to split the data up in order to do analysis of it.) I don't get blank records. If the repetition is empty, no corresponding record is created in the target table.
To find a record where a given field is empty, enter find mode and put a lone = operator in the field. (You probably know this already). This can be a manual or scripted operation.
Yeah, I already had this step. Is quite easy... Just need to be careful now, I have for example three recipes under the same product code. I need to copy only the actual record. Find/omit could be dangerous. (I might use wrong terms, I use dutch version...)
Apologies for stating the obvious but "three recipes under the same product code" does not sound like a good thing. You need some sort of ID that uniquely identifies each recipe so that you can treat them differently from each other. That can be a field other than the product code. (Perhaps your recipes produce the same product but substitutes different ingredients such as using different forms of sugar for different recipe variations of the same product?)
Yes I know. These are older versions of the same recipe.
I'm trying to look if I can find something to select only the actual record, if not, I must get rid of the doubles.