Apex Code Development You need to sign in to do that
Don't have an account?
levi6db Need help in simplifying/fixing a formula field.
Hi all,
We have two custom objects:
1. Locations
2. Address Data
The Location object has a lookup field where you select the street address from the Address Data object. Once you hit save, the address info is auto populated to the Location object via a workflow. This works great.
The problem that I am having is one of the fields on the Location object is called "Market". This is a formula field that is populated based on either the State, ZIP or Country. I need to simplify this code/fix it because it is returning errors when the Country field is referenced.
Here is the code:
IF(State__c="MA" || State__c="RI" , "Boston", IF(State__c="CT" || State__c="NY" || State__c="NJ" ,"NY/NJ", IF(State__c="PA" , "Philadelphia", IF(State__c="MD" , "Baltimore", IF(State__c="VA" || State__c="DC", "WDC/VA", IF(State__c="GA" , "Atlanta", IF(State__c="IL" , "Chicago", IF((VALUE(Zip__c)) >= 75001 && (VALUE(Zip__c)) <= 76487 , "Dallas", IF((VALUE(Zip__c)) >= 73301 && (VALUE(Zip__c)) <= 73344 , "Austin", IF((VALUE(Zip__c)) >= 78602 && (VALUE(Zip__c)) <= 78957 , "Austin", IF((VALUE(Zip__c)) >= 77001 && (VALUE(Zip__c)) <= 77598 , "Houston", IF(State__c="AZ" , "Phoenix", IF((VALUE(Zip__c)) >= 90001 && (VALUE(Zip__c)) <= 93599, "Los Angeles", IF((VALUE(Zip__c)) >= 94001 && (VALUE(Zip__c)) <= 95487 , "SF Bay Area", IF(State__c="OR" , "Portland", IF(State__c="FL", "Miami", IF(State__c="CO", "Denver", IF(Country__c="United Kingdom", "London", IF(Country__c="Germany", "Frankfurt", IF(Country__c="France", "Paris", IF(Country__c="Netherlands", "Amsterdam", IF(State__c="WA" , "Seattle", "Other" ))))))))))))))))))))))
Any suggestions would be greatly appreciated.
Thanks,
Alex
What errors are you getting for Country? Is it Country alone that causes errors and not State or Zip? What type of field is Country__c (text, picklist, etc)?
One of our biggest issues with the BillingCountry and ShippingCountry fields is that they are text fields therefore users can type whatever they want in there. Then you get US, USA, U.S., U.S.A., United States,... you get the point. We had to make a validation rule that consisted of naming every single country and preventing users from typing it any different way. If yours is a text field as well, is the error due to typos or something such as "UK" rather than "United Kingdom"?
The error looks like this within the "market" field: #Error!
The Country, State and Zip fields are all text fields.
It seems like the formula works when:
1. The Zip is referenced, it populates regardless of the state and country field.
2. The State is referenced, it populates regardless of the zip or country field.
3. If Country is referenced, it gives me the error, unless the zip and state fields are blank, then it works.
So Zip and State can both be populated and this formula still works, but if Zip and Country are populated, it goes bonkers?
It appears to me that you only have each market accounted for once, so there shouldnt be any cross-formula errors (if Zip = 75002 but State = FL, the formula would fizzle).
Now, your formula only has a select few states, zip ranges, and countries. When would you have a State or ZIp populated along with one of the countries in your formula, to make the #Error show?
I am leaving for the day but will check back in the morning.
Cheers.
It only fails when the Country is referenced:
For example: Country - United Kingdom Zip - W4 5YS
So really the only issue is non-US locations.
Just for the sake of trial and error (usually how I solve things here), try putting in an all numbers Zip Code and then United Kingdom for the country and see how it reacts.
I changed the zip to all numbers and it still gave the error. I also removed the space in the zip code and it gave the error.
Most likely, the problem is with the zip code. The VALUE() function is trying to convert letters into numbers, espeically for non US postal codes that have letters. Rearrange your IF statement to put the country conditions first. For example,
Another alternative is to put a condition for "United States", that forks the state and zip conditions underneath it.
I will give this a shot, thanks!
Do you know if you can mix CASE and IF statements? I haven't been able to successfully. I like to use CASE when I can due to the 5,000 character limit.