Combining Multiple Orders into One (Consolidating line items)

[uemtux]

Here's my situation,

i have a bunch of order records with line items. For a given found set of orders, I do a "Get Related Records" and get a found set of all the line items across several orders. Let's say one order has 20 units of product A for a total of 20$ and another order has 10 units of Product A at $25.00 (pricing for products differs throughout the year)

How do I loop through this data and generate at the end a set of consolidated line items for all the orders, so that my new consolidated order (the idea of the script is to take many small orders and generate one big order out of them) would have 30 units of product A at a price of $45.00 ?

Any insights? If FileMaker were PHP, I'd loop through the records and use a keyed array to keep track of the products, quantities, and dollar totals... it would be easy! But my brain can't come up with an equivalent solution in filemaker script.

Any ideas?

[Vaughan]

If FileMaker were PHP, I'd loop through the records and use a keyed array to keep track of the products, quantities, and dollar totals...

Why can't you do that in FMP using a script variable?

OTOH you could display/print the existing line items through a summary report and total them on the fly.

[comment]

the idea of the script is to take many small orders and generate one big order out of them

What's the idea of the "consolidated order"? It doesn't seem to represent anything real or useful.

OTOH, you can easily produce a report of the LineItems table, summarized by product. Or add calculation fields to the Products table, summing the quantity and the extended price of the related line items.

[uemtux]

Thanks everyone I used the export grouped and re-import technique and it worked like a charm. The idea behind a "consolidated order" is to get hundred of small (a few bucks each) web orders into the accounting system under a single monthly "web sales" order.