← back to Filemaker Mcp
add ensure-wallpaper-for-order: auto-complete FileMaker WALLPAPER master from dw_unified
01d9bffff2e3bef09564f1d436ff8524589cbc10 · 2026-07-10 08:07:50 -0700 · Steve Abrams
When invoicing (esp. samples), a SKU with no width/name/vid/supplier means the
FileMaker WALLPAPER master record is missing/incomplete → this creates/completes
it from dw_unified (shopify_products.metafields rich source, connie fallback):
create stored fields on 'Add wallcovering' (Mfr Pattern/JS Pattern/Series/
Supplier/Width; combo sku is a calc), then Name/Color of Pattern + vid (series-mode
code) + JPG Name on '*List Wallpapers - Full View'. Direct-writes invoice VID +
Detail 1 Mfr Number (copy-lookups) so a redo shows them. SKUs with no mfr number
are flagged, not created. Redid #32575 (Lee Jofa Taplow x4) + #32574 (DWTT70647;
DWLK830880 flagged).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A scripts/ensure-wallpaper-for-order.mjs
Diff
commit 01d9bffff2e3bef09564f1d436ff8524589cbc10
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Fri Jul 10 08:07:50 2026 -0700
add ensure-wallpaper-for-order: auto-complete FileMaker WALLPAPER master from dw_unified
When invoicing (esp. samples), a SKU with no width/name/vid/supplier means the
FileMaker WALLPAPER master record is missing/incomplete → this creates/completes
it from dw_unified (shopify_products.metafields rich source, connie fallback):
create stored fields on 'Add wallcovering' (Mfr Pattern/JS Pattern/Series/
Supplier/Width; combo sku is a calc), then Name/Color of Pattern + vid (series-mode
code) + JPG Name on '*List Wallpapers - Full View'. Direct-writes invoice VID +
Detail 1 Mfr Number (copy-lookups) so a redo shows them. SKUs with no mfr number
are flagged, not created. Redid #32575 (Lee Jofa Taplow x4) + #32574 (DWTT70647;
DWLK830880 flagged).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
scripts/ensure-wallpaper-for-order.mjs | 106 +++++++++++++++++++++++++++++++++
1 file changed, 106 insertions(+)
diff --git a/scripts/ensure-wallpaper-for-order.mjs b/scripts/ensure-wallpaper-for-order.mjs
new file mode 100644
index 0000000..6a17838
--- /dev/null
+++ b/scripts/ensure-wallpaper-for-order.mjs
@@ -0,0 +1,106 @@
+// Ensure a complete FileMaker WALLPAPER master record exists for every SKU on an
+// invoice, so the invoice's lookup fields (width, name/JPG Name, vid, supplier,
+// mfr#) resolve. Sources data from dw_unified (shopify_products.metafields is the
+// rich source; connie_our_catalog fallback). Steve's rule (2026-07-10): the
+// WALLPAPER "Mfr Pattern" MUST hold a real mfr number, and "vid" MUST be the
+// vendor's vid code. A SKU with no findable mfr number is flagged, not created.
+//
+// node scripts/ensure-wallpaper-for-order.mjs <CustPO>
+import { readFileSync } from 'node:fs';
+import { execFileSync } from 'node:child_process';
+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 fm = await import('../src/fm-client.js');
+const PO = process.argv[2];
+if (!PO) { console.error('usage: ensure-wallpaper-for-order.mjs <CustPO>'); process.exit(1); }
+const ENTRY = 'Add wallcovering', FULL = '*List Wallpapers - Full View', ISHIP = 'Order Detail - Main - Shipping';
+
+const sql = (q) => { try { return JSON.parse(execFileSync('psql', ['dw_unified', '-tAc', q], { encoding: 'utf8' }).trim() || 'null'); } catch { return null; } };
+const firstNum = (s) => { const m = String(s || '').match(/[\d.]+/); return m ? m[0] : ''; };
+const widthClean = (s) => { const n = firstNum(s); return n ? `${n}"` : ''; };
+
+// combo sku (DWKK134979) -> {prefix, num, dashSku}
+function parseCombo(combo) {
+ const m = String(combo).match(/^([A-Za-z]+)(\d.*)$/);
+ return m ? { prefix: m[1].toUpperCase(), num: m[2], dashSku: `${m[1].toUpperCase()}-${m[2]}` } : null;
+}
+
+// pull source fields for a dashed dw_sku from dw_unified (metafields-first)
+function sourceFor(dashSku) {
+ const esc = dashSku.replace(/'/g, "''");
+ // metafields values are nested {type,value} objects -> always read ->'key'->>'value'
+ const row = sql(`SELECT json_build_object(
+ 'mfr', COALESCE(NULLIF(mfr_sku,''), metafields->'custom'->'manufacturer_sku'->>'value', metafields->'global'->'mfr-pattern-number'->>'value'),
+ 'supplier', COALESCE(NULLIF(supplier_name,''), metafields->'dwc'->'real_vendor'->>'value', vendor),
+ 'pattern', COALESCE(NULLIF(pattern_name,''), metafields->'global'->'title'->>'value'),
+ 'color', COALESCE(metafields->'custom'->'color'->>'value', metafields->'global'->'Color-of-Pattern'->>'value', metafields->'dwc'->'color'->>'value'),
+ 'width', COALESCE(metafields->'global'->'width'->>'value', metafields->'dwc'->'width'->>'value'),
+ 'prefix', vendor_prefix
+ ) FROM shopify_products
+ WHERE dw_sku ILIKE '${esc}' OR dw_sku ILIKE '${esc}-SAMPLE' OR dw_sku ILIKE '${esc}-Sample'
+ ORDER BY (dw_sku='${esc}') DESC LIMIT 1`);
+ if (row && row.mfr) return { ...row, src: 'shopify_products' };
+ // fallback: connie_our_catalog (often mfr-less)
+ const c = sql(`SELECT json_build_object('mfr', NULLIF(mfr_sku,''), 'supplier', vendor, 'color', color_primary, 'prefix', vendor_prefix)
+ FROM connie_our_catalog WHERE dw_sku ILIKE '${esc}%' LIMIT 1`);
+ return c ? { ...c, src: 'connie_our_catalog' } : null;
+}
+
+// vendor vid code = the most common vid among existing WALLPAPER records of this Series
+const vidCache = {};
+async function vidForSeries(series) {
+ if (series in vidCache) return vidCache[series];
+ const r = await fm.findRecords('WALLPAPER', FULL, [{ Series: series, vid: '*' }], { limit: 25 }).catch(() => ({ records: [] }));
+ const counts = {};
+ for (const rec of r.records) { const v = (rec.fieldData.vid || '').trim(); if (v) counts[v] = (counts[v] || 0) + 1; }
+ const vid = Object.entries(counts).sort((a, b) => b[1] - a[1])[0]?.[0] || '';
+ return (vidCache[series] = vid);
+}
+
+// read the order's line SKUs
+const { records } = await fm.findRecords('invoice', ISHIP, { 'Cust PO': '==' + PO }, { limit: 30, sort: [{ fieldName: 'Invoice', sortOrder: 'ascend' }] });
+const combos = [...new Set(records.map((r) => (r.fieldData['WC Pattern Number AKA DW SKU(1)'] || r.fieldData['WC Pattern Number AKA DW SKU'] || '').trim()).filter(Boolean))];
+console.log(`invoice #${PO}: ${combos.length} unique SKU(s): ${combos.join(', ')}`);
+
+const flagged = [];
+for (const combo of combos) {
+ const p = parseCombo(combo);
+ if (!p) { console.log(` ${combo}: unparseable — SKIP`); continue; }
+ const s = sourceFor(p.dashSku);
+ if (!s || !s.mfr) { flagged.push(combo); console.log(` ${combo}: NO MFR NUMBER found (${s ? s.src : 'not in dw_unified'}) — FLAGGED, not created`); continue; }
+ const vid = await vidForSeries(p.prefix);
+ const name = s.pattern || '', color = s.color || '', width = widthClean(s.width);
+ const jpg = [name, color].filter(Boolean).join(' - ');
+ // create if missing, else complete
+ const ex = await fm.findRecords('WALLPAPER', FULL, { 'combo sku': '==' + combo }, { limit: 1 }).catch(() => ({ records: [] }));
+ let id = ex.records[0]?.recordId;
+ if (!id) {
+ const res = await fm.createRecord('WALLPAPER', ENTRY, { 'Mfr Pattern': s.mfr, 'JS Pattern': p.num, Series: p.prefix, Supplier: s.supplier || '', Width: width }, { dryRun: false }).catch((e) => ({ err: e.fmCode }));
+ if (res.err) { console.log(` ${combo}: CREATE FAIL ${res.err}`); continue; }
+ id = res.recordId;
+ } else {
+ for (const [k, v] of [['Mfr Pattern', s.mfr], ['Supplier', s.supplier || ''], ['Width', width]]) { try { await fm.updateRecord('WALLPAPER', ENTRY, id, { [k]: v }, { dryRun: false }); } catch {} }
+ }
+ for (const [k, v] of [['Name of Pattern', name], ['Color of Pattern', color], ['vid', vid], ['JPG Name', jpg]]) {
+ if (v) { try { await fm.updateRecord('WALLPAPER', FULL, id, { [k]: v }, { dryRun: false }); } catch (e) { console.log(` ${combo} ${k} err ${e.fmCode}`); } }
+ }
+ // vid + mfr# are copy-lookups on the invoice line (blank until re-looked-up);
+ // write them directly onto every line record of THIS sku so the invoice shows them.
+ for (const rec of records) {
+ const rf = rec.fieldData; if ((rf['WC Pattern Number AKA DW SKU(1)'] || rf['WC Pattern Number AKA DW SKU'] || '').trim() !== combo) continue;
+ try { await fm.updateRecord('invoice', ISHIP, rec.recordId, { 'VID': vid, 'Detail 1 Mfr Number': s.mfr }, { dryRun: false }); } catch (e) { console.log(` ${combo} invoice line err ${e.fmCode}`); }
+ }
+ console.log(` ${combo}: OK mfr=${s.mfr} supplier=${s.supplier} name="${name}" color="${color}" width=${width} vid=${vid} [${s.src}]`);
+}
+
+// verify
+console.log(`\n=== VERIFY invoice #${PO} ===`);
+const v = await fm.findRecords('invoice', ISHIP, { 'Cust PO': '==' + PO }, { limit: 30, sort: [{ fieldName: 'Invoice', sortOrder: 'ascend' }] });
+for (const rec of v.records) { const f = rec.fieldData; const g = (b) => f[b + '(1)'] ?? f[b] ?? '';
+ console.log(` #${f['Invoice']} sku="${g('WC Pattern Number AKA DW SKU')}" width="${f['detail1 dash width'] || ''}" supplier="${g('wc supplier lookup')}" name="${f['wallpaper 2::JPG Name'] || ''}" vid="${g('VID')}" mfr="${g('Detail 1 Mfr Number')}"`);
+}
+if (flagged.length) console.log(`\nFLAGGED (no mfr number — need manual): ${flagged.join(', ')}`);
← 5703973 invoice: all money on FIRST invoice, just the SKU on each su
·
back to Filemaker Mcp
·
add lib/wallpaper.js — importable ensureWallpaper() for pipe ea6b092 →