Workflow Automation CoE >Flow Designer > Do Until Hierarchical Lookup
Overview
Update
Check out this new article on how to build the hierarchical lookup subflow with the new go back to flow logic. This design pattern and guidance in this article is still valid and can be used for existing and new development. Using 'go back to' instead of 'do the following until' is simply an alternative way to build this.
Goal
In this Workflow Automation Center of Excellence article, you’ll learn to use “Do the following until” flow logic with a flow variable to iterate up a hierarchy until a defined condition is met.
Technology and Use Case
Flow Designer is our central low-code workflow automation technology. We’ll use the do-until flow logic and a flow variable which we can set with the corresponding flow action.
For this use case, we want to create an approval for a catalog item, where the approver is the first user up the management chain [sys_user.manager] with a title containing “VP”.
This flow logic pattern can also be used to look up other parent-child record relationships, for example looking up:
- Cost center [cmn_cost_center] parent records with a certain Manager or Location
- Looking up location [cmn_location] parent records until you reach the region or state level
- Looking up group [sys_user_group] parent records
- Looking up department [cmn_department] parent records
- Etc.
Instructions
First, we’ll look at the users in our example scenario. As you can see, we have multiple layers of users that each have a manager until we reach John Retak, whose title is “VP, Finance and Securities”.
First Draft: Initial Flow building
Now we’ll get an idea of what flow might look like. This is the first pass of our flow, and we’ll iterate on it throughout this article until we have a flow that covers all our bases.
- Create a reference flow variable on the User table
- Initiate flow variable with the manager of the RITM’s Requested For user
- Start the do-until logic
- Look up the Manager of the user currently stored in the flow variable
- Update the flow variable with the new manager
- Repeat this until the user we looked up has a title that contains “VP”. This will end the loop
- Ask for Approval from the manager in the flow variable
Step 1: Create the flow variable
To create a flow variable, click on the three dots in the top right corner of your Flow Designer. Click on ‘Flow Variables’ to open the modal.
Click on the plus in the top right of the modal to add a flow variable. Configure it to be a Reference/Record on the User table.
Step 2: Initiate the flow variable
Add a new flow logic step “Set Flow Variables” in your flow. Click on the plus in the top right corner of the step to add a new row. Choose the flow variable you created from the drop-down on the left. With data pill dot-walking, set it to the current user’s manager. In our example, this is Trigger - Service Catalog -> Requested Item Record -> Requested for -> Manager. Confirm and close the step.
Step 3: Add do-until logic
As the next step, add a new flow logic step “Do the following until”. We’ll add two steps within this loop and define the exit condition below.
Step 4: Look up the current user’s manager
The first step within the loop is a “Look up Record” action. We’ll set the table to User [sys_user] and set the condition as Sys ID is the Sys ID of the manager to the user currently saved in the flow variable.
Step 5: Set the flow variable
The second step within the loop is another “Set Flow Variables” flow logic. We want to update the flow variable with the next manager in the hierarchy, the result of the previous lookup step.
Step 6: Define the exit condition for the loop
We want to define two different exit conditions for our loop. First, if a manager up the hierarchy is found whose title contains “VP”. The second condition will protect us from an error in our flow. We will also exit the loop if the user we just found does not have a manager. Thus we would not find the next manager in the hierarchy in the next iteration.
Step 7: Ask for Approval
Finally, we get to what we came for. After the end of the do-until block, we can now add an Ask for Approval action, which we will request from the last manager we found and stored in the flow variable.
Second Draft: Further Considerations
Step 8: Workaround with a Timer!
Flow Designer sometimes loops faster than the flow variable can be updated. This can lead to unnecessary loops around the block. To avoid this, add a flow logic. Wait for a duration of time just before the exit condition.
Step 9: Edge Cases!
If you followed along this far, you might have wondered: what if the requestor themselves is a VP or their direct manager is a VP? When you start defining your use cases, you should align with the business process owner to determine what should happen in these edge cases. For example, you can add an if-then-else logic block to your flow to ask for approval from the Requestor’s manager directly if you’re already on the right hierarchy level and skip the whole do-until-loop. Maybe there is a certain group of people or a certain approver that you’ll want to maintain in a decision table to ensure the right people are granting the right approvals.
Step 10: Consider subflows!
This is likely not the only flow where you’ll need this kind of approval. Consider converting this into a subflow or building it as a subflow to begin with.
Step 11: Approvals and now?
Of course, our flow is not fully complete, and you’ll need to add further logic and actions to determine how to proceed once approval was granted or rejected or not given by a specific due date, but for the sake of this article, this is as far as we go. Your use cases that may not be Service Catalog flows will go different routes, and the purpose of this article was to explain how to iterate up a hierarchy chain.
Conclusion
Let’s review our flow once more. We check if the condition is potentially already fulfilled with the current records, and we use a decision table to determine the correct approval group for this edge case. If not, we then call a subflow to repeatedly look up records in a hierarchical chain until we find one that matches our criteria and can use this in any subsequent flow logic.
