[object Object]

← back to Filemaker Mcp

wallpaper: resolve mfr# from flat metafields + vendor_catalog/registry tiers

4d2ed048ac6e65aa442fde4801d079b6aa99802b · 2026-07-10 11:17:40 -0700 · Steve Abrams

Invoices 102660 (DWBR170698) and 102662 (DWDX220241) had blank vid+mfr#
because their WALLPAPER master records were never created — ensureWallpaper's
sourceFor() couldn't find a mfr number:
- DWDX (Designtex): mfr lived in a FLAT metafield (custom.manufacturer_sku
  = '8405252'), but SP_FIELDS only read the nested {value} shape.
- DWBR (Malibu private-label): absent from shopify_products entirely
  (dw_sku null); real mfr 2999-39028 lives in vendor_catalog + dw_sku_registry.

Fixes:
- SP_FIELDS now COALESCEs nested (->'k'->>'value') AND flat (->>'k') metafields
  for mfr/supplier/pattern/color/width.
- sourceFor() adds two universal fallback tiers: vendor_catalog (all scraped
  vendors, joined to dw_sku_registry for a clean supplier name) and
  dw_sku_registry (mfr+vendor backstop).

Ran ensure-wallpaper-for-order.mjs 32580: all 10 lines now carry vid+mfr#.

Files touched

Diff

commit 4d2ed048ac6e65aa442fde4801d079b6aa99802b
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Fri Jul 10 11:17:40 2026 -0700

    wallpaper: resolve mfr# from flat metafields + vendor_catalog/registry tiers
    
    Invoices 102660 (DWBR170698) and 102662 (DWDX220241) had blank vid+mfr#
    because their WALLPAPER master records were never created — ensureWallpaper's
    sourceFor() couldn't find a mfr number:
    - DWDX (Designtex): mfr lived in a FLAT metafield (custom.manufacturer_sku
      = '8405252'), but SP_FIELDS only read the nested {value} shape.
    - DWBR (Malibu private-label): absent from shopify_products entirely
      (dw_sku null); real mfr 2999-39028 lives in vendor_catalog + dw_sku_registry.
    
    Fixes:
    - SP_FIELDS now COALESCEs nested (->'k'->>'value') AND flat (->>'k') metafields
      for mfr/supplier/pattern/color/width.
    - sourceFor() adds two universal fallback tiers: vendor_catalog (all scraped
      vendors, joined to dw_sku_registry for a clean supplier name) and
      dw_sku_registry (mfr+vendor backstop).
    
    Ran ensure-wallpaper-for-order.mjs 32580: all 10 lines now carry vid+mfr#.
---
 lib/wallpaper.js | 41 +++++++++++++++++++++++++++++++++++------
 1 file changed, 35 insertions(+), 6 deletions(-)

diff --git a/lib/wallpaper.js b/lib/wallpaper.js
index 1155ed4..05394b1 100644
--- a/lib/wallpaper.js
+++ b/lib/wallpaper.js
@@ -26,13 +26,26 @@ function parseCombo(combo) {
   return m ? { prefix: m[1].toUpperCase(), num: m[2], dashSku: `${m[1].toUpperCase()}-${m[2]}` } : null;
 }
 
-// shopify_products SELECT expression (metafields-first) — reused by both lookups
+// shopify_products SELECT expression (metafields-first) — reused by both lookups.
+// Metafields come in TWO shapes: nested {type,value} objects AND flat scalar strings
+// (e.g. Designtex rows store "manufacturer_sku":"8405252" directly). Read both:
+// ->'k'->>'value' catches nested, ->>'k' catches flat.
 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'),
+      'mfr', COALESCE(NULLIF(mfr_sku,''),
+             metafields->'custom'->'manufacturer_sku'->>'value', metafields->'custom'->>'manufacturer_sku',
+             metafields->'global'->'mfr-pattern-number'->>'value', metafields->'global'->>'mfr-pattern-number',
+             metafields->'global'->'manufacturer_sku'->>'value', metafields->'global'->>'manufacturer_sku'),
+      'supplier', COALESCE(NULLIF(supplier_name,''),
+             metafields->'dwc'->'real_vendor'->>'value', metafields->'dwc'->>'real_vendor', vendor),
+      'pattern', COALESCE(NULLIF(pattern_name,''),
+             metafields->'custom'->'pattern_name'->>'value', metafields->'custom'->>'pattern_name',
+             metafields->'global'->'title'->>'value', metafields->'global'->>'pattern_name'),
+      'color', COALESCE(metafields->'custom'->'color'->>'value', metafields->'custom'->>'color',
+             metafields->'global'->'Color-of-Pattern'->>'value',
+             metafields->'dwc'->'color'->>'value', metafields->'dwc'->>'color'),
+      'width', COALESCE(metafields->'global'->'width'->>'value', metafields->'global'->>'width',
+             metafields->'dwc'->'width'->>'value', metafields->'dwc'->>'width',
+             metafields->'specs'->>'width'),
       'canonical', dw_sku)`;
 
 // pull source fields for a dashed dw_sku from dw_unified (metafields-first).
@@ -47,6 +60,22 @@ function sourceFor(dashSku) {
   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)' };
+  // (3) universal vendor_catalog (all scraped vendors, keyed on dw_sku) — the authoritative
+  // source for private-label lines absent from shopify_products (e.g. DWBR/Malibu→Brewster).
+  // Join dw_sku_registry for a clean supplier name (vendor_code is a slug like 'brewster_york').
+  row = sql(`SELECT json_build_object(
+      'mfr', NULLIF(vc.mfr_sku,''),
+      'supplier', COALESCE(NULLIF(reg.vendor_name,''), NULLIF(vc.original_vendor_name,'')),
+      'pattern', NULLIF(vc.pattern_name,''),
+      'color', COALESCE(NULLIF(vc.color_name,''), NULLIF(vc.color_primary,'')),
+      'width', COALESCE(NULLIF(vc.width,''), NULLIF(vc.width_inches::text,'')))
+    FROM vendor_catalog vc LEFT JOIN dw_sku_registry reg ON reg.dw_sku = vc.dw_sku
+    WHERE vc.dw_sku ILIKE '${esc}' AND NULLIF(vc.mfr_sku,'') IS NOT NULL LIMIT 1`);
+  if (row && row.mfr) return { ...row, src: 'vendor_catalog' };
+  // (4) dw_sku_registry — authoritative mfr + vendor_name backstop (no pattern/color/width)
+  row = sql(`SELECT json_build_object('mfr', NULLIF(mfr_sku,''), 'supplier', NULLIF(vendor_name,''))
+    FROM dw_sku_registry WHERE dw_sku ILIKE '${esc}' AND NULLIF(mfr_sku,'') IS NOT NULL LIMIT 1`);
+  if (row && row.mfr) return { ...row, src: 'dw_sku_registry' };
   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;

← e138cce invoice: wire WALLPAPER auto-complete into the live daemon p  ·  back to Filemaker Mcp  ·  invoice: flag+notify (not swallow) SKUs with no findable mfr f8ad188 →