← back to Filemaker Mcp
wallpaper: resolve legacy DWLK sku -> canonical DWSW via shopify sku mapping
0c0536f4b0cf6457c164ec54aee15c3d1c579b9d · 2026-07-10 08:24:03 -0700 · Steve Abrams
DWLK is a legacy Schumacher prefix; shopify_products maps old sku
(DWLK-830880-Sample) -> canonical dw_sku (DWSW-5003662) + real mfr (5003662).
ensureWallpaper sourceFor now tries dw_sku, then legacy sku/variant_sku
(base-normalized), then connie. Also backfilled connie_our_catalog.mfr_sku for
912 DWLK rows from this mapping (backup: _bak_connie_dwlk_mfr_20260710; rollback
list: _filled_connie_dwlk_mfr_20260710; 50 orphans remain). Refactored
ensure-wallpaper-for-order.mjs to import lib/wallpaper.js. #32574 DWLK830880 now
resolves mfr 5003662.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M lib/wallpaper.jsM scripts/ensure-wallpaper-for-order.mjs
Diff
commit 0c0536f4b0cf6457c164ec54aee15c3d1c579b9d
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Fri Jul 10 08:24:03 2026 -0700
wallpaper: resolve legacy DWLK sku -> canonical DWSW via shopify sku mapping
DWLK is a legacy Schumacher prefix; shopify_products maps old sku
(DWLK-830880-Sample) -> canonical dw_sku (DWSW-5003662) + real mfr (5003662).
ensureWallpaper sourceFor now tries dw_sku, then legacy sku/variant_sku
(base-normalized), then connie. Also backfilled connie_our_catalog.mfr_sku for
912 DWLK rows from this mapping (backup: _bak_connie_dwlk_mfr_20260710; rollback
list: _filled_connie_dwlk_mfr_20260710; 50 orphans remain). Refactored
ensure-wallpaper-for-order.mjs to import lib/wallpaper.js. #32574 DWLK830880 now
resolves mfr 5003662.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
lib/wallpaper.js | 22 ++++++---
scripts/ensure-wallpaper-for-order.mjs | 89 +++++-----------------------------
2 files changed, 27 insertions(+), 84 deletions(-)
diff --git a/lib/wallpaper.js b/lib/wallpaper.js
index 15445f4..1155ed4 100644
--- a/lib/wallpaper.js
+++ b/lib/wallpaper.js
@@ -26,19 +26,27 @@ function parseCombo(combo) {
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, "''");
- const row = sql(`SELECT json_build_object(
+// shopify_products SELECT expression (metafields-first) — reused by both lookups
+const SP_FIELDS = `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')
- ) FROM shopify_products
+ 'width', COALESCE(metafields->'global'->'width'->>'value', metafields->'dwc'->'width'->>'value'),
+ 'canonical', dw_sku)`;
+
+// pull source fields for a dashed dw_sku from dw_unified (metafields-first).
+// Tries (1) canonical dw_sku, (2) LEGACY sku/variant_sku match (e.g. DWLK-830880
+// -> canonical DWSW-5003662) base-normalized, (3) connie_our_catalog fallback.
+function sourceFor(dashSku) {
+ const esc = dashSku.replace(/'/g, "''");
+ let row = sql(`SELECT ${SP_FIELDS} 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' };
+ if (row && row.mfr) return { ...row, src: 'shopify_products(dw_sku)' };
+ row = sql(`SELECT ${SP_FIELDS} FROM shopify_products
+ WHERE upper(regexp_replace(COALESCE(NULLIF(sku,''),variant_sku),'-Sample$','','i')) = upper('${esc}') AND mfr_sku<>'' LIMIT 1`);
+ if (row && row.mfr) return { ...row, src: 'shopify_products(legacy sku)' };
const c = sql(`SELECT json_build_object('mfr', NULLIF(mfr_sku,''), 'supplier', vendor, 'color', color_primary)
FROM connie_our_catalog WHERE dw_sku ILIKE '${esc}%' LIMIT 1`);
return c ? { ...c, src: 'connie_our_catalog' } : null;
diff --git a/scripts/ensure-wallpaper-for-order.mjs b/scripts/ensure-wallpaper-for-order.mjs
index 6a17838..45a58d7 100644
--- a/scripts/ensure-wallpaper-for-order.mjs
+++ b/scripts/ensure-wallpaper-for-order.mjs
@@ -1,13 +1,10 @@
-// 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.
+// Ensure a complete FileMaker WALLPAPER master record for every SKU on an invoice
+// (so the invoice's width/name/vid/supplier/mfr lookups resolve), then direct-write
+// the copy-lookup fields (VID + Detail 1 Mfr Number) onto each line. Uses
+// lib/wallpaper.js ensureWallpaper(). See memory filemaker-wallpaper-record-for-invoice.
//
// 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)), '..');
@@ -15,92 +12,30 @@ 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 { ensureWallpaper } = await import('../lib/wallpaper.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 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.
+ const wp = await ensureWallpaper(combo);
+ if (!wp.ok) { flagged.push(`${combo} (${wp.reason})`); console.log(` ${combo}: FLAGGED — ${wp.reason}`); continue; }
+ // vid + mfr# are copy-lookups on the invoice line — write them onto every line of THIS sku
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}`); }
+ try { await fm.updateRecord('invoice', ISHIP, rec.recordId, { 'VID': wp.vid, 'Detail 1 Mfr Number': wp.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}]`);
+ console.log(` ${combo}: OK mfr=${wp.mfr} supplier=${wp.supplier} name="${wp.name}" color="${wp.color}" width=${wp.width} vid=${wp.vid} [${wp.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(', ')}`);
+if (flagged.length) console.log(`\nFLAGGED: ${flagged.join(' | ')}`);
← ea6b092 add lib/wallpaper.js — importable ensureWallpaper() for pipe
·
back to Filemaker Mcp
·
invoice: wire WALLPAPER auto-complete into the live daemon p e138cce →