Introduction
Our probono client Baby Give Back is a Charity in Queensland that provides free material aid to families in need in the South East of the state.
With warehouses in Brisbane and the Gold Coast, hundreds of orders, and dozens of social workers accessing their service each week, they needed a way to manage these orders more efficiently and prioritise them automatically.
Most orders are for babies that are not yet born, some are due very soon, and some months away. A smaller number of orders are for babies that are already born, and have nowhere safe to sleep, or no way to travel safely.
Orders are received from social workers via the Baby Give Back website, using Form Assembly and hosted on the Case Object.
The Case Object has a Standard picklist field called Priority and we created a custom Date Field called Birth/Due Date.
When the case is created on the Baby Give Back website, the priority needs to be automatically populated based on the Birth/Due Date, but when the due date comes close to the current date, the priority should be updated automatically.
For example, if the Birth/Due Date during case creation is in the past or within 14 days, then the priority should be ’Urgent’. If it is more than 60 days from the current date, then the priority should be ‘Waitlist’. But when the Birth/Due Date comes into the range between 14 & 60 days, the priority should be changed to ‘Secondary’.
This automation saves the staff from having to review each order manually to determine the urgency.
The use of list views and other fields on the case object like a Status field means the team can group and revise orders quickly.
If the baby is not due for several months, the team does not need to closely scrutinise the order with the same level of detail as they do an urgent order for a baby born or due next week.
Automating this process makes the workload much more manageable and less prone to human error.
Challenge
We wanted to make sure that as the due date nears, the priority field on the Case records would automatically update.
We implemented a solution using Process Builder, however the Process Builder is only invoked if there is a field update.
We needed the Priority to be changed automatically as the current date comes closer to the Birth/Due Date. But the priority should not be affected when the case status is Successful, Cancelled, or Booked for Distribution.
Solution
Use Schedule-Triggered Flow.
The scheduled flow will run everyday and automatically update the priority based on the set conditions.
Go to Setup → Flows → New Flow → Schedule-Triggered Flow → Freeform.
PreRequisites:
Before proceeding to build the flow, we need to create a couple of formulas and collection variables that are used in the flow.
Formula 1: DueWithin14Days
In the Toolbox, go to Manager → New Resource → Resource Type : Formula → Data Type : Date.
Fill in the API name and Formula as in the screenshot -1.
Formula 2: Today_Plus_60_Days
As like formula 1, Create formula 2 as in the screenshot -2
Collection Variable:
In the Toolbox, go to Manager → New Resource → Resource Type : Variable → Data Type : Record → Object : Case.
Select Allow multiple values checkbox
Select Available for input & output checkboxes
Now that the required formulas and variables are created, we can go ahead and create the flow.
1. Set a Schedule
In the Start element, Click on ‘Set Schedule’ and select Start Date, Start Time, and Frequency.
In our case, the flow will run everyday at 03:00 am from 23/08/2021.
2. Get Case Records
From the toolbox, drag the ‘Get Records’ element, label it as ‘Get All cases’ and choose the object ‘Case’.
As stated earlier, the priority should not be affected if the case status is Successful, Cancelled, or Booked for Distribution. So we will filter out those records and pick only the records having a due date value greater than or equal to the current date.
Below are the picklist values to be filtered out:
Successful → Closed Won
Cancelled → Closed Lost
Booked for Distribution → Escalated
After filtering the case records, Choose all records, required fields, and let Salesforce store them in a variable as below:
How Many Records to Store → All Records
How to Store Record Data → Choose fields and let Salesforce do the rest.
3. Loop All Cases
Drag the Loop element from the Toolbox, select the Collection Variable ‘Get All Cases’ which was created in the previous ‘Get All Cases’ element.
Select the direction as ‘First item to last item’.
4. Decision - Due Date Criteria Check
Drag a Decision element from the Toolbox and label it as Due Date Criteria Check. Connect the output of the Loop All Cases (For Each Item) to the Decision element.
The default outcome from the Decision element should be connected back to the Loop, else flow will end in error. The reason for doing this is, If the incoming case record doesn’t match any of the conditions set in the outcome, then it will be sent back to the loop element and the next record will be picked for iteration.
4.1. Outcome - Due Date Within 14 Days
In Decision Element, Create a New Outcome and label it as Due Date Within 14 Days.
Add the below conditions:
- Due date greater than or equal to current date ({!$Flow.CurrentDate})
- Due date less than or equal to DueWithin14Days (Formula field)
- Age value equals Unborn
- Age value is Null
Condition Logic: 1 AND 2 AND (3 OR 4)
4.2. Outcome - Due Date between 14 and 60 days
In Decision Element, Create a New Outcome and label it as Due Date between 14 and 60 days.
Add the below conditions:
- Due date greater than DueWithin14Days (Formula field)
- Due date less than Today_Plus_60_Days (Formula field)
- Age value equals Unborn
- Age value is Null
Condition Logic: 1 AND 2 AND (3 OR 4)
4.3. Outcome - Due Date Greater Than 60 Days
In Decision Element, Create a New Outcome and label it as Due Date Greater Than 60 Days.
Add the below conditions:
- Due date greater than Today_Plus_60_Days (Formula field)
- Age value equals Unborn
- Age value is Null
Condition Logic: 1 AND (2 OR 3)
5. Assignment Element
Drag three Assignment elements from Toolbox and assign each outcome to an Assignment element as below:
In these three assignment elements, we will modify the priority of the case based on the outcome conditions.
Assignment 1: Update Priority to Urgent
In this Assignment element, We are setting the priority value of all the cases that pass through the first outcome to Priority 1.
Outcome (Due Date Within 14 Days) → Assignment (Update Priority to Urgent )
Variable: Choose Priority field from current item from Loop All Cases.
Value: Priority 1
Assignment 2: Update Priority to Secondary
In this Assignment element, We are setting the priority value of all the cases that pass through the second outcome to Priority 2.
Outcome (Due Date greater than 60 Days) → Assignment (Update Priority to Secondary)
Variable: Choose Priority field from current item from Loop All Cases.
Value: Priority 2
Assignment 3: Update Priority to Waitlist
In this Assignment element, We are setting the priority value of all the cases that pass through the third outcome to Priority 3.
Outcome (Due Date between 14 and 60 Days) → Assignment (Update Priority to Waitlist)
Variable: Choose Priority field from current item from Loop All Cases.
Value: Priority 3
Assignment 4: Collection Variable
Drag the output of all the above three Assignment elements to this Assignment element.
In this assignment element, the cases with modified priority will be stored in a collection variable and will be updated in the further steps.
Variable: CollectionVariable
Value: Current item from Loop
The output of this assignment element should be connected to the Loop for the iteration to continue.
Loop, Decision and Assignment elements should look like this:
6. Update Cases
From the Toolbox, Drag the Update Records element and label it as Update Cases.
In Select Records to update, Choose the Collection Variable we created.
The Loop should be connected to the Update Cases element (After Last Item). So once the loop is completed, the Update Cases element will execute and cases will be updated with the modified priority values.
