Calculation field on parent table calculating child field on all related records

[mpau]

Does anybody know how to make a calculation field that sees into all related records? I've tried a variety of calculation structures but none seem to be able to see beyond the first of the related records.

I have a company that does partial shipments, ie, the order lines of an order can have individual and different ship dates. On the Order table I want the Ship Date field to read "" (blank) when nothing has shipped, "partial ship" or "" (blank) when at least one of the order lines has a shipped and "shipped" (or date of last shipment) when all have shipped.

I've been able to get this to work using a Case calculation and others but only for the first related record. Is there a parameter I'm missing that will tell the calculation to gather the date and verify the criteria in all related records?

Parent Table Orders

Child Table Orderline

Calculation field Orders::Ship Date

Calculating the field Orderline::Ship Date

Any help with this would be appreciated.

Thanks

[David Jondreau]

A simple way would be to create a calculation field in the Orderline table

Shipped = not IsEmpty(Orderline::ShipDate)

This will return 1 if the item has been shipped.

Then in the Orders table, create a calc field=

Let([

lines = Count(orderline::key field);

ships = Sum(orderline::Shipped);

none = "";

partial = "Partially Shipped";

all = "Shipped";

error = "error";

result = Case(not ships ; none ; lines = ships; all; lines > ships; partial; error)

];

result

)

You can change the variables for none, partial, all, and error to whatever you want to display. Max(orderline::ShipDate) will give you the last ship date if you want to use that instead of "Shipped".

[comment]

You can count the Orderline::Ship Date field directly. If the count returns 0, then nothing has been shipped. If it equals Count ( Orderline::OrderID ), then everything has been shipped. Anything else is a partial shipment.

[mpau]

Thank you DJ!!!

This worked perfectly. Honestly, I'm amazed and grateful!

One question though, where would I plug in the Max calculation?

Also, I've got another question about calculation fields that you may be able to answer.

Also on the orders table I've got a field called "Shipping Total"Sum ( Orderline::ShippingTotal )

The Orderline field "ShippingTotal" is also a calculation: Quantity * ShippingUnitPrice

and ShippingUnitPrice is yet another calculation:

Case (Type of Salefk = "Wholesale" ; Products::Shipping ; Type of Salefk = "Direct" ; Products::Shipping ; Type of Salefk = "To-The-Trade" ; Products::Shipping ; Type of Salefk = "Drop-Ship" ; "0")

Essentially the ShippingUnitPrice = the shipping price listed on the Product page/record

Everything works fine here, except for when I need to change/update my shipping rates. Plugging in new shipping rates writes over all my old data. Is there a way to work around this?

I posted this question in the forum if you want to tackle it there. Post: [Calculation] Updating Calculation field pricing structures without writing over old data (Topic#198855)

Edited October 22, 2008 by Guest