← back to Filemaker Mcp
auto-save: 2026-06-29T12:19:02 (5 files) — bin/shopify-sync.js lib/sync-core.js lib/invoice-core.js lib/invoice.js lib/notify.js
8f367cdc1e89292b5053aedbe992498bd55c3271 · 2026-06-29 12:19:06 -0700 · Steve Abrams
Files touched
M bin/shopify-sync.jsA lib/invoice-core.jsA lib/invoice.jsA lib/notify.jsM lib/sync-core.js
Diff
commit 8f367cdc1e89292b5053aedbe992498bd55c3271
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Mon Jun 29 12:19:06 2026 -0700
auto-save: 2026-06-29T12:19:02 (5 files) — bin/shopify-sync.js lib/sync-core.js lib/invoice-core.js lib/invoice.js lib/notify.js
---
bin/shopify-sync.js | 14 +++----
lib/invoice-core.js | 108 +++++++++++++++++++++++++++++++++++++++++++++++++
lib/invoice.js | 114 ++++++++++++++++++++++++++++++++++++++++++++++++++++
lib/notify.js | 16 ++++++++
lib/sync-core.js | 45 ++++++++++++++++-----
5 files changed, 279 insertions(+), 18 deletions(-)
diff --git a/bin/shopify-sync.js b/bin/shopify-sync.js
index 868566a..f59448c 100644
--- a/bin/shopify-sync.js
+++ b/bin/shopify-sync.js
@@ -55,15 +55,15 @@ async function fetchOrder(num) {
}
// optional: report a created client back to steve-office via George (internal send, allowed)
-async function reportCreated(results) {
- const created = results.filter((r) => r.action === 'created');
- if (!created.length) return;
- const rows = created.map((c) => `Order #${c.orderNumber} → new client acct ${c.accountNumber} — ${c.company} (${c.email})`).join('<br>');
+async function reportProcessed(results) {
+ const done = results.filter((r) => r.action === 'processed');
+ if (!done.length) return;
+ const rows = done.map((c) => `Order #${c.orderNumber} → invoice ${c.invoiceNumber} ($${c.invoiceTotal}) · client ${c.client} acct ${c.accountNumber} — ${c.company}`).join('<br>');
await fetch(`${GEORGE}/api/send`, {
method: 'POST', headers: { Authorization: GEORGE_AUTH, 'Content-Type': 'application/json' },
body: JSON.stringify({ account: 'steve-office', to: 'steve@designerwallcoverings.com',
- subject: `FileMaker: ${created.length} new client(s) added from Shopify orders`,
- body: `<p>Added to the FileMaker Clients file (auto-synced from Shopify orders):</p><p>${rows}</p>` }),
+ subject: `FileMaker: ${done.length} order(s) synced (client + invoice) from Shopify`,
+ body: `<p>Auto-synced from Shopify orders (client ensured + invoice created + Slack alert):</p><p>${rows}</p>` }),
}).catch((e) => log('report email failed:', e.message));
}
@@ -81,7 +81,7 @@ async function main() {
if (!order) { log(`order ${num} not found in Shopify, skipping`); continue; }
const res = await syncOrder(order, { commit: true });
results.push(res);
- log(`#${num}: ${res.action}${res.action === 'created' ? ' acct ' + res.accountNumber + ' (' + res.company + ')' : res.action === 'skipped' ? ' (' + res.reason + (res.matchedOn ? ' on ' + res.matchedOn.join('/') : '') + ')' : ''}`);
+ log(`#${num}: client=${res.client} acct ${res.accountNumber} (${res.company}) → invoice ${res.invoiceNumber} $${res.invoiceTotal} · slack=${res.slack}`);
appendFileSync(LEDGER, JSON.stringify({ ts: new Date().toISOString(), ...res }) + '\n');
state.processed = [...new Set([...(state.processed || []), num])].slice(-500);
state.maxProcessed = Math.max(state.maxProcessed || 0, num);
diff --git a/lib/invoice-core.js b/lib/invoice-core.js
new file mode 100644
index 0000000..465ec4d
--- /dev/null
+++ b/lib/invoice-core.js
@@ -0,0 +1,108 @@
+// Build a full FileMaker invoice from a Shopify order, applying Steve's field
+// rules. Called by the sync after the client is ensured. Separate base tables
+// in FileMaker mean some fields are written via different layouts but the SAME
+// recordId only works on its own base table — so header/line fields go via
+// 'Invoice to Client Copy', the middle SKU via 'Layout #47', WC PATTERN via
+// '1-order detail 1 Copy3'.
+import * as fm from '../src/fm-client.js';
+
+const HDR = 'Invoice to Client Copy'; // header + line items (base table that owns the record)
+const SKU_LAY = 'Layout #47'; // has 'JS Easy SKU #' (same base table as HDR)
+const WCP_LAY = '1-order detail 1 Copy3';// has 'wc pattern' (same base table as HDR)
+const RESALE_LAY = 'PROFORMA'; // has 'Resale card lookup' (same base table as HDR)
+
+const today = () => { const d = new Date(); return `${String(d.getMonth()+1).padStart(2,'0')}/${String(d.getDate()).padStart(2,'0')}/${d.getFullYear()}`; };
+const stamp = () => new Date().toLocaleString('en-US', { month:'short', day:'numeric', year:'numeric', hour:'numeric', minute:'2-digit' });
+export const baseSku = (s) => { const m=(s||'').match(/^([A-Za-z]+-?\d+)/); return m?m[1]:(s||''); };
+export const sampleStrip = (s) => (s||'').replace(/-?sample/ig,'').trim();
+function uom(li){
+ const t=`${li.variant_title||''} ${li.name||''} ${li.sku||''}`.toLowerCase();
+ if(/per yard|\/yard|\byard/.test(t)) return 'yards';
+ if(/double roll|per roll|\/roll|\broll/.test(t)) return 'rolls';
+ if(/bolt/.test(t)) return 'bolts';
+ if(/sample|memo|swatch/.test(t)) return 'sample';
+ if(/panel/.test(t)) return 'panels';
+ return 'each';
+}
+function resaleFor(ship){
+ const country=(ship.country_code||'US').toUpperCase();
+ const state=(ship.province_code||'').toUpperCase();
+ if(country!=='US') return 'Out of Country';
+ if(state && state!=='CA') return 'Out of State';
+ return 'On File';
+}
+
+async function nextInvoiceNumber(){
+ const top=await fm.findRecords('invoice',HDR,{'Invoice':'10*'},{limit:25,sort:[{fieldName:'Invoice',sortOrder:'descend'}]});
+ const nums=top.records.map(r=>parseInt((r.fieldData['Invoice']||'').replace(/\D/g,''))).filter(n=>n>100000&&n<200000);
+ return (nums.length?Math.max(...nums):102261)+1;
+}
+
+// Create the invoice. Returns {invoiceNumber, recordId, grandTotal} or {skipped}.
+export async function createInvoiceForOrder(order, accountNumber, { commit=true } = {}){
+ const c=order.customer||{}, s=order.shipping_address||order.billing_address||{};
+ const fullName=((c.first_name||'')+' '+(c.last_name||'')).trim();
+ const shipVia=(order.shipping_lines&&order.shipping_lines[0]&&order.shipping_lines[0].title)||'';
+ const shipping=parseFloat((order.total_shipping_price_set?.shop_money?.amount) ?? (order.shipping_lines?.[0]?.price) ?? 0)||0;
+ const tax=parseFloat(order.total_tax||0)||0;
+ const lines=order.line_items||[];
+
+ if(!commit){
+ return { invoiceNumber:'(next)', would:true, header:{Cust_PO:order.order_number, account:accountNumber, sold:fullName, shipVia, shipping, tax,
+ resale:resaleFor(s)}, lines: lines.map(li=>({sku:li.sku, base:baseSku(li.sku), wcPattern:sampleStrip(li.sku), qty:li.quantity, unit:uom(li), price:li.price, detail:`${baseSku(li.sku)} — ${li.name}`})) };
+ }
+
+ const inv=String(await nextInvoiceNumber());
+ // 1) header
+ const res=await fm.createRecord('invoice',HDR,{
+ 'Invoice':inv,'Cust PO':String(order.order_number),'Account #':String(accountNumber||''),
+ 'SOLD NAME':fullName,'SOLD COMPANY':s.company||'','SOLD ADDRESS':s.address1||'','SOLD CITY':s.city||'','SOLD STATE':s.province_code||'','SOLD ZIP':s.zip||'','TELEPHONE':s.phone||order.phone||'',
+ 'Date':today()
+ },{dryRun:false});
+ const ID=res.recordId;
+ const set=async(lay,k,v)=>{ try{ await fm.updateRecord('invoice',lay,ID,{[k]:v},{dryRun:false}); }catch(e){ /* skip calc/missing */ } };
+
+ // 2) header extras + ship-to
+ for(const [k,v] of Object.entries({
+ 'ship name':fullName,'SHIP COMAPNY':s.company||'','SHIP ADDRESS':s.address1||'','SHIP CITY':s.city||'','SHIP STATE':s.province_code||'','SHIP ZIP':s.zip||'',
+ 'Resale #':resaleFor(s),'BUYER':fullName,'SALES PERSON':'Otto','SHIP VIA':shipVia,
+ 'SHIPPING HANDLING':shipping,'SALES TAX YN':(tax>0?'Y':'N'),'check Number':today()
+ })) await set(HDR,k,v);
+
+ // 3) line items -> repetitions 1..N
+ for(let i=0;i<lines.length;i++){
+ const li=lines[i], r=i+1, price=parseFloat(li.price||0)||0;
+ await set(HDR,`DETAIL 1(${r})`,`${baseSku(li.sku)} — ${li.name}`);
+ await set(HDR,`Q1(${r})`,li.quantity);
+ await set(HDR,`Unit 1(${r})`,uom(li));
+ await set(HDR,`RETAIL 1(${r})`,price);
+ await set(HDR,`NET 1(${r})`,price);
+ await set(HDR,`Sidemark 1(${r})`,s.company||'');
+ }
+ // 4) middle SKU + WC PATTERN (primary line)
+ const primary=lines[0]||{};
+ await set(SKU_LAY,'JS Easy SKU #',baseSku(primary.sku));
+ await set(WCP_LAY,'wc pattern',sampleStrip(primary.sku));
+
+ // 5) paid-in-full from the computed grand total, then the import stamp
+ let grand=0;
+ try{ grand=parseFloat((await fm.getRecord('invoice',HDR,ID)).fieldData['GRAND TOTAL']||0)||0; }catch{}
+ await set(HDR,'PAID ON ACCOUNT',grand);
+ await set(HDR,'Notes',`Imported by Otto — ${stamp()}`);
+
+ return { invoiceNumber:inv, recordId:ID, grandTotal:grand };
+}
+
+// Post a new-order notification to Slack #general.
+export async function postSlackOrder({ order, invoiceNumber, clientName }){
+ const token=process.env.SLACK_BOT_TOKEN; if(!token) return { ok:false, error:'no SLACK_BOT_TOKEN' };
+ const channel=process.env.SLACK_ORDER_CHANNEL||'#general';
+ const store=process.env.SHOPIFY_STORE;
+ const time=new Date(order.created_at||Date.now()).toLocaleString('en-US',{hour:'numeric',minute:'2-digit'});
+ const link=`https://${store}/admin/orders/${order.id}`;
+ const text=`🧾 *New Order* — *${clientName}*\nInvoice #${invoiceNumber} · ${time} · *$${order.total_price}*\n<${link}|View order #${order.order_number}>`;
+ const r=await fetch('https://slack.com/api/chat.postMessage',{method:'POST',
+ headers:{Authorization:'Bearer '+token,'Content-Type':'application/json'},
+ body:JSON.stringify({channel,text})});
+ return await r.json();
+}
diff --git a/lib/invoice.js b/lib/invoice.js
new file mode 100644
index 0000000..6ebfab7
--- /dev/null
+++ b/lib/invoice.js
@@ -0,0 +1,114 @@
+// Build a FileMaker invoice from a Shopify order — encodes all of Steve's field
+// rules (learned 2026-06-29 building invoice 102262 from order #32474):
+// - header on "Invoice to Client Copy"; line fields are REPEATING -> "(n)" syntax
+// - SALES TAX / TOTALS / GRAND TOTAL are CALC fields (never written)
+// - Unit = unit of measure (yards/rolls/bolts), NOT price; price -> RETAIL/NET
+// - tax zeroed (SALES TAX YN = N) when the order carried no tax (out-of-state)
+// - PAID ON ACCOUNT = the computed GRAND TOTAL (paid in full -> Net Due 0)
+// - Resale # falls back to Out of State / Out of Country when none on file
+// - Sales Person = Otto; Buyer = customer; Sidemark = project (company)
+// - JS Easy SKU # (middle SKU field, Layout #47) = base SKU (no variant suffix)
+// - WC PATTERN (1-order detail 1 Copy3) = SKU minus -Sample/Sample
+// - SOLD/SHIP address written explicitly (Data API create does NOT fire lookups)
+// - Notes stamped "Imported by Otto — <date time>"
+import * as fm from '../src/fm-client.js';
+
+const HDR = 'Invoice to Client Copy';
+const SKU_LAYOUT = 'Layout #47'; // exposes "JS Easy SKU #"
+const WCPAT_LAYOUT = '1-order detail 1 Copy3'; // exposes "wc pattern"
+
+const today = () => { const d = new Date(); return `${String(d.getMonth()+1).padStart(2,'0')}/${String(d.getDate()).padStart(2,'0')}/${d.getFullYear()}`; };
+const stamp = () => new Date().toLocaleString('en-US', { month:'short', day:'numeric', year:'numeric', hour:'numeric', minute:'2-digit' });
+const baseSku = (sku) => { const m = (sku||'').match(/^([A-Za-z]+-?\d+)/); return m ? m[1] : (sku||''); };
+const wcPattern = (sku) => (sku||'').replace(/-?\s*sample\s*/ig, '').trim();
+function uom(li) {
+ const t = `${li.variant_title||''} ${li.name||''} ${li.sku||''}`.toLowerCase();
+ if (/per yard|\/ ?yard|\byard/.test(t)) return 'yards';
+ if (/double roll|per roll|\/ ?roll|\broll/.test(t)) return 'rolls';
+ if (/\bbolt/.test(t)) return 'bolts';
+ if (/\bpanel/.test(t)) return 'panels';
+ if (/sample|memo|swatch/.test(t)) return 'each';
+ return 'each';
+}
+function resale(s) {
+ const country = (s.country_code || 'US').toUpperCase();
+ const state = (s.province_code || '').toUpperCase();
+ if (country !== 'US') return 'Out of Country';
+ if (state && state !== 'CA') return 'Out of State';
+ return 'On File';
+}
+
+async function nextInvoiceNumber() {
+ const r = await fm.findRecords('invoice', HDR, { 'Invoice': '10*' }, { limit: 25, sort: [{ fieldName: 'Invoice', sortOrder: 'descend' }] });
+ const nums = r.records.map((x) => parseInt((x.fieldData['Invoice']||'').replace(/\D/g,''))).filter((n) => n > 100000 && n < 200000);
+ return (nums.length ? Math.max(...nums) : 102261) + 1;
+}
+
+// safe single-field patch (skips calc / non-modifiable fields without aborting)
+async function patch(id, field, value) {
+ try { await fm.updateRecord('invoice', HDR, id, { [field]: value }, { dryRun: false }); }
+ catch (e) { if (!['102','201','509'].includes(e.fmCode)) throw e; }
+}
+
+export async function createInvoiceForOrder(order, accountNumber, { commit = true } = {}) {
+ const c = order.customer || {}, s = order.shipping_address || order.billing_address || {};
+ const name = ((c.first_name||'') + ' ' + (c.last_name||'')).trim();
+ const ship = order.shipping_lines?.[0];
+ const shipVia = ship?.title || '';
+ const shipping = Number(ship?.price || 0);
+ const orderTax = Number(order.total_tax || 0);
+ const items = order.line_items || [];
+ const primarySku = items[0]?.sku || '';
+
+ if (!commit) {
+ return { wouldCreate: true, invoiceNumber: '(next)', cust_po: order.order_number, account: accountNumber,
+ shipVia, shipping, orderTax, resale: resale(s), lines: items.map((li) => ({ sku: li.sku, qty: li.quantity, unit: uom(li), price: li.price })) };
+ }
+
+ let inv = await nextInvoiceNumber(), id = null, createdInv = null;
+ for (let tries = 0; tries < 5 && !id; tries++, inv++) {
+ const dup = await fm.findRecords('invoice', HDR, { 'Invoice': '==' + inv }, { limit: 1 }).catch(() => ({ records: [] }));
+ if (dup.records.length) continue;
+ const res = await fm.createRecord('invoice', HDR, {
+ 'Invoice': String(inv), 'Cust PO': String(order.order_number), 'Account #': String(accountNumber || ''),
+ 'SOLD NAME': name, 'SOLD COMPANY': s.company || '', 'SOLD ADDRESS': s.address1 || '', 'SOLD CITY': s.city || '',
+ 'SOLD STATE': s.province_code || '', 'SOLD ZIP': s.zip || '', 'TELEPHONE': s.phone || order.phone || '',
+ 'ship name': name, 'SHIP COMAPNY': s.company || '', 'SHIP ADDRESS': s.address1 || '', 'SHIP CITY': s.city || '',
+ 'SHIP STATE': s.province_code || '', 'SHIP ZIP': s.zip || '', 'Date': today(),
+ }, { dryRun: false }).catch(() => null);
+ if (res) { id = res.recordId; createdInv = inv; }
+ }
+ if (!id) throw new Error('could not allocate an invoice number');
+
+ // header extras
+ await patch(id, 'SALES PERSON', 'Otto');
+ await patch(id, 'BUYER', name);
+ await patch(id, 'Resale #', resale(s));
+ await patch(id, 'SHIP VIA', shipVia);
+ await patch(id, 'SHIPPING HANDLING', shipping);
+ await patch(id, 'SALES TAX YN', orderTax > 0 ? 'Y' : 'N');
+ await patch(id, 'check Number', today());
+
+ // line items -> repetitions
+ for (let i = 0; i < items.length; i++) {
+ const li = items[i], r = i + 1, price = Number(li.price || 0);
+ await patch(id, `DETAIL 1(${r})`, `${li.sku || ''} — ${li.name || ''}`.trim());
+ await patch(id, `Q1(${r})`, li.quantity);
+ await patch(id, `Unit 1(${r})`, uom(li));
+ await patch(id, `RETAIL 1(${r})`, price);
+ await patch(id, `NET 1(${r})`, price);
+ await patch(id, `Sidemark 1(${r})`, s.company || '');
+ }
+
+ // cross-table fields (different base-table layouts; share the record id)
+ try { await fm.updateRecord('invoice', SKU_LAYOUT, id, { 'JS Easy SKU #': baseSku(primarySku) }, { dryRun: false }); } catch {}
+ try { await fm.updateRecord('invoice', WCPAT_LAYOUT, id, { 'wc pattern': wcPattern(primarySku) }, { dryRun: false }); } catch {}
+
+ // paid-in-full = computed grand total -> Net Due 0
+ const grand = (await fm.getRecord('invoice', HDR, id))?.fieldData?.['GRAND TOTAL'] || 0;
+ await patch(id, 'PAID ON ACCOUNT', grand);
+ await patch(id, 'Notes', `Imported by Otto — ${stamp()}`);
+
+ return { invoiceNumber: String(createdInv), recordId: id, grandTotal: grand,
+ cust_po: order.order_number, account: accountNumber };
+}
diff --git a/lib/notify.js b/lib/notify.js
new file mode 100644
index 0000000..83f29dd
--- /dev/null
+++ b/lib/notify.js
@@ -0,0 +1,16 @@
+// Post a new-order alert to Slack #general: client name, invoice#, time, total, order link.
+export async function postOrderToSlack({ order, invoiceNumber, total, clientName }) {
+ const token = process.env.SLACK_BOT_TOKEN;
+ const channel = process.env.SLACK_GENERAL_CHANNEL;
+ if (!token || !channel) return { ok: false, error: 'slack not configured' };
+ const store = process.env.SHOPIFY_STORE;
+ const when = new Date(order.created_at || Date.now()).toLocaleString('en-US', { month: 'short', day: 'numeric', hour: 'numeric', minute: '2-digit' });
+ const link = `https://${store}/admin/orders/${order.id}`;
+ const text = `🛎️ *New order* — ${clientName} · Invoice #${invoiceNumber} · ${when} · *$${total}* · <${link}|View order #${order.order_number}>`;
+ const res = await fetch('https://slack.com/api/chat.postMessage', {
+ method: 'POST',
+ headers: { Authorization: `Bearer ${token}`, 'Content-Type': 'application/json; charset=utf-8' },
+ body: JSON.stringify({ channel, text, unfurl_links: false }),
+ });
+ return res.json();
+}
diff --git a/lib/sync-core.js b/lib/sync-core.js
index a8b6fca..9692494 100644
--- a/lib/sync-core.js
+++ b/lib/sync-core.js
@@ -58,24 +58,47 @@ export async function findDuplicates({ fields }) {
return [...hits.values()];
}
-// Process one order: skip if duplicate, else create. Returns a result record.
+// Process one order end-to-end: ensure the client exists (create if new, dedup
+// by email/phone/name otherwise), create the FileMaker invoice, then post a
+// new-order alert to Slack. Every order gets an invoice + Slack ping regardless
+// of whether the client was new or already on file.
export async function syncOrder(order, { commit = true } = {}) {
const mapped = mapOrder(order);
if (!mapped.fields['Email address'] && !mapped.fields['Phone'] && !mapped.fields['Company']) {
return { orderNumber: mapped.orderNumber, action: 'skipped', reason: 'no identifying fields' };
}
+
+ // 1) ensure client + resolve account number
+ let clientAction, accountNumber;
const dupes = await findDuplicates(mapped);
if (dupes.length) {
- return { orderNumber: mapped.orderNumber, action: 'skipped', reason: 'duplicate',
- matchedOn: [...new Set(dupes.flatMap((d) => d.why))], existing: dupes.map((d) => ({ acct: d.acct, company: d.company })) };
+ clientAction = 'existing';
+ accountNumber = dupes[0].acct;
+ } else if (commit) {
+ const res = await fm.createRecord('Clients', LAYOUT, mapped.fields, { dryRun: false });
+ try { accountNumber = (await fm.getRecord('Clients', LAYOUT, res.recordId))?.fieldData?.['Account Number']; } catch {}
+ clientAction = 'created';
+ } else {
+ clientAction = 'would-create';
}
- if (!commit) {
- return { orderNumber: mapped.orderNumber, action: 'would-create', fields: mapped.fields };
+
+ // 2) invoice
+ const { createInvoiceForOrder, postSlackOrder } = await import('./invoice-core.js');
+ const inv = await createInvoiceForOrder(order, accountNumber, { commit });
+
+ // 3) Slack alert (only on real commit)
+ let slack = null;
+ if (commit) {
+ try { slack = await postSlackOrder({ order, invoiceNumber: inv.invoiceNumber, clientName: mapped.fields['Company'] }); }
+ catch (e) { slack = { ok: false, error: e.message }; }
}
- const res = await fm.createRecord('Clients', LAYOUT, mapped.fields, { dryRun: false });
- // read back the auto-assigned account number
- let acct = null;
- try { acct = (await fm.getRecord('Clients', LAYOUT, res.recordId))?.fieldData?.['Account Number']; } catch {}
- return { orderNumber: mapped.orderNumber, action: 'created', recordId: res.recordId, accountNumber: acct,
- company: mapped.fields['Company'], email: mapped.fields['Email address'] };
+
+ return {
+ orderNumber: mapped.orderNumber,
+ action: commit ? 'processed' : 'would-process',
+ client: clientAction, accountNumber, company: mapped.fields['Company'], email: mapped.fields['Email address'],
+ invoiceNumber: inv.invoiceNumber, invoiceTotal: inv.grandTotal,
+ slack: slack?.ok ? 'posted' : (slack?.error || 'n/a'),
+ matchedOn: dupes.length ? [...new Set(dupes.flatMap((d) => d.why))] : undefined,
+ };
}
← cecc780 shopify->filemaker client sync: info@ trigger, 3-way dedup (
·
back to Filemaker Mcp
·
sync: every order now auto-creates client + full invoice (Ot b60dfd5 →