← back to Filemaker Mcp
add read-only dup-invoice audit: group by Cust PO, split double-billing (money) vs $0 tracking clutter
6a8406b30e608b6ee442eb9dc233867ec74fb446 · 2026-07-07 07:15:26 -0700 · Steve Abrams
Files touched
A scripts/audit-dup-invoices.mjs
Diff
commit 6a8406b30e608b6ee442eb9dc233867ec74fb446
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jul 7 07:15:26 2026 -0700
add read-only dup-invoice audit: group by Cust PO, split double-billing (money) vs $0 tracking clutter
---
scripts/audit-dup-invoices.mjs | 115 +++++++++++++++++++++++++++++++++++++++++
1 file changed, 115 insertions(+)
diff --git a/scripts/audit-dup-invoices.mjs b/scripts/audit-dup-invoices.mjs
new file mode 100644
index 0000000..1b6941f
--- /dev/null
+++ b/scripts/audit-dup-invoices.mjs
@@ -0,0 +1,115 @@
+// READ-ONLY audit: find Shopify orders that got MORE THAN ONE FileMaker invoice
+// (the mac2 split-brain / old-code per-SKU "second invoice" duplication).
+// Groups invoices by `Cust PO` (= Shopify order_number). No writes, no deletes.
+//
+// node scripts/audit-dup-invoices.mjs # last 21 days
+// node scripts/audit-dup-invoices.mjs 60 # last 60 days
+import { findRecords } from '../src/fm-client.js';
+import { loadEnv, num, money } from '../lib/fm-script-helpers.js';
+
+loadEnv(import.meta.url);
+
+const HDR = 'Invoice to Client Copy';
+const DAYS = parseInt(process.argv[2] || '21', 10);
+
+// Build a MM/DD/YYYY...MM/DD/YYYY FileMaker date range for the window.
+const d = new Date();
+const end = new Date(d);
+const start = new Date(d); start.setDate(start.getDate() - DAYS);
+const fmDate = (x) => `${String(x.getMonth() + 1).padStart(2, '0')}/${String(x.getDate()).padStart(2, '0')}/${x.getFullYear()}`;
+const range = `${fmDate(start)}...${fmDate(end)}`;
+
+async function main() {
+ // Pull every invoice in the window that carries a Shopify order number (Cust PO).
+ let records = [];
+ try {
+ ({ records } = await findRecords('invoice', HDR, [{ 'Date': range, 'Cust PO': '*' }],
+ { limit: 2000, sort: [{ fieldName: 'Invoice', sortOrder: 'ascend' }] }));
+ } catch (e) {
+ if (e.fmCode === '401') { console.log(`No invoices with a Cust PO in the last ${DAYS} days.`); return; }
+ throw e;
+ }
+
+ // Group by Cust PO (Shopify order number). Only REAL order numbers — a Shopify
+ // order_number is a 4-6 digit int; skip blanks, "NA", "0", freight refs, etc.,
+ // which otherwise falsely lump unrelated invoices together.
+ const byOrder = new Map();
+ for (const r of records) {
+ const f = r.fieldData || {};
+ const po = String(f['Cust PO'] || '').trim().replace(/^#+/, '');
+ if (!/^\d{4,6}$/.test(po)) continue;
+ const inv = String(f['Invoice'] || '').trim();
+ if (!/^\d+$/.test(inv) || inv === '999999') continue;
+ const row = {
+ inv: parseInt(inv, 10),
+ total: num(f['GRAND TOTAL 2'] ?? f['PAID ON ACCOUNT']),
+ name: String(f['SOLD NAME'] || f['Sold Company Name Lookup'] || '').trim(),
+ date: String(f['Date'] || '').trim(),
+ item: String(f['DETAIL 1(1)'] || f['DETAIL 1'] || '').trim().slice(0, 60),
+ dwsku: String(f['WC Pattern Number AKA DW SKU'] || '').trim(),
+ };
+ if (!byOrder.has(po)) byOrder.set(po, []);
+ byOrder.get(po).push(row);
+ }
+
+ // Keep only orders with >1 invoice, then classify each order.
+ // keeper = the single largest positive-total invoice (the real bill)
+ // zeroOrphans = $0.00 per-SKU tracking records (createSkuRecords clutter; no $ impact)
+ // doubleBill = a SECOND positive-nonzero, non-refund invoice (real double-count)
+ // refunds/legit= negative totals (shipping/return) — left alone
+ const isRefund = (r) => r.total < 0 || /refund/i.test(r.item);
+ const orders = [...byOrder.entries()]
+ .filter(([, rows]) => rows.length > 1)
+ .map(([po, rows]) => {
+ rows.sort((a, b) => a.inv - b.inv);
+ const positives = rows.filter((r) => r.total > 0 && !isRefund(r));
+ const maxTotal = positives.length ? Math.max(...positives.map((r) => r.total)) : 0;
+ const keeper = positives.filter((r) => r.total === maxTotal).sort((a, b) => a.inv - b.inv)[0];
+ for (const r of rows) {
+ if (isRefund(r)) r.role = 'legit(refund)';
+ else if (keeper && r.inv === keeper.inv) r.role = 'KEEP';
+ else if (r.total === 0) r.role = 'orphan($0)';
+ else r.role = 'DOUBLE-BILL';
+ }
+ return { po, name: rows[0].name, rows, hasDoubleBill: rows.some((r) => r.role === 'DOUBLE-BILL') };
+ })
+ .sort((a, b) => Math.min(...a.rows.map((r) => r.inv)) - Math.min(...b.rows.map((r) => r.inv)));
+
+ if (!orders.length) {
+ console.log(`✓ No duplicated orders found in the last ${DAYS} days (${records.length} invoices scanned, range ${range}).`);
+ return;
+ }
+
+ const money9 = (n) => money(n).padStart(9);
+ let zeroCount = 0, dblCount = 0, dblDollars = 0;
+ const dblOrders = orders.filter((o) => o.hasDoubleBill);
+ const zeroOnly = orders.filter((o) => !o.hasDoubleBill && o.rows.some((r) => r.role === 'orphan($0)'));
+
+ console.log(`\n============ DUPLICATE-INVOICE AUDIT (READ-ONLY) ============`);
+ console.log(`window: last ${DAYS} days (${range}) · ${records.length} invoices scanned · ${orders.length} multi-invoice orders\n`);
+
+ console.log(`### CATEGORY A — REAL DOUBLE-BILLING (positive $ counted twice) — ${dblOrders.length} order(s)`);
+ for (const o of dblOrders) {
+ console.log(`Order #${o.po} — ${o.name}`);
+ for (const r of o.rows) {
+ if (r.role === 'DOUBLE-BILL') { dblCount++; dblDollars += r.total; }
+ console.log(` ${r.role.padEnd(13)} #${r.inv} ${money9(r.total)} ${r.date.padEnd(10)} ${r.dwsku || r.item}`);
+ }
+ console.log('');
+ }
+
+ console.log(`### CATEGORY B — $0 PER-SKU TRACKING CLUTTER (no revenue impact) — ${zeroOnly.length} order(s)`);
+ for (const o of zeroOnly) {
+ const zeros = o.rows.filter((r) => r.role === 'orphan($0)');
+ zeroCount += zeros.length;
+ const keeper = o.rows.find((r) => r.role === 'KEEP');
+ console.log(` Order #${o.po} ${o.name.slice(0, 24).padEnd(24)} keep #${keeper?.inv ?? '—'} (${money(keeper?.total || 0)}) +${zeros.length} × $0 records: #${zeros.map((r) => r.inv).join(', #')}`);
+ }
+
+ console.log(`\n============ SUMMARY ============`);
+ console.log(`A) Real double-billed orders: ${dblOrders.length} (${dblCount} extra invoices, ${money(dblDollars)} double-counted)`);
+ console.log(`B) $0 tracking-record clutter: ${zeroOnly.length} orders (${zeroCount} zero-dollar records)`);
+ console.log(`\nRead-only. Nothing deleted. Category A is the money problem; Category B is cosmetic clutter.`);
+}
+
+main().catch((e) => { console.error('audit error:', e.message, e.fmCode || ''); process.exit(1); });
← a19a17d invoicing: require client account number before any invoice
·
back to Filemaker Mcp
·
audit: add --csv export (both categories, per-invoice rows) 4b94f5f →