Discussions

 View Only
Expand all | Collapse all

How to auto-fill a county field based on zip code?

  • 1.  How to auto-fill a county field based on zip code?

    Posted 09-07-2017 19:37
    I am having an issue with the address field, it pulls all information except for county. Does anyone have a formula or know of a solution to have a field auto-filled once the zip code has been entered to identify the county name?

    Thanks for your help!


  • 2.  RE: How to auto-fill a county field based on zip code?

    Posted 09-07-2017 19:50
    I suggest googling until you can find a free down load of an Excel file with all the US Zip codes.

    Then create a new Table called Counties by Zip Codes, where the Key field is set to Zip Code.

    Then build a relationship to the Address field based on the Zip code there.  You may find it easier to create a Zip Code mirror field with the formula and use that to make the relationship.

    [Address: Postal Code]

    Then, of course lookup the County.


  • 3.  RE: How to auto-fill a county field based on zip code?

    Posted 03-01-2019 17:11
    Hi again!
    I came across this question because I am trying to do the same thing.
    I have a table of zip codes and counties (fortunately I only need Massachusetts).
    I set the zip code as the key field.
    I created a relationship between this table and the Contacts table (where the address is with the zip code I want to match up).

    Now I'm stuck. How do I tell it to match up the zip code in the address field with the zip code in the related counties table?


  • 4.  RE: How to auto-fill a county field based on zip code?

    Posted 03-01-2019 17:44
    np,
    Look at your address field properties. It will give you the name of the subfields.

    Then make a new formula text field called [zip code] with a formula like

    [Address: Postal Code]

    Now you have a Text field eligible to be in a relationship.


  • 5.  RE: How to auto-fill a county field based on zip code?

    Posted 03-01-2019 19:53
    Ok - I did that.
    I'm still not sure how to match them up. Maybe I'm being dense because it's Friday afternoon. What am I missing?


  • 6.  RE: How to auto-fill a county field based on zip code?

    Posted 03-01-2019 19:57
    Happy Friday,

    Well the One side of the relationship will the the Key field of the Zip code table which I hope is the zip code.

    Th eMany side needs to be that new field you just set up in the Contact table.  If you have a relationship just based on like [Related Zip code}, then you will need to kill it and start over or change that field to be of type formula text and make the formula

    [Address;Postal Code]


  • 7.  RE: How to auto-fill a county field based on zip code?

    Posted 03-01-2019 20:40
    Aha! Got it now! Thanks again for your patient and expert help. :)


  • 8.  RE: How to auto-fill a county field based on zip code?

    Posted 09-07-2017 19:54
    The file "2010 ZCTA to County Relationship File" on the following pages list ZipCode, State FIPS, and County Fips inthe first three columns:

    https://www.census.gov/geo/maps-data/data/zcta_rel_download.html

    A little script could reduce teh 44,000+ records to a QuickBse formula

    State and County FIPS codes can be found here:

    https://www2.census.gov/geo/docs/reference/codes/files/national_county.txt

    But if you want accuracy be aware that zip code are just labels for postal delivery routes. Here in Michigan we have some places in western Upper Peninsula that have a Wisconsin zip code simple because that is the closest post office.


  • 9.  RE: How to auto-fill a county field based on zip code?

    Posted 09-07-2017 20:02
    Also, I am pretty sure the first three digits in the zip code define the upper and lower bounds of the zip code for the county, principal county, unorganized borough, independent cities or parish.

    But then I read this: "If the ZIP Code crosses county lines, this field will contain the name of the county in which the greatest portion of that ZIP Code lies."

    Actually I think I created such a formula in the past as I remember a QuickBase engineer contacted me years ago asking why I was taking up so much space in my application for formulas. I might still have that application somewhere.


  • 10.  RE: How to auto-fill a county field based on zip code?

    Posted 09-07-2017 20:20
    Use this resource:

    https://www.gaslampmedia.com/download-zip-code-latitude-longitude-city-state-county-csv/

    To convert to formula just use underscorejs, groupBy State and County combo, extract the min and max zip code and spit out a QuickBase formulas.


  • 11.  RE: How to auto-fill a county field based on zip code?

    Posted 09-07-2017 21:20
    Well I wrote a quick script using the algorithm I outlined above and spit out a formula.

    I think the general idea is true that zip codes for a county are within certain upper and lower bounds but there appear to be exceptions you would have to hunt down.

    Oddly the first test in the formula for zip code 00501 is an IRS Service Center out of sequence from the other zip codes for Suffolk County:

    https://www.unitedstateszipcodes.org/00501/

    I know there are so called "stealth" zip codes for US Marshal Servcies, Military Installations, Area 51 and apparently the IRS. I will contact Mulder & Scully and see if they have any insight.


    var Number z = ToNumber([ZipCode]);
    If(
      501 <= $z and $z <= 11980, "Suffolk, NY",
      601 <= $z and $z <= 601, "Adjuntas, PR",
      602 <= $z and $z <= 602, "Aguada, PR",
      603 <= $z and $z <= 690, "Aguadilla, PR",
      606 <= $z and $z <= 606, "Maricao, PR",
      610 <= $z and $z <= 610, "Anasco, PR",
      611 <= $z and $z <= 641, "Utuado, PR",
      612 <= $z and $z <= 688, "Arecibo, PR",
      617 <= $z and $z <= 617, "Barceloneta, PR",
      622 <= $z and $z <= 623, "Cabo Rojo, PR",
      624 <= $z and $z <= 624, "Penuelas, PR",
      627 <= $z and $z <= 627, "Camuy, PR",
      631 <= $z and $z <= 669, "Lares, PR",
      636 <= $z and $z <= 683, "San German, PR",
      637 <= $z and $z <= 637, "Sabana Grande, PR",
      638 <= $z and $z <= 638, "Ciales, PR",
      646 <= $z and $z <= 646, "Dorado, PR",
      647 <= $z and $z <= 653, "Guanica, PR",
      650 <= $z and $z <= 650, "Florida, PR",
      656 <= $z and $z <= 785, "Guayanilla, PR",
      659 <= $z and $z <= 659, "Hatillo, PR",
      660 <= $z and $z <= 660, "Hormigueros, PR",
      662 <= $z and $z <= 662, "Isabela, PR",
      664 <= $z and $z <= 664, "Jayuya, PR",
      667 <= $z and $z <= 667, "Lajas, PR",
      670 <= $z and $z <= 670, "Las Marias, PR",
      674 <= $z and $z <= 674, "Manati, PR",
      676 <= $z and $z <= 676, "Moca, PR",
      677 <= $z and $z <= 677, "Rincon, PR",
      678 <= $z and $z <= 678, "Quebradillas, PR",
      680 <= $z and $z <= 682, "Mayaguez, PR",
      685 <= $z and $z <= 685, "San Sebastian, PR",
      687 <= $z and $z <= 687, "Morovis, PR",
      692 <= $z and $z <= 692, "Vega Alta, PR",
      693 <= $z and $z <= 694, "Vega Baja, PR",
      698 <= $z and $z <= 698, "Yauco, PR",
      703 <= $z and $z <= 703, "Aguas Buenas, PR",
      704 <= $z and $z <= 784, "Guayama, PR",
      705 <= $z and $z <= 786, "Aibonito, PR",
      707 <= $z and $z <= 707, "Maunabo, PR",
      714 <= $z and $z <= 714, "Arroyo, PR",
      715 <= $z and $z <= 780, "Ponce, PR",
      718 <= $z and $z <= 744, "Naguabo, PR",
      719 <= $z and $z <= 719, "Naranjito, PR",
      720 <= $z and $z <= 720, "Orocovis, PR",
      721 <= $z and $z <= 745, "Rio Grande, PR",
      723 <= $z and $z <= 723, "Patillas, PR",
      725 <= $z and $z <= 727, "Caguas, PR",
      729 <= $z and $z <= 729, "Canovanas, PR",
      735 <= $z and $z <= 742, "Ceiba, PR",
      736 <= $z and $z <= 737, "Cayey, PR",
      738 <= $z and $z <= 740, "Fajardo, PR",
      739 <= $z and $z <= 739, "Cidra, PR",
      741 <= $z and $z <= 792, "Humacao, PR",
      751 <= $z and $z <= 751, "Salinas, PR",
      754 <= $z and $z <= 754, "San Lorenzo, PR",
      757 <= $z and $z <= 757, "Santa Isabel, PR",
      765 <= $z and $z <= 765, "Vieques, PR",
      766 <= $z and $z <= 766, "Villalba, PR",
      767 <= $z and $z <= 767, "Yabucoa, PR",
      769 <= $z and $z <= 769, "Coamo, PR",
      771 <= $z and $z <= 771, "Las Piedras, PR",
      772 <= $z and $z <= 772, "Loiza, PR",
      773 <= $z and $z <= 773, "Luquillo, PR",
      775 <= $z and $z <= 775, "Culebra, PR",
      777 <= $z and $z <= 777, "Juncos, PR",
      778 <= $z and $z <= 778, "Gurabo, PR",
      782 <= $z and $z <= 782, "Comerio, PR",
      783 <= $z and $z <= 783, "Corozal, PR",
      794 <= $z and $z <= 794, "Barranquitas, PR",
      795 <= $z and $z <= 795, "Juana Diaz, PR",
      801 <= $z and $z <= 805, "Saint Thomas, VI",
      820 <= $z and $z <= 851, "Saint Croix, VI",
      830 <= $z and $z <= 831, "Saint John, VI",
      901 <= $z and $z <= 982, "San Juan, PR",
      934 <= $z and $z <= 961, "Bayamon, PR",
      949 <= $z and $z <= 952, "Toa Baja, PR",
      953 <= $z and $z <= 954, "Toa Alta, PR",
      962 <= $z and $z <= 963, "Catano, PR",
      965 <= $z and $z <= 971, "Guaynabo, PR",
      976 <= $z and $z <= 978, "Trujillo Alto, PR",
      983 <= $z and $z <= 988, "Carolina, PR",
      1001 <= $z and $z <= 1521, "Hampden, MA",
      1002 <= $z and $z <= 1243, "Hampshire, MA",
      1005 <= $z and $z <= 1772, "Worcester, MA",
      1029 <= $z and $z <= 1343, "Berkshire, MA",
      1054 <= $z and $z <= 1380, "Franklin, MA",
      1431 <= $z and $z <= 2495, "Middlesex, MA",
      1810 <= $z and $z <= 5544, "Essex, MA",
      2018 <= $z and $z <= 2770, "Plymouth, MA",
      2019 <= $z and $z <= 2762, "Norfolk, MA",
      2031 <= $z and $z <= 2791, "Bristol, MA",
      2101 <= $z and $z <= 2297, "Suffolk, MA",
      2532 <= $z and $z <= 2675, "Barnstable, MA",
      2535 <= $z and $z <= 2713, "Dukes, MA",
      2554 <= $z and $z <= 2584, "Nantucket, MA",
      2801 <= $z and $z <= 2878, "Newport, RI",
      2802 <= $z and $z <= 2940, "Providence, RI",
      2804 <= $z and $z <= 2898, "Washington, RI",
      2806 <= $z and $z <= 2885, "Bristol, RI",
      2816 <= $z and $z <= 2893, "Kent, RI",
      3031 <= $z and $z <= 3468, "Hillsborough, NH",
      3032 <= $z and $z <= 3885, "Rockingham, NH",
      3046 <= $z and $z <= 3307, "Merrimack, NH",
      3215 <= $z and $z <= 3785, "Grafton, NH",
      3218 <= $z and $z <= 3837, "Belknap, NH",
      3227 <= $z and $z <= 3897, "Carroll, NH",
      3280 <= $z and $z <= 3782, "Sullivan, NH",
      3431 <= $z and $z <= 3609, "Cheshire, NH",
      3570 <= $z and $z <= 3598, "Coos, NH",
      3805 <= $z and $z <= 3884, "Strafford, NH",
      3901 <= $z and $z <= 4095, "York, ME",
      4003 <= $z and $z <= 4260, "Cumberland, ME",
      4008 <= $z and $z <= 4579, "Sagadahoc, ME",
      4010 <= $z and $z <= 4292, "Oxford, ME",
      4210 <= $z and $z <= 4291, "Androscoggin, ME",
      4225 <= $z and $z <= 4992, "Franklin, ME",
      4259 <= $z and $z <= 4989, "Kennebec, ME",
      4341 <= $z and $z <= 4852, "Lincoln, ME",
      4354 <= $z and $z <= 4988, "Waldo, ME",
      4401 <= $z and $z <= 4969, "Penobscot, ME",
      4406 <= $z and $z <= 4485, "Piscataquis, ME",
      4408 <= $z and $z <= 4693, "Hancock, ME",
      4413 <= $z and $z <= 4694, "Washington, ME",
      4471 <= $z and $z <= 4788, "Aroostook, ME",
      4478 <= $z and $z <= 4985, "Somerset, ME",
      4547 <= $z and $z <= 4865, "Knox, ME",
      5001 <= $z and $z <= 5772, "Windsor, VT",
      5033 <= $z and $z <= 5679, "Orange, VT",
      5042 <= $z and $z <= 5873, "Caledonia, VT",
      5101 <= $z and $z <= 5363, "Windham, VT",
      5152 <= $z and $z <= 5776, "Bennington, VT",
      5401 <= $z and $z <= 5495, "Chittenden, VT",
      5440 <= $z and $z <= 5486, "Grand Isle, VT",
      5441 <= $z and $z <= 5488, "Franklin, VT",
      5442 <= $z and $z <= 5680, "Lamoille, VT",
      5443 <= $z and $z <= 5778, "Addison, VT",
      5601 <= $z and $z <= 5682, "Washington, VT",
      5701 <= $z and $z <= 5777, "Rutland, VT",
      5820 <= $z and $z <= 5875, "Orleans, VT",
      5824 <= $z and $z <= 5907, "Essex, VT",
      6001 <= $z and $z <= 6489, "Hartford, CT",
      6018 <= $z and $z <= 6798, "Litchfield, CT",
      6029 <= $z and $z <= 6279, "Tolland, CT",
      6226 <= $z and $z <= 6387, "Windham, CT",
      6249 <= $z and $z <= 6474, "New London, CT",
      6401 <= $z and $z <= 6770, "New Haven, CT",
      6404 <= $z and $z <= 6928, "Fairfield, CT",
      6409 <= $z and $z <= 6498, "Middlesex, CT",
      7001 <= $z and $z <= 8989, "Middlesex, NJ",
      7002 <= $z and $z <= 7399, "Hudson, NJ",
      7003 <= $z and $z <= 7199, "Essex, NJ",
      7005 <= $z and $z <= 7999, "Morris, NJ",
      7010 <= $z and $z <= 7677, "Bergen, NJ",
      7011 <= $z and $z <= 7544, "Passaic, NJ",
      7016 <= $z and $z <= 7974, "Union, NJ",
      7059 <= $z and $z <= 8896, "Somerset, NJ",
      7416 <= $z and $z <= 7890, "Sussex, NJ",
      7701 <= $z and $z <= 8750, "Monmouth, NJ",
      7820 <= $z and $z <= 8886, "Warren, NJ",
      7830 <= $z and $z <= 8889, "Hunterdon, NJ",
      8001 <= $z and $z <= 8347, "Salem, NJ",
      8002 <= $z and $z <= 8110, "Camden, NJ",
      8005 <= $z and $z <= 8759, "Ocean, NJ",
      8010 <= $z and $z <= 8641, "Burlington, NJ",
      8014 <= $z and $z <= 8344, "Gloucester, NJ",
      8037 <= $z and $z <= 8406, "Atlantic, NJ",
      8202 <= $z and $z <= 8270, "Cape May, NJ",
      8302 <= $z and $z <= 8362, "Cumberland, NJ",
      8520 <= $z and $z <= 8695, "Mercer, NJ",
      9007 <= $z and $z <= 9899, "undefined, AE",
      10001 <= $z and $z <= 10292, "New York, NY",
      10301 <= $z and $z <= 10314, "Richmond, NY",
      10451 <= $z and $z <= 10499, "Bronx, NY",
      10501 <= $z and $z <= 10805, "Westchester, NY",
      10509 <= $z and $z <= 12563, "Putnam, NY",
      10901 <= $z and $z <= 10995, "Rockland, NY",
      10910 <= $z and $z <= 12780, "Orange, NY",
      11001 <= $z and $z <= 11855, "Nassau, NY",
      11004 <= $z and $z <= 11697, "Queens, NY",
      11201 <= $z and $z <= 11256, "Kings, NY",
      12007 <= $z and $z <= 12469, "Albany, NY",
      12008 <= $z and $z <= 12345, "Schenectady, NY",
      12010 <= $z and $z <= 13452, "Montgomery, NY",
      12015 <= $z and $z <= 12496, "Greene, NY",
      12017 <= $z and $z <= 12593, "Columbia, NY",
      12018 <= $z and $z <= 12198, "Rensselaer, NY",
      12019 <= $z and $z <= 12884, "Saratoga, NY",
      12025 <= $z and $z <= 13470, "Fulton, NY",
      12031 <= $z and $z <= 13459, "Schoharie, NY",
      12057 <= $z and $z <= 12887, "Washington, NY",
      12064 <= $z and $z <= 13861, "Otsego, NY",
      12108 <= $z and $z <= 13436, "Hamilton, NY",
      12167 <= $z and $z <= 13860, "Delaware, NY",
      12401 <= $z and $z <= 12782, "Ulster, NY",
      12501 <= $z and $z <= 12604, "Dutchess, NY",
      12701 <= $z and $z <= 12792, "Sullivan, NY",
      12801 <= $z and $z <= 12886, "Warren, NY",
      12851 <= $z and $z <= 12998, "Essex, NY",
      12901 <= $z and $z <= 12992, "Clinton, NY",
      12914 <= $z and $z <= 13655, "Franklin, NY",
      12922 <= $z and $z <= 13699, "Saint Lawrence, NY",
      13020 <= $z and $z <= 13290, "Onondaga, NY",
      13021 <= $z and $z <= 13166, "Cayuga, NY",
      13028 <= $z and $z <= 13493, "Oswego, NY",
      13032 <= $z and $z <= 13484, "Madison, NY",
      13040 <= $z and $z <= 13863, "Cortland, NY",
      13053 <= $z and $z <= 14886, "Tompkins, NY",
      13054 <= $z and $z <= 13599, "Oneida, NY",
      13065 <= $z and $z <= 14860, "Seneca, NY",
      13124 <= $z and $z <= 13844, "Chenango, NY",
      13143 <= $z and $z <= 14590, "Wayne, NY",
      13305 <= $z and $z <= 13648, "Lewis, NY",
      13324 <= $z and $z <= 13491, "Herkimer, NY",
      13601 <= $z and $z <= 13693, "Jefferson, NY",
      13732 <= $z and $z <= 14892, "Tioga, NY",
      13737 <= $z and $z <= 13905, "Broome, NY",
      14001 <= $z and $z <= 14280, "Erie, NY",
      14003 <= $z and $z <= 14557, "Genesee, NY",
      14008 <= $z and $z <= 14305, "Niagara, NY",
      14009 <= $z and $z <= 14591, "Wyoming, NY",
      14029 <= $z and $z <= 14897, "Allegany, NY",
      14041 <= $z and $z <= 14788, "Cattaraugus, NY",
      14048 <= $z and $z <= 14787, "Chautauqua, NY",
      14098 <= $z and $z <= 14571, "Orleans, NY",
      14410 <= $z and $z <= 14694, "Monroe, NY",
      14414 <= $z and $z <= 14846, "Livingston, NY",
      14415 <= $z and $z <= 14878, "Yates, NY",
      14424 <= $z and $z <= 14585, "Ontario, NY",
      14529 <= $z and $z <= 14898, "Steuben, NY",
      14805 <= $z and $z <= 14893, "Schuyler, NY",
      14814 <= $z and $z <= 14925, "Chemung, NY",
      15001 <= $z and $z <= 16141, "Beaver, PA",
      15004 <= $z and $z <= 15483, "Washington, PA",
      15006 <= $z and $z <= 15295, "Allegheny, PA",
      15012 <= $z and $z <= 15631, "Fayette, PA",
      15062 <= $z and $z <= 15954, "Westmoreland, PA",
      15310 <= $z and $z <= 15380, "Greene, PA",
      15411 <= $z and $z <= 15963, "Somerset, PA",
      15521 <= $z and $z <= 17211, "Bedford, PA",
      15536 <= $z and $z <= 17267, "Fulton, PA",
      15630 <= $z and $z <= 16263, "Armstrong, PA",
      15681 <= $z and $z <= 16256, "Indiana, PA",
      15711 <= $z and $z <= 15865, "Jefferson, PA",
      15714 <= $z and $z <= 16699, "Cambria, PA",
      15721 <= $z and $z <= 16881, "Clearfield, PA",
      15821 <= $z and $z <= 16734, "Elk, PA",
      15828 <= $z and $z <= 16370, "Forest, PA",
      15832 <= $z and $z <= 15861, "Cameron, PA",
      16001 <= $z and $z <= 16066, "Butler, PA",
      16028 <= $z and $z <= 16375, "Clarion, PA",
      16101 <= $z and $z <= 16172, "Lawrence, PA",
      16110 <= $z and $z <= 16440, "Crawford, PA",
      16113 <= $z and $z <= 16311, "Mercer, PA",
      16301 <= $z and $z <= 16374, "Venango, PA",
      16312 <= $z and $z <= 16436, "Warren, PA",
      16333 <= $z and $z <= 16751, "Mckean, PA",
      16401 <= $z and $z <= 16565, "Erie, PA",
      16601 <= $z and $z <= 16693, "Blair, PA",
      16611 <= $z and $z <= 17264, "Huntingdon, PA",
      16677 <= $z and $z <= 16882, "Centre, PA",
      16720 <= $z and $z <= 17729, "Potter, PA",
      16822 <= $z and $z <= 17779, "Clinton, PA",
      16901 <= $z and $z <= 17765, "Tioga, PA",
      16910 <= $z and $z <= 18854, "Bradford, PA",
      17001 <= $z and $z <= 17266, "Cumberland, PA",
      17002 <= $z and $z <= 17099, "Mifflin, PA",
      17003 <= $z and $z <= 17088, "Lebanon, PA",
      17005 <= $z and $z <= 17177, "Dauphin, PA",
      17006 <= $z and $z <= 17090, "Perry, PA",
      17014 <= $z and $z <= 17094, "Juniata, PA",
      17017 <= $z and $z <= 17881, "Northumberland, PA",
      17019 <= $z and $z <= 17415, "York, PA",
      17022 <= $z and $z <= 19501, "Lancaster, PA",
      17201 <= $z and $z <= 17272, "Franklin, PA",
      17301 <= $z and $z <= 17375, "Adams, PA",
      17701 <= $z and $z <= 17776, "Lycoming, PA",
      17731 <= $z and $z <= 18632, "Sullivan, PA",
      17810 <= $z and $z <= 17889, "Union, PA",
      17812 <= $z and $z <= 17882, "Snyder, PA",
      17814 <= $z and $z <= 18631, "Columbia, PA",
      17821 <= $z and $z <= 17884, "Montour, PA",
      17901 <= $z and $z <= 19549, "Schuylkill, PA",
      18001 <= $z and $z <= 18351, "Northampton, PA",
      18011 <= $z and $z <= 18195, "Lehigh, PA",
      18012 <= $z and $z <= 18624, "Carbon, PA",
      18039 <= $z and $z <= 19067, "Bucks, PA",
      18041 <= $z and $z <= 19525, "Montgomery, PA",
      18056 <= $z and $z <= 19640, "Berks, PA",
      18058 <= $z and $z <= 18610, "Monroe, PA",
      18201 <= $z and $z <= 18774, "Luzerne, PA",
      18324 <= $z and $z <= 18464, "Pike, PA",
      18401 <= $z and $z <= 18473, "Wayne, PA",
      18403 <= $z and $z <= 18653, "Lackawanna, PA",
      18413 <= $z and $z <= 18847, "Susquehanna, PA",
      18419 <= $z and $z <= 18657, "Wyoming, PA",
      19003 <= $z and $z <= 19373, "Delaware, PA",
      19019 <= $z and $z <= 19255, "Philadelphia, PA",
      19301 <= $z and $z <= 19520, "Chester, PA",
      19415 <= $z and $z <= 19415, "undefined, PA",
      19701 <= $z and $z <= 19899, "New Castle, DE",
      19901 <= $z and $z <= 19980, "Kent, DE",
      19930 <= $z and $z <= 19975, "Sussex, DE",
      20001 <= $z and $z <= 20599, "District Of Columbia, DC",
      20101 <= $z and $z <= 22093, "Loudoun, VA",
      20106 <= $z and $z <= 22749, "Rappahannock, VA",
      20108 <= $z and $z <= 20110, "Manassas City, VA",
      20111 <= $z and $z <= 20113, "Manassas Park City, VA",
      20115 <= $z and $z <= 22742, "Fauquier, VA",
      20120 <= $z and $z <= 22321, "Fairfax, VA",
      20136 <= $z and $z <= 22195, "Prince William, VA",
      20601 <= $z and $z <= 20695, "Charles, MD",
      20606 <= $z and $z <= 20692, "Saint Marys, MD",
      20607 <= $z and $z <= 20799, "Prince Georges, MD",
      20610 <= $z and $z <= 20754, "Calvert, MD",
      20701 <= $z and $z <= 21794, "Howard, MD",
      20711 <= $z and $z <= 21412, "Anne Arundel, MD",
      20812 <= $z and $z <= 20997, "Montgomery, MD",
      21001 <= $z and $z <= 21161, "Harford, MD",
      21013 <= $z and $z <= 21286, "Baltimore, MD",
      21048 <= $z and $z <= 21797, "Carroll, MD",
      21201 <= $z and $z <= 21298, "Baltimore City, MD",
      21501 <= $z and $z <= 21766, "Allegany, MD",
      21520 <= $z and $z <= 21561, "Garrett, MD",
      21601 <= $z and $z <= 21679, "Talbot, MD",
      21607 <= $z and $z <= 21690, "Queen Annes, MD",
      21609 <= $z and $z <= 21688, "Caroline, MD",
      21610 <= $z and $z <= 21678, "Kent, MD",
      21613 <= $z and $z <= 21869, "Dorchester, MD",
      21701 <= $z and $z <= 21798, "Frederick, MD",
      21711 <= $z and $z <= 21795, "Washington, MD",
      21801 <= $z and $z <= 21875, "Wicomico, MD",
      21811 <= $z and $z <= 21872, "Worcester, MD",
      21817 <= $z and $z <= 21890, "Somerset, MD",
      21901 <= $z and $z <= 21930, "Cecil, MD",
      22030 <= $z and $z <= 22038, "Fairfax City, VA",
      22040 <= $z and $z <= 22046, "Falls Church City, VA",
      22201 <= $z and $z <= 22246, "Arlington, VA",
      22301 <= $z and $z <= 22336, "Alexandria City, VA",
      22401 <= $z and $z <= 22404, "Fredericksburg City, VA",
      22403 <= $z and $z <= 22555, "Stafford, VA",
      22407 <= $z and $z <= 22565, "Spotsylvania, VA",
      22427 <= $z and $z <= 22580, "Caroline, VA",
      22432 <= $z and $z <= 22579, "Northumberland, VA",
      22433 <= $z and $z <= 22972, "Orange, VA",
      22436 <= $z and $z <= 23115, "Essex, VA",
      22442 <= $z and $z <= 22581, "Westmoreland, VA",
      22448 <= $z and $z <= 22547, "King George, VA",
      22460 <= $z and $z <= 22572, "Richmond, VA",
      22480 <= $z and $z <= 22578, "Lancaster, VA",
      22601 <= $z and $z <= 22604, "Winchester City, VA",
      22602 <= $z and $z <= 22656, "Frederick, VA",
      22610 <= $z and $z <= 22649, "Warren, VA",
      22611 <= $z and $z <= 22663, "Clarke, VA",
      22626 <= $z and $z <= 22847, "Shenandoah, VA",
      22650 <= $z and $z <= 22851, "Page, VA",
      22701 <= $z and $z <= 22741, "Culpeper, VA",
      22709 <= $z and $z <= 22989, "Madison, VA",
      22801 <= $z and $z <= 22807, "Harrisonburg City, VA",
      22802 <= $z and $z <= 24471, "Rockingham, VA",
      22843 <= $z and $z <= 24486, "Augusta, VA",
      22901 <= $z and $z <= 24590, "Albemarle, VA",
      22902 <= $z and $z <= 22910, "Charlottesville City, VA",
      22920 <= $z and $z <= 24599, "Nelson, VA",
      22935 <= $z and $z <= 22973, "Greene, VA",
      22963 <= $z and $z <= 23084, "Fluvanna, VA",
      22980 <= $z and $z <= 22980, "Waynesboro City, VA",
      23001 <= $z and $z <= 23191, "Gloucester, VA",
      23002 <= $z and $z <= 23105, "Amelia, VA",
      23004 <= $z and $z <= 23936, "Buckingham, VA",
      23005 <= $z and $z <= 23192, "Hanover, VA",
      23009 <= $z and $z <= 23181, "King William, VA",
      23011 <= $z and $z <= 23141, "New Kent, VA",
      23014 <= $z and $z <= 23238, "Goochland, VA",
      23021 <= $z and $z <= 23163, "Mathews, VA",
      23023 <= $z and $z <= 23177, "King And Queen, VA",
      23024 <= $z and $z <= 23170, "Louisa, VA",
      23027 <= $z and $z <= 23040, "Cumberland, VA",
      23030 <= $z and $z <= 23147, "Charles City, VA",
      23031 <= $z and $z <= 23180, "Middlesex, VA",
      23058 <= $z and $z <= 23294, "Henrico, VA",
      23081 <= $z and $z <= 23188, "James City, VA",
      23090 <= $z and $z <= 23696, "York, VA",
      23101 <= $z and $z <= 23139, "Powhatan, VA",
      23112 <= $z and $z <= 23838, "Chesterfield, VA",
      23173 <= $z and $z <= 23298, "Richmond City, VA",
      23186 <= $z and $z <= 23187, "Williamsburg City, VA",
      23301 <= $z and $z <= 23488, "Accomack, VA",
      23304 <= $z and $z <= 23898, "Isle Of Wight, VA",
      23307 <= $z and $z <= 23486, "Northampton, VA",
      23320 <= $z and $z <= 23328, "Chesapeake City, VA",
      23432 <= $z and $z <= 23439, "Suffolk City, VA",
      23450 <= $z and $z <= 23521, "Virginia Beach City, VA",
      23501 <= $z and $z <= 23551, "Norfolk City, VA",
      23601 <= $z and $z <= 23628, "Newport News City, VA",
      23630 <= $z and $z <= 23681, "Hampton City, VA",
      23662 <= $z and $z <= 23662, "Poquoson City, VA",
      23701 <= $z and $z <= 23709, "Portsmouth City, VA",
      23801 <= $z and $z <= 23875, "Prince George, VA",
      23803 <= $z and $z <= 23806, "Petersburg City, VA",
      23821 <= $z and $z <= 23920, "Brunswick, VA",
      23822 <= $z and $z <= 23894, "Dinwiddie, VA",
      23824 <= $z and $z <= 23955, "Nottoway, VA",
      23827 <= $z and $z <= 23878, "Southampton, VA",
      23834 <= $z and $z <= 23834, "Colonial Heights City, VA",
      23839 <= $z and $z <= 23899, "Surry, VA",
      23847 <= $z and $z <= 23847, "Emporia City, VA",
      23851 <= $z and $z <= 23851, "Franklin City, VA",
      23860 <= $z and $z <= 23860, "Hopewell City, VA",
      23867 <= $z and $z <= 23897, "Sussex, VA",
      23870 <= $z and $z <= 23879, "Greensville, VA",
      23901 <= $z and $z <= 23966, "Prince Edward, VA",
      23915 <= $z and $z <= 24580, "Mecklenburg, VA",
      23923 <= $z and $z <= 23976, "Charlotte, VA",
      23938 <= $z and $z <= 23974, "Lunenburg, VA",
      23939 <= $z and $z <= 24593, "Appomattox, VA",
      24001 <= $z and $z <= 24048, "Roanoke City, VA",
      24018 <= $z and $z <= 24179, "Roanoke, VA",
      24019 <= $z and $z <= 24438, "Botetourt, VA",
      24023 <= $z and $z <= 24162, "Montgomery, VA",
      24053 <= $z and $z <= 24185, "Patrick, VA",
      24054 <= $z and $z <= 24168, "Henry, VA",
      24058 <= $z and $z <= 24347, "Pulaski, VA",
      24065 <= $z and $z <= 24184, "Franklin, VA",
      24069 <= $z and $z <= 24594, "Pittsylvania, VA",
      24072 <= $z and $z <= 24380, "Floyd, VA",
      24086 <= $z and $z <= 24167, "Giles, VA",
      24095 <= $z and $z <= 24570, "Bedford, VA",
      24112 <= $z and $z <= 24115, "Martinsville City, VA",
      24127 <= $z and $z <= 24131, "Craig, VA",
      24141 <= $z and $z <= 24143, "Radford, VA",
      24153 <= $z and $z <= 24157, "Salem, VA",
      24201 <= $z and $z <= 24209, "Bristol, VA",
      24210 <= $z and $z <= 24361, "Washington, VA",
      24215 <= $z and $z <= 24293, "Wise, VA",
      24217 <= $z and $z <= 24607, "Dickenson, VA",
      24218 <= $z and $z <= 24282, "Lee, VA",
      24224 <= $z and $z <= 24649, "Russell, VA",
      24239 <= $z and $z <= 24658, "Buchanan, VA",
      24244 <= $z and $z <= 24290, "Scott, VA",
      24273 <= $z and $z <= 24273, "Norton City, VA",
      24292 <= $z and $z <= 24379, "Grayson, VA",
      24311 <= $z and $z <= 24375, "Smyth, VA",
      24312 <= $z and $z <= 24382, "Wythe, VA",
      24314 <= $z and $z <= 24366, "Bland, VA",
      24317 <= $z and $z <= 24381, "Carroll, VA",
      24333 <= $z and $z <= 24333, "Galax City, VA",
      24377 <= $z and $z <= 24651, "Tazewell, VA",
      24401 <= $z and $z <= 24402, "Staunton City, VA",
      24412 <= $z and $z <= 24487, "Bath, VA",
      24413 <= $z and $z <= 24468, "Highland, VA",
      24415 <= $z and $z <= 24579, "Rockbridge, VA",
      24416 <= $z and $z <= 24416, "Buena Vista City, VA",
      24422 <= $z and $z <= 24422, "Clifton Forge City, VA",
      24426 <= $z and $z <= 24426, "Covington City, VA",
      24448 <= $z and $z <= 24474, "Alleghany, VA",
      24450 <= $z and $z <= 24450, "Lexington City, VA",
      24501 <= $z and $z <= 24515, "Lynchburg City, VA",
      24512 <= $z and $z <= 24588, "Campbell, VA",
      24520 <= $z and $z <= 24598, "Halifax, VA",
      24521 <= $z and $z <= 24595, "Amherst, VA",
      24523 <= $z and $z <= 24523, "Bedford City, VA",
      24540 <= $z and $z <= 24544, "Danville City, VA",
      24701 <= $z and $z <= 25971, "Mercer, WV",
      24716 <= $z and $z <= 25943, "Wyoming, WV",
      24801 <= $z and $z <= 24899, "Mcdowell, WV",
      24851 <= $z and $z <= 25697, "Mingo, WV",
      24901 <= $z and $z <= 25984, "Greenbrier, WV",
      24915 <= $z and $z <= 26291, "Pocahontas, WV",
      24918 <= $z and $z <= 24993, "Monroe, WV",
      24919 <= $z and $z <= 25988, "Summers, WV",
      25002 <= $z and $z <= 26680, "Fayette, WV",
      25003 <= $z and $z <= 25396, "Kanawha, WV",
      25004 <= $z and $z <= 25989, "Raleigh, WV",
      25005 <= $z and $z <= 25286, "Roane, WV",
      25009 <= $z and $z <= 25529, "Boone, WV",
      25011 <= $z and $z <= 25569, "Putnam, WV",
      25018 <= $z and $z <= 26617, "Clay, WV",
      25022 <= $z and $z <= 25654, "Logan, WV",
      25059 <= $z and $z <= 26691, "Nicholas, WV",
      25095 <= $z and $z <= 25550, "Mason, WV",
      25231 <= $z and $z <= 26173, "Jackson, WV",
      25234 <= $z and $z <= 26151, "Calhoun, WV",
      25258 <= $z and $z <= 26638, "Gilmer, WV",
      25401 <= $z and $z <= 25440, "Berkeley, WV",
      25410 <= $z and $z <= 25446, "Jefferson, WV",
      25411 <= $z and $z <= 25434, "Morgan, WV",
      25431 <= $z and $z <= 26865, "Hampshire, WV",
      25501 <= $z and $z <= 25573, "Lincoln, WV",
      25504 <= $z and $z <= 25779, "Cabell, WV",
      25507 <= $z and $z <= 25709, "Wayne, WV",
      26003 <= $z and $z <= 26074, "Ohio, WV",
      26030 <= $z and $z <= 26075, "Brooke, WV",
      26031 <= $z and $z <= 26055, "Marshall, WV",
      26034 <= $z and $z <= 26062, "Hancock, WV",
      26101 <= $z and $z <= 26187, "Wood, WV",
      26134 <= $z and $z <= 26170, "Pleasants, WV",
      26135 <= $z and $z <= 26434, "Tyler, WV",
      26138 <= $z and $z <= 26160, "Wirt, WV",
      26148 <= $z and $z <= 26421, "Ritchie, WV",
      26155 <= $z and $z <= 26581, "Wetzel, WV",
      26201 <= $z and $z <= 26237, "Upshur, WV",
      26203 <= $z and $z <= 26298, "Webster, WV",
      26224 <= $z and $z <= 26296, "Randolph, WV",
      26238 <= $z and $z <= 26416, "Barbour, WV",
      26260 <= $z and $z <= 26292, "Tucker, WV",
      26301 <= $z and $z <= 26568, "Harrison, WV",
      26321 <= $z and $z <= 26452, "Lewis, WV",
      26328 <= $z and $z <= 26456, "Doddridge, WV",
      26335 <= $z and $z <= 26641, "Braxton, WV",


  • 12.  RE: How to auto-fill a county field based on zip code?

    Posted 09-07-2017 21:28
    Here is my processing script in case you want to pursue this:

    //"zip_code","latitude","longitude","city","state","county"
    var data = [
      ["00501",40.922326,-72.637078,"Holtsville","NY","Suffolk"],
      ["00544",40.922326,-72.637078,"Holtsville","NY","Suffolk"],
      ...
      ["99929",56.449893,"-132.364407","Wrangell","AK","Wrangell Petersburg"],
      ["99950",55.542007,"-131.432682","Ketchikan","AK","Ketchikan Gateway"]
    ];
    var result = _.chain(data)
      .groupBy(function(item) {
        return item[5] + ", " + item[4];
      })
      .map(function(data, county) {
         var minzip = _.chain(data).pluck(0).map(function(zip) {return parseInt(zip, 10)}).min().value();
         var maxzip = _.chain(data).pluck(0).map(function(zip) {return parseInt(zip, 10)}).max().value();
         return '  ${minzip} <= $z and $z <= ${maxzip}, "${county}",';
      })
      .value();
      var formula = '
    var Number z = ToNumber([ZipCode]);
    If(
    ${result.join("\n")}
    )
    ';
    console.log(formula);