Error
This content is not currently supported on this browser.
+ 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 MolisSteve Molis
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 MolisSteve Molis
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.