function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
SFDC_Dev_2009SFDC_Dev_2009 

Custom Formula Address field

I have created a Cross object Custom formula field that displays the Address .Here is the format to display the address field.

 

 

Account.BillingStreet  & BR() &
Account.BillingCity & ", "
 & Account.BillingState  & " " &
Account.BillingPostalCode  & BR()
& Account.BillingCountry

 

Now my question is there is a Comma in the formula and it displays, when the BillingStreet,BillingCity,etc., fields are empty.

How to resolve this?

 

 

Best Answer chosen by Admin (Salesforce Developers) 
SFDC_Dev_2009SFDC_Dev_2009

Hi Steve,

 

Thanks for you suggestion.

It worked.Here is the formula

 

 

if(LEN(Account.BillingStreet)>0, Account.BillingStreet& " " , "" ) & BR() &
if(LEN(Account.BillingCity)>0, Account.BillingCity& ", " , "" )&
if(LEN(Account.BillingState)>0, Account.BillingState& " " , "" )&
if(LEN(Account.BillingPostalCode)>0, Account.BillingPostalCode,"") & BR() &
If(LEN( Account.BillingCountry) >0, Account.BillingCountry,"")

All Answers

Steve :-/Steve :-/
You probably have to wrap an IF statement around it that evaluates each field, and if it's blank omits the field and comma from the output.
SFDC_Dev_2009SFDC_Dev_2009

Hi Steve,

 

Thanks for you suggestion.

It worked.Here is the formula

 

 

if(LEN(Account.BillingStreet)>0, Account.BillingStreet& " " , "" ) & BR() &
if(LEN(Account.BillingCity)>0, Account.BillingCity& ", " , "" )&
if(LEN(Account.BillingState)>0, Account.BillingState& " " , "" )&
if(LEN(Account.BillingPostalCode)>0, Account.BillingPostalCode,"") & BR() &
If(LEN( Account.BillingCountry) >0, Account.BillingCountry,"")

This was selected as the best answer
Steve :-/Steve :-/
No problem, that's why they pay me the BIG Bucks!  ;-)
jaredjared

I am trying to put referral contact information into a custom formula (text Field) and used the same syntax you did. However I get syntax error messages. I'm just trying to pass the valid field data used in the formula below into a formula field (similar) to the address default "address" field in SalesForce.

 

If(LEN( Referring_First_Name__c )>0,  Referring_First_Name__c & " " , "" ) &
If(LEN( Referring_Last_Name__c )>0,  Referring_Last_Name__c & ", " , "" )& BR() &
If(LEN( Referring_Address__c )>0,  Referring_Address__c & " " , "" )& BR() &
If(LEN( Referring_City__c )>0,  Referring_City__c &"") & BR() &
If(LEN( Referring_State__c ) >0,  Referring_State__c ,"") &
If(LEN( Referring_Zip__c ) >0,  Referring_Zip__c &"") BR&

"Phone:" If(LEN( Referring_Phone__c )>0,  Referring_Phone__c & ", " , "" )& BR() &

"Gift =" TEXT(MS_Rewards_Gift)

 

In addition, I want to pull in a picklist value from a gift selection drop down at the bottom of this contact field. I continue to get syntax errors. 

 

It should display information as follows: 

 

John Doe

1234 Sample Street

City, State Zip

Phone: 333-333-3333

Gift = Headphones

 

 

Thanks for your help. 

 

 

 
PkirchPkirch

I am not a developer.  Is there a way I can create this type of field using this code?  Sorry for my ignorance, but the address customization headaches with SfDC are shortening my life and this looks like a potential answer.
Matt FosterMatt Foster
I am getting the Error. Seems to be a new one. 
ERROR: Expected text recieved Location.

Does anyone know how to convert location = text?
Seth RowlandSeth Rowland
I did a little cleanup with the Line Breaks and their positioning to account for Blank Values.

If ( LEN ( Operator_Name__r.ShippingStreet ) > 0 , Operator_Name__r.ShippingStreet & " " & BR(), "" )  &
If ( LEN ( Operator_Name__r.ShippingCity ) > 0 , Operator_Name__r.ShippingCity & ", " , "" ) &
If ( LEN ( Operator_Name__r.ShippingState ) > 0 , Operator_Name__r.ShippingState & " " , "" ) &
If ( LEN ( Operator_Name__r.ShippingPostalCode ) > 0, Operator_Name__r.ShippingPostalCode , "")  &
If ( LEN ( Operator_Name__r.ShippingCountry ) > 0 ,  BR() & Operator_Name__r.ShippingCountry , "" )