+ Start a Discussion
Jacek DJacek D 

How to shorten IF-statement formula

Hi,

 

I have a very long formula:

 

IF (AND (ISBLANK(Phone__c ), ISBLANK (Address__c) , ISBLANK (Hours__c ) , ISBLANK (ID__c)) , null , 
IF (AND (ISBLANK (Phone__c) , OR (NOT(ISBLANK(Address__c)) , NOT (ISBLANK ( Hours__c )), NOT ( ISBLANK ( ID__c )) ) ) , '"' & " " & '"' , '"' & Phone__c & '"' ))

 Above code repeats 10 times in the formula field.

 

It is testing conditions to display 3 variables: a null value, empty quotation marks " " , and the phone number in quotation marks "Phone".

 

Running the formula on all fields (meaning 10 times) results in a compiled formula of more than 5,000 characters. Can you think of a way to shorten it?

 

Thanks

SteveMo__cSteveMo__c

where are you doing this, and why?  Is this a VR, a WFR, or a Formula Field?

Jacek DJacek D

It's a formula field. I need to export the value produced by this formula in this particular way (and want to avoid working it in excel).

 

The formula concatenates values for address 1-10, phone 1-10, ID 1-10 and Hours 1-10, where quotation marks are functioning as delimiter.

Josephadm401Josephadm401

I would try using the "LEN" function instead of "ISBLANK" or "NOTISBLANK" This should decrease your compiled size. Let me know if this helps. As a tip: Brign it in to word and user the rplace function. Might make thing go by quicker.

Jacek DJacek D

You mean LEN (field__c) > 0 ?

 

That did not work. Or is there an alternative?

 

Thanks for the word tip. It is indeed very helpful to use replace. I often use Excel for creating repetitive formulas like these. Concatenating is made really easy there.

Josephadm401Josephadm401

What was the error?

What is your current compiled size?

You will have to use LEN(TEXT(Field__c)) > 0 at some parts of your formula as phone numbers are number fields. If this doesnt help then you should try contacting Salesforce support to see if someone from there can help you out.

Jacek DJacek D

The compiled size went up actually. Really strange.

 

Well, I've opened a ticket with Salesforce help, maybe I will receive something like a response. I will post it here!

 

Thanks for your help!