...
Code Block |
---|
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.
Code Block |
---|
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