Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
twrig15twrig15 

TimeZone Formula field

Helo,

I'm trying to figure out why the below formula is only populating some records and not all. Can some one please assist?

IF(CASE(ShippingState, 'BC', 1, 'CA', 1, 'NV', 1,'OR', 1, 'WA', 1, 'YT', 1, 0) >=1, "Pacific (PST)", null)+ 
IF(CASE(ShippingState, 'AB', 1, 'AZ', 1, 'CO', 1,'ID', 1, 'MT', 1, 'NM', 1, 'UT', 1, 'WY', 1, 0) >= 1, "Mountain (MST)", null)+ 
IF(CASE(ShippingState, 'AL', 1, 'AR', 1, 'IL', 1, 'IA', 1,'KS', 1, 'LA', 1, 'MB', 1, 'MN', 1,'MS', 1,'MO', 1,'NE', 1,'ND', 1, 'OK', 1,'SD', 1, 'SK', 1, 'WI', 1, 0) >= 1, "Central (CST)", null)+ 
IF(CASE(ShippingState, 'CT', 1, 'DE', 1, 'GA', 1, 'ME', 1, 'MD', 1, 'MA', 1,'MI', 1, 'NH', 1, 'NJ', 1, 'NY', 1, 'NC', 1, 'OH', 1, 'ON', 1, 'PA', 1, 'QC', 1, 'RI', 1, 'SC', 1, 'VT', 1, 'VA', 1, 'WV', 1, 0) >= 1, "Eastern (EST)", null)+ 
IF(CASE(ShippingState, 'AK', 1, 0) >=1, "Alaskan (AKST)", null)+ 
IF(CASE(ShippingState, 'HI', 1, 0) >=1, "Hawaiian (HAST)", null)+ 
IF(ShippingState = 'FL', IF(MID(Phone,2,3) = "850","Central (CST)","Eastern (EST)"),null)+ 
IF(ShippingState = 'IN', IF(MID(Phone,2,3) = "219","Central (CST)","Eastern (EST)"),null)+ 
IF(ShippingState = 'KY', IF(MID(Phone,2,3) = "270","Central (CST)","Eastern (EST)"),null)+ 
IF(ShippingState = 'TX', IF(MID(Phone,2,3) = "915","Mountain (MST)","Central (CST)"),null)+ 
IF(ShippingState = 'TN', IF(CASE(MID(Phone,2,3),"865",1,"423",1,0)>=1,"Eastern (EST)", "Central (CST)"),null)
Parker EdelmannParker Edelmann
Could you give an example of a record that doesn't populate, the shipping state and phone number at least? But there is two things that you could try first. Replace '+' with '&', and if the option is available, select treat blank fields as zeros underneath the formula editor.
twrig15twrig15
I've tried the about and it still doesnt work. There are no specific state/province its just random.
Parker EdelmannParker Edelmann
You could try this formula. It's quite a beast, but it does have a smaller compile size, believe it or not. 
CASE(CASE(ShippingState,
                    'BC', 1,
                    'CA', 1,
                    'NV', 1,
                    'OR', 1,
                    'WA', 1,
                    'YT', 1,
                    'AB', 2,
                    'AZ', 2,
                    'CO', 2,
                    'ID', 2,
                    'MT', 2,
                    'NM', 2,
                    'UT', 2,
                    'WY', 2,
                    'AL', 3,
                    'AR', 3,
                    'IL', 3,
                    'IA', 3,
                    'KS', 3,
                    'LA', 3,
                    'MB', 3,
                    'MN', 3,
                    'MS', 3,
                    'MO', 3,
                    'NE', 3,
                    'ND', 3,
                    'OK', 3,
                    'SD', 3,
                    'SK', 3,
                    'WI', 3,
                    'CT', 4,
                    'DE', 4,
                    'GA', 4,
                    'ME', 4,
                    'MD', 4,
                    'MA', 4,
                    'MI', 4,
                    'NH', 4,
                    'NJ', 4,
                    'NY', 4,
                    'NC', 4,
                    'OH', 4,
                    'ON', 4,
                    'PA', 4,
                    'QC', 4,
                    'RI', 4,
                    'SC', 4,
                    'VT', 4,
                    'VA', 4,
                    'WV', 4,
                    'AK', 5,
                    'HI', 6,
                    'FL', IF(MID(Phone,2,3) = "850",3,4),
                    'IN', IF(MID(Phone,2,3) = "219",3,4),
                    'KY', IF(MID(Phone,2,3) = "270",3,4),
                    'TX', IF(MID(Phone,2,3) = "915",2,3),
                    'TN', CASE(MID(Phone,2,3),"865",4,"423",3,0),
                    0),
     1, "Pacific (PST)",
     2,"Mountain (MST)",
     3,"Central (CST)",
     4,"Eastern (EST)",
     5,"Alaskan (AKST)",
     6,"Hawaiian (HAST)",
     "")
