Shortening IF formula for multiple variations of a Custom naming convention Field

Question

I have a Custom Field formula that spits out different naming conventions according to specific rules. The main issue I'm having is when I try to add additional IF statements, my compiled formula goes over the 5,000 character limit. Is there anyway to shorten the below formula in order to add new naming conventions for different clients? I know that the date can be shortened to TEXT(Date field), but the format that gives me is not ideal since I want MM.DD.YY. Would using a CASE function lower the character count?

IF(
 AND(CONTAINS(Account.Parent.Name ,"CLIENT1")= TRUE, 
 ISBLANK(Combo_Event_1__c)=FALSE, 
 ISBLANK(Flight_Name__c)=TRUE),
 Show_Code__r.Name & "_" & 
 SUBSTITUTE(Combo_Event_1__r.VenueCity__c," ","") & "-" &  
 SUBSTITUTE(Combo_Event_2__r.VenueCity__c," ","") & "_" &
 TEXT(Month(Show_Close_Date__c)) & "." & 
 TEXT(DAY(Show_Close_Date__c)) & "." & 
 Right(text(YEAR(Show_Close_Date__c)),2) & "_" & 
 "CustomName" & "_" &  
 ID__c 

,

IF(
 AND(CONTAINS(Account.Parent.Name ,"CLIENT1")= TRUE, 
 ISBLANK(Combo_Event_1__c)=FALSE, 
 ISBLANK(Flight_Name__c)=FALSE),
 Show_Code__r.Name & "_" & 
 SUBSTITUTE(Combo_Event_1__r.VenueCity__c," ","") & "-" &  
 SUBSTITUTE(Combo_Event_2__r.VenueCity__c," ","") & "-" & 
 SUBSTITUTE(Flight_Name__c," ","") & "_" & 
 TEXT(Month(Show_Close_Date__c)) & "." & 
 TEXT(DAY(Show_Close_Date__c)) & "." & 
 Right(text(YEAR(Show_Close_Date__c)),2) & "_" & 
 "CustomName" & "_" &  
 ID__c 

,

IF(
 AND(CONTAINS(Account.Parent.Name ,"CLIENT1")= TRUE, 
 ISBLANK(Combo_Event_1__c)=TRUE, 
 ISBLANK(Flight_Name__c)=TRUE),
 Show_Code__r.Name & "_" & 
 SUBSTITUTE(VenueCity__c," ","") & "_" &
 TEXT(Month(Show_Close_Date__c)) & "." & 
 TEXT(DAY(Show_Close_Date__c)) & "." & 
 Right(text(YEAR(Show_Close_Date__c)),2) & "_" & 
 "CustomName" & "_" &
 ID__c

,

IF(
 AND(CONTAINS(Account.Parent.Name ,"CLIENT1")= TRUE, 
 ISBLANK(Combo_Event_1__c)=TRUE, 
 ISBLANK(Flight_Name__c)=FALSE),
 Show_Code__r.Name & "_" & 
 SUBSTITUTE(VenueCity__c," ","") & "_" &
 TEXT(Month(Show_Close_Date__c)) & "." & 
 TEXT(DAY(Show_Close_Date__c)) & "." & 
 Right(text(YEAR(Show_Close_Date__c)),2) & "_" & 
 "CustomName" & "_" &
 ID__c

,

IF(
 AND(CONTAINS(Account.Parent.Name ,"CLIENT2")= TRUE, 
 ISBLANK(Flight_Name__c)=TRUE),
 Show_Code__r.Name & "_" & 
 SUBSTITUTE(VenueCity__c," ","") & "_" &
 TEXT(Month(Show_Close_Date__c)) & "." & 
 TEXT(DAY(Show_Close_Date__c)) & "." & 
 Right(text(YEAR(Show_Close_Date__c)),2) & "_" & 
 "CustomName" & "_" &
 ID__c

,

IF(
 AND(CONTAINS(Account.Parent.Name ,"CLIENT2")= TRUE, 
 ISBLANK(Flight_Name__c)=FALSE),
 Show_Code__r.Name & "_" & 
 SUBSTITUTE(VenueCity__c," ","") & "-" & 
 SUBSTITUTE(Flight_Name__c," ","") & "_" &
 TEXT(Month(Show_Close_Date__c)) & "." & 
 TEXT(DAY(Show_Close_Date__c)) & "." & 
 Right(text(YEAR(Show_Close_Date__c)),2) & "_" & 
 "CustomName" & "_" &
 ID__c

,

IF(
 AND(CONTAINS(Account.Parent.Name,"CLIENT3")= TRUE), 
 Account.Name & "_" & 
 'FY' & 
 RIGHT(TEXT( YEAR(  Target_End_Date__c  ) + IF( MONTH(  Target_End_Date__c  ) > 6, 1, 0)), 2) & "_" & 
 TEXT(Tactic__c) & "_" & 
 SUBSTITUTE(Flight_Name__c," ","") & "_" & 
 ID__c

,

""

)))))))

