I hope I can explain this without making it sound more complicated than it really is.

I want to set up a set of records with an Airbill number, shipping charges, and the order number that was shipped. My problem is that multiple orders ship in one package and I want to be able to divide the shipping charges by each of those orders.

So, I'd like to have these fields:

airbill_number

order_number

total_shipping_charges

and some sort of calculation field that divides the total_shipping_charges by the number of order_numbers that share this airbill number.

We will also have multiple airbill_numbers for each order_number (each with it's own record) so I'd like to be able to get a summary of the charges for an order.

example:

Record 1: Airbill: 999991 Shipping Cost: $10 Order Number 12345 Divided Shipping Cost: $5

Record 2: Airbill: 999991 Shipping Cost: $10 Order Number 12346 Divided Shipping Cost: $5

Record 3: Airbill: 999992 Shipping Cost: $30 Order Number 12345 Divided Shipping Cost: $10

Record 4: Airbill: 999992 Shipping Cost: $30 Order Number 12347 Divided Shipping Cost: $10

Record 5: Airbill: 999992 Shipping Cost: $30 Order Number 12348 Divided Shipping Cost: $10

Summary of Shipping Charges for ON 12345 = $15

So, if I haven't confused the heck out of everyone, what would be the best way to go about this?

TIA,

.sly

Wow! 40 views and no one has any suggestions?

Hi, sly. Forgive me if i slip into FM6 langauge, but hopefully you can translate.

Relate TableOccurrence01::orderNumber to TableOccurrence02::orderNumber. Now you can get

total_airbills_for_order = Count ( TableOccurrence02::Airbill )

You can also find

total_shipping_charges = Sum ( TableOccurrence02::divided_shipping_cost )

OR (these are mutually exclusive so as to avoid circular calcs)

divided_shipping_cost = ( TableOccurrence02::total_shipping_charges / Count ( TableOccurrence02::order_number ) )

Hopefully this points you in the right direction, as there is probably a lot more you can do with this beyond the questions you asked.

Jerry

Thanks! That did it! Now I just have to add up all the shipping charges for each job and I will know what to bill the customer.

.sly