Skip to main content
Question

Address Importing Best Practices

  • January 6, 2023
  • 0 replies
  • 288 views
Forum|alt.badge.img+4
Hello, looking to crowd source some best practices for importing and standardizing addresses in Quickbase.  I know QB uses MapBox but they have some odd formatting practices.  For example, they spell out pre-directionals and don't follow common address formatting that we all familiar to: USPS and Google Maps.

I have a lot of existing addresses that I would like to import, not all are in the best condition.  The address needs to serve has a unique field since multiple records & tables will relate to an address.  My users will also be entering new addresses so I want to avoid the following which would cause several different address records even though they are the same.

123 N Main St
123 North Main St
123 N Main Street

Thanks!

------------------------------
David Semitekol
------------------------------
    This topic has been closed for replies.

    MarkShnierYou
    Forum|alt.badge.img+24

    I doubt that there is a magic bullet here.  I suggest that you import your raw data into a staging table in your app.  Then use formula using the SearchAndReplace function to successively standardlze non standard elements, for example,

    Change Street into St.
    Change St into St.

    some will be a judgment call.

    for example, 

    123 North St. is probably correct

    but 123 Main St. North should probably 123 Main St. N

    so my suggestion is to use a staging stable with a human review before pushing into your main table and then to keep improving the formula as you learn about new formats to standardize.

    You will also want to document what your standard is so your users can follow it and all be in agreement both for the manual entries and those running the imports.




    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    Mark Shnier. Your Quickbase Coach
    Forum|alt.badge.img+4
    • DavidSemitekol
    • Author
    • Registered
    • January 9, 2023
    I've been experimenting with an address table built into the database.  Here are the fields:

    Address Number
    Predirectional
    Street Name
    Suffix
    Post Directional
    City
    State
    Postal Code

    The Street Name and City are fields related back to their respective tables that are preloaded.  Since these are constant it should help the end user when entering new addresses.

    The Pre & Post Directionals along with the Suffix are simple dropdown fields since there are a lot less variables.

    Inside the table I also have a couple of formula fields to create the full address and run a duplication check.

    The idea is that this table is then related throughout the database.  The other main tables can then be linked together through a common address that is consistent.

    I'm not 100% sold on this solution though, I'm not sure if it will be too cumbersome for the end users.

    ------------------------------
    David Semitekol
    ------------------------------
    Terms & ConditionsAccessibility statement