+ Start a Discussion
msglsmomsglsmo 

Custom Formula Field Help

Good Morning, All,

 

I am pulling what little hair I have left out on this one.  We use a custom formula field to update a field on our support cases with the account's warranty service provider.  We have one account that uses their own company to do warranty work, other's are based on state and now we have Canadian locations to add into the mix.  Everything works well except the Canada accounts.  We get "Unknown" on our custom field in the Canadian Accounts.  Any ideas?  We tried a couple different approaches for the Canada locations, but nothing works.

 

Here is the formula:

------------------------------

IF(CONTAINS("ABC Company", Corporate_Name__c ), "N/A",
IF(AND(ShippingCountry = "Canada", ShippingCity ="Acton:Ajax:Ancaster:Aurora:Barrie:Bolton:Brampton:Burlington:Caledon:Cambridge:Collingwood:East York:Etobicoke:Fergus:Guelph:Georgetown:Hamilton:Kitchener:Markham:Milton:Mississauga:Newmarket:North York:Oakville:Orangeville:Oshawa:Pickering:Richmond Hill:Scarborough:Agincourt:Toronto:Weston:Whitby:Willowdale:Woodbridge"), "Service Company 1",
IF (AND (ShippingCountry = "Canada",
CONTAINS (ShippingCity, "Aylmer:Chatham:Corunna:Delhi:Exeter:Forest:Glencoe:Grand Bend:Ingersoll:London:Mount Brydges:Norwich:Petrolia:Port Stanley:Ridgetown:Sarnia:St. Marys:St. Thomas:Stratford:Tavistock:Thamesford:Tillsonburg:Watford:Woodstock")), "Service Company 2",
IF(CONTAINS("FL:NH:MA", ShippingState), "Service Company A",
IF(CONTAINS("CA:NJ", ShippingState), "Service Company B",
IF(CONTAINS("AK:AZ:HA:NV:NM:OR:UT:WA:CO:ID:MT:KS:OK:TX:WY:NY:PA:AL:AR:DC:DE:KY:LA:MD:MS:TN:VA:WV:IL:IA:MI:MN:MO:NE:ND:SD:WI:ME:VT:CT:RI:OH:IN:NC:SC:GA", ShippingState), "Service Company C","Unknown"))))))

----------------------------------

 

Thank you in advance.  You help is much appreciated!

SteveMo__cSteveMo__c

That's a lot of code to go through, but I just skimmed it and I noticed that ShippingState part of your formula does not have any of the Alpha-2 CA Province Codes.  

msglsmomsglsmo

Do you think the Province abbreviations are needed?  I can see where it would result in better data (posibility of same name in different provinces).  However, I can't even get the forumula to work as is for the Canadian locations.  It works fine for the US locations.

SteveMo__cSteveMo__c

I dunno, that's a lot of code to digest (and I got pulled away to put out a fire).  It's just something that caught my eye.  If I can get the fire under control I'll try to take a more in depth look at it.  Have you tried ripping out all of the new code, going back the the original formula (that worked) and swapping in the new code 1 chunk at a time?  

 

Also, this is just my personal preference, but since you're dealing with the standard Account.Shipping Address, there can only be 1 value in there (a Shipping Address can't be BOTH  "US" and "CA") so I would split it into separate formulas.  All of the VR Formulas on my org are compartmentalized.     

msglsmomsglsmo

Any help you can provide is much appreciated!

 

The two lines we added:

 

IF(AND(ShippingCountry = "Canada", ShippingCity ="Acton:Ajax:Ancaster:Aurora:Barrie:Bolton:Brampto

 n:Burlington:Caledon:Cambridge:Collingwood:East York:Etobicoke:Fergus:Guelph:Georgetown:Hamilton:K itchener:Markham:Milton:Mississauga:Newmarket:Nort h York:Oakville:Orangeville:Oshawa:Pickering:Richmon d Hill:Scarborough:Agincourt:Toronto:Weston:Whitby:W illowdale:Woodbridge"), "Service Company 1",


and

 

IF (AND (ShippingCountry = "Canada",
CONTAINS (ShippingCity, "Aylmer:Chatham:Corunna:Delhi:Exeter:Forest:Glenco e:Grand Bend:Ingersoll:London:Mount Brydges:Norwich:Petrolia:Port Stanley:Ridgetown:Sarnia:St. Marys:St. Thomas:Stratford:Tavistock:Thamesford:Tillsonburg: Watford:Woodstock")), "Service Company 2",

 

Are the two lines that don't work.  All of the other lines appear to update the field correctly.  We tried two different appoaches and neither appears to work.

 

Thanks!

SteveMo__cSteveMo__c

Okay, well right off the bat I notice you're missing a CONTAINS function in your first chunk of code for all of those CA Shipping Cities...  (back to the fire)

msglsmomsglsmo

Hi All,

 

I changed the formula for the Account field a bit based on the above suggestions.  The code is accepted as being formatted right, but doesn't product the result I am looking for.  We get "Unknown" on our custom field in the Canadian Accounts.  I don't have any issues with the lines that reference a specific company or reference a specific US state.  The two lines that reference Canada are my troublespots:

 

IF(CONTAINS("ABC Co", Corporate_Name__c ), "N/A",
IF (AND (ShippingState = "ON", CONTAINS (ShippingCity, "Acton:Ajax:Ancaster:Aurora:Barrie:Bolton:Brampton:Burlington:Caledon:Cambridge:Collingwood:East York:Etobicoke:Fergus:Guelph:Georgetown:Hamilton:Kitchener:Markham:Milton:Mississauga:Newmarket:North York:Oakville:Orangeville:Oshawa:Pickering:Richmond Hill:Scarborough:Agincourt:Toronto:Weston:Whitby:Willowdale:Woodbridge")), "Service 1",
IF (AND (ShippingState="ON", CONTAINS (ShippingCity, "Aylmer:Chatham:Corunna:Delhi:Exeter:Forest:Glencoe:Grand Bend:Ingersoll:London:Mount Brydges:Norwich:Petrolia:Port Stanley:Ridgetown:Sarnia:St. Marys:St. Thomas:Stratford:Tavistock:Thamesford:Tillsonburg:Watford:Woodstock")), "Service 2",
IF(CONTAINS("FL:NH:MA", ShippingState), "Service 3",
IF(CONTAINS("CA:NJ", ShippingState), "Service 4",
IF(CONTAINS("AK:AZ:HA:NV:NM:OR:UT:WA:CO:ID:MT:KS:OK:TX:WY:NY:PA:AL:AR:DC:DE:KY:LA:MD:MS:TN:VA:WV:IL:IA:MI:MN:MO:NE:ND:SD:WI:ME:VT:CT:RI:OH:IN:NC:SC:GA", ShippingState), "Service 5","Unknown"))))))

 

Thanks again!