Rows, columns and data sources

API reference › Rows, columns and data sources

grid.rows

Data usually arrives after the grid does. Build it with rows: [], then load when your fetch resolves, the sort, filters, grouping and column layout you set up in the meantime all survive, and apply to the new data.

const grid = createGrid(el, { columns, rowKey: 'id', rows: [] });
grid.overlay.show('loading');

const data = await fetch('/api/circuits').then(r => r.json());
grid.rows.load(data);          // replaces whatever was there
grid.overlay.hide();

grid.columns

Sources

Where rows come from. memory is the default and needs no configuration.

Derived sources: a grid built from another grid

Most dashboards put a summary panel beside a table, the top five sales people, the breakdown by region, the exceptions list. Built by hand, that panel runs its own query, and sooner or later somebody filters the table and the panel does not follow. Everyone who has shipped a dashboard has been in the meeting where two numbers on one screen disagree.

A derived grid removes the possibility. It is a second grid whose rows are built from the first (grouped, unnested, filtered, ranked or profiled) so the panel is the table, one derivation later, and one filter moves both. It answers the questions a summary panel exists for: the top five sales people, the most-sold SKUs, a statistical profile of whatever the user has filtered to.

It is a source rather than a new kind of grid, so everything downstream: its own sorting and filters, totals, shadow columns, formatting, export, themes: works on the result and knows nothing about where the rows came from. Charts bind to one as readily as to any grid.

source: {
  mode: 'derived',
  from: salesGrid,
  follow: 'filtered',

  groupBy: 'rep',
  select: { revenue: { of: 'amount', fn: 'sum' }, deals: { fn: 'count' } },
  sort: [{ col: 'revenue', dir: 'desc' }],
  limit: 5,
}

The pipeline runs in one order, and the order is the contract: unnest → where → bucket → group → reduce → sort → limit. where sits before grouping deliberately: filtering afterwards is a different question, which groups, not which rows, and one key cannot mean both.

Read-only. A derived row is an answer, not a record: there is no write-back for the sum of four hundred rows, so writes are refused with a reason rather than accepted and discarded on the next refresh.

They chain. A derived grid can be the source of another to any depth: a profile of the top five, and a change at the root travels the whole chain. A cycle is refused rather than recursed.

The key. rowKey defaults to the derived key and need not be set. It is the group value, which is what makes a live ranking readable: the row moves rather than the values under it changing.

Cost. The first derivation is linear in the rows read and largely independent of what is reduced: roughly 900 ms per 200,000 rows grouped into forty, whether the selection is one sum or four statistics. After that, a change that names the rows it touched is patched rather than re-derived: only the groups those rows entered or left are reduced again, so a live feed costs time proportional to what changed rather than to the table. Five hundred updates against that same source take under 300 ms in total, not 300 ms each. A joined derivation is maintained the same way from both sides: the lookup is held between derivations rather than rebuilt, a change to the fact table rejoins only the rows that moved, and a change to the lookup rejoins only the rows behind the keys whose match actually changed, about 2 ms per fact update and 1.5 ms per lookup edit against a 200,000-row source joined to 2,000 customers. A change that cannot be reasoned about that way, a new filter, a regrouping, a derivation using unnest or where, or a lookup row arriving for rows an inner join had dropped, falls back to a full derivation, which is correct but costs the full linear pass. Narrow with follow: 'filtered' so the derivation reads what the user is looking at rather than the whole table.

What a change firing promises

Anything maintaining state from rows:changed (a derived grid, a chart, your own cache) needs to know whether a firing names the rows that moved or merely says that something did. One rows.apply announces itself more than once: the source reports how many rows moved, the row model reports which ones, and the grid reports that a change happened. Acting on all three does the work three times over.

The default is the safe one. A firing that carries neither flag is treated as a change of unknown extent, so a listener re-reads rather than assuming nothing moved. Read the flags rather than the shape of the payload: a firing that reports a row move carries counts, because no row changed value, and only the flags distinguish that case from a duplicate announcement.

Confidence intervals: how much to trust the figure

Every other statistic here describes the data you have. An interval describes how well that data pins down the figure you actually care about, and it is the one thing a descriptive tool can honestly say about the world beyond its rows.

The line this draws. Lattice quantifies uncertainty; it does not adjudicate hypotheses. There are no p-values, no significance tests and no verdicts, and reading two non-overlapping intervals as a significance test is a mistake often enough to be worth not encouraging. An interval says "the mean is 42, and the sample pins that down to between 39 and 45". It does not say 42 differs from 40.

