Order Reporting
Automation System

Knowledge locked inside people, not systems.

Generating a single customer order report required staff to navigate across multiple related Dataverse tables by hand. Although foreign-key relationships existed in the system, users still had to open each record, trace the relationships, and copy data into Excel one field at a time.

The process was slow, inconsistent, and entirely dependent on experienced staff who understood how the underlying data connected. When those staff were unavailable, reporting stopped. The challenge was never generating the report — it was making complex enterprise data understandable without requiring users to understand the database structure themselves.

01Fragmented data

Related records spread across four Dataverse tables — no unified view.

02Manual FK resolution

Users mentally traced PK/FK links between the master sales table and three transactional sub-tables.

03Undocumented logic

Business rules — coalesce fallbacks, field mappings, country codes — lived only in experienced staff.

04Repetitive Excel work

Final report assembly done by hand, every single time.

As-Is → To-Be

The same report, before and after. What changed is not the number of steps but who performs them, and how much a person has to know before they can start.

Ten to one. The bar is the value, so a tenth of the length means a tenth of the time — a 93% reduction.

Abstracting database complexity from the user

Behind a single order number sat records spread across four Dataverse tables — an enterprise master sales table plus three transactional sub-tables for shipping, localisation and billing — connected by PK/FK relationships with multi-table coalesce fallback logic.

The resolution order checks the shipping sub-table first, falls back to billing, then to the master table — handling fields that exist only in specific sub-tables. Additional encoding covers shipping destination codes, foreign trade name truncation, and agency versus end-user name fallback.

How one report gets built

Six stages, one of which the user touches. Everything the experienced staff used to hold in their heads is encoded between stages two and four.

1. 01

   User input

   A single order number — the only thing the user ever touches.

2. 02

   Relationship resolution

   Power Automate queries Dataverse using the existing PK/FK structure to fetch all related records automatically.

3. 03

   Business rule application

   Coalesce logic, country code mapping, name truncation and agency/end-user fallback, all encoded in the flow.

4. 04

   Data aggregation

   Records from all four tables merged into a single unified structure.

5. 05

   Template instantiation

   The master template is copied into a new transaction-specific file, protecting the original.

6. 06

   Frozen report output

   Office Script runs formatting rules, locks the output, and delivers a standalone customer-ready report.

Template isolation as a design principle

Rather than writing data straight into a shared master template — which risks corruption or accidental modification — the solution copies the master for each run, injects data into the copy, runs the Office Script, and freezes the result as a standalone file.

Separating configuration from runtime data is a standard principle in maintainable enterprise systems. It ensures the master can never be corrupted by an individual report run, and every output is independently auditable.

1. 01

   Template.xlsx

   Master — never modified by a report run.

2. 02

   Template_[OrderID].xlsx

   A per-transaction instance, copied fresh each time.

3. 03

   Frozen_[OrderID].xlsx

   Standalone read-only output, independently auditable.