Repeating fields into lineitems

[falkaholic]

I am fixing up an old system in which the original author (I think was a gang of drunk monkeys) used repeating fields for almost everything (and made an insane number of other design faux pas.)

I'm thinking of making some of them into the standard line items in a related database. But if i do that, I'm not sure if I can import all the old data. Is there a way?

[andygaunt]

Eric,

why not create your line items database and then import the repeating field data into that, but choose splitting into seperate records.

That way, you get all the data out into single fields which is the way we like it

[falkaholic]

Ya. Thats what i was thinking but Im assuming it is easier said then done...

For example, one DB would be your run of the mill invoice with line items. In the new database i'd need one new record for each it in a repeating field AND have the invoiceID in the new records, as well as other invoice data.

I havn't even tried doing it yet, but i'm assuming its a big scripting deal or really simple import/export.

How you personally done it like that?

[andygaunt]

Eric,

Yeah. You would think it would be hard to script, but it really is that simple.

Just set your import to import the repeating fields into their new respective fields, and the invoice id to the invoice id.

Choose seperate fields for repeating fields, and hopefully (crossing my fingers here!) your data will be seperated out and have the invoice id for each line.

Best thing, try it with a sample of data first, just a few records and check the results.

Let me know how you get on

[falkaholic]

thanks,

I'm thinking the scripts won't be too hard. It more wether or not its script or not to script.

I won't be able to put this into action for awhile. I'm just starting this project (actually as a side project.)

[Wasatch]

Careful, it depends how drunk the monkeys were!

When the "Split" box is checked, new RECORDS are created for EACH repetition. If one repeating field in a single record has 25 repetitions, you will create a 50,000 record DB from a 2,000 record one. Similarly if there is one global field with many repetitions.

Most repetitions in most of the 50,000 records will be empty unless the various repeating fields were closely coupled. Some repeating fields may have been used within records for storing parameter sets: you may not want those split up.

I would suggest: 1) keep everything intact as you import into the new DB; 2) figure out which fields to keep intact as repeating; 3) write a simple script to strip the others into line items.

[danjacoby]

quote:

---

Originally posted by Wasatch:

When the "Split" box is checked, new RECORDS are created for EACH repetition.

---

Wrong!

I just checked, because I was pretty sure about this, and I'm right. A new record will only be created for each repetition that contains data.

It even works if you skip a repetition. In other words, if you have data in repetition 1 and repetition 3, but repetition 2 is blank, it'll create two records when you import -- for reps 1 & 3.

[andygaunt]

I was pretty sure about this too.

If you have 20 repetitions but only data in the first ten then you will get ten new records once split, not 10 with data and ten blank.

Thought I had been staring at the screen too long again!

[Wasatch]

Whoops, you are right.

I had figured that only those repeats with data are split, and my "25 repetitions" was meant to imply "25 data entries". Poor choice of words.

However, one of my repeat fields happened to be a global, and that does give rise to a copy for each record. I agree that regular fields behave much better.

Wasatch