Address Importing Best Practices
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 ...
Forum Discussion
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
------------------------------
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
------------------------------
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
------------------------------
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
------------------------------
- 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
------------------------------- MarkShnier__You
Qrew Legend
David,
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)
mark.shnier@gmail.com
------------------------------- 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
------------------------------
