Importing into Repeating Fields

[Chris.Nelson]

Is there a way to import into repeating fields?

[LaRetta]

If it is regular data, you don't want to. If it is Developer-used fields, you won't need to.

I realize that seems abrupt but it's the truth - you will be perpetuating a poor structure. Now is the time to change it so it's right. If you would like assistance correcting it, we will all help you!

LaRetta :wink2:

[Chris.Nelson]

Are you thinking I should use individual fields or related fields and create a separate database for that?

[LaRetta]

It would help to understand what those repeating fields contain but usually they would contain LIKE data. That indicates that they should be records in another table and only ONE field, which are related to the record that repeating field is attached to. It should be easy to do - import into another table, being sure to also import the unique ID. And specify how to handle the repeating fields - by splitting them into records when asked.

As always, we recommend backing up first. And if you explained the data and what that repeating field contains (in relationship to the record), I would feel better about making a suggestion.

LaRetta

Edited August 13, 2007 by Guest

[LaRetta]

And yikes, Chris! Where are my manners? Welcome to FM Forums!!! :laugh2:

[Chris.Nelson]

Cool. It is a simple tracking database for orders. It already links to the Company DB, Event DB, Price DB, etc. I wanted multiple items in the order but didn't think it was worth a separate DB. I didn't want multiple items in different fields (Item 1, Item 2, Item 3, etc) because it makes searches tough....so, I used a repeating field. Do you think I should use a related DB for the items? Same thing with payments? I currently use a repeating field for that as well.

[Chris.Nelson]

And Thanks...I hope this Forum makes me stronger...

[LaRetta]

I am soooooo glad I spoke up.

Yes, they should be split.

Your items on the invoice should be in a LINEITEMS table - as separate records. And the same with payments. If they are records, you will 1) never hit a limit on how many lineitems (or payments) can be added to an invoice and 2) searching will always be piece of cake because you are searching one field. There are many, many more reasons it is powerful to have them as records in a related table; including the ability to add date paid, check number etc to each payment; there is a long list of reasons ...

Edited August 13, 2007 by Guest

[LaRetta]

As for FM Forums making you stronger ... indeed it shall. It is the BEST FileMaker training tool there is. Everything you will need is at your fingertips either to search or type a question.

Welcome!

[Fenton]

Yes, welcome to FMforums. Notice that LaRetta is saying "table". Since FileMaker 7 we can have multiple tables within 1 file. That is always the best way to do line items; NOT a new file.

I can't tell which you're thinking of; the term "database" is a general word for the concept of, well, a database. But it has almost no specific meaning as far as structure goes. It is better to use words like "file" or "table."

[Chris.Nelson]

Cool. THANKS...yes I mean Table instead of a separate DB file. Okay. Will do. I was thinking I already had a ton of Tables...but it truly does make it more flexible.

[Fenton]

Also, I see you have FileMaker Pro Advanced. Just copy those repeating fields, paste them into a new table. Also paste the primary ID of the original table (OrdersID?) into the line items table (where it will be a foreign key). Then remove their repeats, in their field definitions (in OrderLineItems, not Orders yet).*

Go to the Relationship Graph. Create a relationship between Orders and OrderLineItems (should be there on the graph now), based on OrderID; you might want to turn on [x] Allow creation of related records, and [x] Delete related records; on the OrderLineItems side (important to get the side correct -!!

Now, go to your (silly) old repeating fields on the original layout (duplicate the layout for safety, then go back to the original). In Layout mode, double-click each repeating field, and assign it to its new related one. You will then have just 1 row of fields.

Drag a portal around it, based on the OrderLineItems relationship. Send it to the back. Size it to fit the fields. Give it some rows, to fit the space. Presto, relational database -] Looks much the same, acts the same, but is the real thing.

Show All Records in Orders. Go to your new OrderLineItems table. Import records from Orders, only the repeating fields and the OrderID. When it asks, "Do you want to split repeating fields into records?" say, Yes indeedy.

All your repeating data is now properly in the line items table. You can delete the repeating fields from the Orders table.

Save a Copy of your file before starting this process. Good idea to Save a Copy after each major operation; if successful. So you don't have to start over if not.

* I guess you could do this even faster by using the New Table option in just an Import.

Edited August 13, 2007 by Guest

[Chris.Nelson]

Okay. Done. I have Tables for payment and Line Item created and updated into the DB. Now...going to work on the import.

Thanks for the guidance. It will be a more solid DB now!