← back to Sample Followup Sweep
sample-followup: drop DW ref# column, add explicit WIN override; scope drafts tight
0a260b3771acfb1bd31c279523eb9c2a51070e7c · 2026-09-01 11:39:11 -0700 · Steve
Steve 9/01: (1) the letter no longer shows our internal DW reference number
(combo sku / DW###) — each row is now Date Ordered + Manufacturer # (the vendor
own SKU) only. (2) scheduled-run honors an explicit WIN env override for exact
date windows. Regenerated the review drafts scoped to the 08/17-08/20 order-date
window ONLY (7 drafts) instead of the wide 10-60d backlog. resolve-original-dates
is a read-only verifier that showed email-date matching is unreliable (SKU
collisions -> 2024/2025 dates), so FileMaker order dates (correct for the scoped
window) are used.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M lib/compose.jsA scripts/resolve-original-dates.mjsM scripts/scheduled-run.mjs
Diff
commit 0a260b3771acfb1bd31c279523eb9c2a51070e7c
Author: Steve <steve@designerwallcoverings.com>
Date: Tue Sep 1 11:39:11 2026 -0700
sample-followup: drop DW ref# column, add explicit WIN override; scope drafts tight
Steve 9/01: (1) the letter no longer shows our internal DW reference number
(combo sku / DW###) — each row is now Date Ordered + Manufacturer # (the vendor
own SKU) only. (2) scheduled-run honors an explicit WIN env override for exact
date windows. Regenerated the review drafts scoped to the 08/17-08/20 order-date
window ONLY (7 drafts) instead of the wide 10-60d backlog. resolve-original-dates
is a read-only verifier that showed email-date matching is unreliable (SKU
collisions -> 2024/2025 dates), so FileMaker order dates (correct for the scoped
window) are used.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
lib/compose.js | 11 +++---
scripts/resolve-original-dates.mjs | 72 ++++++++++++++++++++++++++++++++++++++
scripts/scheduled-run.mjs | 1 +
3 files changed, 78 insertions(+), 6 deletions(-)
diff --git a/lib/compose.js b/lib/compose.js
index fff9a4a..9f9ca2a 100644
--- a/lib/compose.js
+++ b/lib/compose.js
@@ -10,12 +10,12 @@ function compose(vendor, rows) {
const account = vendor.account_number || '‹CONFIRM DW ACCOUNT #›';
// Steve 8/20: fall back to the vendor's main email when no sample email exists.
const to = vendor.sample_email || vendor.main_email || '‹CONFIRM SAMPLE EMAIL›';
- // Steve 8/20: each memo line shows the DATE WE ORDERED it + a REFERENCE # (our DW order
- // number) alongside the manufacturer #, so the supplier can locate + quote each item easily.
+ // Steve 9/01: show ONLY the DATE WE ORDERED + the MANUFACTURER # (the vendor's own SKU).
+ // Do NOT show our internal DW reference number ("combo sku" / DW###) — it's meaningless to the
+ // supplier and must never be presented as the item's SKU.
const rowsHtml = rows.map(r => `<tr style="border-bottom:1px solid #eee">
-<td style="padding:5px 18px 5px 0;white-space:nowrap"><strong>${esc(r.sku || '—')}</strong></td>
<td style="padding:5px 18px 5px 0;white-space:nowrap">${esc(r.requested || '—')}</td>
-<td style="padding:5px 0">${esc(r.mfr)}</td>
+<td style="padding:5px 0"><strong>${esc(r.mfr || '—')}</strong></td>
</tr>`).join('\n');
const subject = `Sample Follow-Up — Outstanding Memos (Acct ${account}) — Designer Wallcoverings`;
@@ -26,10 +26,9 @@ function compose(vendor, rows) {
Hopefully we removed any disco items internally from this list before asking again.</p>
<hr style="border:none;border-top:1px solid #ccc">
<p><strong>Our account number is ${esc(account)}</strong></p>
-<p>We ordered 1 memo sample of each item below. The <strong>Ref #</strong> is our order reference — please quote it when you reply about an item.</p>
+<p>We ordered 1 memo sample of each item below — please quote the Manufacturer # when you reply about an item.</p>
<table style="border-collapse:collapse;font-size:14px;margin:8px 0">
<tr style="text-align:left;border-bottom:2px solid #333">
-<th style="padding:5px 18px 5px 0">Ref #</th>
<th style="padding:5px 18px 5px 0">Date Ordered</th>
<th style="padding:5px 0">Manufacturer #</th>
</tr>
diff --git a/scripts/resolve-original-dates.mjs b/scripts/resolve-original-dates.mjs
new file mode 100644
index 0000000..0c7c716
--- /dev/null
+++ b/scripts/resolve-original-dates.mjs
@@ -0,0 +1,72 @@
+#!/usr/bin/env node
+// READ-ONLY verifier — for each outstanding memo item, resolve the ORIGINAL "New Sample Request"
+// email date (from info@ Sent) by matching the item's manufacturer SKU (Mfr Pattern) in the email
+// body, and print it next to the FileMaker `today for client` date so we can judge match quality
+// BEFORE rewriting the customer-facing follow-up letter. Creates/sends NOTHING.
+import { readFileSync } from 'node:fs';
+import { homedir } from 'node:os';
+import http from 'node:http';
+
+const cfg = JSON.parse(readFileSync(homedir() + '/.claude.json', 'utf8'));
+const fenv = cfg?.mcpServers?.filemaker?.env || {};
+for (const k of ['FM_CLOUD_HOST', 'FM_CLARIS_EMAIL', 'FM_CLARIS_PASSWORD']) process.env[k] = fenv[k];
+process.env.FM_READONLY = '1';
+const fm = await import('/Users/macstudio3/Projects/filemaker-mcp/src/fm-client.js');
+
+const g = (k) => { for (const f of ['/Users/macstudio3/Projects/Designer-Wallcoverings/DW-MCP/.env', '/Users/macstudio3/Projects/george-gmail/.env']) { try { const m = readFileSync(f, 'utf8').match(new RegExp('^' + k + '=(.+)$', 'm')); if (m) return m[1].trim(); } catch {} } return ''; };
+let bauth = g('GEORGE_BASIC_AUTH'); if (!bauth.includes(':')) bauth = 'admin:' + g('GEORGE_BASIC_AUTH_PASS');
+const A = 'Basic ' + Buffer.from(bauth).toString('base64');
+const george = (p) => new Promise((res) => { http.get({ host: '127.0.0.1', port: 9850, path: p, headers: { Authorization: A } }, (x) => { let b = ''; x.on('data', (d) => b += d); x.on('end', () => { try { res(JSON.parse(b)); } catch { res({}); } }); }).on('error', () => res({})); });
+
+const WIN = process.env.WIN || '07/03/2026...08/22/2026';
+const pad = (n) => String(n).padStart(2, '0');
+const fmt = (d) => `${pad(d.getMonth() + 1)}/${pad(d.getDate())}/${d.getFullYear()}`;
+// normalize for SKU containment: lowercase, keep only alnum + . - /
+const normKey = (s) => String(s || '').toLowerCase().replace(/&[a-z#0-9]+;/gi, ' ').replace(/[^a-z0-9./-]+/g, '');
+// a SKU-like token from the mfr pattern (first run of >=5 chars of digits/dots/dashes/slashes)
+const skuToken = (s) => { const m = String(s || '').match(/[a-z0-9][a-z0-9.\/-]{4,}/i); return m ? normKey(m[0]) : ''; };
+
+// fetch all "New Sample Request" sent emails to a recipient → [{ms, bodyNorm, date}]
+async function requestEmails(recip) {
+ const q = encodeURIComponent(`in:sent to:${recip} subject:"New Sample Request"`);
+ const j = await george(`/api/messages?account=info&maxResults=60&q=${q}`);
+ const out = [];
+ for (const m of (j.messages || [])) {
+ const d = await george(`/api/messages/${m.id}?account=info`);
+ const body = normKey((d.body || '').replace(/<[^>]+>/g, ' '));
+ const ms = Date.parse(d.date || '') || 0;
+ out.push({ ms, date: d.date || '', bodyNorm: body });
+ }
+ return out.sort((a, b) => a.ms - b.ms); // earliest first
+}
+function originalDate(mfr, emails) {
+ const key = normKey(mfr), tok = skuToken(mfr);
+ for (const e of emails) { // earliest-first → first hit = original
+ if ((key.length >= 5 && e.bodyNorm.includes(key)) || (tok.length >= 5 && e.bodyNorm.includes(tok)))
+ return { date: fmt(new Date(e.ms)), how: 'email' };
+ }
+ return null;
+}
+
+// outstanding items in the window
+const A2 = (await fm.findRecords('WALLPAPER', 'REPORT ON SAMPLES ORDERED', { 'Date WP Sample Sent': '=', 'today for client': WIN }, { limit: 1200 })).records;
+// group by vid, keep vendor recipient from contacts/fleet
+const fleet = JSON.parse(readFileSync(new URL('../data/fleet.json', import.meta.url))).vendors;
+const contacts = JSON.parse(readFileSync(new URL('../data/contacts.json', import.meta.url)));
+const vidRecip = {}; for (const v of fleet) { const c = contacts[v.slug] || {}; const e = c.sample_email || c.main_email; if (e && !vidRecip[String(v.vid || '').toUpperCase()]) vidRecip[String(v.vid || '').toUpperCase()] = e; }
+
+const byVid = {};
+for (const r of A2) { const vid = String(r.fieldData.vid || '').toUpperCase(); if (!vid) continue; (byVid[vid] = byVid[vid] || []).push({ mfr: (r.fieldData['Mfr Pattern'] || '').trim(), fmDate: r.fieldData['today for client'] || '' }); }
+
+let hit = 0, miss = 0;
+for (const [vid, rows] of Object.entries(byVid)) {
+ const recip = vidRecip[vid]; if (!recip) continue;
+ const emails = await requestEmails(recip.split(/[,;]/)[0].trim());
+ console.log(`\n[${vid}] ${recip} (${emails.length} request-emails on file)`);
+ for (const row of rows) {
+ const od = originalDate(row.mfr, emails);
+ if (od) hit++; else miss++;
+ console.log(` mfr ${String(row.mfr).slice(0, 30).padEnd(30)} | FM ${row.fmDate.padEnd(11)} | email ${od ? od.date : 'NO MATCH → keep FM date'}`);
+ }
+}
+console.log(`\nmatched to an original email: ${hit} | no email match (fallback to FM date): ${miss}`);
diff --git a/scripts/scheduled-run.mjs b/scripts/scheduled-run.mjs
index 59297b8..c453105 100644
--- a/scripts/scheduled-run.mjs
+++ b/scripts/scheduled-run.mjs
@@ -108,6 +108,7 @@ function lastCoveredHi(today) {
return best;
}
function windowRange(today = new Date()) {
+ if (process.env.WIN) return process.env.WIN; // explicit override, e.g. "08/17/2026...08/20/2026"
if (CATCHUP_DAYS > 0) {
// Manual override — fixed rolling band [today-10-(N-1) .. today-10].
const hi = new Date(today); hi.setDate(hi.getDate() - MIN_AGE_DAYS);
← 4dfe1c2 auto-data-snapshot: 2026-09-01T11:38:20 (3 data files) — dat
·
back to Sample Followup Sweep
·
sample-followup: add vendor Ref/Tracking + Pattern-Name colu 70ec596 →