Lattice Grid Buy a licence

how-to

How to Compare Two Extracts with Audit Mode

Last updated 29 September 2026

grid.diff turns two extracts into one reconciliation: give it a prior snapshot and load the current rows over the top, and every cell that changed carries its prior value, every row that vanished stays visible rather than silently gone, and summary() gives you the counts without a second pass over the data yourself.

Below, a live grid in audit mode against a small dataset of readings shows the pattern: change a value, and the previous one appears on hover. This how-to's own reconcile application runs the same pattern at full scale, over two dropped supplier-invoice files.

Building…
Loading a live grid…

The code

Two calls do the comparison. grid.diff.setSnapshot(records) holds the "before" side; it is never applied as the grid's own rows. Loading the "after" side as the live rows, grid.rows.load() or grid.import.apply() for a CSV, is what triggers the comparison: from then on grid.diff.statusOf(key, row) answers added, removed, changed or unchanged for any row, grid.diff.before(key, field) answers a cell's prior value, and grid.diff.summary() gives you the counts across the whole set. swap() trades which side is "before"; removedRows: 'pinned' keeps a deleted row visible, struck through, instead of letting it disappear.

The reconcile application below is two files: index.html loads the grid and the layout module and declares the mount points, app.js configures the grid, wires the drop zones and the summary tiles, and does the comparison. This is the reconcile showcase's actual, complete source, copied here in full rather than trimmed to a snippet.

index.html

<!doctype html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <title>Reconcile Two Extracts with Lattice Grid's Audit Mode | Lattice Grid</title>
        <link rel="icon" href="data:,">
        <script>
            // `?theme=dark` puts the whole page, grid included, under data-theme="dark".
            if (new URLSearchParams(location.search).get('theme') === 'dark') document.documentElement.dataset.theme = 'dark';
        </script>
        <script>
            // Where the library comes from: the published release from the CDN, or with
            // `?local` a local build from vendor/ (not part of this repository).
            const LOCAL = new URLSearchParams(location.search).has('local');
            // Bumped on every deploy: GitHub Pages caches for 10 minutes, so every
            // same-origin file this page loads carries the stamp to force a fresh fetch.
            window.STAMP = '20260929a';
            const LIB = LOCAL ? 'vendor/' : 'https://cdn.jsdelivr.net/npm/@toclocoinc/lattice-grid@1.76.0/';
            document.write('<link rel="stylesheet" href="' + LIB + 'lattice-grid.min.css">');
            for (const file of ['lattice-grid.min.js', 'modules/layout.min.js']) {
                document.write('<script src="' + LIB + file + '"><' + '/script>');
            }
            document.write('<script src="app.js?v=' + window.STAMP + '" defer><' + '/script>');
        </script>
    </head>
    <body>
        <div id="container" style="height: 1500px"></div>
        <template id="load-panel">
            <div class="drop-row">
                <div class="drop" id="drop-before" tabindex="0" role="button">
                    <b>Before (last month)</b>drop a .csv/.tsv, or click to choose<div id="before-status"></div>
                </div>
                <div class="drop" id="drop-after" tabindex="0" role="button">
                    <b>After (this month)</b>drop a .csv/.tsv, or click to choose<div id="after-status"></div>
                </div>
                <div class="actions">
                    <button type="button" id="swap-btn">Swap sides</button>
                    <span id="swap-label">Comparing After against Before</span>
                    <button type="button" id="exceptions-btn">Only exceptions</button>
                    <button type="button" id="export-btn">Export exceptions (CSV)</button>
                </div>
            </div>
            <input type="file" id="file-before" accept=".csv,.tsv" hidden>
            <input type="file" id="file-after" accept=".csv,.tsv" hidden>
        </template>
        <template id="footer-panel">
            Synthetic seeded data, generated by <code>tools/gen-data.mjs</code> (recipe in the README) &mdash;
            nothing here is a real supplier or invoice, and nothing you drop leaves this browser tab.
            &middot; Lattice Grid <span id="version"></span>
        </template>
        <script>
            // Bound to toclocoinc.github.io; does nothing anywhere else. localhost never needs a key.
            LatticeGrid.setLicence('LG1.eyJ2IjoxLCJwIjoibGF0dGljZS1ncmlkIiwidCI6IlRPQ0xPQ08gSW5jIC0gcHVibGljIGRlbW9zIiwiZSI6IjIwMzAtMDEtMDEiLCJkIjpbInRvY2xvY29pbmMuZ2l0aHViLmlvIl19.9De42ua3aCGpiMB6EVRP7Tv-upUlDI-0T07rlSPzvCrsqg8t4YJi7SRnStEpAg48uzmcG7il1fR_TfwkUE7iCA');
        </script>
    </body>
</html>

app.js

