Is it possible to use the address sub field to reference another table?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have candidate profiles in which I am using an address field.  I need to categorize candidates into different tiers based on zip codes. I have already set up a table for the zip codes and corresponding tiers, I just need a way to bring that information into the candidate's table based on the address. Is it possible to use the address field to reference the zip code table in some way, to bring in the tier information?

Photo of Alison

Alison

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
There are about 43,000 zip codes in active use (and a few secret ones!) so I don't think this is going to be viable in QuickBase. You may want to use some other grouping of zip codes. Here are some ideas:

Zip Code Prefixes
https://en.wikipedia.org/wiki/List_of_ZIP_code_prefixes

Zip codes by State
http://www.structnet.com/instructions/zip_min_max_by_state.html

HUD USPS ZIP Code Crosswalk Files
https://www.huduser.gov/portal/datasets/usps_crosswalk.html

U.S. Zip codes and MSA's (Metropolitan Statistical Area)
https://www.dol.gov/owcp/regs/feeschedule/fee/fee11/fs11_gpci_by_msa-ZIP.pdf
Photo of Alison

Alison

  • 0 Points
I'm actually using a specific group of zip codes that have already been uploaded into a table. i just need a way to reference this using the address field.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
When you create an addy field with fid=6 there will be subfields created with fids {7,8,9,10,11,12}. The second from last is the zip / postal code. These fields are handled in a special way. What I would do is create a new formula text field called [MyZipCode] and set it to the formula [_fid_11]. Then use [_fid_11] to do the lookup.

I haven't tried this but I don't have time. Let us know if this works for you.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
When you look at the field properties for any Address field Type (way down at the bottom), it shows you the name of the 6 sub fields.  They can be used on Square Brackets like any other field.

For example the zip code field for an address field called [Address] is

[Address : Postal Code]
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
That is an alternate way to address the zip /postal code field but I don't think the QuickBase GUI will display that field when building a relationship. That is why I created a distinct formula text field and my answer. But I didn't test it.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
But if you make that field on the table, then it can be used as the reference field on the right side of a relationship.