Importing Repetitions

[LaRetta]

Oh how I love migrations (not). I have a field with several repetitions which holds a number in many of the reps which signifies the account. So Rep 1 might hold $25 indicating Acct 100, rep 3 might hold $50 indicating Acct 300 and so forth. I want to import these reps and split them into records. But, as you will see by the simple attached file, the logic of how FM chooses whether to create a record or not is worthless.

I want each repetition to create a record even if it is empty. It appears that FM always creates a record from the first rep (seeing it as the field) and then only creates records from the remaining if they have a value. This doesn't help me at all. I could loop, I suppose but it seems there should be simple way.

Then I thought I'd create a calculation in the source table which inserts a 0 if there is no value so every rep holds a value and should create a record (when calc is then imported instead). I can then delete out any 0 records after I assign the account to them but I can't even figure that out.

I may have started the Repeaters Society and I still honk every chance I get but I have never become a Repeater Master. Is there easy way to accomplish what I want for this mass migration without looping and testing every repetition?

testrep.zip

[comment]

FM always creates a record from the first rep (seeing it as the field)

No. The first repetition is always imported in this case, because you are importing the ID field, too - and its first repetition is never empty.

I want each repetition to create a record even if it is empty.

I am not sure why you would want this, but if you define a repeating calculation field =

Get (CalculationRepetitionNumber) * 100

and include it in the import, I believe that's what you'll get.

[LaRetta]

I am not sure why you would want this

Well importing the calculation works perfectly but no, I don't WANT blank repetitions; I just didn't know how I could identify which account they went to (after import) without importing them all. If you know a way then that would be wonderful - we don't want accounts if there is no value. It is simple to delete them again otherwise. Yes, the ID must come - there are other fields as well.

Thank you, Michael; much appreciated. :wink2:

[comment]

You do have some 0 values in your example - do you want to import these?

---

Oh, and is the source ID important? Do you need to import it as well?

Edited September 23, 2010 by Guest

[comment]

Yes, the ID must come - there are other fields as well.

You need to create a repeating calculation field for every non-repeating field you want to bring over. For the ID, the calc would be =

Case ( Amount ; Extend ( ID ) )

Also change the account calculation to =

Case ( Amount ; Get (CalculationRepetitionNumber) * 100 )

Import only the repeating fields.

---

If you need to import explicit zero values, use not IsEmpty ( Amount ) instead.

[LaRetta]

I didn't understand at first but gave it a try. It's amazing what one can discover when we just try it (on faith).

This rocks. It is EXACTLY what I needed (modified file attached)! No, I didn't need zero amounts so your first suggestion was perfect! You've saved me a lot of work ... again (and again). :laugh2:

testrepMOD.zip

[comment]

Well, come to think of it...

testrepMOD2.zip

[LaRetta]

Wow. I've used your technique for importing 'unique' quite often but I didn't think of using 'not empty' (or any other validations) in same way. I have to remind myself that the empty reps didn't import because FM saw them as records at the time of import (validation fails on records). I wish FM would produce useful import logs (and ANY import log when importing from another FM table) but I know (well, I think) what it would show - '14 records skipped.'

I would have been implementing your prior suggestion this morning (which I still learned a great deal from, hopefully) but now it'll be even easier.

ps was removed because it was incorrect. :-O

Edited September 24, 2010 by Guest
Corrected ps