Any suggestions are much appreciated.

SOLUTION - this lowered the character count by over 1000 in SF. There is most likely more room for optimization:

IF(CONTAINS(Account.Parent.Name,"CLIENT1"),
    IF(ISBLANK(Combo_Event_1__c),
        IF(ISBLANK(Flight_Name__c),
            Show_Code__r.Name & "_" &
            SUBSTITUTE(VenueCity__c," ","") & "_" &
            TEXT(Month(Show_Close_Date__c)) & "." &
            TEXT(DAY(Show_Close_Date__c)) & "." &
            Right(text(YEAR(Show_Close_Date__c)),2) & "_" &
            "TACTIC" & "_" &
            ID__c
            ,
            Show_Code__r.Name & "_" &
            SUBSTITUTE(VenueCity__c," ","") & "_" &
            TEXT(Month(Show_Close_Date__c)) & "." &
            TEXT(DAY(Show_Close_Date__c)) & "." &
            Right(text(YEAR(Show_Close_Date__c)),2) & "_" &
            "TACTIC" & "_" &
            ID__c
            ),
        IF(ISBLANK(Flight_Name__c),
            Show_Code__r.Name & "_" &
            SUBSTITUTE(Combo_Event_1__r.VenueCity__c," ","") & "-" &
            SUBSTITUTE(Combo_Event_2__r.VenueCity__c," ","") & "_" &
            TEXT(Month(Show_Close_Date__c)) & "." &
            TEXT(DAY(Show_Close_Date__c)) & "." &
            Right(text(YEAR(Show_Close_Date__c)),2) & "_" &
            "TACTIC" & "_" &
            ID__c
            ,
            Show_Code__r.Name & "_" &
            SUBSTITUTE(Combo_Event_1__r.VenueCity__c," ","") & "-" &
            SUBSTITUTE(Combo_Event_2__r.VenueCity__c," ","") & "-" &
            SUBSTITUTE(Flight_Name__c," ","") & "_" &
            TEXT(Month(Show_Close_Date__c)) & "." &
            TEXT(DAY(Show_Close_Date__c)) & "." &
            Right(text(YEAR(Show_Close_Date__c)),2) & "_" &
            "TACTIC" & "_" &
            ID__c
            )),
IF(CONTAINS(Account.Parent.Name,"CLIENT2"),
    IF(ISBLANK(Flight_Name__c),
        Show_Code__r.Name & "_" &
        SUBSTITUTE(VenueCity__c," ","") & "_" &
        TEXT(Month(Show_Close_Date__c)) & "." &
        TEXT(DAY(Show_Close_Date__c)) & "." &
        Right(text(YEAR(Show_Close_Date__c)),2) & "_" &
        "TACTIC" & "_" &
        ID__c
        ,
        Show_Code__r.Name & "_" &
        SUBSTITUTE(VenueCity__c," ","") & "-" &
        SUBSTITUTE(Flight_Name__c," ","") & "_" &
        TEXT(Month(Show_Close_Date__c)) & "." &
        TEXT(DAY(Show_Close_Date__c)) & "." &
        Right(text(YEAR(Show_Close_Date__c)),2) & "_" &
        "TACTIC" & "_" &
        ID__c
        ),
IF(CONTAINS(Account.Parent.Name,"CLIENT3"),
    Account.Name & "_" &
    'FY' &
    RIGHT(TEXT(YEAR(Target_End_Date__c)+IF(MONTH(Target_End_Date__c)>6,1,0)),2) & "_" &
    TEXT(Digital_Tactic__c) & "_" &
    SUBSTITUTE(Flight_Name__c," ","") & "_" &
    ID__c
    ,
IF(CONTAINS(Account.Parent.Name,"CLIENT4"),
    Account.Name & "_" &
    'FY' &
    RIGHT(TEXT(YEAR(Target_Campaign_End_Date__c)+IF(MONTH(Target_Campaign_End_Date__c)>6,1,0)),2) & "_" &
    TEXT(Month(Target_Campaign_End_Date__c)) & "." &
    TEXT(DAY(Target_Campaign_End_Date__c)) & "." &
    Right(text(YEAR(Target_Campaign_End_Date__c)),2) & "_" &
    "TACTIC" & "_" &
    ID__c
    ,
IF(CONTAINS(Account.Name,"CLIENT5"),
    Account.Name & "_" &
    TEXT(YEAR(Target_Campaign_End_Date__c)) & "_" &
    TEXT(Month(Target_Campaign_End_Date__c)) & "." &
    TEXT(DAY(Target_Campaign_End_Date__c)) & "." &
    Right(text(YEAR(Target_Campaign_End_Date__c)),2) & "_" &
    "TACTIC" & "_" &
    ID__c
,
""
)))))

