“A process cannot be understood by stopping it. Understanding must move with the flow of the process, must join it and flow with it.”
– Frank Herbert, Dune
No app or feature has ever been permanent. This is a simple and unavoidable truth of things. If you’re maintaining an app to help your organization get work done, that app will need changes from time to time. Your processes will change. The work will change. Your system will need to adapt to those changes and over time, you’ll probably develop a repertoire of techniques to make that happen. One way to smoothly move your data when these changes happen is by performing a field-level version of an older technique, the Lift and Shift.
What is a Lift & Shift?
The Lift and Shift technique is generally used to move a large dataset or perhaps an entire existing database or app between environments with minimal disruption. It relies on transferring the data, unchanged, from one environment to another, and effectively “re-wiring” the destination to accommodate it, sometimes through an extra translation layer that emulates the original environment.
The advantage is that rather than painstakingly importing your original dataset into a new schema, you’re installing your existing data, unchanged, in one fell swoop. Once the dataset is there and is a permanent part of your new system, you’re able to internally massage it over time into a more modern schema, making small changes field by field, making it a great method to minimize end-user-pain during migration to a new solution, but a poor choice for speed.
In a TrackVia app, I often use something similar to update live app designs directly in production without disrupting users.
Let’s suppose I’m administering a TrackVia app that has been around for years, the users get along with it, and the data is good. However, on one of this app’s forms, there is an important question being asked in an ordinary drop-down, or a plain text entry. The method has worked, but as the business has changed, so too have the answers, resulting in a field that is now limiting. In this scenario, I’ve identified a need for that simple drop-down to become a full-blown relationship to another table. This way, the list of choices can be derived from filtered views of the parent table.
But what about my existing data?
What about the tens of thousands of records that were created using the original field? This is an important architectural adjustment to the app, but it will result in data before a certain date being stored in a separate field. Anyone who’s been around the block on data management knows what I’m talking about here: fields appended with the dreaded (DO NOT USE) or (OLD), leaving future admins to wonder forever if it’s safe to ever remove those fields. Preventing this is an important goal for any administrator. It will save you time and effort, and it will prevent enormous headaches for your successors.
I generally prefer to do this without any downtime and without disrupting or retraining users. Any time I can get away with it, I want changes like this to “just work” from the end-user perspective, and fortunately, TrackVia has the tools to make that happen.
Moving data without disruption using a Field-Level Lift & Shift
First: I create the new table and relationship field, but don’t add it to user-visible forms/views yet. This field will become the new field of record for that data and will appear on forms and views. However, if I swap this new field onto the forms and views right now, anyone who opens an older record will see it blank, when in actuality, the original data still exists in the older field. If I try to show both, users will be confused about which one should count. I can display the data procedurally by creating a calculated field that displays the old field only if the new field is empty, but that only solves half of the problem, as users aren’t able to perform manual input on a calculated field.
What I really want is for the data to simply transform, as if by magic, from a plain field in the table to a relationship with a record in a different table. But they’re two very different types of fields, architecturally.
So next, I’ll export the table to a CSV file. I’m going to use this export twice. First to fill the parent table (and therefore, create choices for the relationship field), and second to move the data. To handle the first part, I’ll simply go to the new table I created and import the list, creating one entry for each choice the users have ever provided on the form. Then, I’ll add any additional answers that may not have been used yet, but should still be on this list.
Once I have all previous answers stored in the new parent table, I can set that table’s Record ID to match exactly what was imported. This ensures that the exact same string from the previous answer perfectly corresponds to an entry on the new table.
With that finished, I’ll go back to the original table and re-import the table contents, just with a slight mapping change. That’s right, I’m taking a table export and importing it back directly on top of itself. The only difference is, I’ll use the import wizard to re-map the old field’s values so that they import into the new field. Because I’ve set up the parent table properly, this will result in each historical record now containing that data in both places.
Safe to switch
This is exactly where I want to be. I’m moving data from a standard field to a relationship field, except now, both fields contain the data, so it’s now completely safe to switch them on the forms and views that users see. I can even do a thorough review of the two fields to make sure nothing was missed before making the change to forms and views. By carefully moving the data to the second field before changing anything the users see, I can make this change in a live production environment without any downtime and the users will simply see the form change from one style of drop-down to a slightly different style of drop-down. Retraining is simple, communication with the users is simple, and the data is all where it should be. Once the change is made, I’ll do a quick spot-check in case a user happened to be on the form saving a record during my change, and if the app is very high volume, I might even write a small app script to eliminate this possibility.
Finally, I create a filtered view of the parent table, allowing for those choices to be made active or inactive, so that very old answers, which were converted into a relationship, are still visible on historical records, but aren’t available as choices for new selections.
And now, with that move complete, it is safe for me to fully delete the old field so that no future administrator will need to deal with this field’s mess. Furthermore, if this process ever needs to be reversed, it can be. An export of a relationship field can be inserted back into another field type just as easily. This is a fully reversible procedure, safe to use in production and with live data. It requires a bit of work from the admin side, but it keeps user stress down and most importantly, it keeps your process moving.
I find the Field-Level Lift and Shift more than worthy of a spot in my toolbox, and I hope you find the same.
If you’d like help in moving data into your apps or need assistance with creating one, TrackVia is here to help. Check out our TVU course that covers some of this topic or contact TrackVia Services to get started.
