Hi, my name is John Etherton. I’m one of the engineers that worked on our new import features, which will be available in TrackVia Express and Pro on June 6, 2014. We have done a lot to improve the process of bringing your data into TrackVia from external sources and I wanted to take a moment and walk you through these changes.
Previously, our import process relied heavily on the format of the user’s data exactly matching the format of the TrackVia table. If the customer’s data wasn’t formatted correctly, the import process would fail. The resulting error message may not have clearly explained the problem or how to fix it. We’ve changed all of that.
Data formatting in TrackVia
Now when the TrackVia user starts the import process we do our best to determine what columns in the spreadsheet match what fields in the database, as well as which row in the spreadsheet is the header, but the user has the ability to override these choices. The image below demonstrates how we show the user the data he or she is about to import and give him or her the option to specify how it should match up with what is already in the database.
To help catch formatting errors ahead of time, we now pre-scan the data in the user’s spreadsheet to make sure the data in a given column matches the data type expected by the database. As you can see below, we will highlight any rows that have data values that don’t match in red.
Error handling in TrackVia
If data with formatting errors is imported, we now give the user a choice for how they wish to proceed. A user can either opt to reject the whole row if any field contains an error, or set the field with an error blank and keep the rest of the data from that row. This will better accommodate each user’s workflow and personal preference.
To further help the user work around any issues with imported data, we now create an error report spreadsheet for the user if any formatting errors were detected. This file will contain all of the original data for each row that had an error, as well as a column to indicate if the row was imported or not. There will be an additional column explaining the error, as seen below.
Our hope is that this file will put all the erroneous data in one place; the user can then fix the errors and reimport it. This is much quicker than figuring out if errors were already imported or not, where the errors occurred, and then guessing what the errors include.
Relationships and user fields
With TrackVia, users can now import data that contains relationships to other tables. As an example, let’s imagine I have a table of car manufacturers, named Manufacturers, and another table of cars, named Cars. Each automobile has a manufacturer, thus we can create a link between these two tables. Sometimes we call this a relationship. One way we think of these relationships or links is in “has many” such as, “Manufacturers have many cars.”
If we want to import information about automobiles into our cars table and keep the relationship between a car and its manufacturer, we have to have one column for each field that makes up the ID of the manufacturer table. In this example, I have set the ID for each record in the manufacturer table to a combination of the Name field and the Country field. See the screen shot below.
Therefore, I need to have a column for the manufacturer’s name and the manufacturer’s country in my spreadsheet of cars. Below is a screen shot of how such a spreadsheet might look.
Now when the user imports this data into the cars table, the system will detect the link between cars and manufacturers and will ask the user if they want to link the imported data to the related tables. If the user indicates that they do want to add linked data, they will then see the required fields from the linked table show up in the list of fields in the database when matching spreadsheet columns to database fields.
There are a couple of things to keep in mind when working with TrackVia’s linked tables. The first is the information that makes up the record ID must match exactly with the imported data. In my example, if I want to link Impala to General Motors, I need to have both “General Motors” and “USA” in my spreadsheet. The fields are case sensitive, so “usa” won’t work.
Secondly, importing tables with links will be slower than importing tables without links. For each link we have to check that it’s valid before we import it, so in addition to the import step, we now have an extra look up. To keep this from taking too long we cache the links we find to keep us from having to repeat the same look up multiple times. In my example, once we’ve looked up Ford Motor Company, USA we will not need to look it up again. This works well for a situation like cars and manufacturers, as there will be few manufacturers relative to the number of cars. However, in a situation like an order tracking system, there could be many orders relative to the number of line items, and thus the cache would not help import times very much.
Import to update
TrackVia now also gives users the ability to import to update existing data, which is in contrast to our two previous modes: import to create a table, and import to add data to a table. To use import to update, the data the user is importing must contain the ID for the records he or she wants to update. As mentioned in the section on importing relationships, the ID can be made up of one or more fields, so if the user’s table has a compound ID, then the data being imported must contain one field for each part of the ID. Also, as mentioned before, the IDs are case sensitive.
This feature should be a great help to TrackVia users that get their data from other systems in bulk. Again, as with relationships, import to update will run slower as it needs to verify that the ID specified in the spreadsheet matches an existing record. If there is not a match the system can ignore that row in your spreadsheet, or create a new record in the database. This is the difference between Update Only and Add and Update.
If a field in the database is either completely omitted from the spreadsheet or is left blank in the cell for a given row, that value will be left as it is in the database and won’t be updated.
For example, the screen shot above shows a spreadsheet that will update the year and price for some cars, but will not change the body style or manufacturer information.
Also, if the TrackVia user imports to update and their data contains two or more rows that reference the same record in the database with the same ID, then all of the changes made in those rows in the spreadsheet will be made in the order they appear to the record. So the following would result in a record for Civic with a year of 2016, a body style of coupe, sedan, convertible, and a base price of $20,000.
In addition to these changes, this latest TrackVia release has many performance improvements that should help users with large files of over 10,000 rows. I really hope you enjoy the new features and all the work we have put into making TrackVia’s import the best in the business. If you have any questions, concerns, or feedback please don’t hesitate to reach out to us at help.trackvia.com or on Twitter @trackviasupport.