← back to Filemaker Mcp
invoice: all money on FIRST invoice, just the SKU on each subsequent
57039734658960cae8d45057c0a50db4e35c79bc · 2026-07-09 23:34:40 -0700 · Steve Abrams
Steve's refined rule (2026-07-09): the money record carries the WHOLE order
total on ONE line = total units x per-unit price (GRAND TOTAL = merch subtotal);
DETAIL='Samples' for an all-samples order so Q=2 reads '2 Samples @ $4.25 =
$8.50'. Every subsequent record shows JUST its SKU at $0. Reverts the
line-2-on-first-record approach. PAID ON ACCOUNT computed exactly (merch+tax+
shipping) so money is right even when a summarized per-unit line rounds a cent.
Also updated live invoice 102622 (order #32571) to the new format.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M lib/invoice.jsM scripts/test-real-units-live.mjs
Diff
commit 57039734658960cae8d45057c0a50db4e35c79bc
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Jul 9 23:34:40 2026 -0700
invoice: all money on FIRST invoice, just the SKU on each subsequent
Steve's refined rule (2026-07-09): the money record carries the WHOLE order
total on ONE line = total units x per-unit price (GRAND TOTAL = merch subtotal);
DETAIL='Samples' for an all-samples order so Q=2 reads '2 Samples @ $4.25 =
$8.50'. Every subsequent record shows JUST its SKU at $0. Reverts the
line-2-on-first-record approach. PAID ON ACCOUNT computed exactly (merch+tax+
shipping) so money is right even when a summarized per-unit line rounds a cent.
Also updated live invoice 102622 (order #32571) to the new format.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
lib/invoice.js | 97 ++++++++++++++++++----------------------
scripts/test-real-units-live.mjs | 43 +++++++++---------
2 files changed, 66 insertions(+), 74 deletions(-)
diff --git a/lib/invoice.js b/lib/invoice.js
index e290b90..83be891 100644
--- a/lib/invoice.js
+++ b/lib/invoice.js
@@ -40,23 +40,20 @@ function uom(li) {
if (/sample|memo|swatch/.test(t)) return 'each';
return 'each';
}
-// Build the money record's real order lines: one slot per line item with REAL
-// units + REAL per-unit price (GRAND TOTAL = Σ Q×NET falls out to the merch
-// subtotal). The invoice layout supports only TWO physical line rows (line 1 uses
-// the "DETAIL 1"/"Q1"/"NET 1" family; line 2 uses the DISTINCT "DETAIL 2"/"Q2"/
-// "NET 2" fields — "DETAIL 3"+ don't exist). A >2-SKU order lumps the overflow
-// into line 2 so GRAND TOTAL still totals the whole order.
-function orderLines(items) {
- const mk = (li) => ({
- detail: `${baseSku(li.sku)} — ${li.name || ''}`.trim(),
- qty: li.quantity || 1, unit: uom(li), price: r2(li.price),
- });
- if (items.length <= MAX_LINES) return items.map(mk);
- const head = items.slice(0, MAX_LINES - 1).map(mk);
- const rest = items.slice(MAX_LINES - 1);
- const restExt = r2(rest.reduce((a, li) => a + (Number(li.price) || 0) * (li.quantity || 1), 0));
- head.push({ detail: `+ ${rest.length} more items (${rest.map((li) => baseSku(li.sku)).join(', ')})`.slice(0, 250), qty: 1, unit: 'each', price: restExt });
- return head;
+const isSample = (li) => /sample|memo|swatch/i.test(`${li.variant_title||''} ${li.name||''} ${li.sku||''}`);
+// The money record carries ALL the order's dollars on ONE line (Steve's rule
+// 2026-07-09): total units × per-unit price, so GRAND TOTAL = the whole
+// merchandise subtotal. Q = the order's total quantity, NET/RETAIL = merch ÷
+// total-qty (the real per-unit price — exact for a uniform-price order like a
+// batch of $4.25 samples; a blended per-unit for the rare mixed-price order).
+// DETAIL = "Samples" for an all-samples order (so Q=2 reads "2 Samples @ $4.25"),
+// else headlines the first SKU. Every OTHER SKU goes on its own $0 record (just
+// the SKU), so all the money stays on the first invoice.
+function moneyLine(items, merch) {
+ const totalQty = items.reduce((a, li) => a + (li.quantity || 1), 0) || 1;
+ const allSamples = items.every(isSample);
+ const detail = allSamples ? 'Samples' : `${baseSku(items[0].sku)} — ${items[0].name || ''}`.trim().slice(0, 250);
+ return { detail, qty: totalQty, unit: uom(items[0]), price: r2(merch / totalQty) };
}
function resale(s) {
const country = (s.country_code || 'US').toUpperCase();
@@ -77,14 +74,10 @@ async function patch(id, field, value) {
try { await fm.updateRecord('invoice', HDR, id, { [field]: value }, { dryRun: false }); }
catch (e) { if (!['102','201','509','111'].includes(e.fmCode)) throw e; }
}
-// The invoice's two physical line rows. Only repetition 1 of the "… 1" family is
-// placed on the layout, so line 2 lives in SEPARATE fields ("DETAIL 2"/"Q2"/…).
-// Writes to a 3rd row fail (code 102, field missing) — verified 2026-07-09.
-const LINE_FIELDS = [
- { detail: 'DETAIL 1(1)', q: 'Q1(1)', net: 'NET 1(1)', retail: 'RETAIL 1(1)', unit: 'Unit 1(1)', side: 'Sidemark 1(1)' },
- { detail: 'DETAIL 2', q: 'Q2', net: 'NET 2', retail: 'RETAIL 2', unit: 'UNIT 2', side: 'SIDEMARK 2' },
-];
-const MAX_LINES = LINE_FIELDS.length; // 2
+// Line-field write note: only repetition 1 of the "… 1" family is placed on the
+// layout, so all money rides `DETAIL 1(1)`/`Q1(1)`/`NET 1(1)` on the first record
+// (verified 2026-07-09). The "… 2" fields are intentionally NOT used — every SKU
+// beyond the first gets its own $0 record instead.
// Create one no-dollar tracking record per SKU in the order, with the DW SKU in
// the writable "WC Pattern Number AKA DW SKU" field (FileMaker's pattern Name/Color
@@ -108,17 +101,17 @@ export async function createSkuRecords(order, accountNumber, clientName, { commi
}
// Steve's rule (2026-07-07, refined 2026-07-09): ONE invoice record per SKU, but
-// only the FIRST (money) record carries the dollars. That money record is the
-// REAL invoice — it lists each ordered item as its own repeating line with the
-// REAL number of units (Q1) and a REAL per-unit price (NET/RETAIL), so
-// GRAND TOTAL (= Σ Q×NET, a calc) adds up to the merchandise subtotal on its own.
+// ALL the money rides the FIRST invoice and every subsequent invoice shows JUST
+// its SKU. The first (money) record has ONE line = the order's TOTAL units × the
+// per-unit price, so GRAND TOTAL (= Q×NET, a calc) equals the whole merchandise
+// subtotal — real units, real per-unit price (exact for a uniform-price order,
+// e.g. a batch of $4.25 samples; a blended per-unit for a mixed-price order).
// PAID ON ACCOUNT = that grand total (+ tax + shipping, which ride the money
-// record only). Every subsequent per-SKU record is a $0 stub — real units shown
-// for reference, but NET/RETAIL/PAID = 0 — so the order's money is counted ONCE
-// while each pattern still gets its own record for FileMaker's Name/Color lookup.
-// (>4-SKU orders lump the overflow into the 4th line so the total stays correct.)
-// Returns the first invoice number + its grand total (what the Slack alert
-// references); the $0 stubs have GRAND TOTAL 2 = 0 so the monitor skips them.
+// record only). Every subsequent per-SKU record is a $0 stub — just its SKU, with
+// NET/RETAIL/PAID = 0 — so the order's money is counted ONCE while each pattern
+// still gets its own record for FileMaker's Name/Color lookup. Returns the first
+// invoice number + its grand total (what the Slack alert references); the $0
+// stubs have GRAND TOTAL 2 = 0 so the monitor skips them.
export async function createInvoiceForOrder(order, accountNumber, { commit = true } = {}) {
const c = order.customer || {}, s = order.shipping_address || order.billing_address || {};
const name = ((c.first_name||'') + ' ' + (c.last_name||'')).trim();
@@ -136,7 +129,7 @@ export async function createInvoiceForOrder(order, accountNumber, { commit = tru
return { wouldCreate: true, invoiceNumber: '(next)', invoiceCount: items.length,
cust_po: order.order_number, account: accountNumber, shipVia, shipping, orderTax, resale: resale(s),
merch,
- moneyRecordLines: orderLines(items).map((L) => ({ detail: L.detail, qty: L.qty, unit: L.unit, price: L.price, ext: r2(L.qty * L.price) })),
+ moneyLine: (() => { const L = moneyLine(items, merch); return { detail: L.detail, qty: L.qty, unit: L.unit, price: L.price, ext: r2(L.qty * L.price) }; })(),
stubRecords: items.slice(1).map((li) => ({ sku: li.sku, qty: li.quantity || 1, dollars: 0 })) };
}
@@ -177,22 +170,18 @@ export async function createInvoiceForOrder(order, accountNumber, { commit = tru
await patch(id, 'SHIPPING HANDLING', isFirst ? shipping : 0);
await patch(id, 'SALES TAX YN', (isFirst && orderTax > 0) ? 'Y' : 'N');
- // The FIRST record is the REAL invoice: each order line in its own physical
- // row (line 1 = "… 1" fields, line 2 = "… 2" fields) with real units + real
- // per-unit price, so GRAND TOTAL calcs to the merch subtotal. Records 2..N are
- // $0 SKU-tracking stubs — real units shown for reference, no dollars, so the
- // order's money is counted exactly once.
+ // The FIRST record carries ALL the order's money on ONE line: total units ×
+ // per-unit price (GRAND TOTAL = the whole merch subtotal), with real units +
+ // a real per-unit price. Every OTHER record shows JUST its SKU at $0, so all
+ // the dollars stay on the first invoice and each pattern still gets a record.
if (isFirst) {
- const lines = orderLines(items);
- for (let n = 0; n < lines.length && n < LINE_FIELDS.length; n++) {
- const L = lines[n], F = LINE_FIELDS[n];
- await patch(id, F.detail, L.detail.slice(0, 250));
- await patch(id, F.q, L.qty);
- await patch(id, F.unit, L.unit);
- await patch(id, F.retail, L.price);
- await patch(id, F.net, L.price);
- await patch(id, F.side, s.company || '');
- }
+ const L = moneyLine(items, merch);
+ await patch(id, 'DETAIL 1(1)', L.detail);
+ await patch(id, 'Q1(1)', L.qty);
+ await patch(id, 'Unit 1(1)', L.unit);
+ await patch(id, 'RETAIL 1(1)', L.price);
+ await patch(id, 'NET 1(1)', L.price);
+ await patch(id, 'Sidemark 1(1)', s.company || '');
} else {
const desc = `${baseSku(li.sku)} — ${li.name || ''}`.trim();
await patch(id, 'DETAIL 1(1)', desc.slice(0, 250));
@@ -208,8 +197,10 @@ export async function createInvoiceForOrder(order, accountNumber, { commit = tru
try { await fm.updateRecord('invoice', WCPAT_LAYOUT, id, { 'wc pattern': wcPattern(li.sku) }, { dryRun: false }); } catch {}
try { await fm.updateRecord('invoice', DWSKU_LAYOUT, id, { [DWSKU_FIELD]: dwSku(li.sku) }, { dryRun: false }); } catch {}
- // paid-in-full = grand total (first record) or 0 (SKU records)
- const grand = isFirst ? r2((await fm.getRecord('invoice', HDR, id))?.fieldData?.['GRAND TOTAL'] || 0) : 0;
+ // paid-in-full = the EXACT order total (merch + tax + shipping) on the first
+ // record, 0 on the SKU records. Computed directly (not read from GRAND TOTAL)
+ // so the money is exact even when a summarized per-unit line rounds by a cent.
+ const grand = isFirst ? r2(merch + orderTax + shipping) : 0;
await patch(id, 'PAID ON ACCOUNT', grand);
await patch(id, 'Notes', isFirst
? `Imported by Otto — ${stamp()}`
diff --git a/scripts/test-real-units-live.mjs b/scripts/test-real-units-live.mjs
index 06eb373..a4a94b2 100644
--- a/scripts/test-real-units-live.mjs
+++ b/scripts/test-real-units-live.mjs
@@ -1,6 +1,8 @@
-// LIVE test: create single-SKU AND 2-SKU invoices, read back to PROVE real units
-// (Q1/Q2) + real per-unit price (NET 1/NET 2) landed and GRAND TOTAL/PAID are
-// correct, then DELETE all created records. Monitor is booted out by the wrapper.
+// LIVE test of the "all money on first invoice, just the SKU on each subsequent
+// invoice" model. Creates a single-SKU order and a 2-sample order, reads back to
+// prove: money record = ONE line (total units x per-unit price), GRAND/PAID =
+// whole order, subsequent records = SKU only at $0. Then DELETES all records.
+// Monitor is booted out by the wrapper so no Slack card posts.
import { readFileSync } from 'node:fs';
import { fileURLToPath } from 'node:url';
import { dirname, join } from 'node:path';
@@ -17,36 +19,35 @@ const base = { total_tax: 0, customer: { first_name: 'ZZ-TEST', last_name: 'DELE
shipping_lines: [{ title: 'UPS', price: '0' }] };
const cases = [
- { label: 'SINGLE-SKU', po: 999201, items: [{ sku: 'DWTT-80502', name: 'Edwards', price: '44.18', quantity: 2, variant_title: 'Double Roll' }],
- expect: { grand: 88.36, l1: { q: 2, net: 44.18 }, stubs: 0 } },
- { label: '2-SKU', po: 999202, items: [
- { sku: 'DWTT-80502', name: 'Edwards', price: '12.50', quantity: 2, variant_title: 'Double Roll' },
- { sku: 'DWAK-1120', name: 'Ashbury', price: '4.25', quantity: 3, variant_title: 'Sample' }],
- expect: { grand: 37.75, l1: { q: 2, net: 12.5 }, l2: { q: 3, net: 4.25 }, stubs: 1 } },
+ { label: 'SINGLE-SKU roll', po: 999301, items: [{ sku: 'DWTT-80502', name: 'Edwards', price: '44.18', quantity: 2, variant_title: 'Double Roll' }],
+ expect: { detail: 'DWTT-80502 — Edwards', qty: 2, net: 44.18, grand: 88.36, stubs: 0 } },
+ { label: '2 SAMPLES @ $4.25', po: 999302, items: [
+ { sku: 'DWHF-70193', name: 'Quintessence', price: '4.25', quantity: 1, variant_title: 'Sample' },
+ { sku: 'DWTT-80714', name: 'Ribbon Floral', price: '4.25', quantity: 1, variant_title: 'Sample' }],
+ expect: { detail: 'Samples', qty: 2, net: 4.25, grand: 8.5, stubs: 1 } },
];
let allPass = true;
for (const c of cases) {
const order = { ...base, order_number: c.po, line_items: c.items };
- const res = await createInvoiceForOrder(order, 'ZZ-ACCT', { commit: true });
+ await createInvoiceForOrder(order, 'ZZ-ACCT', { commit: true });
const { records } = await fm.findRecords('invoice', HDR, { 'Cust PO': '==' + c.po }, { limit: 20, sort: [{ fieldName: 'Invoice', sortOrder: 'ascend' }] });
- const money = records[0]?.fieldData || {};
- const grand = Number(money['GRAND TOTAL'] || 0), paid = Number(money['PAID ON ACCOUNT'] || 0);
- console.log(`\n=== ${c.label} (PO ${c.po}) — money #${money['Invoice']} ===`);
- console.log(` line1: Q=${money['Q1(1)']} NET=$${money['NET 1(1)']} ${String(money['DETAIL 1(1)']||'').slice(0,24)}`);
- if (money['DETAIL 2']) console.log(` line2: Q=${money['Q2']} NET=$${money['NET 2']} ${String(money['DETAIL 2']||'').slice(0,24)}`);
- console.log(` GRAND TOTAL=$${grand} PAID=$${paid} | ${records.length-1} stub(s)`);
+ const m = records[0]?.fieldData || {};
+ const grand = Number(m['GRAND TOTAL'] || 0), paid = Number(m['PAID ON ACCOUNT'] || 0);
+ console.log(`\n=== ${c.label} (PO ${c.po}) — money #${m['Invoice']} ===`);
+ console.log(` line: Q=${m['Q1(1)']} NET=$${m['NET 1(1)']} DETAIL="${String(m['DETAIL 1(1)']||'').slice(0,24)}" line2 DETAIL="${m['DETAIL 2']||''}"`);
+ console.log(` GRAND=$${grand} PAID=$${paid} | ${records.length - 1} SKU-only stub(s)`);
let ok = true;
- if (Number(money['Q1(1)']) !== c.expect.l1.q || Number(money['NET 1(1)']) !== c.expect.l1.net) { ok = false; console.log(' x line1 units/price wrong'); }
- if (c.expect.l2 && (Number(money['Q2']) !== c.expect.l2.q || Number(money['NET 2']) !== c.expect.l2.net)) { ok = false; console.log(' x line2 units/price wrong'); }
+ if (String(m['DETAIL 1(1)']||'') !== c.expect.detail) { ok = false; console.log(` x detail != "${c.expect.detail}"`); }
+ if (Number(m['Q1(1)']) !== c.expect.qty || Number(m['NET 1(1)']) !== c.expect.net) { ok = false; console.log(' x units/price wrong'); }
+ if (m['DETAIL 2']) { ok = false; console.log(' x line 2 not cleared'); }
if (grand !== c.expect.grand || paid !== c.expect.grand) { ok = false; console.log(` x grand/paid != ${c.expect.grand}`); }
if ((records.length - 1) !== c.expect.stubs) { ok = false; console.log(` x stub count != ${c.expect.stubs}`); }
for (let i = 1; i < records.length; i++) { if (Number(records[i].fieldData['PAID ON ACCOUNT'] || 0) !== 0) { ok = false; console.log(' x stub carries dollars'); } }
console.log(` ${ok ? 'PASS' : 'FAIL'}`);
allPass = allPass && ok;
-
- for (const r of records) { try { await fm.deleteRecord('invoice', HDR, r.recordId); } catch (e) { console.log(' delete err', e.message); } }
+ for (const r of records) { try { await fm.deleteRecord('invoice', HDR, r.recordId); } catch (e) { console.log(' del err', e.message); } }
console.log(` cleaned up ${records.length} record(s)`);
}
-console.log(`\n${allPass ? 'ALL PASS — real units + real per-unit price; GRAND TOTAL/PAID correct; stubs $0' : 'SOME FAILED'}`);
+console.log(`\n${allPass ? 'ALL PASS — all money on first invoice; SKU-only $0 stubs' : 'SOME FAILED'}`);
← f7c8106 add watch-real-units-order.mjs — live watcher for next real
·
back to Filemaker Mcp
·
add ensure-wallpaper-for-order: auto-complete FileMaker WALL 01d9bff →