Using Custom Metadata Tables for Scalable Business Processes in Salesforce

Businesses often need to drive processes using complex calculations. A common use case is calculating a number based on a matrix of two different business attributes, or dimensions. For example, a company might need to determine maximum discount percentages based on the region and size/segment of the account being sold to. Here’s an example matrix:

Traditionally, you might handle this by embedding the logic into a Flow or Apex code. However, such an approach can quickly become cumbersome and difficult to maintain. Imagine the head of sales decides to update Europe’s Midmarket discount from 16% to 17%. To implement this change, you’d need to:

1. Edit the Flow or Apex code.
2. Deploy the updated configuration from sandbox to production.

This introduces unnecessary administrative overhead for a simple change. Instead, using a custom metadata table can provide a streamlined and scalable solution.

What Is a Custom Metadata Table?

A custom metadata table in Salesforce is similar to a custom object but resides in the setup menu. Unlike custom objects, custom metadata tables:

• Are only accessible to system administrators.
• Lack typical user-facing features like Tabs, page layouts, and validation rules.

However, custom metadata tables still function as tables, allowing you to:

• Create custom fields (columns).
• Add records (rows) to store important data.

You can then reference this data in your automated processes to simplify logic and improve maintainability.

Why Use a Custom Metadata Table?

By using custom metadata, you separate the data (e.g., discount percentages) from the business logic (e.g., how discounts are applied). This decoupling simplifies updates and reduces errors. Here’s how it works:

1. The query retrieves the appropriate value (e.g., 10%), which your Flow or Apex logic then applies. This design ensures the logic remains unchanged regardless of updates to the values.

Instead of hardcoding logic like:

IF(Region__c = "North America" && Segment__c = "Small Business", 10%,
   IF(Region__c = "North America" && Segment__c = "Midmarket", 12%,
      ...

You query the custom metadata table:

SELECT DiscountPercentage__c
FROM DiscountMatrix__mdt
WHERE Region__c = 'North America' AND Segment__c = 'Small Business'

How to Create a Custom Metadata Table

Creating a custom metadata table is straightforward:

1. Navigate to Setup:
   • Search for “Custom Metadata Types” in the left-hand search bar.
2. Create the Table:
   • Define the metadata type, including its label and API name.
3. Add Fields:
   • Add custom fields for each attribute you need (e.g., Region, Segment, Discount Percentage).

Creating a Metadata Matrix

Custom metadata tables store data in a tabular format. To replicate a matrix, each combination of attributes must have its own record. For example:

For a business process that determines discounts based on Region and Segment, you’d:

1. Create a custom metadata table with fields for Region, Segment, and Discount Percentage.
2. Populate the table with records for each combination of Region and Segment.

Query the table in your Flow or Apex:

SELECT DiscountPercentage__c
FROM DiscountMatrix__mdt
WHERE Region__c = Account.Region__c AND Segment__c = Account.Segment__c

Advantages of This Approach

• Simplicity: Updates to discounts only require editing metadata records, not code.
• Scalability: The same logic applies regardless of the number of dimensions or permutations.
• Maintainability: Changes can be made directly in production without the need for redeployment.

By decoupling data from logic, you create a more efficient and reliable process that is easier to maintain over time.

It can be helpful to also include a "fallback" record on your Custom Metadata object, so in case there is no complete match based on the unique combinations, your process can still proceed using the fallback data instead.

Conclusion

Custom metadata tables are a powerful tool for managing complex business processes in Salesforce. By leveraging their flexibility and scalability, you can significantly reduce the complexity of your Flow and Apex logic while streamlining updates and maintenance. Try implementing a custom metadata table in your next Salesforce project and experience the benefits firsthand.