In a previous post I talked about the powerful new QuickBase Address fields and Map reports. The new Address field type makes all the magic happen because it contains all the components of an address in a single field that is integrated with Google Maps. But what if you have already been tracking address information in an existing table in separate fields? You can't take advantage of these new features until you move that data from the individual fields into the new Address field. Fortunately, moving that address information into the new Address field type is simple and this post will take you step-by-step through the migration process.
In this example I have an "Employees" table that includes employee home address info. As you can see in the picture below, we have address information in typically named fields. We’re going to move the data in those fields into their corresponding components of a new Address field type.
The quickest way to export specific data in QuickBase is to save a report as a spreadsheet, so I'll create a new table report and include the fields listed here:
The key (pun intended) to successful migration is to include the table’s key field in the report. The key field is the unique identifier of a record and we’ll need it when we re-import the data into the table. It will ensure that each record is updated with the same address information it had before. In most tables the key field is named Record ID#, but in your table it maybe something different. To determine which is the key field, just look for the key icon in the field list. Whatever the key field is, make sure that it is included in the report.
Since I want to update all the records in the table, I won’t include any filtering in this report. When the report is complete, I’ll save it and view the records.
My report has all the data I want to migrate. While viewing the report I’ll export the data to a spreadsheet file by going to the report’s “More” menu and choosing “Save as a spreadsheet.” This creates a file named “Employees.csv” in my Downloads folder.
Now I need to create the Address field. This new field will contain all the address information we’ve just exported. I’ll call it “Home Address.”
After adding it to the table, I’ll put it on my form right above where the current individual address elements are located. Don't worry about duplication, I'll clean up the old fields as soon as the data migration is complete.
The last step brings my exported data back into the table and places it in the sub-components of my new "Home Address" field. To do this, I’ll go to the Employees table Home Page and chose “Import/Export” from the page bar.
On the Import page, I choose "Import into a table from a file" and specify the table “Employees.” I then choose the file "Employees.csv" and click “Import From File.”
I get a message telling me that the import includes the key field and that my records may be updated if I continue the import. This is exactly what we want, so I’ll click “OK.”
Now I’ll switch the import fields from the current names to the new “Home Address” sub-components. The imported column “Street 1” will map to “Home Address: Street 1” , "Street 2" will map to “Home Address: Street 2” and so forth till I have the import mapping complete as shown here.
The key field column “Record ID#” stays the same. This is critical since it will ensure that all the correct records are updated with the right data. Also, make sure you leave all the field import settings to import "To Existing Field."
When that's complete, I’ll click the “Import (with Update)” button and my new "Home Address" field will have all the imported data. In my example, I only have 8 records so the import happens almost instantly. If you have significant number of records the import could take a while.
Once the import is complete, I’ll verify that everything worked as planned. The easiest way to do this is to add the new Address field to a report right next to the old address data and scan the records to make sure everything was imported correctly.
Once I’m happy with the results, I can delete the old fields in the field list.
Note: Before deleting make sure you know if these fields have been used in formulas and other place such as filters and permissions. For example, you may have a filter in a report that shows only employees where "State" = "Massachusetts. If this is the case, you'll need to switch the reference to the old field to the new "Home Address" sub-component. The simplest way to find out where a given field is used is to look at the "Usage" tab in the Field Properties of each field. Write down the places where the field is used and update them all using the new address field components. Of course any reports that displayed the old address info should be updated with the new address field too. Once that's complete you can safely delete the old fields.
Now, looking at my form I see the new Address field with all the correct data and a map right next to it.
Now that I have my address data migrated I can create Map reports as well.
Thanks to all our customers who helped with feedback while we were designing and developing these new features. If you have more feedback while using them please let us know through QuickBase User Voice.
Not yet a user?