0

Formula to compute the value of Office__c is as follows. 

CASE( Account__r.Office__c ,
"Americas","Americas",
"EMEA","EMEA",
"China", "China",
"Hongkong", "Hongkong",
"Taiwan", "Taiwan",
"Korea", "Korea",
"Japan", "Japan",
"India", "India",
"Australia", "Australia",
"Rest of APAC", "Rest of APAC",
"Corporate")

The following formula does not work. PQFMap__c is a custom setting hierarchy. I want to generate a single value relevant to a particular region's office. For example I can have Japan's value. This is the purpose to go with custom setting. I also tried this with CASE but the result was the same. Any help will be appreciable.

IF(Office__c = "Americas", $Setup.PQFMap__c.Americas__c,
IF(Office__c = "EMEA",  $Setup.PQFMap__c.EMEA__c,
IF(Office__c = "Corporate",  $Setup.PQFMap__c.Corporate__c,
IF(Office__c = "Australia",   $Setup.PQFMap__c.Australia__c,
IF(Office__c = "China",   $Setup.PQFMap__c.China__c,
IF(Office__c = "Hongkong",  $Setup.PQFMap__c.Hongkong__c,
IF(Office__c = "Taiwan",   $Setup.PQFMap__c.Taiwan__c,
IF(Office__c = "Korea",   $Setup.PQFMap__c.Korea__c,   
IF(Office__c = "Japan",   $Setup.PQFMap__c.Japan__c,
IF(Office__c = "India",   $Setup.PQFMap__c.India__c,
IF(Office__c = "Rest of APAC",   $Setup.PQFMap__c.Rest_of_APAC__c,""

)))))))))))
Improve this question
11
  • 1
    Are any of these other custom fields that you are referencing in this formula formula fields?
    – cmmoutes13
    Jul 21, 2016 at 13:03
  • @cmmoutes13 These are all for now which I've to use in the formula.
    – Superman
    Jul 21, 2016 at 13:23
  • What he meant was: is Office__c a formula field? If so, can you include it in the question.
    – Nick Cook
    Jul 21, 2016 at 13:32
  • @NickCook It is a custom field which will have a value e.g Americas, EMEA etc. And for each I've a value stored in a custom settings already which I need to save in formula field. Formula Field name is not referred here. It is just the criteria I write here.
    – Superman
    Jul 21, 2016 at 13:40
  • ok. The main reason for getting the 'Complied Formula it too big' error is due to a formula field referencing another formula field. Have you tried doing this in a workflow rule instead?
    – Nick Cook
    Jul 21, 2016 at 13:46

2 Answers 2

Reset to default
1

I'll go with the suggestion of Nick above but you can also try this without custom setting. I see use of custom setting is exaggerating here. Instead you can easily do it with another formula field. Because you already tracking the region in one of your fields.

Just create a map of region to quantity value and use the CASE statement in the formula to track the relevant field for each occurrence. Like this.

CASE( Account__r.Office__c ,
"Americas", Product__r.Available_Qty_Americas__c,
"EMEA", Product__r.Available_Qty_EMEA__c,
"Corporate", Product__r.Available_Qty_EMEA__c,
"Australia", Product__r.Available_Qty_APAC__c,
"China", Product__r.Available_Qty_APAC__c,
"Hongkong", Product__r.Available_Qty_APAC__c,
"Taiwan", Product__r.Available_Qty_APAC__c,
"Korea", Product__r.Available_Qty_APAC__c,
"Japan", Product__r.Available_Qty_APAC__c,
"India", Product__r.Available_Qty_APAC__c,
"Rest of APAC", Product__r.Available_Qty_APAC__c,
0)
Improve this answer
2

When Salesforce calculates the formula size, it includes any formulas referenced from the formula, within the formula. So think of substituting the Office__c variable with the content of the Office__c formula with each occurence of it.

In your case, I'd just replace Office__c with Account__r.Office__c and use the case statement. It works better with picklist values.

i.e. 

Case(Account__r.Office__c,
    "Americas", $Setup.PQFMap__c.Americas__c,
    "EMEA",  $Setup.PQFMap__c.EMEA__c,
    ...
    ...,
    "Default Value")

Having said that, I would have assumed using CASE would have gotten around this as well, because your Office__c formula really isn't that complex.

Improve this answer
7
  • Account__r.Office__c is a picklist. Will it give the correct value for each user? Because it can't be same for every user.
    – Superman
    Jul 21, 2016 at 14:04
  • What object is Office__c on?
    – Nick Cook
    Jul 21, 2016 at 14:05
  • Account object. And I have a look up from this (Samples object) to Account object.
    – Superman
    Jul 21, 2016 at 14:07
  • How is the Samples object relevant to the Office__c field then?
    – Nick Cook
    Jul 21, 2016 at 14:09
  • Samples object has a lookup field to the Account. And this is the only relevance samples object has to Account.
    – Superman
    Jul 21, 2016 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.