Formula field limit reached on the account object
DOWNLOADFirst things first ! Show
Download Tips for Reducing Formula Size salesforcedocs Copyright salesforcecom inc All rights reserved Salesforce is a registered trademark of salesforcecom inc as are other names and marks Other marks appearing herein may be trademarks of their respective owners CONTENTSTips for Reducing Formula Size TIPS FOR REDUCING FORMULA SIZEIn Salesforce formulas are used in
many types of business logic including formula fields default values for fields workflow rules and REDUCING THE LENGTH OF YOUR FORMULAFormula syntax is limited by the maximum size of the database field that stores it For any Salesforce edition youll get an error message IFMONTH MembershipStartDatecDATE YEAR MembershipStartDatecDATE YEAR MembershipStartDatecBecause you repeatedly reference YEARMembershipStartDatec you can move that into a formula field called Start IFStartMonthc DATE StartYearcDATE StartYearcYoure now down to characters from Since there are editionbased limits on
how many fields you can create for an object take Date to be the label for your users but you could conserve space by overriding the default API name MembershipStartDate toStartDate or MemStartDate Override the default name when you create a custom field rather than trying to change it later REDUCING YOUR FORMULAS COMPILE SIZEThe query thats compiled from your formula syntax is limited by the maximum query size that the
database can execute This limit is Minimize the number of references to other fields you reference a field the compile size of that field is added to your current formula A simple field type like a Date is small but for otherformula fields the size can add up DATE YEAR Datec MONTH Datec DAY DatecUsing Datec in a formula adds characters to the compile size each time its referenced Datec adds characters each CASE MOD SomeDatec SomeDatecSomeDatec DATESunday Replacing SomeDatec in the above formula with Datec gives us a formula compile size of characters But replacing Reducing Your Formulas Compile Size So how can we reduce the number of times we reference other fields Leverage the default value in CASEThe last argument in the CASE function is the default value If you have a lot of cases with the same value use it as the defaultto reduce the number of checks Lets take another look at the deadline formula CASE MOD SomeDatec SomeDatecSomeDatec DATESunday The default value in this formula is SomeDatec But the value of MOD Datec DATEis always to so the default value is never used This formula could be rewritten as CASE MOD SomeDatec SomeDatecSomeDatec DATESunday By making the MondayTuesdayWednesday case the default date days we reduce the compile size to characters for Use CASE instead of nested OR statementsThe following formula returns the date of the last day of the month for a given date assume February always has days DATEYEAR SomeDatecMONTH SomeDatecIFORMONTH SomeDatecMONTH SomeDatecMONTH SomeDatecMONTH SomeDatecIFMONTH SomeDatecReducing Your Formulas Compile Size The formula first checks for months with days then February and the remaining months are days It requires a nested IFfunction which isnt very readable and compiles to characters for Datec and a whopping characters for Datec DATEYEAR SomeDatecMONTH SomeDatecCASEMONTH SomeDatecNot only is this easier to read the formula compiles to only characters for Datec and characters for Datec The formula calculates a commission based on Base Commission value and a multiplier But because BaseCommissionc is referenced in each condition of the CASE statement the formula exceeds the compile sizeCASE AgentcJohn BaseCommissionc To fix this move BaseCommissionc outside the CASE function The formula can be rewritten asBaseCommissionc CASE AgentcJohn Even if Base Commission is only a Currency field and not a formula itself referencing it once instead of multiple times greatly reduces CASE MOD SomeDatec DATESunday We have now further reduced the size to characters for Datec and to characters for Datec which is almost Reducing Your Formulas Compile Size Minimize the number of times formula functions are called All formula functions are not compiled equally Daterelated functions in particularlike DATE and MONTHgenerate largequeries The fewer times you need to reference functions the smaller your compile size is likely to be We just saw an example where we rearranged the logic in a CASE statement to reduce the number of times a field was referencedThis strategy also works for formula functions website and looked something like this CASE AirlinecAirline HYPERLINKhttpairlinecom Airline There are so many options in the picklist the formula hits the compile size limit But since the friendly name for the link is the same as the picklist text moving HYPERLINK outside the CASE statement allows it to be referenced just onceHYPERLINKCASE AirlinecAirline httpairlinecom CASE generates the URL for the hyperlinkTEXTAirlinec TEXT generates the friendly nameThe revised formula adds only characters to the compile size for each picklist option instead of Rethink your picklist In the airline and agent commission examples above CASE is used to check each value in a picklist to set a formula field value Ifyou have more than picklist options and you plan to check each possible condition in a CASE statement to determine anothervalue
consider a lookup instead of a picklist Reducing Your Formulas Compile Size Added bonus when you add new agents you dont have to remember to change the formula It just works Think about the problem another way A common formula to format the elapsed time between two DateTime fields to days hours and minutes looks like this where Diffcis one DateTime subtracted from another to get the difference in days IFDiffc TEXTFLOOR Diffc FLOORDiffc hoursTEXTROUND ROUND Diffc FLOORDiffcFLOORROUND Diffc FLOORDiffcminutes This formula checks if the difference is a positive value and if it is several operations calculate values for days hours and minutes in However if Diffc is a more complex formula the elapsed time formula becomes too big to compile Why Count the number of times Diffc is referenced Also all those nested FLOOR and ROUND functions add upTheres a simpler way to think about the problem The MOD function can really help The modulus of the number of hours divided by is the number of hours not accounted for bydays The modulus of number of minutes divided by is the number of minutes not accounted for by hours So we can change the IFDiffc The new version of the formula compiles to characters down from and because it only includes Diffc four times it ORCloseDate DATE YEARTODAY MONTHTODAYIFANDMONTH TODAYCloseDate DATE YEARTODAY MONTHTODAYtrue Reducing Your Formulas Compile Size IFANDMONTH TODAYCloseDate DATE YEARTODAYtrue IFANDMONTH TODAYCloseDate DATE YEARTODAYtrue IFANDMONTH TODAYCloseDate DATE YEARTODAYtrue IFANDMONTH TODAYCloseDate DATE YEARTODAYtrue IFANDMONTH TODAYCloseDate DATE YEARTODAYtrue IFANDMONTH TODAYCloseDate DATE YEARTODAYtrue IFANDMONTH TODAYCloseDate DATE YEARTODAYtrue IFANDMONTH TODAYCloseDate DATE YEARTODAYtrue IFANDMONTH TODAYCloseDate DATE YEARTODAYtrue IFANDMONTH TODAYCloseDate DATE YEARTODAYtrue IFANDMONTH TODAYCloseDate DATE YEARTODAYMONTHTODAYMONTHTODAYMONTHTODAYMONTHTODAYMONTHTODAYMONTHTODAYMONTHTODAYMONTHTODAYMONTHTODAYMONTHTODAYMONTHTODAYReducing Your Formulas Compile Size true false This complex check determines whether the Close Date is earlier than the first day of the current month or later than the last day of this NOTANDMONTH CloseDate MONTH TODAYYEAR CloseDate YEAR TODAYThis new version is much more readable and only compiles to characters compared to more than for the original formula If all else fails use a workflow field update DATEYEAR SomeDatecMONTH SomeDatecIFORMONTH SomeDatecMONTH SomeDatecMONTH SomeDatecMONTH SomeDatecIFMONTH SomeDatecReducing Your Formulas Compile Size Another way we could have solved this is by making Datec a custom Date field instead of a formula field and creating a workflow DATE YEAR Datec MONTH Datec DAY DatecNow the size of Datec is the same as Datec because it contains a simple date and the compile size of the NEED MORE HELPWe hope these examples help when you hit one of the formula size limits If you get stuck try posting your question to the very smart How many formula fields can you have on an object?A Formula that references a field on another object is known as a Spanning Relationship. The limit of spanning relationships per object is 15. This means that an object can only contain up to 15 different object references.
What is the limit of formula field in Salesforce?Character limit — Formula fields can contain up to 3,900 characters, including spaces, return characters, and comments. If your formula needs more characters, create separate formula fields and reference them in another formula field.
How do I create a formula field on an account object in Salesforce?Build a Formula Field. Begin building a formula field the same way you create a custom field. ... . Select the data type for the formula. ... . Choose the number of decimal places for currency, number, or percent data types. ... . Click Next.. Build your formula. ... . To check your formula for errors, click Check Syntax.. What are two characteristics of formula field?Formula fields have slightly different characteristics from a normal field – they are not editable and their values are generated behind the scenes in the Object using the formula editor. If you can handle basic functions in an Excel sheet, then you can handle formula fields.
|