Use Flow and Custom Metadata Types in a Simple and Sustainable Multi-Variable Formula | Automate This!

Business use case: Quickly communicate quality across multiple rubrics

I manage Salesforce for EdReports, an independent nonprofit that increases the capacity of school and district leaders to seek, identify, and demand the highest quality instructional materials to ensure all students are able to learn and grow. The focus of the organization’s work is facilitating and publishing free, independent educator reviews of K-12 curriculum. District decision-makers then use EdReports’ reviews as a tool when making the best decisions for their local communities. (Curious to know more? Read about EdReports’ Review Process.)

My collaborator and I (shoutout to fellow Salesforce professional Regan Spurlock!) designed a suite of custom objects to support the review process, including the Grade Level Report object to collect the final scoring data from a review for a specific grade level.

A user entering data on a Grade Level Report record should be able to enter raw scores as numbers and use Salesforce to determine whether a score meets, partially meets, or does not meet expectations of high quality. We also want score analysis to be instantly readable through visual indicators — 🟢, 🟡, 🔴, or ⚪️ if blank. Sounds simple enough, right?

The obstacle: Lots of variables

EdReports publishes reviews across multiple content areas from kindergarten to high school; applying one rubric across the board would be highly inappropriate. I analyzed our existing review tools across grade levels and content areas and found 13 rubrics calling for differentiation. EdReports content experts regularly iterate on those review tools to ensure they align with the latest educational research, college and career-ready standards, and the needs of the field, so those 13 review tool rubrics have multiple versions. Adding additional complexity, some of those review tool versions have rules or score modifiers that need to be entered and tracked independently of the raw score; for example, for one version of a review tool, a 🟢 requires no score of zero for any sub-scores/indicators.

If only one or two variables determined whether a raw score received 🟢, 🟡, or 🔴, the functionality needed could be taken care of solely by IF statements in a formula. But in this case, a correct assessment and assignment of a brightly-colored ball emoji depends upon four variables.

• The raw score
• The Review Tool used to determine that score
• The version of the Review Tool used
• Any triggered scoring modifiers that apply to that Review Tool version

I needed a solution that allows me to store and access data on these four variables, and how they relate, as well as an automated solution. And this solution should hold historical data and still be sustainably iterable.

Enter custom metadata types and Flow!

What are custom metadata types?

Custom metadata types are objects designed to hold metadata. You build them similarly to building a custom object, with custom fields and page layouts. But rather than building an object to hold records for end users, you’re building an object to hold records for your Salesforce instance to utilize, including in automation. I like to use custom metadata types to collect and store data for business policies, conditional standards, or any other values I might be tempted to hard code. It’s a great option for storing EdReports’ Review Tool data.

First, I mapped out the custom fields I need for a Review Tool custom metadata type (recognizable in API as “Review_Tool__mdt”). A Review Tool custom metadata type record needs to hold data on the content and grade area for the tool, the tool version, and the raw score cutoffs for each scoring area. I created the Content and Grade field and Tool Version field as picklists since they will be used later to match with Grade Level Report records and have finite, defined inputs. With only three possible labels for the quality of a review — Meets (🟢), Partially Meets (🟡), and Does Not Meet (🔴) — that leaves only two numerical cutoffs to track: the cutoff between 🟢 & 🟡 and 🟡 & 🔴. All in all, it’s only eight custom fields total: Content and Grade, Tool Version, and a Meets and Does Not Meet field for each of the three scoring areas known as Gateways.

📚 LESSON LEARNED: The values for Content and Grade and Tool Version picklists are how Flow will identify which Review Tool record to use when calculating values for a Grade Level Report record. Save yourself some debugging and match picklist API values exactly between the applicable object fields and the custom metadata type fields.

The Review Tool custom metadata type is ready! I’ve defined a naming convention and can start to create a Review Tool record for each version of the 13 review tools. I can create those records through two click paths.

From the All Custom Metadata Types page, I click the Manage Records action followed by the New button, or...

... from the page overviewing my Review Tool custom metadata type, I click the Manage Review Tools button followed by the New button.

After entering all my custom metadata type records (and creating some helpful list views), I can see, edit, and delete my Review Tool custom metadata type records just like any other record in Salesforce.

Before diving into Flow, I double-check that the picklist values on the Grade Level Report object — Review Tool and Tool Version — match with my Review Tool custom metadata type picklist values for Content and Grade and Tool Version.

Make it flow!

I’ve created all my Review Tool custom metadata type records, and now it’s time to build my flow. When it’s finished, the flow for each Gateway looks like this:

I want the user experience to resemble that of a formula field, so I’ll create a record-triggered flow on the Grade Level Report object. The flow will run when the record is created or updated if any of the following conditions are true.

• Created Date is null (that is, there is no created date).
• The field that holds the raw score, Gateway 1 Total, is changed.
• Review Tool is changed.
• Tool Version is changed.
• The score modifier, Gateway 1 indicator scored 0, is changed.

Later on, you’ll notice that all these fields except Created Date hold data important to calculating the correct Gateway label: 🟢, 🟡, 🔴, or ⚪️.

I’m editing the record that started the flow, so I can utilize Fast Field Updates.

Next, I create my flow variables. These are the placeholders for record information that can change from record to record.

• The raw score
• The Review Tool used
• The Tool Version used
• Whether or not the score needs to be modified

I create four flow variables: vGatewayTotal, vReviewTool, vToolVersion, and vIndicatorScoredZero.