Every interval carries its level. The result's confidence field says what it was computed at, so a figure copied out of one cannot lose the thing that makes it readable. An interval without its level is not a smaller claim, it is an unreadable one.

A proportion is Wilson, not Wald. The textbook p ± z√(p(1−p)/n) fails exactly where a rate is most interesting: near zero it reaches below zero, and at no observed successes it collapses to the single point zero: claiming perfect certainty from the least informative sample there is. The Wilson interval stays inside 0 to 1 and stays sensible at the extremes, so "none of forty failed" correctly reads as "the failure rate is under 9%" rather than "the failure rate is zero".

Report the capability interval. Its absence is the commonest way a capability study overstates itself. A Cpk of 1.35 measured on thirty parts has a lower bound below 1.0, so a process that has "passed" a 1.33 requirement on thirty parts has demonstrated very little. The point estimate alone does not say that; the interval does.

It follows the filters. Like every statistic here, an interval reads the rows the filters left, so it narrows as the user narrows the grid. That is the correct behaviour and worth knowing: it describes the filtered population, not the whole table.

Reading an SPC chart

The figures are only half of it. A capability claim is made in a picture, and these are the two the discipline expects.

The lines are named, on opposite edges. CL, UCL and LCL at the right; LSL, USL and Target at the left. Control limits are what the process does; specification limits are what the customer asked for, and reading one as the other is the classic misreading of a control chart. A capable process puts its control limits just inside its tolerance, so the two families sit close together, which is exactly when they need telling apart, and why they are named at opposite ends rather than left to collide.

Rule breaks carry their number. With Western Electric's four rules a marked point was readable on its own; with Nelson's eight it is not. A spike (rule 1) is a bad part; a six-point trend (rule 3) is tool wear or a drifting sensor. They call for different responses, and a chart that marks both the same way has told you the less useful half of what it knows. Set the rule set with rules: 'nelson' on the chart, as on statistics.capability.

The capability report draws two curves, not one. Cp and Cpk are computed from short-term variation, Pp and Ppk from overall. When a process has drifted the two differ, and the four indices say so only as numbers a reader has to know how to compare. Drawn, the gap is the finding: a narrow solid curve inside a wide dashed one is a capable process that has been allowed to wander, a scheduling problem, not a machine problem.

What this covers, and what it does not. These are individuals charts: one reading per point, with short-term variation estimated from the moving range. That is the right instrument when readings arrive one at a time, a sensor, a test rig, a single-piece flow.

It is not the right instrument for subgrouped data, and the difference is not cosmetic. If you measure five parts an hour, the correct chart is X̄-R: its limits come from within-subgroup variation and are roughly √n tighter, which is what makes a shift in the process centre visible. Run an individuals chart over the same readings and the limits are computed from differences that mix within- and between-subgroup variation; they come out around twice as wide, and a two-sigma shift that X̄-R flags a dozen times over reads as scattered noise. Lattice does not ship X̄-R, X̄-S, or the attribute charts (p, np, c, u), and an individuals chart should not be substituted for them.

Pair the two control charts. An individuals chart asks whether the process has moved; a movingRange chart asks whether it has become less repeatable. A process can fail either without failing the other: it can drift while its point-to-point variation holds steady, and it can hold its average while shaking itself apart. The second is close to invisible on the individuals chart alone, because a wider spread pulls that chart's own limits wider with it: it rescales to accommodate the very thing that has gone wrong. Drawn one above the other, they are the standard I-MR pair.

Bind it to the readings, not to a summary of them. A whisker is computed from the values the chart can see behind each mark. Bound to a grid whose rows are already one per mark, a summary or a derived panel, the chart sees a single value per category and there is no spread to draw: the readings that produced each average are upstream and no longer reachable. Bind the chart to the rows the summary was computed from, or carry a margin yourself and use error: { of: 'margin' }. A chart asked for whiskers it cannot compute says so once rather than drawing nothing in silence.

Uncertainty on a chart. error: true draws a whisker on each mark, computed from the readings behind it. Four bars side by side invite a comparison the numbers alone cannot support, a five per cent gap between two categories of eight readings is noise, and between two of eight hundred it is the finding. The whisker is what tells them apart, and its absence is why bar charts are so often over-read. A mark with a single reading gets none, because one reading has no spread and a zero-height whisker would claim certainty rather than admit ignorance.

