Script to copy values from repeating fields

April 23, 200322 yr

Hi,

I've made a big mistake, creating some repeating fields in a related file, I've after reading and wanting to make use of that data I entered, I've found that I should have created a portal on my main layout, and just put in the fields on the portal with any repeating. My related file is called Job Notes, and is used to hold each job history about a customer, for each visit, that is why i created the repeating field, but after reading these forums and a book that I bought, I know know this is not how I should have set it up for my needs.

So I would like if possible to create a one-time script that will search the related file for each of my 600+ customers i've entered so far, and have it copy each different repeated value into its own record. The repeating fields ive used in the related file are Truck, TimeIn, Timeout, JobDate, InvoiceNumber, WorktobePreformed, Contact ID, Crew, and Job Notes.

I have a realation between Contact ID and my main layout's Contact ID. If it is possible to create a script to do this, I think I will need to use a loop, but am not sure how to script certain things, like to copy/check the repeating fields 1st set of data, second set... and so on, into a new record in the related file, so I can get change the fields from repeating to normal. there are no more than 20 possible repeating values in each record in the related file, most only have 1 value in each field which is fine, and dont really want to doing anything with the ones that only have one value in the repeating fields, but some have around 2 or 3, and sometimes more, just a couple have close to 20 values in a repeating field, and would like to take those 2,3,4 values from each repeating field that may have it can create a new record.

Thanks for the help in advance, i'm still new to FM pro, but am learning, and Think I've just learned a big lesson on what to use and not use for certain things. if anything is not clear, just let me know, and I can try to describe it better it is not clear.

Thanks Greg

April 23, 200322 yr

Hi,

Good news you want to switch to a better relational structure.

But...

So I would like if possible to create a one-time script that will search the related file for each of my 600+ customers i've entered so far, and have it copy each different repeated value into its own record. The repeating fields ive used in the related file are Truck, TimeIn, Timeout, JobDate, InvoiceNumber, WorktobePreformed, Contact ID, Crew, and Job Notes.

...check that some of these fields may not need to be stored in different files, depending on your final structure.

Basically, you will need to use the Get Repetition function for your script.

April 23, 200322 yr

Here is way to say goodbye to repeating fields.

1. Make clone of your file and give it a new name,

2. In clone, delete all fields except a uniqueID field and the repeating fields

3. Still in clone, change the repeating fields to non-repeating

4. Now set clone to import all records from your main file, but in the message box (after the import dialog) choose the option to:

Import values in repeating fields by:

* Splitting them into separate records

5. Now go back to your original file and delete all the repeating fields

6. Create a relationship to the new file matching the uniqueID fields

Once all this is done, the data from your repeating fields will be in a related file. You can then put portals on the layouts to display the data (which used to be in the repeating fields).

Of course to backup your file before you start this change.

April 23, 200322 yr

Author

Thanks that worked great, I made a copy, and deleted all records, in the Job Notes related file, and imported from my master copy, and had it split them, I was trying this last night, but the splitting option was grayed out, but was available after doing the process you stated. I know have many more records, and everything looks good as far as I can tell.

Thanks.