3 Replies Latest reply on May 25, 2017 9:36 AM by erolst

    List, Loop, Verify and auto-Update status?


      Hey guys so the task i'm trying to complete is this.  I have service items that customers bring in and each of them has a status to indicate what part of the repair process it is in.  I would like to have my service record auto update once all serving is completed based off the status of all the items for that record.  I recall watching a training video that RC Consulting did and if i remember correctly they used the List function and then looped through the items to see if they all matched the finial status and that the total of both was equal, at that time the Status of the whole record was updated as "Service Complete/Call Customer".


      Does any one have any thoughts on how to do this.


      I have my two parent tables set up with the join table in between them.  I'm just not sure how to collect only the items in that record, verify that they all have the same status and then have the record auto update.   Well the last part is easy enough.  This will all be scripted and SubScripted and will be ran by pushing a button on the main record.


      Thank you for your help.

        • 1. Re: List, Loop, Verify and auto-Update status?
          Jason Wood

          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.

          1 of 1 people found this helpful
          • 2. Re: List, Loop, Verify and auto-Update status?

            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? 

            • 3. Re: List, Loop, Verify and auto-Update status?

              "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 ]

              End if