1 2 3 Previous Next 32 Replies Latest reply on Oct 20, 2016 12:46 AM by globe11123

    Script help

    globe11123

      Basically I want to create a script that looks at a set of values from a related table.

       

      If all values = "Assessed" etc

      then change status = "Complete"

       

      But if one of the values within the search doesn't = "Assessed" then no status change.

       

      Next time script is ran. All values = assessed mark as complete etc etc

       

      I only want to create something like this to automate status changes from say a button. Rather than having to go to the exact record once the criteria has been met and change the status.

       

      What would be the best way in doing this?

        • 1. Re: Script help
          David Moyer

          Hi,

          here's one method (in general) ...

          - go to related records

          - set variable $notAssessed = 0

          - loop

            - if status <> "assessed", then set $notAssessed = 1 and Exit Loop[1]

            - go to next record, exit after last

          - end loop

          - go to original layout

          - exit script with result $notAssessed

          • 2. Re: Script help
            philipHPG

            If I understand correctly what you are attempting, rather than looping through all the related records, you can actually check this with a single calculation:

             

            If [ ValueCount ( List ( RelatedTable::Value ) ) = ValueCount ( FilterValues ( List ( RelatedTable::Value ) ; "Assessed" ) ) ]

            Set Field [ ThisTable::Status ; "Complete" ]

            End If

            • 3. Re: Script help
              David Moyer

              that's a better way to slice the onion.

              I personally would substitute Count(RelatedTable::PrimaryKey) for the left side of the IF to ensure that all records are "assessed".

              • 4. Re: Script help
                philipHPG

                Yes, good point, otherwise related records with a blank Value won't block the status change.

                • 5. Re: Script help
                  globe11123

                  The script just loops and I'm unsure how I update the related record status to complete.

                   

                  WE.png

                   

                  I'm using it on a despatch example. Where if all despatches = Despatched then it should update Orders::Status = "Complete"

                   

                  Sorry for being dim!

                  • 6. Re: Script help
                    philipHPG

                    If you want to use this approach, you need to move the Go To Record Next step after the End If.

                     

                    Then, instead of exiting the script at line 60, you would set the status field if $notDespatched is 0:

                     

                    If [ $notDespatched = 0]

                      Set Field [ Orders::Status ; "Complete" ]

                    End If

                     

                    You might want to try the calculation I suggested (with moyerdave's suggested change), as it will probably be faster than looping through the individual records.

                    • 7. Re: Script help
                      David Moyer

                      sorry, I got a bit confused there ...

                      I was using Exit Script (used for what I call Function Scripts - which return a value to the calling script).

                      • 8. Re: Script help
                        globe11123

                        Its worked now.

                         

                        Does this look correct?

                         

                        WE.png

                        I tried your calculation philipHPG but seen as I have never come across that type of calc it didn't work :S

                        • 9. Re: Script help
                          philipHPG

                          Yes, that looks better, although you may no longer need the Exit Script step at line 66 and if you do keep it, there's no point having the Go to Layout at line 68.

                           

                          To use the calculation I suggested, based on your TOs and field names (just substitute in the name of your primary key field), you could replace lines 51 through 64 with:

                           

                          If [ Count ( Despatch_Notes::PrimaryKeyID ) = ValueCount ( FilterValues ( List ( Despatch_Notes::Status ) ; "Despatched" ) ) ]

                          Set Field [ Orders::Status ; "Complete"]

                          End If

                          • 10. Re: Script help
                            David Moyer

                            you'll need to get rid of the Exit Script step (and the second Go to original layout).

                            • 11. Re: Script help
                              philipHPG

                              Essentially, how it works is it compares the total count of the related records (based on the PrimaryKeyID) with the count of records that have a Status of "Despatched" (it get a list of all the statuses and then filters down to only the "Despatched" statuses). If the counts are the same, then you know all Despatch_Notes have been Despatched. If they aren't then at least one Despatch_Note hasn't been despatched.

                               

                              Does that make better sense of the calculation?

                              • 12. Re: Script help
                                David Moyer

                                unless you need to manipulate the related records, you really should use Philip's block of code to replace all of the script shown (you don't even need to change layouts) ...

                                 

                                If [ Count ( Despatch_Notes::PrimaryKeyID ) = ValueCount ( FilterValues ( List ( Despatch_Notes::Status ) ; "Despatched" ) ) ]

                                   Set Field [ Orders::Status ; "Complete"]

                                End If

                                 

                                • 13. Re: Script help
                                  David Moyer

                                  One more thing, or two ...

                                  It would be safer if you wrapped the above small block of script with "If Count(RelatedTable::PrimaryKey) > 0" so that you won't adjust the status if there are no related records.  And, this might help ...

                                   

                                  Capture.PNG

                                  • 14. Re: Script help
                                    BruceRobertson

                                    You might fix the spelling of "Dispatch" while you are at it.

                                    "Despatch" is not a valid word.

                                    1 2 3 Previous Next