1 of 1 people found this helpful
So you have a "Service Record" that may have 1 or more "Service Items". When all "Service Items" are marked complete, you want the "Service Record" to be marked complete.
Do I understand?
If yes, then why not do this...
Add a number field to "Service Items" called "itemStatusCompleteBln". Set it to be an auto-enter calculated value "If ( lineStatus = "Complete" ; 1 ; "" )". Turn off "do not replace existing value of field". I'm assuming the status is currently a text value, probably from a popup menu.
Then in "Service Records", add a calculation field, "recordStatusCompleteBln" and make it =
If ( Count ( ServiceItems::id ) = Sum ( ServiceItems::itemStatusCompleteBln ) ; 1 ; "" )
Which in english reads as "If the number of related service items is equal to the number of complete service items, TRUE, otherwise FALSE". I'm assuming you have a key field called "id". Any field that is guaranteed non-empty will do fine.
For better performance, make it a number field instead of a calculation field. But then you'll need to be careful to use script triggers to trigger an update any time a Service Item status changes.
Hey Jason that works just fine. Question for you, I'm intending on running a script attached to a button on screen that will do this check for me as not to slow down the system with checking this process every single time a status is changed on the line items table. Insert Calculated Result script step comes to mind. Would this be a viable option for this process?
"Set Field" is almost always the correct step to write values into a field. Avoid steps that require the presence of the target field on the layout (or that, like Copy and Paste, manipulate the clipboard).
btw, you don't need the calculation field; just use the expression
Count ( ServiceItem::id ) = ValueCount ( FilterValues ( List ( ServiceItem::status ) ; "Complete" ) )
In a script:
If [ Count ( ServiceItem::id ) = ValueCount ( FilterValues ( List ( ServiceItem::status ) ; "Complete" ) ) ]
Set Field [ Service::completed ; 1 ]