Converting repeating field to separate records

[merkaba22]

In a record having a name field and a repeating field (say 5 repetitions), is there a way to create five records having the (same) name and one of each of the 5 repetitions.

[comment]

Are you talking about a one-time conversion or an ongoing process?

[Fenton]

Yes. But it invvolves using another table (which can be created on the fly, using the Import command). Choose your own file/table as the source to Import. Drop down the list of "destination" tables. At the bottom is "Create a new table"; don't worry about the name, you can change it later. Select only those repeating fields as the fields to import, AND the primary ID of the parent table. (If you don't have one, then you need to create and populate one before this; but that's another post.)

After you hit the button to Import, a small 2nd dialog will appear, with 2 radio buttons, "keep repeats in same record" and "split into new records" (something like that). You want to Split.

You now have a 2nd table with your repeats split into separate records, with the ID of their parent record.

If you want a primary ID field in that child table, now is the time to create and populate it, before creating any new records.

Tie the new child table to the parent table, on the Relationship Graph, using the ID (of the parent to child foreign key). You can also turn on "Allow creation of related records" (on the Child side!), ditto for "Delete related records" (if you want, and you likely do).

Change the repeating fields on the parent layout to target the new child table. They will only show as 1 "row" now (as the new fields are NOT repeating). Surround the 1 row with a Portal, based on the relationship to the child. Adjust size, send behind the fields (or bring them to the front). The result will look almost exactly like your old repeats.You're mostly done; but I usually put a "trash can" icon in the portal, using the script step, "Delete portal row" (otherwise it's a little dangerous to delete a row, too easy to screw up and delete the parent record by mistake).

P.S. The old repeats are now obsolete. They can be deleted, or just marked as obsolete. Adjust Import orders (if any) set in other scripts, if the fields were deleted.

Edited March 6, 2010 by Guest

[bruceR]

Shouldn't this be moved to an appropriate topic?

Prominently stated at the head of the page:

"This forum is for you to post your articles, tips, tricks & techniques.

Please don't use this forum for requests for "how to..." or help requests."

[merkaba22]

Fenton, that is absolutely brilliant -- thank you so much.

[Lee Smith]

Bruce,

I'm wondering how this could be showing in the Articles, Tips, Techniques & Solutions topic for you to see at 03/06/10 09:05 AM when I had moved it on 03/05/10 06:40 PM to this location.

Lee

[bcooney]

Lee, I moved it.

[Lee Smith]

Okay, now I see what happened, I moved a different one by merkaba22 from the same topic area.

[merkaba22]

I am now looking over the methodology and I am finding that the two radio buttons that offer the chance for "splitting them into separate records" is grayed out.

Is there another setting that needs to be addressed prior to this point that I am missing?

[merkaba22]

I think I sorted it out -- thanks:)

[LeahGrrl]

My radio buttons are also grayed out when I follow these instructions (and the similar ones I found on FileMaker's knowledge base). What am I doing wrong?

My initial field is a list of pages separated by commas ("20, 22, 29"). I performed Substitute and replaced commas with paragraphs; now the field has a list

20

22

29

and then followed the instructions here.

Something wrong with the list created? I tried creating using the ASCII 29 code (group separator) instead of paragraph and got the same result: grayed-out radio buttons in the Import Options window. Many, many thanks for any help--I've been struggling for 2 hours.

[comment]

You need an actual repeating field, not a delimited list. If you already have a field with a return-separated list, you can define a repeating calculation field =

Let ( [

i = Get ( CalculationRepetitionNumber ) ;

v = Extend ( YourField )

] ;

Case (

i ≤ ValueCount ( v ) ;

GetValue ( v ; i ) 

)

)

Give the field enough repetitions to cover the worst case, and import it instead of YourField.

[bdarch]

fenton - thanks for posting this excellent workflow for converting repeating fields into separate records.

question: when you say "Choose your own file/table as the source to Import" do you mean import the file into itself?

when I try this I get a message saying the maximum number of users has been exceeded and the application shuts down. the file is being served up by fmpserver.

[Fenton]

I said "file/table," because I didn't know whether you were wanted to create a new table in the same file as the table with the repeating fields; same file is usually the case (since FileMaker 7). But another file would be OK also.

If the files are being hosted then you certainly would want to choose Open Remote to target the "other" file (even if it's the same file). If you are opening it via file system sharing; well, that is about the worst thing you can do to a poor FileMaker file; well maybe not the worst, but all too common; it can eventually cause corruption to the file.

I know, if you're ON the Server computer, also running FileMaker Pro, then it's tempting to just use Open; but wouldn't you get the "this is a hosted file" error in that case? That "too many users" error message can come up for other reasons.

Edited June 7, 2010 by Guest