// Reconcile two extracts: grid.diff (audit mode) does the comparison - this
// file wires two drop zones, a status column kept in step with it, five KPI
// tiles, a swap and an export. See README.md for the recipe and F-RECON-1
// (why "status" is a stamped field, not a computed column: a computed column
// reading grid.diff would itself be diffed against the fieldless snapshot).
const $ = (sel) => document.querySelector(sel);
const panel = (id, heading, html) => {
    const el = $('#' + id + '-body');
    el.className = 'panel';
    el.innerHTML = '<div class="panel__head">' + heading + '</div><div class="panel__body">' + (html || '') + '</div>';
    return el.lastChild;
};
LatticeGridLayout.createLayout($('#container'), {
    columns: 24, rows: 20, gap: 6, padding: 6, overflowX: 'static', overflowY: 'static',
    windows: [
        { id: 'load', title: 'Load two extracts', xPos: 1, yPos: 1, xSize: 24, ySize: 3, chrome: false },
        { id: 'table', title: 'Reconciliation', xPos: 1, yPos: 4, xSize: 17, ySize: 16, chrome: false },
        { id: 'kpis', title: 'Summary', xPos: 18, yPos: 4, xSize: 7, ySize: 16, chrome: false },
        { id: 'footer', title: 'Credits', xPos: 1, yPos: 20, xSize: 24, ySize: 1, chrome: false },
    ],
});
panel('load', 'Load two extracts &middot; ships with a seeded pair, or drop your own', $('#load-panel').innerHTML);
$('#footer-body').innerHTML = $('#footer-panel').innerHTML;
const money = { style: 'currency', currency: 'GBP' };
const grid = LatticeGrid.createGrid(panel('table', 'Reconciliation &middot; drop-shadow shows the prior value', ''), {
    rowKey: 'invoiceId', rows: [], filterRow: true, selection: 'multiple', diff: { removedRows: 'pinned' },
    columns: [
        { field: 'invoiceId', title: 'Invoice', layout: { width: 110 } },
        { field: 'supplier', title: 'Supplier', layout: { flex: 2, min: 160 } },
        { field: 'amount', title: 'Amount', type: 'number', format: money, total: 'sum' },
        { field: 'vatRate', title: 'VAT rate', type: 'number', format: { style: 'percent' } },
        { field: 'invoiceDate', title: 'Invoice date', type: 'date' },
        { field: 'status', title: 'Status', layout: { width: 110 }, filter: { type: 'set' },
            cell: { decoration: 'pill', variant: { map: { added: 'success', removed: 'danger', changed: 'warning', unchanged: 'neutral' } } } },
        { field: 'supplierBefore', title: 'Supplier (before)', layout: { hidden: true } },
        { field: 'amountBefore', title: 'Amount (before)', type: 'number', format: money, layout: { hidden: true } },
        { field: 'vatRateBefore', title: 'VAT rate (before)', type: 'number', format: { style: 'percent' }, layout: { hidden: true } },
    ],
});
$('#version').textContent = grid.getVersion();
// Re-stamp status/before fields from grid.diff onto every live row, then
// reload - called after loading either side and after a swap.
function sync() {
    const rows = grid.rows.data().map((r) => ({
        ...r,
        status: grid.diff.enabled ? grid.diff.statusOf(r.invoiceId, r) : 'unchanged',
        supplierBefore: grid.diff.enabled ? grid.diff.before(r.invoiceId, 'supplier') : undefined,
        amountBefore: grid.diff.enabled ? grid.diff.before(r.invoiceId, 'amount') : undefined,
        vatRateBefore: grid.diff.enabled ? grid.diff.before(r.invoiceId, 'vatRate') : undefined,
    }));
    grid.rows.load(rows);
    refreshStats();
}
function netAmountDelta() {
    let net = 0;
    grid.rows.forEachAll((row) => {
        if (!grid.diff.enabled || grid.diff.statusOf(row.key, row) !== 'changed') return;
        const before = grid.diff.before(row.key, 'amount');
        if (typeof before === 'number') net += row.data.amount - before;
    });
    return Math.round(net * 100) / 100;
}
const kpis = panel('kpis', 'Summary &middot; summary() + the engine', '<div class="tiles"></div>');
const tile = (id, title) => {
    const el = document.createElement('div');
    el.className = 'tile';
    el.innerHTML = '<div class="tile-title">' + title + '</div><div class="tile-value" id="tile-' + id + '"></div>';
    kpis.firstChild.appendChild(el);
    return el.lastChild;
};
const stats = ['added', 'removed', 'changed', 'unchanged'].map((s) => LatticeGrid.createStat({
    grid, container: tile(s, s[0].toUpperCase() + s.slice(1)), value: (g) => g.diff.summary()[s], scope: 'all', live: false,
}));
stats.push(LatticeGrid.createStat({
    grid, container: tile('net', 'Net amount difference (changed rows)'), value: netAmountDelta, format: money, scope: 'all', live: false,
}));
const refreshStats = () => stats.forEach((s) => s.refresh());
// Before -> the diff snapshot, never applied as live rows. After -> the
// grid's own rows, replaced wholesale - both through import.preview.
async function loadBefore(text) {
    const preview = grid.import.preview(text);
    grid.diff.setSnapshot(preview.records);
    $('#before-status').textContent = preview.rowCount + ' rows loaded';
    sync();
}
async function loadAfter(text) {
    const preview = grid.import.preview(text);
    grid.import.apply(preview, { mode: 'replace' });
    $('#after-status').textContent = preview.rowCount + ' rows loaded';
    sync();
}
function wireDrop(zoneId, fileInputId, handler) {
    const zone = $('#' + zoneId), input = $('#' + fileInputId);
    zone.addEventListener('click', () => input.click());
    zone.addEventListener('keydown', (e) => { if (e.key === 'Enter' || e.key === ' ') input.click(); });
    ['dragover', 'dragleave', 'drop'].forEach((evt) => zone.addEventListener(evt, (e) => {
        e.preventDefault();
        zone.classList.toggle('over', evt === 'dragover');
        if (evt === 'drop' && e.dataTransfer.files[0]) e.dataTransfer.files[0].text().then(handler);
    }));
    input.addEventListener('change', () => { if (input.files[0]) input.files[0].text().then(handler); });
}
wireDrop('drop-before', 'file-before', loadBefore);
wireDrop('drop-after', 'file-after', loadAfter);
// Strip the stamped fields before swap, or they ride into the new snapshot
// and read back as a spurious change on every row once the sides trade places.
$('#swap-btn').addEventListener('click', () => {
    grid.rows.load(grid.rows.data().map(({ status, supplierBefore, amountBefore, vatRateBefore, ...rest }) => rest));
    if (!grid.diff.swap()) return;
    $('#swap-label').textContent = grid.diff.swapped
        ? 'Comparing Before against After (swapped)' : 'Comparing After against Before';
    sync();
});
let exceptionsOnly = false;
$('#exceptions-btn').addEventListener('click', () => {
    exceptionsOnly = !exceptionsOnly;
    grid.filters.set(exceptionsOnly ? { col: 'status', op: 'ne', value: 'unchanged', type: 'text' } : null);
    $('#exceptions-btn').textContent = exceptionsOnly ? 'Show all rows' : 'Only exceptions';
});
// Export always covers every exception, independent of the current filter -
// briefly including removed rows so they can be selected; processCell fills
// their status/before columns, since a removed row's before and current are
// the same snapshot record.
$('#export-btn').addEventListener('click', () => {
    const wasMode = grid.config().diff.removedRows;
    grid.set('diff', { ...grid.config().diff, removedRows: 'data' });
    const keys = grid.diff.removedKeys();
    grid.rows.forEachAll((row) => { if (row.data.status !== 'unchanged') keys.push(row.key); });
    grid.selection.set(keys);
    grid.export.csv({
        rows: 'selected', hidden: true, download: true, fileName: 'reconcile-exceptions.csv',
        columns: ['status', 'invoiceId', 'supplier', 'supplierBefore', 'amount', 'amountBefore', 'vatRate', 'vatRateBefore', 'invoiceDate'],
        processCell: (p) => {
            if (!p.row.removed) return p.text;
            const raw = { status: 'removed', supplierBefore: p.row.data.supplier, amountBefore: p.row.data.amount, vatRateBefore: p.row.data.vatRate }[p.colId];
            return raw === undefined ? p.text : String(p.column.formatValue ? p.column.formatValue(raw, p.row, p.row.data) : raw);
        },
    });
    grid.set('diff', { ...grid.config().diff, removedRows: wasMode });
    grid.selection.clear();
});
// The seeded pair, so the page works with no upload.
Promise.all(['data/invoices-after.csv', 'data/invoices-before.csv'].map((f) => fetch(f + '?v=' + window.STAMP).then((r) => r.text())))
    .then(([after, before]) => loadAfter(after).then(() => loadBefore(before)));
window.__demo = { grid, loadBefore, loadAfter, sync, netAmountDelta };

Try the live application, or read the full source on GitHub. See it described as a showcase entry too: Reconcile two extracts.

Computed columns and diff

The status column above is written as a plain stored field, re-stamped from grid.diff after every load, rather than as a computed column that reads grid.diff itself. That is deliberate: by default, audit mode compares stored fields only, so a computed column, one with no field of its own, never contributes to a row's status, summary(), changedColumns() or before(). A column can opt back in explicitly with diff: true to have its computed value compared (a real row shape is synthesised for the snapshot side), or opt out on a stored field with diff: false to leave it out of the comparison entirely. Set the flag on the column definition itself: 

{ field: "total", compute: (row) => row.qty * row.price, diff: true }

Before this, reading grid.diff from inside a computed column's own compute corrupted every row's status, because the column was itself diffed against a snapshot that had no column to compute from. The stamped-field pattern above works regardless and stays the simplest choice when a "before" value needs formatting or combining before it lands in the grid; diff: true is the shorter route when the computed value itself is what should be compared.

See the audit mode and diff view guide for the rest of the surface, and bringing your own file into the grid with DuckDB for the local-file pattern the same showcase set also demonstrates, with nothing you drop ever uploaded.