function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

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

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?
Desert DudeDesert Dude
For what it is worth, I constructed a formula field (County) that enters the county based on the 5-digit zip code. The problem with formula fields is that there is a 5000 character limit so if you are from a state that has numerous zip codes and counties, then you may run out of characters before entering all the information and the formula will be useless. I also use a similar one to determine the Federal Congressional District. I'd love to find out other solutions.

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")))))))))))))))))))))))))))))))
DatajunkieDatajunkie

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!