+ Start a Discussion
levi6dblevi6db 

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

 

 

netTrekker_ADnetTrekker_AD

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"?

levi6dblevi6db

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.

netTrekker_ADnetTrekker_AD

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.

 

 

levi6dblevi6db

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.

netTrekker_ADnetTrekker_AD

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.

levi6dblevi6db

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.

 

 

apex whistlerapex whistler

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,

 

 

IF(Country__c="United Kingdom", "London",
IF(Country__c="Germany", "Frankfurt",
IF(Country__c="France", "Paris",
IF(Country__c="Netherlands", "Amsterdam",
IF(State__c="AZ" , "Phoenix", 
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(State__c="OR" , "Portland",
IF(State__c="FL", "Miami", 
IF(State__c="CO", "Denver",
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((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="WA" , "Seattle", "Other"
))))))))))))))))))))))

 Another alternative is to put a condition for "United States", that forks the state and zip conditions underneath it.

 

levi6dblevi6db

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.