Summarizing Order Totals on the first record

[OffTheDeepEnd.com]

I am creating a db that will take an order import file and translate it from one system to another mail order system. I was able to get all the data into the correct layout but I need to figure out how to make the fields only display the order total on the first set of records... Here is an example...

The way it works now:

Line# Order# ProductID Price ShipAmt Total

11 12345 CE121 14.95 7.5 52.35

12 12345 CE122 14.95 7.5 52.35

13 12345 CE123 14.95 7.5 52.35

14 12357 CE121 14.95 7.5 52.35

15 12357 CE122 14.95 7.5 52.35

16 12357 CE123 14.95 7.5 52.35

What I need the system to do:

Line# Order# ProductID Price ShipAmt Total

11 12345 CE121 14.95 7.5 52.35

12 12345 CE122 14.95

13 12345 CE123 14.95

14 12357 CE121 14.95 7.5 52.35

15 12357 CE122 14.95

16 12357 CE123 14.95

Line #11 and 14 are the only line that should indicate the order total and shipping amount. Unfortunately I cannot even begin to figure out how to get FileMaker to do just that. All records have unique numerical ids but there is usually 50 orders in one file so it needs to judge which line is the first line for a certain order number. Also orders vary as far as number of items per order. Any help is greatly appreciated.

[mr_vodka]

You can create a self join relationship keyed on OrderNum. Then for your shipping amount, create a calc field:

Case ( GetNthRecord ( SelfJoin::Line ; 1 ) = Line; ShipAmt )

and for your total:

Case ( GetNthRecord ( SelfJoin::Line ; 1 ) = Line; Sum ( SelfJoin::Price ) + cShipping )

or since you are already doing the check for it being the first related line in the shipping calculation, you could use:

Case ( cShipping; Sum ( SelfJoin::Price ) + cShipping )

[mr_vodka]

Just wanted to add, that normally, there would be an Orders table where you would have the shipping and Sum of the amounts calculations there instead of the lineitems.

Since your situation is a little different as you are importing them for a specifc purpose I guess you could go the other route. If you do intend to keep this data in FM for any other reasons though, I would look into importing it so that you have an orders table with related lineitems table.

[comment]

How about a report with leading sub-summary by Order#?

BTW, this seems very similar to this thread.

[mr_vodka]

Hi Comment,

I was going to suggest using a summary report initially, but then to make it look exactly like the format that the OP wanted would have been a little more trouble than to just use a couple of calc fields.

Unless I am missing something...

firstline.zip

[comment]

No, of course it wouldn't look EXACTLY like that. But i thought it was worth mentioning, because it is much simpler (you need a couple of calc fields AND a relationship), and to me it seems a more logical way to present such data.