1 2 Previous Next 18 Replies Latest reply on Feb 14, 2019 12:04 PM by blwallace

    Getting All Approvers

    blwallace Specialist

      ITSM 2018.3.1

       

      I have a Request Offering that needs 1:n approvals.  I want to have a quick action that will send an email out that shows the names of all the people who have approved the request.  The business object ServiceReq (Service Request) has a relationship to FRS_Approval (Approval).  The FRS_Approval DB table holds the ServiceReq RecId in the ParentLink_RecID field.  The FRS_Approval business object has a relationship to FRS_ApprovalVoteTracking.  FRS_ApprovalVoteTracking holds information on who approved the request.  FRS_Approval holds information on whether the request was approved or denied.  I would like to include in the email, all persons who approved the request.

       

      The following expression (1) returns all RecId's for each approved status..

      $(ForEachChild("ServiceReq#", RecId, "FRS_Approval#", 'if Find("Approved", Status) != -1 then RecId else ""'))

      ...as one long string, I might add.

       

      The following expression (2) returns the approvers' full name for a given RecId...

      $(EvalField("FRS_ApprovalVoteTracking#", "161723400F944BC1A243CF458B90A61F", "OwnerFullName"))

       

      Somehow I need to get the RecId of (1) into the RecId of (2) - so I tried...

      $(EvalField("FRS_ApprovalVoteTracking#", "(ForEachChild("ServiceReq#", RecId, "FRS_Approval#", 'if Find("Approved", Status) != -1 then RecId else ""')", "OwnerFullName"))

      ...but that didn't work (didn't think it would be that easy)

       

      Can someone show me how to get all the approvers' names into an email being sent from service request quick action?

       

       

        • 1. Re: Getting All Approvers
          IJU Expert

          The ForEachChild Function can do exactly what you need. Under the following link you can find an example for creating a formatted string with line breaks:

           

          https://help.ivanti.com/ht/help/en_US/ISM/2017.3/Content/Reference/Functions/ForEachChild.htm?Highlight=foreachchild

           

          ForEachChild("Issue_Fix", RecId, "ResNoteHistory#.", "LastModBy + '\t'

          + LastModDateTime

          + '\r\n' + ResolutionNote

          + '\r\n______________\r\n'", "CreatedDateTime ASC")

           

          That should do the trick

           

          Kind regards

           

          Immanuel Jungheim

          Senior Consultant

           

          ITSM Group – Be Better.

          www.itsmgroup.com

          • 2. Re: Getting All Approvers
            blwallace Specialist

            Yes, I understand ForEachChild will work, my real issue is obtaining the RecId for the FRS_ApprovalVoteTracking which holds the information I'm looking for, namely all people who have approved the request.

             

            The associations between the business objects are:

                 ServiceReq Contains FRS_Approval and FRS_Approval Contains FRS_ApprovalVoteTracking

            FRS_ApprovalVoteTracking doesn't have a direct relationship to ServiceReq, it has a relationship to FRS_Approval. 

             

            It appears to me that I need to get the RecId of all "Approved" votes by:

                 $(ForEachChild("ServiceReq", RecId, "FRS_Approval#.", "RecId + '\r\n'", "CreatedDateTime ASC")

            which will give me a list of RecId's [in my workflow case, I will get a maximum of four].

            Then I need to use each of those RecId's to pass into:

                 $(ForEachChild("FRS_Approval", RecId, "FRS_ApprvalVoteTracking#.", "OwnerFullName + '\r\n'", "CreatedDateTime ASC")

             

            In essence, use each RecId returned by the $(ForEachChild("ServiceReq"... expression as the RecId for $(ForEachChild("FRS_Approval"... expression.

             

            I don't know how to do this.

            • 3. Re: Getting All Approvers
              IJU Expert

               

              $(ForEachChild("ServiceReq", RecId, "FRS_Approval#.", "ForEachChild("FRS_Approval", RecId, "FRS_ApprvalVoteTracking#.", "OwnerFullName + '\r\n'", "CreatedDateTime ASC"+ '\r\n'", "CreatedDateTime ASC")

               

               

              If you put the one into the other. Does this work?

               

              Kind regards

               

              Immanuel Jungheim

              Senior Consultant

               

              ITSM Group – Be Better.

              www.itsmgroup.com

               

              • 4. Re: Getting All Approvers
                blwallace Specialist

                That saves, but returns nothing but the expression's text, i.e. $(ForEachChild("ServiceReq#", RecId, "FRS_Approval#.", "ForEachChild("FRS_Approval#", RecId, "FRS_ApprovalVoteTracking#.", "'\r\n' + OwnerFullName", "CreatedDateTime ASC" + '\r\n'", "CreatedDateTime ASC"))

                • 5. Re: Getting All Approvers
                  blwallace Specialist

                  I've also tried to nest the expressions, like...

                  $(ForEachChild("FRS_Approval", 'ForEachChild("ServiceReq", RecId, "FRS_Approval#.", "RecId + '\r\n'", "CreatedDateTime ASC")', "FRS_ApprvalVoteTracking#.", "OwnerFullName + '\r\n'", "CreatedDateTime ASC")

                   

                  trying to substitute the returned RecId of the ServiceReq->FRS_Approval relationship into the RecId field for FRS_Approval->FRS_ApprovalVoteTracking but that doesn't work either.

                  • 6. Re: Getting All Approvers
                    IJU Expert

                    Hi,

                     

                    before trying to get a stacked expression working I would create a field in FRS_Approval "AllApproves_Approved" for example and have this field field via update quick action whith the ForEachChild to collect the approvers. Or have each vote tracking run a run for child on the parant FRS_Approval and execute that ForEachChild there. So this field is always filled with the approvers who approved.

                     

                    kind regards

                     

                    Immanuel Jungheim

                    Senior Consultant

                     

                    ITSM Group – Be Better.

                    www.itsmgroup.com

                    • 7. Re: Getting All Approvers
                      blwallace Specialist

                      I tried your suggestion.  I created an AllApprovers field on the FRS_Approval business object.  Then created a 'Get All Approvers', FRS_Approval quick action with the following code...

                      Selection_186.png

                       

                      In my workflow and for one service request, I have three different 'Named' approval blocks.  After each approval block where the request was 'Approved', I have this block..

                      Selection_187.png

                       

                      This didn't produce a field with a list of names.  It does fill in the field with the LAST approver's name.

                       

                      Maybe my code is wrong, but baring any error in logic (probably not the best assumption ;-) ), I began to look at the database.  Each approval creates a unique record in FRS_Approval which in turn creates a unique record in FRS_ApprovalVoteTracking.  You can see from below, that FRS_ApprovalVoteTracking contains the RecId of each FRS_Approval, but also contains the ServiceReq RecId.  It would be nice to run a quick action one time to get all the names.  In order to do that I would need to lookup in FRS_ApprovalVoteTracking where the PrimarlyParentRecID (which is equal to the service request) is used and the FRS_ApprovalVoteTracking status is equal to 'Approved' and not 'Denied'.

                       

                      Here's what my fullfilled service request looks like.  You can see I have several approvals, two being "manager level", one "executive level" and one for a defined person.

                      Selection_182.png

                      and here's the ServiceReq, FRS_Approval, FRS_ApprovalVoteTracking DB tables [filtered for important info only pertaining to SR10298]

                       

                      Service Request Query

                      Selection_183.png

                       

                      FRS_Approval Query

                      Selection_184.png

                       

                      FRS_ApprovalVoteTracking Query

                      Selection_185.png

                       

                      I'm wondering if I need to create a ServiceReqAssociatedFRS_ApprovalVoteTracking relationship but would that alter my OOTB database too much with respect to Service Requests?

                      If created, what does that do to the normal working ServiceReq->FRS_Approval->FRS_ApprovalVoteTracking relationship?

                      • 8. Re: Getting All Approvers
                        IJU Expert

                        Hello,

                         

                        thanks for the excessive answer. Great level of detail

                         

                        I think I found the error in your code.

                         

                        Your code:

                         

                        New code:

                         

                        $(AllApprovers + ForEachChild("FRS_Approval#", RecId, "FRS_ApprovalVoteTracking#.", "OwnerFullName + '\r\n'", CreatedDateTime ASC"))

                         

                        This should do the trick.

                         

                        But to answer your question: Of course you could build a relationship between FRS_ApprovalVoteTracking and ServiceReq. This wouldn't affect anything really if you just build new fields etc. and don't use anything the system uses.

                        Just build a 1:n relationship and a Link Field on the FRS_VoteTracking object. On the votetracking build an editing rule which triggers when the PrimaryParentRecId field gets filled and saves that recid directly in the new Linkfield which you use in the new relationship. This should build a relationship and you could then use a ForEachChild on the ServiceRequest to collect the names straight from the now directly attached VoteTrackings. But if you correct the ForEachChild (see above) that should work as well.

                         

                        Kind Regards

                         

                        Immanuel Jungheim

                        Senior Consultant

                         

                        ITSM Group – Be Better.

                        www.itsmgroup.com

                        • 9. Re: Getting All Approvers
                          blwallace Specialist

                          Thank you for your reply.  Your code is what I first used, but when selecting Simplify Expression, it converts it to what I showed.  However, I did enter your code and didn't Simplify and still get the same result.  I don't know why AllApprovers doesn't ever have more than one name.  I added the AllApprovers field (which is just a text field) on the FRS_Approval form but after getting four approvals, the value in AllApprovers is the last person to approve the request.

                           

                          [you are missing a leading quote around CreatedDateTime ASC]

                           

                           

                          I'm going to try this method below and if it doesn't work, then create a relationship.

                           

                          Create FRS_Approval field "ApproverName"

                          Create ServiceReq field "ListOfAllApprovers"

                           

                          In the workflow, after approved, use a Run For Child (child is FRS_Approval) workflow block of 'Get Current Approver'

                          to get the lastest approvers' FullName using:

                                      ApproverName = $(ForEachChild("FRS_Approval#", RecId, "FRS_ApprovalVoteTracking#.", "OwnerFullName + '\r\n'", "CreatedDateTime ASC"))

                                      puts the last approvers full name in ApproverName field

                           

                          After 'Get Approver' block use a Update Object (object is ServiceReq) workflow block of 'Add Approver'

                          to add the last approver's name (from Get Current Approver) to existing list

                                      ListOfAllApprovers = $(ListOfAllApprovers + '\r\n' + ([ServiceReq#.FRS_Approval]ApproverName))

                          • 10. Re: Getting All Approvers
                            IJU Expert

                            Hi,

                             

                            the thing I don't get is that the ForEachChidl should actually do what we're trying to use it for.

                             

                            Maybe it's the first part where you add the foreachchild to the Approverfield. Try this simplified version:

                             

                             

                            $(ForEachChild("FRS_Approval#", RecId, "FRS_ApprovalVoteTracking#.", "$(OwnerFullName + '\r\n')"))

                             

                             

                            But when I look at what you wrote in your last post you should have probably already found out that it works (?).

                             

                            Kind regards

                             

                            Immanuel Jungheim

                            Senior Consultant

                             

                            ITSM Group – Be Better.

                            www.itsmgroup.com

                            • 11. Re: Getting All Approvers
                              blwallace Specialist

                              Looking for the correct field reference in my ListOfAllApprovers code above, I noticed my ServiceReqAssociatedFRS_Approval doesn't have an Internal Reference Name

                              Selection_188.png

                              which means this is an older configuration.  Could this be causing and issue?  I'm learning more and more about relationships and using field references so still tentative, but any "new" relationship has to have an internal reference name but older ones are grandfathered in unless and until they are changed - then they will need an internal reference name.

                              • 12. Re: Getting All Approvers
                                blwallace Specialist

                                That provides the same result.

                                 

                                The issue is there doesn't seem to be a way to get the RecId's returned by this code...

                                $(ForEachChild("ServiceReq#", RecId, "FRS_Approval#.", "'\r\n' + RecId", "CreatedDateTime ASC"))

                                 

                                to use in this

                                $(ForEachChild("FRS_Approval#", RecId, "FRS_ApprovalVoteTracking#.", "OwnerFullName + '\r\n'", "CreatedDateTime ASC"))

                                where RecId = $(ForEachChild("ServiceReq#", RecId, "FRS_Approval#.", "'\r\n' + RecId", "CreatedDateTime ASC"))

                                • 13. Re: Getting All Approvers
                                  blwallace Specialist

                                  Well, I've made some progress!?!  My original goal (see initial post) is to be able to send an email with all the approvers' names.  This is a quick action created within ServiceReq.  I now have my email listing all approvers by using the following code in a 'send email' quick action:

                                       $(ForEachChild("ServiceReq#", RecId, "FRS_Approval#.", 'ForEachChild("FRS_Approval#", RecId, "FRS_ApprovalVoteTracking#.", "OwnerFullName", "CreatedDateTime ASC")', "CreatedDateTime ASC"))

                                  I got the idea from About SubExpressions.

                                   

                                  Getting the syntax correct is one awful thing.  Taking the same code and placing it into a 'update object' quick action where one can run Simplify Expression yields:

                                       $(ForEachChild⁽¹⁾("ServiceReq#",

                                         RecId,

                                         "FRS_Approval#.",

                                         $(ForEachChild("FRS_Approval#",

                                             RecId,

                                             "FRS_ApprovalVoteTracking#.",

                                             $(OwnerFullName),

                                             "CreatedDateTime ASC")),

                                         "CreatedDateTime ASC"))

                                   

                                  and pasting that back into the 'send email' quick action and it won't save.

                                   

                                  The other thing, is the list is straight text, i.e. OwnerFullNameOwnerFullNameOwnerFullNameOwnerFullName - all four of my approvers.

                                   

                                  I've experimented with

                                  1. "OwnerFullName + \r\n"
                                  2. "OwnerFullName" + "\r\n"
                                  3. "OwnerFullName + '\r\n'" (single quotes around \r\n)
                                  4. "(OwnerFullName" + \r\n)"

                                  but can't get anything to work to get a list like:

                                  • OwnerFullName
                                  • OwnerFullName
                                  • OwnerFullName
                                  • OwnerFullName
                                  • 14. Re: Getting All Approvers
                                    blwallace Specialist

                                    I finally have it!  This will create a list of approvers for a particular service request!

                                    $(ForEachChild("ServiceReq#", RecId, "FRS_Approval#.", '(ForEachChild("FRS_Approval#", RecId, "FRS_ApprovalVoteTracking#.", "OwnerFullName", "CreatedDateTime ASC") + "\r\n")', "CreatedDateTime ASC"))

                                     

                                    1 2 Previous Next