Apex Code Development You need to sign in to do that
Don't have an account?
Formula Simplification
Hi All,
Just wondering if there's a way to simplify the formula I am working on.
To give you a background, I am trying to create a formula that will execute the standard address formatting that will be pulled to a Conga template.
There are 2 types of address consists of fields namely:
1. Postal Address
PO Box<br>
City<double space>State<double space>Postcode<br>
Country
2. Residential Address
Building/property name<double space>Flat Unit Details<double space>Street No.<double space>Street Name<br>
City<double space>State<double space>Postcode<br>
Country
The formula checks if any of postal address fields have values, so
if YES, it will return the concatenated postal address
if NO, it will check if Building/property name or Flat Unit details have values
so if YES, then it will display all the address fields for the residential address
if NO, then it will only display Street No. and Street name on the first line of the residential address and the rest of the designated fields
Here's the actual formula:
IF(
OR(
Other_City__c <> "", Other_Country__c <> "", Other_Street__c <> "", Other_Zip_Postal_Code__c <> "", Other_State_Provience__c <> "" ),
UPPER(Other_Street__c) & " " & UPPER(Postal_street_number__c) & " " & UPPER(Postal_street_name__c) & "<br>" & UPPER(Other_City__c ) & " " & UPPER(CASE( Other_State_Provience__c , "Queensland", "QLD", "QLD", "QLD", "Western Australia", "WA", "WA", "WA", "Australian Capital Territory", "ACT", "ACT", "ACT", "New South Wales", "NSW", "NSW", "NSW", "Northern Territory", "NT", "NT", "NT", "South Australia","SA", "SA", "SA", "Tasmania", "TAS", "TAS", "TAS", "Victoria", "VIC", "VIC", "VIC", "Other (Overseas but not an Australian territory or dependency)", "OVERSEAS", "OVERSEAS", "OVERSEAS", "OTHER" )) & " " & Other_Zip_Postal_Code__c & "<br>" & UPPER(Other_Country__c ),
IF(OR(Address_building_property_name__c <> "", Address_flat_unit_details__c <> ""),
UPPER(Address_building_property_name__c) & " " & UPPER(Address_flat_unit_details__c) & " " & UPPER(Address_street_number__c) & " " & UPPER(Address_street_name__c) & "<br>" & UPPER(Mailing_City__c) & " " & UPPER(CASE( Mailing_State_Provience__c , "Queensland", "QLD", "QLD", "QLD", "Western Australia", "WA", "WA", "WA", "Australian Capital Territory", "ACT", "ACT", "ACT", "New South Wales", "NSW", "NSW", "NSW", "Northern Territory", "NT", "NT", "NT", "South Australia","SA", "SA", "SA", "Tasmania", "TAS", "TAS", "TAS", "Victoria", "VIC", "VIC", "VIC", "Other (Overseas but not an Australian territory or dependency)", "OVERSEAS", "OVERSEAS", "OVERSEAS", "OTHER" )) & " " & Mailing_Zip_Postal_Code__c & "<br>" & UPPER(Mailing_Country__c ),
UPPER(Address_street_number__c) & " " & UPPER(Address_street_name__c) & "<br>" & UPPER(Mailing_City__c) & " " & UPPER(CASE( Mailing_State_Provience__c , "Queensland", "QLD", "QLD", "QLD", "Western Australia", "WA", "WA", "WA", "Australian Capital Territory", "ACT", "ACT", "ACT", "New South Wales", "NSW", "NSW", "NSW", "Northern Territory", "NT", "NT", "NT", "South Australia","SA", "SA", "SA", "Tasmania", "TAS", "TAS", "TAS", "Victoria", "VIC", "VIC", "VIC", "Other (Overseas but not an Australian territory or dependency)", "OVERSEAS", "OVERSEAS", "OVERSEAS", "OTHER" )) & " " & Mailing_Zip_Postal_Code__c & "<br>" & UPPER(Mailing_Country__c )
)
)
You may notice that there are case methods on the formula which eat up a lot on the editor so to explain it, the 'State' data on the app was not uniformed so I had to check both formats (Complete State name and Short name) and return only one (Short name will be returned).
Problem: I cannot save the formula I created because it exceeds 5000 characters on the editor.
To-do: Simplify the formula
Any idea or suggestion to solve this? Thank you very much.
Just wondering if there's a way to simplify the formula I am working on.
To give you a background, I am trying to create a formula that will execute the standard address formatting that will be pulled to a Conga template.
There are 2 types of address consists of fields namely:
1. Postal Address
PO Box<br>
City<double space>State<double space>Postcode<br>
Country
2. Residential Address
Building/property name<double space>Flat Unit Details<double space>Street No.<double space>Street Name<br>
City<double space>State<double space>Postcode<br>
Country
The formula checks if any of postal address fields have values, so
if YES, it will return the concatenated postal address
if NO, it will check if Building/property name or Flat Unit details have values
so if YES, then it will display all the address fields for the residential address
if NO, then it will only display Street No. and Street name on the first line of the residential address and the rest of the designated fields
Here's the actual formula:
IF(
OR(
Other_City__c <> "", Other_Country__c <> "", Other_Street__c <> "", Other_Zip_Postal_Code__c <> "", Other_State_Provience__c <> "" ),
UPPER(Other_Street__c) & " " & UPPER(Postal_street_number__c) & " " & UPPER(Postal_street_name__c) & "<br>" & UPPER(Other_City__c ) & " " & UPPER(CASE( Other_State_Provience__c , "Queensland", "QLD", "QLD", "QLD", "Western Australia", "WA", "WA", "WA", "Australian Capital Territory", "ACT", "ACT", "ACT", "New South Wales", "NSW", "NSW", "NSW", "Northern Territory", "NT", "NT", "NT", "South Australia","SA", "SA", "SA", "Tasmania", "TAS", "TAS", "TAS", "Victoria", "VIC", "VIC", "VIC", "Other (Overseas but not an Australian territory or dependency)", "OVERSEAS", "OVERSEAS", "OVERSEAS", "OTHER" )) & " " & Other_Zip_Postal_Code__c & "<br>" & UPPER(Other_Country__c ),
IF(OR(Address_building_property_name__c <> "", Address_flat_unit_details__c <> ""),
UPPER(Address_building_property_name__c) & " " & UPPER(Address_flat_unit_details__c) & " " & UPPER(Address_street_number__c) & " " & UPPER(Address_street_name__c) & "<br>" & UPPER(Mailing_City__c) & " " & UPPER(CASE( Mailing_State_Provience__c , "Queensland", "QLD", "QLD", "QLD", "Western Australia", "WA", "WA", "WA", "Australian Capital Territory", "ACT", "ACT", "ACT", "New South Wales", "NSW", "NSW", "NSW", "Northern Territory", "NT", "NT", "NT", "South Australia","SA", "SA", "SA", "Tasmania", "TAS", "TAS", "TAS", "Victoria", "VIC", "VIC", "VIC", "Other (Overseas but not an Australian territory or dependency)", "OVERSEAS", "OVERSEAS", "OVERSEAS", "OTHER" )) & " " & Mailing_Zip_Postal_Code__c & "<br>" & UPPER(Mailing_Country__c ),
UPPER(Address_street_number__c) & " " & UPPER(Address_street_name__c) & "<br>" & UPPER(Mailing_City__c) & " " & UPPER(CASE( Mailing_State_Provience__c , "Queensland", "QLD", "QLD", "QLD", "Western Australia", "WA", "WA", "WA", "Australian Capital Territory", "ACT", "ACT", "ACT", "New South Wales", "NSW", "NSW", "NSW", "Northern Territory", "NT", "NT", "NT", "South Australia","SA", "SA", "SA", "Tasmania", "TAS", "TAS", "TAS", "Victoria", "VIC", "VIC", "VIC", "Other (Overseas but not an Australian territory or dependency)", "OVERSEAS", "OVERSEAS", "OVERSEAS", "OTHER" )) & " " & Mailing_Zip_Postal_Code__c & "<br>" & UPPER(Mailing_Country__c )
)
)
You may notice that there are case methods on the formula which eat up a lot on the editor so to explain it, the 'State' data on the app was not uniformed so I had to check both formats (Complete State name and Short name) and return only one (Short name will be returned).
Problem: I cannot save the formula I created because it exceeds 5000 characters on the editor.
To-do: Simplify the formula
Any idea or suggestion to solve this? Thank you very much.
IF(
OR(
//check if Postal Address is not empty
Other_City__c != "",
Other_Country__c != "",
Other_Street__c != "",
Other_Zip_Postal_Code__c != "",
Other_State_Provience__c != "" ),
//if postal address not empty return formatted address (change your API's here)
UPPER(PO BOX & "<br>" &
City & " " &
IF(State == 'Western Australia', 'WA',
IF(State=='Queensland', 'QLD',
IF(State=='Australian Capital Territory, 'ACT',
IF(State=='New South Wales', 'NSW',
IF(State=='Northern Territory','NT',
IF(State=='South Australia','SA',
IF(State=='Tasmania','TAS',
IF(State=='Victoria','VIC',
IF(State=='Other (Overseas but not an Australian territory or dependency)','OVERSEAS',
'OTHER')
)
)
)
)
)
)
)
)
& " " & Postcode
& "<br>" & Country),
//If postal address is empty check if flat and building is not emptry
IF(
OR(Address_building_property_name__c != "",
Address_flat_unit_details__c!=""),
//display all address fields for residential address
UPPER(Address_building_property_name__c & " " &
Address_flat_unit_details__c & " " &
Address_street_number__c & " " &
Address_street_name__c & "<br>" &
Mailing_City__c & " " &
IF(State == 'Western Australia', 'WA',
IF(State=='Queensland', 'QLD',
IF(State=='Australian Capital Territory, 'ACT',
IF(State=='New South Wales', 'NSW',
IF(State=='Northern Territory','NT',
IF(State=='South Australia','SA',
IF(State=='Tasmania','TAS',
IF(State=='Victoria','VIC',
IF(State=='Other (Overseas but not an Australian territory or dependency)','OVERSEAS',
'OTHER')
)
)
)
)
)
)
)
)& " " &
Mailing_Zip_Postal_Code__c & "<br>" &
Mailing_Country__c),
UPPER(Address_street_number__c & " " &
Address_street_name__c & "<br>" &
Mailing_City__c & " " &
IF(State == 'Western Australia', 'WA',
IF(State=='Queensland', 'QLD',
IF(State=='Australian Capital Territory, 'ACT',
IF(State=='New South Wales', 'NSW',
IF(State=='Northern Territory','NT',
IF(State=='South Australia','SA',
IF(State=='Tasmania','TAS',
IF(State=='Victoria','VIC',
IF(State=='Other (Overseas but not an Australian territory or dependency)','OVERSEAS',
'OTHER')
)
)
)
)
)
)
)
)& " " &
Mailing_Zip_Postal_Code__c & "<br>" &
Mailing_Country__c)
)
)
You will need to replace the fields with the right APIs. You can also save more characters by removing spaces etc...
Cheers Jannis