Price list: async checks and lookups

One product per row. Each SKU is checked against a catalogue service as it’s typed, and a new SKU fills in its description and category from it. Prices can’t be formulas; the price with tax must be.

price-list.xlsxLoads as you scroll to it

Try: type SKU-1013 under the last SKU (discontinued, after a pause); SKU-2003 (fills itself in); a price of -1 or =B2*2; a category that isn’t in the list; the same SKU twice.

The schema

price-list.ts

import { defineSchema } from '@ruled/sheet';
import { lookupSku } from './services';

export const priceList = defineSchema({
  id: 'docs.price-list',
  name: 'Price list',
  sheets: [
    {
      name: 'Prices',
      tables: [
        {
          key: 'prices',
          at: 'A1',
          records: { min: 1, max: 500 },
          fields: [
            {
              key: 'sku',
              label: 'SKU',
              type: 'text',
              required: true,
              unique: true,
              pattern: '^SKU-\\d{4}$',
              patternHint: 'like SKU-1234',
              // An async check against the catalogue service. The editor shows the cell
              // as being checked, aborts the call if the value changes again, and re-runs
              // it only when this cell's value changes.
              validate: async (value, { signal }) => {
                const item = await lookupSku(String(value), signal);
                if (item?.discontinued) return { message: 'The catalogue lists this product as discontinued.', severity: 'warning' };
              },
            },
            { key: 'description', label: 'Description', type: 'text', required: true },
            { key: 'category', label: 'Category', type: 'enum', values: ['Hardware', 'Software', 'Services'], required: true },
            { key: 'price', label: 'Unit price', type: 'number', required: true, min: 0, decimals: 2, formula: 'forbid' },
            { key: 'priceIncTax', label: 'Price inc. tax', type: 'number', formula: 'require', description: 'Unit price plus 20% tax.' },
            { key: 'from', label: 'Effective from', type: 'date', required: true, min: '2020-01-01' },
            { key: 'active', label: 'Active', type: 'boolean' },
          ],
        },
      ],
    },
  ],
  // Business logic after each edit: a new SKU fills in its description and category.
  onChange: async (ctx) => {
    for (const change of ctx.changes) {
      if (change.field !== 'sku' || typeof change.after !== 'string' || change.record === undefined) continue;
      const record = ctx.sheet(change.sheet)?.table('prices')?.records[change.record];
      if (!record || record.values.description) continue;
      const item = await lookupSku(change.after, ctx.signal);
      if (!item) continue;
      const at = { sheet: change.sheet, table: 'prices', record: change.record };
      ctx.set({ ...at, field: 'description' }, item.description);
      ctx.set({ ...at, field: 'category' }, item.category);
    }
  },
});

The pretend services

The catalogue answers after half a second, and stops when the editor aborts the check because the value changed again, as a fetch given the same signal would.

services.ts

/**
 * Pretend services for the examples: a product catalogue and a budget
 * service, each answering after a delay, as a real API would. They stop
 * early when the editor aborts a check because the value changed again.
 */
export function wait(ms: number, signal?: AbortSignal): Promise<void> {
  return new Promise((resolve, reject) => {
    const timer = setTimeout(resolve, ms);
    signal?.addEventListener('abort', () => {
      clearTimeout(timer);
      reject(new DOMException('Aborted', 'AbortError'));
    });
  });
}

const CATALOGUE: Record<string, { description: string; category: string; discontinued?: boolean }> = {
  'SKU-1013': { description: 'Legacy adaptor', category: 'Hardware', discontinued: true },
  'SKU-2001': { description: 'Barcode scanner', category: 'Hardware' },
  'SKU-2002': { description: 'Label printer', category: 'Hardware' },
  'SKU-2003': { description: 'Analytics add-on', category: 'Software' },
  'SKU-2004': { description: 'On-site audit', category: 'Services' },
};

/** Looks a product up in the catalogue. */
export async function lookupSku(sku: string, signal?: AbortSignal) {
  await wait(500, signal);
  return CATALOGUE[sku.toUpperCase()];
}

/** Asks the budget service whether a total needs review. */
export async function needsReview(total: number, signal?: AbortSignal): Promise<boolean> {
  await wait(800, signal);
  return total > 400_000;
}

What to notice

• The SKU field has declarative checks (required, unique, pattern) and a code check beside them. The code check runs for every SKU typed, and never for an empty cell, which required covers.
• onChange writes with ctx.set, and its writes are part of the same undo step as the SKU that caused them.
• formula: 'forbid' on the price and 'require' on the price with tax keep calculated columns calculated.

Read more: Async checks, Business logic.