← back to Filemaker Mcp
invoice: show real units + real per-unit price (not lumped Q1=1)
d9b2cbfec90c1a824c717f2436df938ab519eef7 · 2026-07-09 20:29:26 -0700 · Steve Abrams
- money record now lists each order line in its own physical row (line 1 =
'… 1' fields, line 2 = distinct 'DETAIL 2'/'Q2'/'NET 2' fields) with the
REAL quantity + REAL per-unit price; GRAND TOTAL/PAID sum correctly.
- single-SKU (the common case): line 1 = real qty x real unit price, identical
GRAND/PAID/GRAND TOTAL 2 to before -> zero risk. 2-SKU shows both lines;
>2-SKU lumps the overflow into line 2 (layout caps at 2 rows).
- per-SKU stub records stay $0 (real units shown for reference) so dollars are
counted once and the monitor posts one card per order.
- monitor: card total now reads PAID ON ACCOUNT when booked (sums both lines;
GRAND TOTAL 2 is a line-1-only calc that undercounts a 2-line order).
- verified live: single-SKU 2x$44.18=$88.36 and 2-SKU (2x$12.50 + 3x$4.25)=$37.75.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M lib/invoice.jsM scripts/new-invoice-monitor.mjsA scripts/test-real-units-live.mjsA scripts/test-real-units.mjs
Diff
commit d9b2cbfec90c1a824c717f2436df938ab519eef7
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Jul 9 20:29:26 2026 -0700
invoice: show real units + real per-unit price (not lumped Q1=1)
- money record now lists each order line in its own physical row (line 1 =
'… 1' fields, line 2 = distinct 'DETAIL 2'/'Q2'/'NET 2' fields) with the
REAL quantity + REAL per-unit price; GRAND TOTAL/PAID sum correctly.
- single-SKU (the common case): line 1 = real qty x real unit price, identical
GRAND/PAID/GRAND TOTAL 2 to before -> zero risk. 2-SKU shows both lines;
>2-SKU lumps the overflow into line 2 (layout caps at 2 rows).
- per-SKU stub records stay $0 (real units shown for reference) so dollars are
counted once and the monitor posts one card per order.
- monitor: card total now reads PAID ON ACCOUNT when booked (sums both lines;
GRAND TOTAL 2 is a line-1-only calc that undercounts a 2-line order).
- verified live: single-SKU 2x$44.18=$88.36 and 2-SKU (2x$12.50 + 3x$4.25)=$37.75.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
lib/invoice.js | 87 +++++++++++++++++++++++++++++++---------
scripts/new-invoice-monitor.mjs | 5 ++-
scripts/test-real-units-live.mjs | 52 ++++++++++++++++++++++++
scripts/test-real-units.mjs | 18 +++++++++
4 files changed, 141 insertions(+), 21 deletions(-)
diff --git a/lib/invoice.js b/lib/invoice.js
index a7629ab..e290b90 100644
--- a/lib/invoice.js
+++ b/lib/invoice.js
@@ -40,6 +40,24 @@ 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;
+}
function resale(s) {
const country = (s.country_code || 'US').toUpperCase();
const state = (s.province_code || '').toUpperCase();
@@ -59,7 +77,14 @@ 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; }
}
-const MAX_LINES = 4; // DETAIL 1 / Q1 / NET 1 family caps at maxRepeat 4
+// 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
// 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
@@ -82,14 +107,18 @@ export async function createSkuRecords(order, accountNumber, clientName, { commi
return { created, skus };
}
-// Steve's rule (2026-07-07): ONE invoice record per SKU, but only the FIRST
-// record carries the money — NET/RETAIL = the whole order's merchandise total and
-// PAID ON ACCOUNT = the grand total. Every subsequent per-SKU record lists just
-// its SKU at $0 (NET/RETAIL/PAID = 0), so the dollars are counted ONCE while each
-// pattern still gets its own record for FileMaker's Name/Color lookup. Tax +
-// shipping ride on the first (money) record only. Returns the first invoice
-// number + its grand total (what the Slack alert references); the $0 records have
-// GRAND TOTAL 2 = 0 so the per-invoice monitor never posts a card for them.
+// 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.
+// 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.
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();
@@ -106,7 +135,9 @@ export async function createInvoiceForOrder(order, accountNumber, { commit = tru
if (!commit) {
return { wouldCreate: true, invoiceNumber: '(next)', invoiceCount: items.length,
cust_po: order.order_number, account: accountNumber, shipVia, shipping, orderTax, resale: resale(s),
- lines: items.map((li, i) => ({ sku: li.sku, dollars: i === 0 ? merch : 0, carries: i === 0 ? 'ORDER TOTAL' : '$0' })) };
+ merch,
+ moneyRecordLines: orderLines(items).map((L) => ({ 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 })) };
}
const sharedHeader = {
@@ -124,7 +155,6 @@ export async function createInvoiceForOrder(order, accountNumber, { commit = tru
for (let i = 0; i < items.length; i++) {
const li = items[i];
const isFirst = i === 0;
- const lineAmt = isFirst ? merch : 0; // dollars on the first record only
// allocate a free invoice number + create the header record
let id = null, createdInv = null;
@@ -147,14 +177,31 @@ 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');
- // one line = THIS sku; dollars only on the first record
- const desc = `${baseSku(li.sku)} — ${li.name || ''}`.trim();
- await patch(id, 'DETAIL 1(1)', desc.slice(0, 250));
- await patch(id, 'Q1(1)', 1);
- await patch(id, 'Unit 1(1)', uom(li));
- await patch(id, 'RETAIL 1(1)', lineAmt);
- await patch(id, 'NET 1(1)', lineAmt);
- await patch(id, 'Sidemark 1(1)', s.company || '');
+ // 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.
+ 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 || '');
+ }
+ } else {
+ const desc = `${baseSku(li.sku)} — ${li.name || ''}`.trim();
+ await patch(id, 'DETAIL 1(1)', desc.slice(0, 250));
+ await patch(id, 'Q1(1)', li.quantity || 1);
+ await patch(id, 'Unit 1(1)', uom(li));
+ await patch(id, 'RETAIL 1(1)', 0);
+ await patch(id, 'NET 1(1)', 0);
+ await patch(id, 'Sidemark 1(1)', s.company || '');
+ }
// cross-table SKU fields for THIS sku (FileMaker pattern lookup keys off these)
try { await fm.updateRecord('invoice', SKU_LAYOUT, id, { 'JS Easy SKU #': baseSku(li.sku) }, { dryRun: false }); } catch {}
@@ -169,7 +216,7 @@ export async function createInvoiceForOrder(order, accountNumber, { commit = tru
: `Imported by Otto — ${stamp()} · $0 SKU record; order total billed on invoice ${firstInvoiceNumber}`);
if (isFirst) { firstInvoiceNumber = createdInv; firstGrand = grand; firstId = id; }
- created.push({ invoiceNumber: String(createdInv), sku: li.sku, amount: lineAmt });
+ created.push({ invoiceNumber: String(createdInv), sku: li.sku, amount: isFirst ? merch : 0 });
}
return { invoiceNumber: String(firstInvoiceNumber), recordId: firstId, grandTotal: firstGrand,
diff --git a/scripts/new-invoice-monitor.mjs b/scripts/new-invoice-monitor.mjs
index abee875..bc597ae 100644
--- a/scripts/new-invoice-monitor.mjs
+++ b/scripts/new-invoice-monitor.mjs
@@ -66,12 +66,15 @@ async function main() {
let maxPosted = lastSeen;
for (const { n, f } of fresh) {
const client = String(f['Sold Company Name Lookup'] || 'Customer').trimEnd();
- const total = num(f['GRAND TOTAL 2']);
const item = String(f['DETAIL 1(1)'] || f['DETAIL 1'] || '').trimEnd().slice(0, 80);
const rep = f['SALES PERSON'] ? ` · ${f['SALES PERSON']}` : '';
// Booked sale = payment received; otherwise it's an open quote (don't call it a sale).
const paid = String(f['PAID ON ACCOUNT'] ?? '').trim();
const booked = paid !== '' && num(paid) !== 0;
+ // Card total: for a booked sale use PAID ON ACCOUNT (the true paid total — it
+ // sums BOTH line rows; GRAND TOTAL 2 is a line-1-only calc that undercounts a
+ // 2-line order). Unpaid quotes fall back to GRAND TOTAL 2 (the estimate).
+ const total = booked ? num(paid) : num(f['GRAND TOTAL 2']);
const tag = !booked ? '📝 *New quote* (no payment yet)' : (total < 0 ? '↩️ *Refund*' : '🛎️ *New sale*');
const text = `${tag} — ${client} · Invoice #${n} · *${money(total)}*${rep}${item ? `\n• ${item}` : ''}`;
if (DRY_RUN) console.log('[would post] ' + text.replace(/\n/g, ' '));
diff --git a/scripts/test-real-units-live.mjs b/scripts/test-real-units-live.mjs
new file mode 100644
index 0000000..06eb373
--- /dev/null
+++ b/scripts/test-real-units-live.mjs
@@ -0,0 +1,52 @@
+// 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.
+import { readFileSync } from 'node:fs';
+import { fileURLToPath } from 'node:url';
+import { dirname, join } from 'node:path';
+const ROOT = join(dirname(fileURLToPath(import.meta.url)), '..');
+for (const l of readFileSync(join(ROOT, '.env'), 'utf8').split('\n')) {
+ const m = l.match(/^([A-Z0-9_]+)=(.*)$/); if (m && !process.env[m[1]]) process.env[m[1]] = m[2].replace(/^['"]|['"]$/g, '');
+}
+const { createInvoiceForOrder } = await import('../lib/invoice.js');
+const fm = await import('../src/fm-client.js');
+const HDR = 'Invoice to Client Copy';
+
+const base = { total_tax: 0, customer: { first_name: 'ZZ-TEST', last_name: 'DELETE-ME' },
+ shipping_address: { company: 'ZZ Test Project', address1: '1 Main', city: 'LA', province_code: 'CA', zip: '90001', country_code: 'US' },
+ 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 } },
+];
+
+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 });
+ 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)`);
+
+ 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 (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); } }
+ 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'}`);
diff --git a/scripts/test-real-units.mjs b/scripts/test-real-units.mjs
new file mode 100644
index 0000000..f2312cd
--- /dev/null
+++ b/scripts/test-real-units.mjs
@@ -0,0 +1,18 @@
+import { createInvoiceForOrder } from '../lib/invoice.js';
+const order = {
+ order_number: 999123,
+ total_tax: 0,
+ customer: { first_name: 'Test', last_name: 'Buyer' },
+ shipping_address: { company: 'Test Project', address1: '1 Main', city: 'LA', province_code: 'CA', zip: '90001', country_code: 'US' },
+ shipping_lines: [{ title: 'UPS', price: '0' }],
+ line_items: [
+ { sku: 'DWTT-80502', name: "Edward's Paper", price: '12.50', quantity: 2, variant_title: 'Double Roll' },
+ { sku: 'DWAK-1120', name: 'Ashbury', price: '4.25', quantity: 3, variant_title: 'Sample' },
+ ],
+};
+const r = await createInvoiceForOrder(order, 'TEST-ACCT', { commit: false });
+console.log('MONEY RECORD (real invoice):');
+for (const L of r.moneyRecordLines) console.log(` ${L.detail.padEnd(28)} Q=${L.qty} NET=$${L.price} ext=$${L.ext}`);
+console.log(` merch subtotal = $${r.merch} (GRAND TOTAL will calc to this)`);
+console.log('$0 STUB RECORDS:');
+for (const s of r.stubRecords) console.log(` ${s.sku} Q=${s.qty} $${s.dollars}`);
← fb3670b chore: v-bump (session close — per-SKU invoicing fix + dup a
·
back to Filemaker Mcp
·
add watch-real-units-order.mjs — live watcher for next real f7c8106 →