NOTE: Could I have skipped creating and assigning variables by using field values instead? Absolutely. I chose this approach because I found the variables were easier to read throughout the flow. I’m also trying to make it clear to future admins what is going on in the flow at a glance.

📚 LESSON LEARNED: The “v” in my naming convention tells me at a glance that these flow resources are variables. You’ll see I use “f” later on in a naming convention for my formula.

Next, I’ll start to add elements.

The first step in my flow is to assign Grade Level Report field values to my variables using the Assignment element. The vGatewayTotal variable is equal to the Gateway Total field, the vReviewTool variable is equal to the Review Tool field, the vToolVersion variable is equal to the Tool Version field, and the vIndicatorScoredZero variable is equal to the Gateway 1 indicator scored 0 field.

Now, the custom metadata types come into play.

With the flow variables set, the next step of my flow is to find the Review Tool custom metadata type record that matches my variables. I do that by adding a Get Records element.

I set the Get Records element to look in my new custom metadata type object, Review Tool, for Review Tool custom metadata type records where the Content and Grade field I created matches the vReviewTool variable, and the custom metadata type Tool Version field matches the vToolVersion variable.

Since I’m looking for a single record, I leave the records unsorted and store only the first record. My custom metadata type record doesn’t have many fields, so I have the flow store all the record’s fields automatically, but you could limit it to hand-picked fields to speed up your flow.

With the flow variables set, the next step of my flow is to find the Review Tool custom metadata type record that matches my variables. I do that by adding a Get Records element.

I set the Get Records element to look in my new custom metadata type object, Review Tool, for Review Tool custom metadata type records where the Content and Grade field I created matches the vReviewTool variable, and the custom metadata type Tool Version field matches the vToolVersion variable.

Flow has now created another resource for me automatically: a record variable to hold the custom metadata type record retrieved by the Get Records element. From that record variable, I can access all the field values I’ve stored in the flow.

I’ve got all the values I need to calculate whether this Gateway earns a 🟢, 🟡, 🔴, or ⚪️. It’s time to craft a formula.

I create a formula as a new resource and name it fGatewayScore. I set the Data Type as Text since my visual indicators are emojis. The formula itself is a series of IF statements.

IF(
ISBLANK(
{!vGatewayTotal}
),
"⚪️",
IF(
{!vGatewayTotal}<{!Find_Review_Tool_CMDT_record.Gateway_1_Does_Not_Meet__c},
"🔴",
IF(
AND(
{!vGatewayTotal}>={!Find_Review_Tool_CMDT_record.Gateway_1_Meets__c},
{!vIndicatorScoredZero}=FALSE
),
"🟢",
"🟡"
)
)
)

Formula translation:
If the Gateway Total variable is blank, display ⚪️.
If it’s less than the Does Not Meet field on the Review Tool custom metadata type record, display 🔴.
If the Total variable is greater than or equal to the Meets field on the Review Tool custom metadata type record
and the Indicator modifier has not been checked true, display 🟢. Both have to be true to get 🟢.
Otherwise, display 🟡.

With my formula ready to go, I create the last element of my flow: another Assignment element to set the value for the Grade Level Report picklist — Gateway 1 — equal to the value calculated by fGatewayScore.

📚 LESSON LEARNED: The Grade Level Report page uses Dynamic Forms. Using record field values, I can surface and hide fields from my end users. The Gateway 1 indicator scored 0 field doesn’t apply to every Review Tool version. By hiding it until it’s needed, I can prevent input errors and simplify my flow and formula.

Here’s the completed record-triggered flow!

I’ve checked the formula syntax, saved the flow, debugged and tested... it’s ready to activate! Here’s a test of the flow from a user’s perspective. As the user edits field values for the flow variable inputs — Gateway 1 Total, Review Tool, Tool Version, and Gateway 1 indicator scored 0 — on a Grade Level Report record, the ball emoji in the Gateway 1 field changes color.

Possible future iterations

Salesforce Admins embrace meaningful iteration. Here are things on my radar regarding this solution.

• I currently have three independent flows, one for each Gateway scoring area for simpler-looking flows and more targeted troubleshooting. A future version may combine all three using Decision elements and subflows.
• As I mentioned earlier, the initial Assignment element is not technically necessary but aids transparency and sustainability. If I were to remove this Assignment element, I would make sure any Descriptions and other documentation clearly listed what data is needed for the flow’s formula.
• As EdReports content experts iterate, new Review Tool custom metadata type records will need to be created, and picklist values on the Review Tool field and Tool Version field will need to be edited. It’s also possible new modifiers may need to be added and the Dynamic Form adjusted. And there’s always the possibility that EdReports will add another new content review area. I can easily add new fields and edit picklist values on the Review Tool custom metadata type to create space for EdReports’ future.
• Is there a future where this data is collected and analyzed on a more granular level? What would need to be true to make that worthwhile and not over-designed?

Which complex calculation are you ready to tackle with custom metadata types and Flow? Share with us by tagging us on X or LinkedIn or using #AwesomeAdmin.

Resources

• Salesforce Help: What are Custom Metadata Types?
• Trailhead: Custom Metadata Types Basics
• Salesforce Admins Blog: The Ultimate Guide to Flow Best Practices and Standards
• External Site: Salesforce Ben: Introduction to Custom Metadata Types in Salesforce
• External Site: Salesforce Flowsome: How to use Custom Metadata Types

Want to see more good stuff? Subscribe to our channel!