Fitted lines. fit: true draws a least-squares line through a scatter with its R² beside it; fit: 'line' draws the line alone. A cloud of points invites a reader to draw the line themselves, and people are consistently poor at it, the eye is pulled by the extremes, which is exactly what least squares is not.

The interval travels with the index. The statistics panel shows the bounds under Cpk and Ppk, and createStat takes an interval function that puts them under the value. A tile is where a figure is read fastest and questioned least, which makes it the place an interval earns its keep rather than the place it is least needed.

Pushdown adapters: one query, many engines

A remote source already receives a structured request: range, sort, filters, quick text, grouping, pivoting and totals. A pushdown adapter turns that request into whatever an engine speaks, so connecting a new back end is a translation layer rather than a new source.

import { createPushdownSource, odataAdapter } from '@toclocoinc/lattice-grid';

const source = createPushdownSource({
  adapter: odataAdapter({ url: 'https://api.example.com/Orders' }),
  compute,
});

createGrid(host, { source, columns: [...] });

An adapter never carries an engine. Each one takes what it needs as a parameter: restAdapter takes a fetch and bundles no HTTP library, dfqlAdapter takes a token, and duckdbAdapter takes a connection you have already made. So a grid can drive a full analytical engine without this package carrying one, and installing Lattice never installs anything else.

An adapter declares what it can answer. No engine speaks the whole query. OData takes a condition tree but only some operators; a single-term API takes one field and one value; a hand-written endpoint may take nothing but a page number. The adapter states its capabilities, the SDK divides the request accordingly, and the grid finishes whatever is left.

Anything left over means the whole result is fetched. Filtering a window of rows in the browser is not a slower way to get the right answer, it is a fast way to get a wrong one: the rows that belong on the first page may be on the ninth, and the count is whatever the engine happened to return. So when the grid has work left to do it asks the engine for the complete result, applies the remainder, and pages from what it holds. It says so once, naming the part that could not be pushed, because the fix is usually a wider adapter rather than a bigger machine. source.lastPlan() reports the division for any request.

A conjunction splits; a disjunction does not. An and group narrows with each condition, so the engine can apply the conditions it understands and the grid narrows what comes back. An or group widens with each branch, so pushing only the supported branches returns fewer rows than the filter allows, and the grid cannot recover rows that were never fetched. A disjunction the engine cannot fully answer therefore stays with the grid whole. The same asymmetry governs faceting.

A sort is pushed whole or not at all. Ordering by the first column and fixing the rest in the browser needs every row anyway, so a partial sort buys nothing and returns rows in an order that is wrong until the grid corrects it.

Wiring it to the API you already have

Most data sits behind a service someone on your team wrote. The adapter below sends four parameters and expects { rows, total } back. Start by declaring only what the endpoint genuinely does, and widen it as you teach the endpoint more.

const source = createPushdownSource({
  compute,
  adapter: restAdapter({
    url: '/api/orders',
    // Only the comparisons the endpoint really applies. Claiming more here
    // returns the wrong rows rather than merely running slowly.
    operators: ['eq', 'gt', 'lt', 'contains'],
    params: { offset: 'skip', limit: 'take' },
  }),
});

The request that reaches your service, and the answer it owes:

// Express. FastAPI and ASP.NET differ only in how the query string is read.
app.get('/api/orders', async (req, res) => {
  const { skip = 0, take = 100, sort, order, filter } = req.query;

  let q = db('orders');
  if (filter) q = applyConditions(q, JSON.parse(filter));   // your translation
  if (sort) {
    sort.split(',').forEach((col, i) => {
      q = q.orderBy(col, (order || '').split(',')[i] === 'desc' ? 'desc' : 'asc');
    });
  }

  // The count is of everything matching, not of the page. A grid scrollbar is
  // sized from it, so a page-sized total makes the grid look empty below.
  const [{ count }] = await q.clone().clearOrder().count({ count: '*' });
  const rows = await q.offset(Number(skip)).limit(Number(take));

  res.json({ rows, total: Number(count) });
});

The total is the commonest mistake. It is the number of rows matching the filter, not the number returned in this page. The grid sizes its scrollbar from it and requests windows against it, so returning the page length makes a large result look like one page.

Building an adapter from the parts

createPushdownSource is the whole story for most callers. When an engine needs a source of its own, the four pieces it is assembled from are exported separately, so a custom source can plan and finish work the same way rather than reimplementing the split.

