Your historical data can move with you: migrating TIC records into a new system

You do not need a perfect database before you can start improving your systems. If your customer, site or equipment records are in Excel, we can work with that.

The important question is whether we can understand the data, identify what needs correcting and preserve the connections that make it useful. In certification and inspection, that includes the relationship between a client, a site, a certificate or asset, and its history.

A new application should help people use that information. Starting again from an empty database is often neither necessary nor sensible.

Start with the records the team already uses

Before choosing an import tool, we look at the source data with the people who know it. Which file is current? What does each identifier mean? Is a repeated company name a duplicate, or does it represent a separate site?

We agree what belongs in the new system and what can remain in an accessible archive. We also keep an unchanged source copy, so every cleaning decision can be checked against the original.

Excel is useful at this stage because business users can review it. It gives us a shared working format for resolving questions before they become errors in the new application.

Clean the data through repeatable steps

Power Query is useful for transformations such as standardising dates, trimming spaces, combining files and splitting columns. Its recorded steps can be rerun when a revised source file arrives. Microsoft’s Power Query overview explains this repeatable preparation model.

AI can assist with the review. For example, a task we could give Copilot Cowork is to flag possible duplicate organisations, inconsistent names or unusual values in an approved working copy. We would test it on a sample and review its suggestions before changing the data.

A possible duplicate is a question to resolve. Similar company names may belong to different legal entities, and an unusual inspection date may be valid. The person who understands the business needs to make that decision.

Decide how records will connect

For a typical inspection migration, customers need to exist before their sites can be linked, and sites need to exist before their equipment can be attached. Historical inspections then need to point to the correct equipment.

We retain source identifiers and map them to the new records. This is more reliable than trying to connect everything using names alone, especially when one client has several locations or similar pieces of equipment.

We also define what a repeat run should do. A migration that is restarted after an error should recognise records already processed, rather than create another copy of each one.

Use an import process that fits the relationships

Standard Dynamics 365 and Dataverse import options can be suitable for straightforward data loads. For projects where we need to coordinate several related tables and apply custom logic, our approach is to use Power Automate flows.

Those flows can process the tables in the required order, check existing records, resolve relationships and record exceptions. This is a project choice, not a claim that flows are the fastest option for every data volume. Larger datasets may call for a different loading tool.

Before importing, we configure duplicate-detection rules and test how the chosen import route handles them. Publishing a rule is not enough to assume every automated write will be checked: enforcement depends on the operation used. Microsoft documents this distinction for the Dataverse Web API.

Run the logic before writing to the live system

One of the most useful parts of the process is a dry run. We build a flow that evaluates the intended migration and produces an Excel report without writing business records to the live system.

For each source record, the report should show the planned action, the target or proposed relationship, and any issue requiring review. That might be a new customer, an update to an existing site, a suspected duplicate or an asset with no matching site.

The team can review this output in a familiar format. We resolve the exceptions, rerun the checks and then rehearse the actual import in a test environment. A dry run reduces avoidable surprises; it does not replace testing the real writes.

Agree what a successful migration looks like

A completed import is not enough to establish that the data is ready for use. A flow can finish successfully while a record is linked to the wrong customer or an important historical field is missing. Acceptance needs to cover the business meaning of the result as well as the technical execution.

Before the final load, agree the checks with the people who will use the application. For example, take a customer with several sites, open its equipment records and follow a previous inspection through to its supporting information. This tests the relationships that matter in daily work.

Document any deliberate exclusions too. If obsolete records remain in an archive, a lower target record count may be correct. The team should be able to explain the difference, identify where the excluded information can be found and distinguish that decision from a failed import.

Keep a trace of what changed

Audit logging belongs in the setup, before the production import. In Dataverse, auditing needs to be enabled for the environment and the relevant tables and columns. Retention and log capacity also need attention. Microsoft’s auditing guidance describes these settings.

We also recommend custom fields for the source system, original record ID, migration batch and import date. For major corrections, record the reason and approval where needed. These fields make the migration easier to investigate; the audit history records changes to the configured data. One does not replace the other.

Finally, reconcile the result against the source: record counts, failed rows, customer-to-site links, asset relationships and a sample of historical records. Agree how changes made in the old system during the migration will be handled, and keep a recovery plan for the cutover.

The goal is for the team to recognise and trust its records when it opens the new application. That starts with understanding the data you already have.

If historical spreadsheets or an old database are holding back your next system, contact JP Digital. We can start by reviewing a representative sample.

Results to aim for and KPIs to track

A successful migration leaves the team with usable records, correct relationships and a clear account of what changed.

These are recommended acceptance measures for a migration project, rather than results claimed for a specific implementation.

Agree the thresholds before the production load and review exceptions explicitly. This gives the team a concrete basis for accepting the new system and identifying any remaining work.