Dataverse normalization · migration plan
A single Dataverse table, pathix_assetregister, currently stores four distinct entities discriminated by a string field, held together by six plugins that hand-roll the relational machinery the platform provides natively. This plan splits it into proper related tables and retires most of that code.
The asset register is not one table needing a few lookups — it is a mega-table. The discriminator column pathix_recordtype takes at least four values, each a separate business entity: Location, AssetType, Asset, and SubAsset. Records are linked not by relationships but by three string columns matched at runtime via FetchXML: pathix_locationcode, pathix_typename, and pathix_parentassettag.
Six custom plugins compensate for the missing structure. Five of them exist only because the data is denormalized — give the entities real tables and lookups, and those plugins have nothing left to do. The recommended end state is four core tables plus the already-existing (but denormalized) maintenance log, with native lookups, rollups, choice columns, and cascade behaviour replacing custom code.
Behavioral analysis (High confidence) shows that five of the six plugins silently swallow all exceptions via empty catch blocks; only MegaTableValidator throws. That means existing rollups, copied attributes, generated tags, cascaded statuses, and stamped maintenance dates may already be wrong in production wherever a plugin failed unnoticed. Do not trust-migrate these values — recompute them during the move.
The register carries ~25 business columns (plus the usual system fields). What look like attributes of one record are really attributes of four different things flattened together:
| recordtype | Really is | Evidence |
|---|---|---|
| 'Location' | A physical location | StatusCascade pushes a Location's status to its assets; MegaTableValidator validates assets against existing Location rows |
| 'AssetType' | An asset type / model | MegaTableValidator requires manufacturer; TypeAttributeCopyDown reads type attributes off it |
| 'Asset' | A top-level asset | Tag generation, validation, rollup parent |
| 'SubAsset' | A child asset | SubAssetRollup aggregates them onto the parent |
The pathix_maintenancelog table already exists as a separate child table — but it is itself denormalized, carrying four "snapshot copy" columns and joining to assets by the pathix_assettag string rather than a real lookup.
Explode the mega-table into the four latent entities, repair the existing maintenance log, and let the platform enforce the relationships. Asset and SubAsset collapse into one table — the difference is simply whether parentassetid is set, so pathix_recordtype and pathix_issubasset both disappear.
Optional: a Manufacturer table if you want manufacturer normalized out of AssetType rather than left as a text field.
Where each business column on pathix_assetregister ends up:
| Column | Disposition | Notes |
|---|---|---|
| assettag, name, serialnumber, installdate, currentvalue, componentposition | stay | per-instance attributes of the Asset |
| site, building, floor, locationcode, parentlocationcode | → Location | parentlocationcode becomes a self-lookup |
| typename, manufacturer, model, expectedlifeyears, maintenanceintervaldays | → AssetType | Asset gets a lookup instead |
| parentassettag | → lookup | becomes parentassetid self-reference |
| status, criticality, recordtype-driven flags | → choice | status partly duplicates system statecode/statuscode |
| childcount, totalvalue, lastmaintenancedate | rollup | recompute on migrate; do not trust current values |
| locationpath, issubasset | drop / derive | path derives from hierarchy; issubasset = parent is set |
| recordtype | drop | becomes table identity once entities are split |
| Plugin | What it really does | Fate | Replaced by |
|---|---|---|---|
| MegaTableValidator Update |
Referential integrity (locationcode must exist) + required field (manufacturer on AssetType) | eliminate | Native lookup FK + required column. The only plugin that throws rather than swallowing. |
| TypeAttributeCopyDown Create |
Copies manufacturer / model / expectedlife / interval from AssetType onto each Asset | eliminate | AssetType lookup — attributes read through the relationship, never copied |
| SubAssetRollup Create |
Counts & sums child values onto the parent (create-only → goes stale) | eliminate | Native rollup columns over the parentassetid relationship |
| MaintenanceLogStamp Create |
Copies asset snapshot onto each log row; back-stamps lastmaintenancedate | eliminate | Asset lookup on the log + max() rollup for lastmaintenancedate; drop the 4 copy columns |
| StatusCascade Update |
Propagates a Location's status to its Assets & SubAssets | rewire | Repoint to the Location→Asset relationship. Field-value propagation isn't a standard cascade type, so this likely stays as (cleaner) logic on the real lookup |
| AssetCodeGenerator Create |
Generates assettag ({loc}-{type}-{seq:D3}) and sets criticality by value (≥100k Critical, ≥25k High, else Standard) | rewire | Keep as genuine logic; repoint its reads at the Location / AssetType lookups. Criticality could move to a formula column |
Net: four plugins eliminated, two rewired. The eliminated four are also the fragile ones — all four silently swallow exceptions.
Create Location, AssetType, and Asset. Add lookups (parentlocation, assettype, location, parentasset, log→asset), alternate keys on locationcode, typename, assettag, choice columns for status/criticality, and the rollups. Repair MaintenanceLog with the asset lookup.
Split existing rows by recordtype into the new tables. Resolve the string keys (locationcode, typename, parentassettag, log assettag) into real lookup references using the alternate keys.
Because failed plugins were swallowed silently, treat childcount, totalvalue, lastmaintenancedate, and the copied AssetType attributes as untrusted. Let the new rollups and lookups recompute them rather than carrying the old values over.
Delete the four eliminated plugins; rewire AssetCodeGenerator and StatusCascade to read through the new relationships. Verify native rollups and the FK/required-field constraints cover what SubAssetRollup and MegaTableValidator used to do.
Forms, views, charts and dashboards are recreated per entity (they were excluded from the logic footprint by design). Validate, then decommission the mega-table.
The behavioral narrations here are High confidence but were generated by automated analysis of decompiled plugin code (2026-06-04). Confirm against the actual assembly source before retiring plugins.
Validate the discriminator. Four recordtype values are confirmed in code paths; query production for any additional values before assuming the entity set is complete.
Silent failures = dirty data. The empty catch blocks mean some records likely already hold stale rollups, missing tags, or un-copied attributes. Plan a reconciliation/cleansing pass — the migration is a good moment to surface and fix these.
Alternate keys are load-bearing. assettag, locationcode, and typename drive both the data-migration key resolution and any external integrations; keep them as alternate keys even after introducing surrogate PKs.