Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Jakson MonteiroJakson Monteiro 

Help in constructing the formula

We have two fields criteria and gap which needs to be calculated in Account. both the fields are formula fields.

Criteria checks two things. First it checks for the account Type and than on the basis of the Type of account it checks the Region.

I have constructed the formula for one type of the Account(NON-SAVE) but i am not able to make one for the other type(SAVE) since formulas can be only of 5000 as a limit.both the types need to be calculated in the same formula.Is there any alternative way to do so.

CASE(
Typel__c , 'NON-SAVE',

CASE( Region__c,
'NSW/ACT', 85000,
'VIC/TAS',85000,
'QLD',85000,
'WA',75000,
'SA/NT', 60000,0
),

0) 

Once done with criteria field the Gap will be calculated after subtracting it.


Thanks in Advance
Best Answer chosen by James Loghry
James LoghryJames Loghry
Here are a few tips from Salesforce documentation on how to reduce your formula size: https://help.salesforce.com/help/pdfs/en/salesforce_formula_size_tipsheet.pdf

Also you could try the following:
  • If the switch statements use common values (like 85000) use that as the DEFAULT to reduce some space.  
  • If there are common values across your Account types, move them outside of the switch case statement
  • If all else fails, try using either workflow field updates or triggers or a combination of all 3 (including formulas) to reduce space.  (It would be pretty easy to create a map of Account Types and Regional values, throw them in a custom setting and have them populate your criteria and gap fields, for instance)

All Answers

James LoghryJames Loghry
Here are a few tips from Salesforce documentation on how to reduce your formula size: https://help.salesforce.com/help/pdfs/en/salesforce_formula_size_tipsheet.pdf

Also you could try the following:
  • If the switch statements use common values (like 85000) use that as the DEFAULT to reduce some space.  
  • If there are common values across your Account types, move them outside of the switch case statement
  • If all else fails, try using either workflow field updates or triggers or a combination of all 3 (including formulas) to reduce space.  (It would be pretty easy to create a map of Account Types and Regional values, throw them in a custom setting and have them populate your criteria and gap fields, for instance)
This was selected as the best answer
Yogesh KulkarniYogesh Kulkarni
Hi Jakson,

If anyhow you are not able to put the formula for the field required, place a trigger to calculate and put the required value in record.

Thanks,
Yogesh