Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 8 Current »

With State and Country Picklists

Accounts

IF(NOT(ISBLANK(ShippingCity)),ShippingCity+" ","")+
IF(NOT(ISBLANK(TEXT(ShippingStateCode))),TEXT(ShippingStateCode)+" ","")+
IF(NOT(ISBLANK(TEXT(ShippingCountryCode))),TEXT(ShippingCountryCode)+" ",
IF(NOT(ISBLANK(BillingCity)),BillingCity+" ","")+
IF(NOT(ISBLANK(TEXT(BillingStateCode))),TEXT(BillingStateCode)+" ","")+
IF(NOT(ISBLANK(TEXT(BillingCountryCode))),TEXT(BillingCountryCode)+" ",
""))

Contacts

IF(NOT(ISBLANK(OtherCity)),OtherCity+" ","")+
IF(NOT(ISBLANK(TEXT(OtherStateCode))),TEXT(OtherStateCode)+" ","")+
IF(NOT(ISBLANK(TEXT(OtherCountryCode))),TEXT(OtherCountryCode)+" ",
IF(NOT(ISBLANK(MailingCity)),MailingCity+" ","")+
IF(NOT(ISBLANK(TEXT(MailingStateCode))),TEXT(MailingStateCode)+" ","")+
IF(NOT(ISBLANK(TEXT(MailingCountryCode))),TEXT(MailingCountryCode)+" ",
""))

Without State and Country Picklists

Accounts

IF(NOT(ISBLANK(ShippingCity)),ShippingCity+" ","")+ 
IF(NOT(ISBLANK(ShippingState)),ShippingState+" ","")+ 
IF(NOT(ISBLANK(ShippingCountry)),ShippingCountry+" ", 
IF(NOT(ISBLANK(BillingCity)),BillingCity+" ","")+ 
IF(NOT(ISBLANK(BillingState)),BillingState+" ","")+ 
IF(NOT(ISBLANK(BillingCountry)),BillingCountry+" ", 
""))


Or depending on how much garbage you have in your Addresses, then you may need to reduce the formula to under 76 Characters to show it on a list view - yep, that's a thing apparently. So I also removed the Country if it's Aus

LEFT( 
IF(NOT(ISBLANK(ShippingCity)),ShippingCity+" ","")+ 
IF(NOT(ISBLANK(ShippingState)),ShippingState+" ","")+ 
IF(ShippingCountry = "Australia","",IF(NOT(ISBLANK(ShippingCountry)),ShippingCountry+" ", 
IF(NOT(ISBLANK(BillingCity)),BillingCity+" ","")+ 
IF(NOT(ISBLANK(BillingState)),BillingState+" ","")+ 
IF(BillingCountry = "Australia","",IF(NOT(ISBLANK(BillingCountry)),BillingCountry+" ", 
"")))) 
,75)

Contacts

IF(NOT(ISBLANK(OtherCity)),OtherCity+" ","")+ 
IF(NOT(ISBLANK(OtherState)),OtherState+" ","")+ 
IF(NOT(ISBLANK(OtherCountry)),OtherCountry+" ", 
IF(NOT(ISBLANK(MailingCity)),MailingCity+" ","")+ 
IF(NOT(ISBLANK(MailingState)),MailingState+" ","")+ 
IF(NOT(ISBLANK(MailingCountry)),MailingCountry+" ", 
""))

LEFT(
IF(NOT(ISBLANK(OtherCity)),OtherCity+" ","")+
IF(NOT(ISBLANK(OtherState)),OtherState+" ","")+
IF(OtherCountry = "Australia","",IF(NOT(ISBLANK(OtherCountry)),OtherCountry+" ",
IF(NOT(ISBLANK(MailingCity)),MailingCity+" ","")+
IF(NOT(ISBLANK(MailingState)),MailingState+" ","")+
IF(MailingCountry = "Australia","",IF(NOT(ISBLANK(MailingCountry)),MailingCountry+" ",
""))))
,75)

State Only

For Australia and New Zealand, and only showing State Codes - ideal for integration with other apps.

IF(ISBLANK(MailingState),
IF( OR(Lower(Account.BillingCountry) = "australia",Account.BillingCountry = "AUS"),

CASE(Account.BillingState,
"New South Wales","NSW",
"Victoria","VIC",
"Queensland","QLD",
"Tasmania","TAS",
"Western Australia","WA",
"South Australia","SA",
"Northern Territory","NT",
"Australian Capital Territory","ACT",Account.BillingState),
IF(OR(Lower(Account.BillingCountry) = "new zealand",Account.BillingCountry = "NZ"),"NZ",
IF(NOT(ISBLANK(Account.BillingCountry)),"International",""))),

IF( OR(lower(MailingCountry) = "australia",MailingCountry = "AUS"),
CASE(MailingState,
"New South Wales","NSW",
"Victoria","VIC",
"Queensland","QLD",
"Tasmania","TAS",
"Western Australia","WA",
"South Australia","SA",
"Northern Territory","NT",
"Australian Capital Territory","ACT",MailingState),
IF(OR(Lower(MailingCountry) = "new zealand",MailingCountry = "NZ"),"NZ",
IF(NOT(ISBLANK(MailingCountry)),"International",""))))

NOTE

  • Sometimes you might want to include the Account City State and Country in the Contact formula also - so if Contact is blank, it gets the details from the Account. 

  • Use this field in all cases in list views rather than Mailing State

  • I often remove the second address on Contacts, so modify this formula to suit. 

  • If you are using NPSP address management, then this might not be correct. 

  • You should always enable State and Country Picklists unless you have a recalcitrant app that has not enabled it yet - get onto them, there is no excuse! 

  • Rename your State and Country fields away from Shipping and Billing - see /wiki/spaces/WLSRH/pages/1998865

  • No labels