invoice: populate 'WC Pattern Number AKA DW SKU' from primary line base SKU

707cde7e6ea78519e9fe7e15e0609b4d63b979c2 · 2026-06-29 13:16:37 -0700 · Steve

Discovered exact field name (Text[4], repeating, writable) and wired it into
createInvoiceForOrder via the '1-order detail 1 Copy3' layout (shares the
header base table). Proven on invoice 102262 -> DWCC-112200.

Files touched

M lib/invoice.js

Diff

commit 707cde7e6ea78519e9fe7e15e0609b4d63b979c2
Author: Steve <steve@designerwallcoverings.com>
Date:   Mon Jun 29 13:16:37 2026 -0700

    invoice: populate 'WC Pattern Number AKA DW SKU' from primary line base SKU
    
    Discovered exact field name (Text[4], repeating, writable) and wired it into
    createInvoiceForOrder via the '1-order detail 1 Copy3' layout (shares the
    header base table). Proven on invoice 102262 -> DWCC-112200.
---
 lib/invoice.js | 29 +++++++++++++++++++++++++++++
 1 file changed, 29 insertions(+)

diff --git a/lib/invoice.js b/lib/invoice.js
index fa9ea6f..dc6c6c7 100644
--- a/lib/invoice.js
+++ b/lib/invoice.js
@@ -16,6 +16,11 @@ import * as fm from '../src/fm-client.js';
 const HDR = 'Invoice to Client Copy';
 const SKU_LAYOUT = 'Layout #47';        // exposes "JS Easy SKU #"
 const WCPAT_LAYOUT = '1-order detail 1 Copy3'; // exposes "wc pattern"
+// Steve's writable Text[4] field (repeating, maxRepeat 4 -> rep 1 written via the
+// bare field name). Shares the header base table via "1-order detail 1 Copy3".
+// NOT the calc fields "wc pattern no" / "wc pattern no2" / "wc pattern".
+const DWSKU_LAYOUT = '1-order detail 1 Copy3';
+const DWSKU_FIELD = 'WC Pattern Number AKA DW SKU';
 
 const today = () => { const d = new Date(); return `${String(d.getMonth()+1).padStart(2,'0')}/${String(d.getDate()).padStart(2,'0')}/${d.getFullYear()}`; };
 const stamp = () => new Date().toLocaleString('en-US', { month:'short', day:'numeric', year:'numeric', hour:'numeric', minute:'2-digit' });
@@ -53,6 +58,27 @@ async function patch(id, field, value) {
 }
 const MAX_LINES = 4; // DETAIL 1 / Q1 / NET 1 family caps at maxRepeat 4
 
+// Create one no-dollar tracking record per SKU in the order, with the DW SKU in
+// the 'wc pattern no' field (which looks up the pattern Name/Color). Separate
+// from the billing invoice. Returns the created SKUs.
+const WCPATNO_LAYOUT = '1-order detail 1 Copy3'; // has 'wc pattern no', shares header base table
+export async function createSkuRecords(order, accountNumber, clientName, { commit = true } = {}) {
+  const items = order.line_items || [];
+  const skus = items.map((li) => wcPattern(li.sku));
+  if (!commit) return { wouldCreate: skus.length, skus };
+  const created = [];
+  for (const sku of skus) {
+    const res = await fm.createRecord('invoice', HDR, {
+      'Cust PO': String(order.order_number), 'Account #': String(accountNumber || ''),
+      'SOLD NAME': clientName || '', 'Date': today(),
+    }, { dryRun: false }).catch(() => null);
+    if (!res) continue;
+    try { await fm.updateRecord('invoice', WCPATNO_LAYOUT, res.recordId, { 'wc pattern no': sku }, { dryRun: false }); } catch {}
+    created.push({ recordId: res.recordId, sku });
+  }
+  return { created, skus };
+}
+
 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();
@@ -115,6 +141,9 @@ export async function createInvoiceForOrder(order, accountNumber, { commit = tru
   // 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]: baseSku(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);

← 7525e58 fix: consolidate multi-item orders to single line (exact tot · back to Filemaker Mcp · sync: every order also creates one no-$ per-SKU tracking rec ea51d97 →