From James Barker: Using Formulas with Data Fetcher to Reactively Push the Envelope’s Edge
With all of the news and updates with Reactive Screen Flow Components in tandem with Data Fetcher on the AppExchange, we can take multi-screen processes and simplify the user experience with less clicks and more real-time changes.
This article will go through examples in-depth on creating a Data Table with multiple levels of complexity:
- Single Dimension
- Multidimensional
- Multidimensional Criteria With Reactive Components
- Extending Our Criteria With SOSL
Before We Start – How Data Fetcher and Reactivity Work Together
The solutions discussed here make use of and require the Data Fetcher component. Data Fetcher enables you to dynamically query Salesforce records using SOQL and SOSL to return records in a collection variable based on user interaction on a screen flow. For example, a user can select a specific query from a picklist and return those records to a data table.
For a deeper understanding of Data Fetcher, see Data Fetcher on the AppExchange. In addition, see Take Your Screen Flows to the Next Level with Reactivity | Automate This! on YouTube.
Primary Use-Case
As a project manager, I need to be able to identify which consultants are a good fit for the project that I am staffing using the individual’s department..
Data Fetcher Eliminates Steps
Prior to this package, this would have to be built using separate screens with a GET RECORDS between them. With reactive screen flow components we could dynamically show the criteria on the first screen, but the GET RECORDS would still be in between.
Note that in this scenario, to adjust the Department we had to go back to change the value then repeat the GET RECORDS.
When we add the Data Fetcher component to the screen, we’re able to query for our record data without leaving the screen. To do this we tap into screen reactivity and so need to use reactive components. , I have switched the Picklist component to Quick Choice for the reactivity with Data Fetcher and the Data Table.
Building Simple Formulas for Data Fetcher
To connect our QuickChoice component to our DataTable, we’ll load Data Fetcher with this formula:
Formula
“SELECT Id, Name, Department, Tenure__c FROM Contact WHERE Department= ” + {!DepartmentQuickChoice.value}
See the components that were used below as well as a video walking through the logic for this.
Multidimensional
Sub Use-Case
As a project manager, I need to be able to identify which consultants are a good fit for the project that I am staffing using a list of different types of criteria.
Now we have our single-screen experience to identify the best resource. Our sub-use case requires another dimension for different types of criteria.
In this version we are using two different types of input fields, Quick Choice and Slider. We are using these because we need to be able to pull the “value” into the formula.
Now we have two dimensions in our query for Data Fetcher and thus the Data Table. Note in the formula that we couldn’t just use the value from the Tenure input, but had to convert it to TEXT first.
Formula
“SELECT Id, Name, Department, Tenure__c FROM Contact WHERE Department= ” + {!DepartmentQuickChoice.value} + “AND Tenure__c>=” +TEXT({!Tenure.value})
Aggregated Queries
Data Fetcher is not only great for managing the queries for a Data Table, but also for getting aggregations such as SUM or COUNT.
With what is available OOTB today with Flows, you are not able to do any sort of aggregation for search results. At best, we use assignments to handle a similar count, but this would require splitting up this experience into multiple screens/sections.
This is another reason why SOQL and SOSL are so powerful to use with Data Fetcher as part of our user experience.
Below you can see the Total Results reacting as the Tenure slider moves up and down.
Formula
“SELECT Count(Id) FROM Contact WHERE Department= ” + {!DepartmentQuickChoice.value} + “AND Tenure__c>=” +TEXT({!Tenure.value})
Multidimensional Criteria With Reactive Components
Our original use-case was: As a project manager, I need to be able to identify which consultants are a good fit for the project that I am staffing using a list of different types of criteria.
Now with a combination of reactive components, we have the following all embedded in a single experience:
Label Formula
<div class=”slds-box”>Feedback Notes: <br>{!Results.firstSelectedRow.Notes__c}</div>
Formula
“SELECT Id, Name, Department, Tenure__c, Notes__c, Title,Ptnr_Onbd_Fmwk__Onboarding_Status__c FROM Contact WHERE Department= ” + {!DepartmentQuickChoice.value} + “AND Tenure__c>=” +TEXT({!Tenure.value})
Extending Our Criteria With SOSL
Suppose you want to solve for multiple attributes within a single criteria. This could be multiple skills for a contact, where skills are stored as a custom object and are based on records.
With SOQL statements, we would require the data to be structured to the point where each skill would require its own lookup to map to a specific field on the contact. This is not an optimal experience.
With SOSL, you would be able to extend the search capabilities across multiple attributes within the criteria and not need the data to reside in specific mapped fields.
Here, I wanted to build a way to use a list of skills that our Project Manager in the original use-case can use to further filter their talent.
So, how do we do this with SOSL and Data Fetcher?
First, we need a multi-select option to select our skills that will output all of the records in a consistent format. Enabling multi-select for the standard lookup input will work well as a simple UI for a user to interact with.
Once we have the output, then we need to be able to do a query off of it.
The output of the lookup component looks like:
a7cHs0000006Bp8IAE,a7cHs0000006BpDIAU
The challenge with this is that a SOSL statement requires the use of “ OR” in order to add multiple search terms. So we need to clean the output before passing it to the actual SOSL statement. To do this, I used the Substitute formula.
Original SUBSTITUTE function:
SUBSTITUTE({!Skills.recordIds}, “,”, ” AND “)
The next challenge with this is that this will only substitute the first “ ,” and not any subsequent. So we need to iterate on this to match the number of records we allow the user to search for in the lookup.
The first iteration of the clean-up for the SUBSTITUTE is:
SUBSTITUTE({!CleanSkillsList1}, “,”, ” AND “)
Now our output from the lookup component is formatted the way we need it for our SOSL statement.
Specifically for the SOSL query, we have:
“Find {” + {!CleanSkillsList} +”} IN ALL FIELDS RETURNING Contact(id,name)”
Now, I can write a simple query to get the right contacts with the right skills (there is a separate flow that when a skill is added to the contact, we stamp the ID onto the record detail directly for this search).
New Use Case: Multiple Data Tables & Related Lists For A Consumer Goods Brand
Let’s look at another use-case that can be built with formulas and Data Fetcher. This solution also uses Flow Button Bar by David Fromstein.
As a Campaign Manager for a consumer goods brand, I want to make sure that every store (example Loblaws, Whole Foods, etc) has the right promotional setup so that we know that they are in compliance.
Effectively, this role wants to see images from a Promotion and compare it to the images from multiple store Visits.Note that visits are related to the account and the account represents the store.
SOQL query for Promotions
“SELECT Id, Name, Image_1_Preview__c, Image_1_View__c, Image_2_Preview__c, Image_2_View__c FROM Promotion WHERE Name =” + {!PromotionChoice.value}
SOQL query for Visits
“Find {” + {!DynamicStoreId} +”} IN ALL FIELDS RETURNING Visit(id,name,AccountId,cgcloud__Subject__c, Image_1__c ,Image_1_Preview__c,Image_1_View__c, Image_2__c, Image_2_Preview__c, Image_2_View__c WHERE cgcloud__Completed_Date__c > “+{!FromCleanDate}+” AND cgcloud__Completed_Date__c < “+{!ToCleanDate}+”)”
Example substitute formula to clean the lookup records
SUBSTITUTE({!Stores.recordIds}, “,”, ” OR “)
Date clean-up formula
TEXT(YEAR({!From})) & “-” & {!FromMonthCleanup} & “-” & {!FromDayCleanup}
Day data clean-up formula for SOSL format
CASE(Day({!From}),
1,”01″,2,”02″,3,”03″,4,”04″,5,”05″,6,”06″,7,”07″,8,”08″,9,”09″,10,”10″,11,”11″,12,”12″,13,”13″,14,”14″,15,”15″,16,”16″,17,”17″,18,”18″,19,”19″,20,”20″,21,”21″,22,”22″,23,”23″,24,”24″,25,”25″,26,”26″,27,”27″,28,”28″,29,”29″,30,”30″,”31″)
Thank you so much for following along with this article. I hope that it helped give some great ideas for how you can be leveraging Data Fetcher and Reactive Screen Flow Components.
If you have any questions or feedback, please comment down below.