status fields not evaluating all related line items

[Alan M]

I am making a solution for creating and tracking estimates and work orders. I would like to make a layout that shows the status of the work orders in each dept. that may be working on them. I have this set up in a list view that shows the different depts. with a calculation field for each that describes the status. This layout is based on the work order table. Each work order has a line item portal which is based on the line items table (the portal, that is). The problem I'm having is that each caculation field describes only the first line item for each work order instead of evaluating the status of every line item. If every related line item had status of "done" then I would like the calc field to indicate "done". If, however, only one of the related line items had status "done" then I would like the calc field to indicate "open". Sorry if this sounds confusing. I'm a noob and might have bitten off more than I can chew.

Edited May 4, 2006 by Guest

[comment]

In the line items table, define a calculation field cDone (result is Number) =

Status = "Done"

In the work order table, define a calculation field (result is Text) =

Case ( Count ( LineItems::ItemID ) = Sum ( LineItems::cDone ) ; "Done" ; "Open" )

Note:

ItemID is an auto-entered serial number. You can count any field that is certain to never be empty.

[Alan M]

Thank you very much Comment. That makes complete sense and better than than, it works! I understand the concept now and can see where this could be used in different ways.

Again thanks for the quick response.