View Only
Expand all | Collapse all

Address Importing Best Practices

  • 1.  Address Importing Best Practices

    Posted 01-06-2023 16:53
    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


    David Semitekol

  • 2.  RE: Address Importing Best Practices

    Posted 01-07-2023 09:34

    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)

  • 3.  RE: Address Importing Best Practices

    Posted 01-09-2023 10:46
    Thanks @Mark Shnier (Your Quickbase Coach) for the suggestion.  My main concern is how the QuickBase address field creates the addresses.  They don't seem to use a standard convention that we normally see in Google Maps, or more importantly, USPS guidelines.

    Unfortunately, ​I'm importing a few thousand addresses so I'm desperately trying to avoid the human review.  I'm also trying to plan for the future, for when end users enter addresses into the database.  I'm trying to control accuracy and consistently of the newly entered addresses.

    Is it possible to plug in the Google Address Autocomplete into QuickBase?

    Or QuickBase, is it possible to change the formatting of your Address Autocomplete?

    David Semitekol

  • 4.  RE: Address Importing Best Practices

    Posted 01-09-2023 10:52
    I've been experimenting with an address table built into the database.  Here are the fields:

    Address Number
    Street Name
    Post Directional
    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

  • 5.  RE: Address Importing Best Practices

    Posted 01-10-2023 09:17
    I have a similar problem at a client where data has to be cleaned up before it can go into the main system.   We built a separate application to Normalize the data.   The fields that would be exported from the Normalization App were all Formulas working one to one on the data thaw was imported by the User.

    So using your fields as an example you would have 

    Postal Code and Postal Code Formula

    You can then build a complex formula to clean up what they have imported to a readable, standard Postal Code.

    Then building your full address from those parts can be used in your Duplication check.  Are using a Formula Query for this?  Maybe a Pipeline?

    I used a PHP script to take the data from the first application to the second one because that allowed for very complex handling of the data.  Scripts are not intuitive but they are powerful.

    Don Larson

  • 6.  RE: Address Importing Best Practices

    Posted 01-10-2023 15:24
    Thanks Don, this sounds like good advice I'll have to look into it.  How are your users adding individual addresses?  Did you ever adopt the QB built-in address field?

    I'm researching the Google Address Validation API and seeing if that can be incorporated into QB.  It requires learning about QB Code Pages too unfortunately.

    For my address duplication check I'm using 2 parts.  The first is a formula field that combines all of the necessary elements of the address.  The second is then a custom data rule on the table that alerts the user if they are trying to enter a duplicate address.

    QB won't run a duplication check on a formula field and my end users won't have the option to bulk import additional addresses.  The workflow will only require them to enter a few addresses here and there.

    David Semitekol

  • 7.  RE: Address Importing Best Practices

    Posted 01-10-2023 16:15

    What do you mean by this?

    QB won't run a duplication check on a formula field

    You can have a Formula Query look for duplicates and that will happen even before the record is saved. 

    Mark Shnier (Your Quickbase Coach)

  • 8.  RE: Address Importing Best Practices

    Posted 01-10-2023 16:31
    Hi Mark, I'm referring to the "Must be unique" checkbox in the field settings.  Here is my formula:
    List(ToText([Number Start]),ToText([Number End]),[Address Number],[Predirectional],[Street Name],[Suffix],[Post Directional])​

    When I tried to save the field, I got this error:

    When I did some research, I came across this post in the community:
    Enforcing Uniqueness on a Formula Field

    David Semitekol

  • 9.  RE: Address Importing Best Practices

    Posted 01-10-2023 16:41

    Perhaps some of their fields are lookup fields?  I would need to know the field type of each to suggest which one is the culprit. 

    If you do a formula query it's also possible to detect duplicates, regardless of what the components are of the unique field.   If you need help with that we can muddle through it together. The Formula Query would be if the Size of the result was greater than 1. 

    Mark Shnier (Your Quickbase Coach)

  • 10.  RE: Address Importing Best Practices

    Posted 01-10-2023 16:59
    Sorry Mark, yes, the Street Name is a lookup field.  I'm okay with the current solution of using a custom data rule.

    What I'm really struggling with is the QB Address Field and the formatting of the MapBox address.  They just are not using standard address formatting that we expect from the USPS or Google Maps.

    This causes 2 issues:
    The first is that searching for addresses becomes very difficult because end users are accustomed to entering an address using the standard format.  But when the QB Address field changes the way an address gets entered, the user can no longer find the address using the search records tool.

    The second is there is no way to prevent duplicate entries easily, if at all, so it becomes a data entry nightmare.  Sure, we could write a complex formula that looks for "North" and converts to "N" but that won't account for all variables in an address.  Like when "North" is actually the street name and not the predirectional.

    Today's end user wants to be able to start entering an address and have a dropdown autofill the result.  We need to find a way to duplicate that experience in QB.  I think that is why I've been experimenting with this complex address table but I'm not sure if it is future proof, provides a quality user experience, or can scale when we go from 1k to 5k to 10k plus records.

    Any help would be greatly appreciated in solving this Rubik's Cube.

    David Semitekol