absolutely must import into repeating fields

[Panama Coffee]

Ultimately I'll be eliminating repeating fields in my client's database but in this transition period I must must must import into repeating fields.

To simplify: I have a LineItems database with an Orders field. Each record has an order number and an item field. I have an Invoice database with an Orders field (that matches the LineItems Orders field) and a repeating field for the items. I must get the items from LineItems into the repeating fields found in the Invoice. Yes, a portal works beautifully but for very complicated reasons I cannot use the portal just yet. I must get those items into the repeating fields.

I've been gnawing away at this problem and feel I'm making some progress, but I feel someone else may have done this before and may give me a jump start.

Any help greatly appreciated!

FileMaker Version: 6

Platform: Mac OS X Panther

[Ugo DI LUCA]

Hi,

With not too much risk, I'd say this is impossible with a classic Import script.

Also, I'd think 99% of us here are just doing the reverse process, by splitting repeating fields in as many records.

Would you mind telling us WHY this would be easier and so important to keep them in repeating fields if ultimately you'd move to a portal ?

Now for the answer.

If you don't need these items to come in the order they appear in the Line Items, and if you want to parse out duplicate (would this be possible to have 2 lines for the same product within a single invoice ?), then an option would be to use a related value list at first.

Then, you'd loop through the Repeating Field and set each repetition rank to its corresponding value in the List.

If you need the order, then you'd loop in the Line Items, grab the Items in a global multiline, pass this multiline to the Invoice File and process the same sequence above parsing your global field instead of the Value List.

Oh, I forgot, to loop through a repeating field, just place it in its layout and move from one place to the other with the NextFieldscript step.

FileMaker Version: 6

Platform: Mac OS 9

[ernst]

You could have separate layout in the Invoice Database with the Line Items displaying through a portal.

Then you could make a script that goes to this layout and steps through the each portal row and copies the item through the corresponding repetition.

Problem is that you can't index the repetitions with a variable in the 'Set' or 'Insert' script steps. So you got to 'hard code' for each separate repetition (Fortunately the max amount of repetitions is only 999... )

But hopefully the maximum number of items on an invoice is not too big...

Regards,

Ernst.

[ernst]

Ugo DI LUCA said:

>>Oh, I forgot, to loop through a repeating field, just place it in its layout and move from one place to >>the other with the NextFieldscript step.

Dirty but smart!

[Ugo DI LUCA]

Ernst,

I'm just always referencing your Insert Repeat sampler here, and abusively used it for some other ones, but for sure a loop is quicker than a lookup, and wouldn't need any prior settings.

FileMaker Version: 6

Platform: Mac OS 9

[Panama Coffee]

Thanks for all the replies that came in while I was mowing the yard! Looks like some very useful tips among them. I'll experiment and get back with the final version.

As far as WHY repeating fields, well, as I said, it's complicated. Hint: it's related to client bureaucracy.

Thanks again.

[Panama Coffee]

Ugo DI LUCA said:

....

If you need the order, then you'd loop in the Line Items, grab the Items in a global multiline, pass this multiline to the Invoice File and process the same sequence above parsing your global field instead of the Value List.

....

FileMaker Version: 6

Platform: Mac OS 9

This procedure works nicely for one order - there's a direct match between the global repeating field in Line Items and the repeating field in Invoice. However, I'm struggling with covering more than one order at a time, since it looks like I'd have to import one order, then build the global field for the second order, and so forth.

Any further ideas?

Thanks.

FileMaker Version: Dev 6

Platform: Mac OS X Panther

[Panama Coffee]

ernst said:

You could have separate layout in the Invoice Database with the Line Items displaying through a portal.

Then you could make a script that goes to this layout and steps through the each portal row and copies the item through the corresponding repetition.

...

I like this idea very much since in the end I will have a portal layout in the Invoice database anyway. However, I find that if the portal is, say, 10 lines long and only two rows have items in them, then as I run through the portal rows to copy and paste into the repeating field, when I reach row three and every row thereafter, the script simply goes to row two and pastes that item into row 3, 4, ...10 of the repeating field. (I have to run the script through all the portal rows, I believe, because it's the only way to match each one to a repeating field, and I don't know in advance how many portal rows will be occupied.)

At this point I'm stuck on how to work around this behavior. Any ideas?

Thanks.

FileMaker Version: Dev 6

Platform: Mac OS X Panther

[bruceR]

1. You can write directly to a repetition with applescript. See attached example. Note that as in most applescript techiques, this can be generalized to copy data from any file.

2. You are thinking "import" but that is not the only way to do what you want. You can (effectively) write directly to a repetition by using a lookup technique.

FileMaker Version: Dev 6

Platform: Mac OS X Panther

CopyRepeatz.fp5.zip

[ernst]

Panama Coffee said:

... I don't know in advance how many portal rows will be occupied.)...Any ideas?FileMaker Version: Dev 6

Platform: Mac OS X Panther

You can check the amount of related records in advance in several ways. Probably the easiest is starting the script with:

GoTo PortalRow (Last)

Set Global_Number_Of_Related_Records, Status(CurrentPortalRow)

And of course use the value of Global_Number_Of_Related_Records in your copieing script.

Bruce's solutions are also nice.

Regards,

Ernst

[Panama Coffee]

Thanks for all the ideas.

I worked out a script that uses the status of the current portal row and the status of the repetition number (of the repeating field) to do the work. It's attached. I like the length of Bruce's AppleScript, though!

FileMaker Version: Dev 6

Platform: Mac OS X Panther

PortalToRepeatingField.pdf