How do I Display Custom Metadata in a Custom Field? - Answers - Salesforce Trailblazer Community
Ask Search:
Stephen MaderStephen Mader 

How do I Display Custom Metadata in a Custom Field?

Hello Trailblazers,

I hope everyone is doing well in 2020. Can I please ask for help with my current situation? Here is the scenario that I find myself in. I need to display on the account page a tax rate based on Shipping Address fields. I have created custom metadata in my org with the following fields:

Custom Metadata API Name: Tax_Rate__mdt
 
  • Country_Code__c
  • Country_Name__c
  • Country_Province__c
  • Country_Province_Code__c
  • Country_State__c
  • Country_State_Code__c
  • Tax_Rate__c
  • My custom metadata contains tax rates for both US and Canada
  • Tax rates for Canada are based on two variables: Country and Province
  • Tax rates for the US are based on three variables: Country, State, City
  • There are over 1,800 entries in my managed data
  • I’ve placed a custom field called “Tax Rate” on the account page. (Tax_Rate__c)

I’ve completed the following Trailheads:
Configure Your App with Custom Metadata Types (https://trailhead.salesforce.com/en/content/learn/trails/configure-your-app-with-custom-metadata-types" style="color:blue; text-decoration:underline)

I found the following similar scenario another Trailblazer posted:
Referencing Custom Metadata Type from formula field (https://success.salesforce.com/answers?feedtype=RECENT&criteria=BESTANSWERS#!/feedtype=SINGLE_QUESTION_SEARCH_RESULT&id=9063A0000019fs9QAA" style="color:blue; text-decoration:underline)

I’m now lost as to my next step(s). Can this be a simple validation rule for my custom Tax Rate field? My validation rule / formula writing skills are weak. How do I create an IF statement with multiple possible variables?

IF BillingCountry = ? AND BillingState = ? AND BillingCity = ? then insert Tax rate associated with the correct custom metadata type here.

From the post I read above, will I need to use a combination of Flow and Process Builder to update the custom tax rate field? Any and all suggestions are welcome. Thank you for the help and it is super appreciated.
 
Best Answer chosen by Stephen Mader
Amnon KruviAmnon Kruvi
Unfortunately, you can't do this with a formula. If you need to do this declaratively (without code), you will have to build a flow. It may seem difficult, but we can walk through it together. What you'll need to do is this:
1. Create a new flow.
2. Create the following variables in the flow:
2.1. AccountID
2.2. BillingCountry
2.3. BillingState
2.4. BillingCity
All variables will have the Text type, and all will have the Available as Input checkbox selected.
3. Add a Get Records step to the flow. The Get Records step will look at the custom metadata object you created (Tax Rate). Select to choose records matching conditions, and add the billing country, state, and city fields. The value for each field will be the relevant variable that you created in step 2. At the bottom of the step, tick the option to put null if no records are found.
4. Use a decision step to check if the field you want to copy to the account from the tax rate retrieved by step 3 is null. I'd imagine this would be some sort of numeric field called Rate or something similar.
5. Add an Update Record step after the decision step. Connect the decision step to it so that it only runs if the tax rate field is not null. The Update Record step should update the Account object and have a condition of the ID field being equal to the AccountID variable from step 2. You would want to update a custom field on the account "Tax Rate" (number, percent, or whatever data type you want to use) with the value you retrieved from step 3.
6. Using process builder, identify whenever a new account is created and the billing country is USA, and billing state/city are not empty, or when the country/state/city change. Add an immediate action to execute the flow, passing the account ID, billing state, billing country, and billing city onto the input variables.
7. Add another criteria that checks for Canada. It will be similar to step 6, but will not check the city. It will also not set a value in the city input variable.

And that should do it. It isn't the most simple solution, but the closest you can get without code :)

All Answers

Amnon KruviAmnon Kruvi
Hi Stephen, 

I don't think any of the methods you mentioned could help here. Other than code, the only way you would be able to scan the metadata records by values is with a flow, which you could launch automatically using process builder. 
Stephen MaderStephen Mader
Hey Amnon, 

Thank you for your insight. Practice makes perfect and I know that I need as much experience with creating Flows as possible. The power is incredible. Can I create an advanced formula field (https://help.salesforce.com/articleView?id=custommetadatatypes_formula_fields.htm&type=0) on my account page called "Tax Rate", then use a formula that reads the "Billing Country", "Billing State/Province", "Billing City" of an account and match those fields to those in my custom metadata, therefore displaying the tax rate saved in my metadata?  
Amnon KruviAmnon Kruvi
Unfortunately, you can't do this with a formula. If you need to do this declaratively (without code), you will have to build a flow. It may seem difficult, but we can walk through it together. What you'll need to do is this:
1. Create a new flow.
2. Create the following variables in the flow:
2.1. AccountID
2.2. BillingCountry
2.3. BillingState
2.4. BillingCity
All variables will have the Text type, and all will have the Available as Input checkbox selected.
3. Add a Get Records step to the flow. The Get Records step will look at the custom metadata object you created (Tax Rate). Select to choose records matching conditions, and add the billing country, state, and city fields. The value for each field will be the relevant variable that you created in step 2. At the bottom of the step, tick the option to put null if no records are found.
4. Use a decision step to check if the field you want to copy to the account from the tax rate retrieved by step 3 is null. I'd imagine this would be some sort of numeric field called Rate or something similar.
5. Add an Update Record step after the decision step. Connect the decision step to it so that it only runs if the tax rate field is not null. The Update Record step should update the Account object and have a condition of the ID field being equal to the AccountID variable from step 2. You would want to update a custom field on the account "Tax Rate" (number, percent, or whatever data type you want to use) with the value you retrieved from step 3.
6. Using process builder, identify whenever a new account is created and the billing country is USA, and billing state/city are not empty, or when the country/state/city change. Add an immediate action to execute the flow, passing the account ID, billing state, billing country, and billing city onto the input variables.
7. Add another criteria that checks for Canada. It will be similar to step 6, but will not check the city. It will also not set a value in the city input variable.

And that should do it. It isn't the most simple solution, but the closest you can get without code :)
This was selected as the best answer
Stephen MaderStephen Mader
Thank you for the step by step! I don't think I have everything set up correctly but here is what I have so far. 

My Flow Screen

Get Records
Get Records
Decision
Decision 2
 
Stephen MaderStephen Mader

Update Records

I have some of the Process Builder completed but I'll perfect the Flow first. So my question are:
  • I think I have the "Get Records" elements properly setup.
  • For the "Decision" element, should the resource be {!Look_at_Custom_Metadata}? 
  • If "Tax Rate Null" connects to "Update Records", what should "Tax Rate Not Null" connect to?
  • For the "Update Records" element the "Value" field isn't populating anything and therefore asking me to create a New Resource or "{!$Flow.}"  
Thank you for the help so far Amnon! You are leading me in the right direction and helping me with Flow and Process Builder experience. I really appreciate this.   
Amnon KruviAmnon Kruvi
Tax rate not​​​​ null should connect to the update step, whereas the other should not connect to anything (end the flow).

I noticed you've got a Province field on the metadata as well, and you're comparing it to state. Are you sure that's correct? 

In the Get Records step, you only need to return the tax rate into a tax rate variable. You already have the country, state, and city. You can then use that variable in the decision step and update step.
Stephen MaderStephen Mader
Amnon, I thank you! By using the debug tool I was able to ensure that the Flow you helped me build works. I have more experience with Process Builder so it is working in conjunction with the Flow.

You were right to point out, "I noticed you've got a Province field on the metadata as well, and you're comparing it to state. Are you sure that's correct?" I did have custom metadata with a field labeled "Country_Province__c" but edited my custom metadata and removed it.

You provided the right amount of guidance, pointing me in the right direction, and you shed light on a topic that I now have a better understanding of. I'm not an expert YET but you helped make me better at building Flows and have now helped me become better at my job. Thank you Amnon very much!   
Amnon KruviAmnon Kruvi
Stephen, I'm very happy to hear this :)
A lot of people come here asking for step by step instructions, and will come back the next day asking for the exact same thing. It's the people like you, who take the time to actually learn the tools and concepts used in a solution, who are the real reason I'm here.