Residual work needs the complete result. applyResidual expects every matching row, not a window. Filtering a window is not a slower route to the right answer, it is a fast route to a wrong one: the rows that belong on page one may sit on page nine. planQuery sets needsAll whenever that applies, and createPushdownSource switches to fetching everything and paging from what it holds.

Joining two grids

Two grids each holding their own data, and a third showing where they meet. Orders against customers; shipments against carriers; enrolments against students. The third grid derives from one side and names the other as its join partner.

const joined = createGrid(host, {
  source: {
    mode: 'derived',
    from: orders,
    join: {
      with: customers,
      on: { left: 'customerId', right: 'id' },
      select: ['name', 'tier'],
    },
  },
  columns: [{ field: 'ref' }, { field: 'name' }, { field: 'tier' }, { field: 'amount' }],
});

The row count does not change. A key appearing twice on the right keeps the first match rather than emitting a row per pair. SQL would multiply them out; here that would change the row count of a grid the reader thinks of as “the orders” and quietly double every total taken from it.

Both sides are live. The partner is read at derivation time, not captured when the grid was built, and editing it re-derives, a corrected tier in the customer grid moves the order into a different band in the joined one.

Cross-filtering: the path back up

Derivation runs one way. A derived grid reads its source and never writes to it, which is what makes a chain of them safe to reason about. Cross-filtering is the single deliberate path back up: clicking a row in a summary panel filters the grid it summarises.

const byRep = createGrid(panel, {
  source: {
    mode: 'derived', from: main, groupBy: 'rep', refresh: 'live',
    crossFilter: true,
    select: { total: { of: 'amount', fn: 'sum' } },
  },
  columns: [{ field: 'rep' }, { field: 'total' }],
});

byRep.on('row:click', (e) => byRep.crossFilter.toggle(e.key));

A panel does not filter itself. The grid pushing the filter leaves its own condition out when it reads the source back. Without that, clicking one rep would collapse the panel to that single row and strand the reader with nothing else to click. It is the same rule that keeps a header histogram showing every bar after you click one (facets), applied between grids instead of within one.

Several panels compose. Each leaves out only its own condition, so two panels over different columns narrow each other while both stay whole: pick a rep and the region panel shows that rep's regions, pick a region and the rep panel shows that region's reps.

It needs a memory source. Leaving a panel's own condition out means asking the source for every row that survives the other filters, which a source holding one page cannot answer. Over a remote, paged or stream source the read falls back to the ordinary filtered rows (narrowed by the very column the panel asked to be excluded from) and the panel collapses to the row that was clicked. It warns when it does. Exclude the originating panel on the server instead.

It is an ordinary filter. The condition goes through the source's filters.set, so it undoes, rides in a saved view, and appears in whatever filter UI the grid already has. There is no second filter model beside the real one.

The remote request

Your fetch receives one object and returns { rows, total }.

grid.updates

Control over an incoming feed: hold it, let it through, and see what the batching is actually saving you. Pausing does not drop anything: held changes keep merging, so a long pause costs one entry per changed row rather than one per update.

grid.updates.pause();                // hold the feed; it keeps arriving and merging
grid.updates.stats();                // { pending, queued, coalesced, flushes, ... }
grid.updates.flush();                // apply what is waiting, stay paused
grid.updates.resume();               // apply everything and go live again

coalesced is the number a batching strategy is actually bought with: rows that arrived more than once in a window and were written once. A feed where it stays at zero is not being coalesced, whatever the interval says.

The log is bounded two ways, because an entry is not a fixed size, one carrying a single changed cell and one carrying a fifty-thousand-row batch both count as one.

Applying changes

rows.apply(change) applies immediately and returns what happened; rows.queue(change) batches into the next flush and returns a promise. Both take the same shape: add, update, remove, and an optional at insert position.

An update is a patch, not a replacement. Fields absent from the update are untouched, so a delta from a websocket or a save response can be applied as it arrives without reading the row back first. Coalescing merges fields too: {price} and {volume} arriving as separate messages inside one window both survive.

grid.rows.apply({ update: [{ id: 'R1', price: 42 }] });
// every other field on R1 is left alone

Rows that cannot be applied are reported, not thrown. A batch of a thousand containing three bad rows applies the other 997 and lists the three.

const result = grid.rows.apply({ update: [ ... ] });
result.rejected;  // [{ operation, id, reason }]

stats() reports held against heldLimit: what the log is carrying now, against what it will carry. rows is a lifetime total of everything that ever arrived and says nothing about memory; these two do. Raise logRows for a deeper scrubber on a grid you have measured, and lower it on a feed of very wide rows.