Apex Code Development You need to sign in to do that
Don't have an account?
Datajunkie Formula field that can lookup values from another table?
I'm trying to determine if something is possible, and before I tell management it's not, I wanted to make sure that there wasn't any other way to get around this.
We are buying a cross-reference database that shows every valid Zip Code in the US and matches them up to a Metropolitan Statistical Area, as well as the County. Management wants to have a field for County and MSA in the Account record that pulls the values from this database.
i.e. if the Account has a ShippingPostalCode of 30004, he wants the County field to automatically be filled in with "Fulton" and the MSA field to be filled in with "Atlanta" (which comes from this database).
I can get the data from the database into a custom object in Salesforce, but how do I match up the ShippingPostalCode to this custom object so that the formula field will automatically populate the County and MSA fields?
It sounds like the VLOOKUP formula is exactly what I need, but it only works in validation rules - which will do me no good at all.
For obvious reasons, I can't write a workflow rule for every possible zip code in the US and then tie it to a field update that will fill in the County and MSA, but it sounds like that's the only way to do this.
Please tell me I'm wrong! Is there some other way to do this?
County: Formula(text)
Here is the code:
IF(LEN(BillingPostalCode)=0, "None",
IF(BEGINS(BillingPostalCode, "841"), "Salt Lake",
IF(CONTAINS("84094:84084:84070:84044:84088:84065:84092:84093:84353:84096:84095:84047", BillingPostalCode), "Salt Lake",
IF(CONTAINS("84651:84005:84045:84020:84601:84602:84603:84604:84605:84606:84663:84664:84003:84004:84042:84043:84057:84058:84059:84062:84097:84626:84633:84653:84655:84660", BillingPostalCode), "Utah",
IF(CONTAINS("84779:84780:84781:84782:84783:84784:84790:84791:84774:84771:84770:84767:84765:84763:84757:84745:84746:84737:84738:84733:84725:84722", BillingPostalCode), "Washington",
IF(CONTAINS("84713:84731:84751:84752",BillingPostalCode),"Beaver",
IF(CONTAINS("84301:84302:84306:84307:84309:84311:84312:84313:84314:84316:84324:84329:84330:84331:84334:84336:84337:84340",BillingPostalCode),"BoxElder",
IF(CONTAINS("84304:84305:84308:84318:84319:84320:84321:84322:84323:84325:84326:84327:84328:84332:84333:84335:84338:84339:84341",BillingPostalCode),"Cache",
IF(CONTAINS("84501:84520:84526:84529:84539:84542",BillingPostalCode),"Carbon",
IF(CONTAINS("84046:84023",BillingPostalCode),"Daggett",
IF(CONTAINS("84010:84011:84014:84015:84016:84025:84037:84040:84041:84054:84056:84075:84087:84089:84315:84405",BillingPostalCode),"Davis",
IF(CONTAINS("84001:84002:84007:84021:84027:84031:84051:84052:84053:84066:84072:84073",BillingPostalCode),"Duchesne",
IF(CONTAINS("84513:84516:84518:84521:84522:84523:84525:84528:84537",BillingPostalCode),"Emery",
IF(CONTAINS("84712:84716:84718:84726:84735:84736:84759:84764:84776",BillingPostalCode),"Garfield",
IF(CONTAINS("84515:84532:84540",BillingPostalCode),"Grand",
IF(CONTAINS("84714:84719:84720:84721:84742:84753:84756:84760:84761:84772",BillingPostalCode),"Iron",
IF(CONTAINS("84628:84639:84645:84648",BillingPostalCode),"Juab",
IF(CONTAINS("84710:84729:84741:84755:84758:84762",BillingPostalCode),"Kane",
IF(CONTAINS("84624:84631:84635:84636:84637:84638:84640:84644:84649:84656:84728",BillingPostalCode),"Millard",
IF(CONTAINS("84050",BillingPostalCode),"Morgan",
IF(CONTAINS("84723:84732:84740:84743:84750",BillingPostalCode),"Piute",
IF(CONTAINS("84028:84038:84064:84086",BillingPostalCode),"Rich",
IF(CONTAINS("84510:84511:84512:84530:84531:84533:84534:84535:84536",BillingPostalCode),"San Juan",
IF(CONTAINS("84621:84622:84623:84627:84629:84630:84632:84634:84642:84643:84646:84647:84662:84665:84667",BillingPostalCode),"Sanpete",
IF(CONTAINS("84620:84652:84654:84657:84701:84711:84724:84730:84744:84754:84766",BillingPostalCode),"Sevier",
IF(CONTAINS("84055:84017:84024:84033:84060:84061:84068:84098",BillingPostalCode),"Summit",
IF(CONTAINS("84022:84029:84034:84069:84071:84074:84080:84083",BillingPostalCode),"Tooele",
IF(CONTAINS("84026:84035:84039:84063:84076:84078:84079:84085",BillingPostalCode),"Uintah",
IF(CONTAINS("84715:84734:84747:84749:84773:84775",BillingPostalCode),"Wayne",
IF(CONTAINS("84032: 84036:84049:84082",BillingPostalCode),"Wasatch",
IF(CONTAINS("84067:84201:84244:84310:84315:84317:84401:84402:84403:84404:84405:84406:84407:84408:84409:84412:84414:84415",BillingPostalCode),"Weber",
"Outside")))))))))))))))))))))))))))))))
Thanks for your reply! Unfortunately, I need to have all the zip codes in the US, not just one state. And with that restriction, there's no way I could fit it all in 5,000 characters. :smileysad: That does give me an idea of how to handle another problem I had, though. So it does help!
Maybe there's someone else out here who knows a workaround that can handle the hundreds of zip codes and counties out there. I really don't like having to tell management, "Sorry. I can't do it."
Thanks again!