← back to Filemaker Mcp
invoicing: one record per SKU, only the FIRST carries the dollars (NET/RETAIL=order total, PAID=grand total); remaining SKU records $0 (Steve 2026-07-07). Tax+shipping ride the money record only; $0 records stay off #new-order via GRAND TOTAL 2=0 filter
bd4cd959e65b26136dcfcea001c52db3fcc3d2b0 · 2026-07-07 09:02:29 -0700 · Steve Abrams
Files touched
Diff
commit bd4cd959e65b26136dcfcea001c52db3fcc3d2b0
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jul 7 09:02:29 2026 -0700
invoicing: one record per SKU, only the FIRST carries the dollars (NET/RETAIL=order total, PAID=grand total); remaining SKU records $0 (Steve 2026-07-07). Tax+shipping ride the money record only; $0 records stay off #new-order via GRAND TOTAL 2=0 filter
---
lib/invoice.js | 141 ++++++++++++++++++++++++++++++++-------------------------
1 file changed, 79 insertions(+), 62 deletions(-)
diff --git a/lib/invoice.js b/lib/invoice.js
index 4a5bd95..dfeb86b 100644
--- a/lib/invoice.js
+++ b/lib/invoice.js
@@ -82,6 +82,14 @@ 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.
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();
@@ -90,71 +98,80 @@ export async function createInvoiceForOrder(order, accountNumber, { commit = tru
const shipping = Number(ship?.price || 0);
const orderTax = Number(order.total_tax || 0);
const items = order.line_items || [];
- const primarySku = items[0]?.sku || '';
+ if (!items.length) throw new Error('order has no line items');
+
+ // whole-order merchandise subtotal — lands on the FIRST record only
+ const merch = r2(items.reduce((a, li) => a + (Number(li.price) || 0) * (li.quantity || 1), 0));
if (!commit) {
- return { wouldCreate: true, invoiceNumber: '(next)', cust_po: order.order_number, account: accountNumber,
- shipVia, shipping, orderTax, resale: resale(s), lines: items.map((li) => ({ sku: li.sku, qty: li.quantity, unit: uom(li), price: li.price })) };
+ 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' })) };
}
- let inv = await nextInvoiceNumber(), id = null, createdInv = null;
- for (let tries = 0; tries < 5 && !id; tries++, inv++) {
- const dup = await fm.findRecords('invoice', HDR, { 'Invoice': '==' + inv }, { limit: 1 }).catch(() => ({ records: [] }));
- if (dup.records.length) continue;
- const res = await fm.createRecord('invoice', HDR, {
- 'Invoice': String(inv), 'Cust PO': String(order.order_number), 'Account #': String(accountNumber || ''),
- 'SOLD NAME': name, 'SOLD COMPANY': s.company || '', 'SOLD ADDRESS': s.address1 || '', 'SOLD CITY': s.city || '',
- 'SOLD STATE': s.province_code || '', 'SOLD ZIP': s.zip || '', 'TELEPHONE': s.phone || order.phone || '',
- 'ship name': name, 'SHIP COMAPNY': s.company || '', 'SHIP ADDRESS': s.address1 || '', 'SHIP CITY': s.city || '',
- 'SHIP STATE': s.province_code || '', 'SHIP ZIP': s.zip || '', 'Date': today(),
- }, { dryRun: false }).catch(() => null);
- if (res) { id = res.recordId; createdInv = inv; }
+ const sharedHeader = {
+ 'Cust PO': String(order.order_number), 'Account #': String(accountNumber || ''),
+ 'SOLD NAME': name, 'SOLD COMPANY': s.company || '', 'SOLD ADDRESS': s.address1 || '', 'SOLD CITY': s.city || '',
+ 'SOLD STATE': s.province_code || '', 'SOLD ZIP': s.zip || '', 'TELEPHONE': s.phone || order.phone || '',
+ 'ship name': name, 'SHIP COMAPNY': s.company || '', 'SHIP ADDRESS': s.address1 || '', 'SHIP CITY': s.city || '',
+ 'SHIP STATE': s.province_code || '', 'SHIP ZIP': s.zip || '', 'Date': today(),
+ };
+
+ let nextNum = await nextInvoiceNumber();
+ let firstInvoiceNumber = null, firstGrand = 0, firstId = null;
+ const created = [];
+
+ 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;
+ for (let tries = 0; tries < 8 && !id; tries++, nextNum++) {
+ const dup = await fm.findRecords('invoice', HDR, { 'Invoice': '==' + nextNum }, { limit: 1 }).catch(() => ({ records: [] }));
+ if (dup.records.length) continue;
+ const res = await fm.createRecord('invoice', HDR, { 'Invoice': String(nextNum), ...sharedHeader }, { dryRun: false }).catch(() => null);
+ if (res) { id = res.recordId; createdInv = nextNum; nextNum++; }
+ }
+ if (!id) throw new Error('could not allocate an invoice number');
+
+ // header extras (same on every record)
+ await patch(id, 'SALES PERSON', 'Otto');
+ await patch(id, 'BUYER', name);
+ await patch(id, 'Resale #', resale(s));
+ try { await fm.updateRecord('invoice', RESALE_LAYOUT, id, { 'Resale card lookup': resale(s) }, { dryRun: false }); } catch {}
+ await patch(id, 'check Number', today());
+ // tax + shipping ride the money record only
+ await patch(id, 'SHIP VIA', isFirst ? shipVia : '');
+ 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 || '');
+
+ // 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 {}
+ 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;
+ await patch(id, 'PAID ON ACCOUNT', grand);
+ await patch(id, 'Notes', isFirst
+ ? `Imported by Otto — ${stamp()}`
+ : `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 });
}
- if (!id) throw new Error('could not allocate an invoice number');
-
- // header extras
- await patch(id, 'SALES PERSON', 'Otto');
- await patch(id, 'BUYER', name);
- await patch(id, 'Resale #', resale(s));
- // 'Resale card lookup' is the resale field actually shown on the invoice (writable, on PROFORMA)
- try { await fm.updateRecord('invoice', RESALE_LAYOUT, id, { 'Resale card lookup': resale(s) }, { dryRun: false }); } catch {}
- await patch(id, 'SHIP VIA', shipVia);
- await patch(id, 'SHIPPING HANDLING', shipping);
- await patch(id, 'SALES TAX YN', orderTax > 0 ? 'Y' : 'N');
- await patch(id, 'check Number', today());
-
- // Consolidate to ONE billable line (line rep 1) — the legacy invoice's
- // MERCHANDISE TOTAL reliably reflects only rep 1, so a single line keeps the
- // total exact. Same unit price -> Qty = total units × that price; mixed
- // prices -> Qty 1 × merchandise subtotal. SKUs listed in the description.
- const merch = r2(items.reduce((a, li) => a + (Number(li.price) || 0) * (li.quantity || 1), 0));
- const totalQty = items.reduce((a, li) => a + (li.quantity || 1), 0);
- const skus = items.map((li) => baseSku(li.sku));
- const samePrice = new Set(items.map((li) => r2(li.price))).size === 1;
- const qty = samePrice ? totalQty : 1;
- const unitPrice = samePrice ? r2(items[0].price) : merch;
- const desc = items.length === 1
- ? `${skus[0]} — ${items[0].name || ''}`.trim()
- : `${items.length} items: ${skus.join(', ')}`;
- await patch(id, 'DETAIL 1(1)', desc.slice(0, 250));
- await patch(id, 'Q1(1)', qty);
- await patch(id, 'Unit 1(1)', uom(items[0] || {}));
- await patch(id, 'RETAIL 1(1)', unitPrice);
- await patch(id, 'NET 1(1)', unitPrice);
- await patch(id, 'Sidemark 1(1)', s.company || '');
-
- // cross-table fields (different base-table layouts; share the record id)
- try { await fm.updateRecord('invoice', SKU_LAYOUT, id, { 'JS Easy SKU #': baseSku(primarySku) }, { dryRun: false }); } catch {}
- try { await fm.updateRecord('invoice', WCPAT_LAYOUT, id, { 'wc pattern': wcPattern(primarySku) }, { dryRun: false }); } catch {}
- // DW SKU = base pattern SKU of the primary line (e.g. DWCC-112200-Per Yard -> DWCC-112200).
- // Writable Text[4] field; bare name targets repetition 1. Resilient — never aborts the invoice.
- try { await fm.updateRecord('invoice', DWSKU_LAYOUT, id, { [DWSKU_FIELD]: dwSku(primarySku) }, { dryRun: false }); } catch {}
-
- // paid-in-full = computed grand total (rounded to cents) -> Net Due 0
- const grand = r2((await fm.getRecord('invoice', HDR, id))?.fieldData?.['GRAND TOTAL'] || 0);
- await patch(id, 'PAID ON ACCOUNT', grand);
- await patch(id, 'Notes', `Imported by Otto — ${stamp()}`);
-
- return { invoiceNumber: String(createdInv), recordId: id, grandTotal: grand,
- cust_po: order.order_number, account: accountNumber };
+
+ return { invoiceNumber: String(firstInvoiceNumber), recordId: firstId, grandTotal: firstGrand,
+ cust_po: order.order_number, account: accountNumber, invoiceCount: created.length, invoices: created };
}
← f903c3b auto-save: 2026-07-07T08:57:11 (2 files) — scripts/tmp-probe
·
back to Filemaker Mcp
·
invoicing: fix double-increment so per-SKU invoice numbers a 4dd914e →