Give it a look over, and make sure it functions as desired before implementing, and tweak it if necessary. If this works, please mark it as best.

Thanks,
Parker
Parker EdelmannParker Edelmann
Note that it will only work with the Shipping State, not the Billing State. I assume you already knew that, but if you want it to work with the billing address, simply change ShippingState to BillingState in the first line.
twrig15twrig15
Thank you. I'm still getting the issue where it only applies to certain records. :(
Parker EdelmannParker Edelmann
Could you post a screenshot of a record containing the values of the fields in question? But in the meantime, if the Shipping state doesn't perfectly match, or the first three digits of the phone# in TN is not equal to 865 or 423, it won't work. For example, if the Shipping State equals California, Ca, or Calif., the formula won't return anything.

Thanks,
Parker
twrig15twrig15
The issue is we are using the full state name. However I get a compile error when I use my original formula with the full state name however when use your formula it only populates 660 of the 7000 plus records where my formula populates 6072. 

User-added image
twrig15twrig15
Example of States/Province not populating
Parker EdelmannParker Edelmann
That is the problem with text literal (Hard-coded text within quotes.). It is not dynamic, so if you put "CA", it will not accept values like "California" or "Calif.", it must be "CA". In order to make sure the formula works, there must be some standardized way of writing the State/Province to taylor the formula to. If the standard is not followed, there will always be a record that doesn't generate a time zone. To enforce the standard, you could write a validation rule similar to this: 
CASE(ShippingState, 
                   "Michigan", false,
                   "Texas", false
                   "California", false,
                   /* repeat for all other states/provinces */
                   true)
But unfortunately, without a naming standard, there isn't an easy way to create a formula that will work for all records like requested.

If you run into compile size errors, you might be able to create a helper formula. It would work like this:
  
/* Helper formula*/
CASE(ShippingState,
'California', 1,
'Arizona', 2,
'Kansas', 3,
0)
/*Put many more states in both formulas of course*/
CASE(CASE('Michigan', 4,
'Alaska', 5,
'Hawaii', 6,
Helper_Formula__c),
1,"Pacific (PST)",
2, "Mountain (MST)",
3, "Central (CST)",
4,"Eastern (EST)",
5,"Alaskan (AKST)",
6, "Hawaiian (HAST)",
"")
Here's a few options for you, if any of them help, please let me know.

Thanks,
Parker
twrig15twrig15
@Parker Edelmann I decised to go with your first suggestion and standardized all records to State Codes and it works great. But the formula for Tennessee just isn't working. 'TN', CASE(MID(Phone,2,3),"865",4,"423",3,0), 

Any suggestions.
Parker EdelmannParker Edelmann
I really don't have any suggestions. Using the phone field may not be a good idea if this is on the contacts object, because it refers to the field labeled 'Phone' and the area code often no longer represents where they live. However, this seems to be the easiest way. The only other way I can think of would be to use the ZIP code. You could take all the ZIP codes on one side of the timezone line, and use a formula like this:
'TN', CASE(MailingPostalCode, 'Code1', 4, 'Code2' , 4, /*More Codes*/ 3) However, I don't know if that will be practical, or even if that will be a 100% confusion free and accurate solution. I think we're running into another problem with the method of implementation again, but I think this time the formula will need to be changed, as not all of your clients in Tennessee will have the phone number with an 865 or 423 area code. Let me know and/or mark a best answer if this helps.
Hal FormanHal Forman
I used this for a custom Lead or Campaign Member field:
IF(CONTAINS("FL,DC,GA,SC,NC,VA,WV,PA,MD,DE,CT,NY,NJ,ME,MA",State), "Eastern", IF(CONTAINS("AL,AR,IL,IN,IA,KS,KY,LA,MI,MN,MS,MO,NE,ND,OK,SD,TN,TX,WI",State), "Central", IF(CONTAINS("AZ,CO,ID,MT,NM,UT,WY",State), "Mountain", IF(CONTAINS("CA,WA,OR,NV",State), "Pacific", "Other"))))