Just a suggestion. Maybe you could edit your code a touch so it becomes easier to read and compare the different parts? (Right now i have to scroll left and right, top to bottom). I think i saw some results that have the same format. If so, you can add those together in one logical test. That could save you a few lines. — Kasper, Commented Mar 13, 2017 at 16:27
@Kasper I edited the code per your suggestion. Please let me know if that format works for you. — Skobiwankanobi, Commented Mar 13, 2017 at 17:01
Much better :). So i gave it a quick look and found that you have the following outcome at three different ifs. You could merge those three logical tests into one big test, and save yourself three times this outcome. Show_Code__r.Name & "" & SUBSTITUTE(VenueCity__c," ","") & "" & TEXT(Month(Show_Close_Date__c)) & "." & TEXT(DAY(Show_Close_Date__c)) & "." & Right(text(YEAR(Show_Close_Date__c)),2) & "" & "CustomName" & "" & — Kasper, Commented Mar 13, 2017 at 17:22
@Kasper Can you give me an example of what it would look like? I'm not sure I understand your suggestion, since each IF statement applies to a specific ISBLANK True/False variable. Each IF statement is unique at the moment since it either includes/excludes a "Flight" or "Combo". — Skobiwankanobi, Commented Mar 13, 2017 at 18:07

sfdcfox · Accepted Answer · 2017-03-13 19:17:27Z

1

I'm mobile, but it seems to me that the first four parts could read like this:

If(contains(parent.name, "client1"),
  If(isblank(field1),
    If(isblank(field2),
      // field1 is blank, field2 is blank
      ,
      // field1 is blank, field2 is not blank
      ),
    If(isblank(field2),
      // field1 is not blank, field2 is blank
      ,
      // field1 is not blank, field2 is not blank)),
  // logic for client 2...

Additional optimization is probable as well. One note: the comparisons "= False" and "= True" do use up extra characters towards the 5k compiled limit. Do not use them with ISBANK and other formulas that return Boolean.

answered Mar 13, 2017 at 19:17

sfdcfox♦

502k21 gold badges473 silver badges828 bronze badges

I'm not sure where the rest of the code fits into the code you provided. What is "// field1 is blank, field2 is blank , // field1 is blank, field2 is not blank"representing?
– Skobiwankanobi
Commented Mar 13, 2017 at 19:45
@Skobiwankanobi those are just comments. You'd put your display logic in the appropriate areas. I'm currently mobile so it would be impractical to give a better answer at the moment.
– sfdcfox ♦
Commented Mar 13, 2017 at 19:48
I'm putting it together and will test soon. This logic is brilliant.
– Skobiwankanobi
Commented Mar 13, 2017 at 20:17
@Skobiwankanobi I'm glad I was able to help. If you want to update this answer with your final solution with some details, feel free to update it.
– sfdcfox ♦
Commented Mar 13, 2017 at 21:32

Add a comment |

Kasper · Accepted Answer · 2017-03-13 18:20:03Z

This is just a start, and not nearly a complete optimalisation. If statements 3, 4 and 5 have the same outcome, namely:

 Show_Code__r.Name & "_" & 
 SUBSTITUTE(VenueCity__c," ","") & "_" &
 TEXT(Month(Show_Close_Date__c)) & "." & 
 TEXT(DAY(Show_Close_Date__c)) & "." & 
 Right(text(YEAR(Show_Close_Date__c)),2) & "_" & 
 "CustomName" & "_" &
 ID__c

This means that the logical tests for these three if statements can be merged:

IF(
 AND(CONTAINS(Account.Parent.Name ,"CLIENT1")= TRUE, 
 ISBLANK(Combo_Event_1__c)=TRUE, 
 ISBLANK(Flight_Name__c)=TRUE),

    IF(
 AND(CONTAINS(Account.Parent.Name ,"CLIENT1")= TRUE, 
 ISBLANK(Combo_Event_1__c)=TRUE, 
 ISBLANK(Flight_Name__c)=FALSE),

IF(
 AND(CONTAINS(Account.Parent.Name ,"CLIENT2")= TRUE, 
 ISBLANK(Flight_Name__c)=TRUE),

This would lead to something like:

IF( OR( CONTAINS(Account.Parent.Name ,"CLIENT1"),
        CONTAINS(Account.Parent.Name ,"CLIENT2"
      )

As you can see i've removed the comboevent and flight name checks. Either value seems to be allowed for those fields.

Mind you, it's late so there are probably typo's in this answer. Also, it could just be completely wrong. In that case i apologize ;). Either way i hope it gives you some idea of how to find your answer.

Stack Exchange Network

Shortening IF formula for multiple variations of a Custom naming convention Field

2 Answers 2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged
formula
custom-field
naming-convention
.

Hot Network Questions

Shortening IF formula for multiple variations of a Custom naming convention Field

2 Answers 2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged formulacustom-fieldnaming-convention.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
formula
custom-field
naming-convention
.