Dividing Shipping Charges By the Number of Records

[slytle]

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

[slytle]

Wow! 40 views and no one has any suggestions?

[QuinTech]

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

[slytle]

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