← back to Filemaker Mcp
fix: fm_find/sort JSON-string coercion (harness stringifies z.any); invoice file live w/ Order Detail default; add invoice-102332 repair script
7b9b08c33e90b31d9c92d0a1743596dd0f3f130d · 2026-07-01 14:19:17 -0700 · Steve Abrams
Files touched
M config.jsonA scripts/fix-invoice-102332.mjsM src/index.js
Diff
commit 7b9b08c33e90b31d9c92d0a1743596dd0f3f130d
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Jul 1 14:19:17 2026 -0700
fix: fm_find/sort JSON-string coercion (harness stringifies z.any); invoice file live w/ Order Detail default; add invoice-102332 repair script
---
config.json | 24 ++++++++++-----
scripts/fix-invoice-102332.mjs | 67 ++++++++++++++++++++++++++++++++++++++++++
src/index.js | 3 ++
3 files changed, 87 insertions(+), 7 deletions(-)
diff --git a/config.json b/config.json
index 698785e..9df3229 100644
--- a/config.json
+++ b/config.json
@@ -1,8 +1,18 @@
{
- "_comment": "Whitelist of FileMaker Cloud files this connector may touch, plus the default Data API layout for each. The Data API reads/writes THROUGH a layout, so 'defaultLayout' should be a layout that exposes the fields we want Claude to see. Fill these in after running `fm layouts <db>`. A file not listed here is rejected by the connector.",
- "databases": {
- "Clients": { "defaultLayout": "wALLPAPER oPTIONS", "writable": true },
- "WALLPAPER": { "defaultLayout": "Basic List of Fields", "writable": true },
- "invoice": { "defaultLayout": "", "writable": true, "note": "NOT YET LIVE — enable the fmrest extended privilege on the 'invoice' file (File > Manage > Security > Extended Privileges) so the Data API can open it. Currently returns FM error 802." }
- }
-}
+ "_comment": "Whitelist of FileMaker Cloud files this connector may touch, plus the default Data API layout for each. The Data API reads/writes THROUGH a layout, so 'defaultLayout' should be a layout that exposes the fields we want Claude to see. Fill these in after running `fm layouts <db>`. A file not listed here is rejected by the connector.",
+ "databases": {
+ "Clients": {
+ "defaultLayout": "wALLPAPER oPTIONS",
+ "writable": true
+ },
+ "WALLPAPER": {
+ "defaultLayout": "Basic List of Fields",
+ "writable": true
+ },
+ "invoice": {
+ "defaultLayout": "Order Detail",
+ "writable": true,
+ "note": "LIVE \u2014 fmrest enabled; Order Detail exposes the denormalized 4-column line fields (Q1/Unit 1/DETAIL 1/NET 1 reps 1-4), PAID ON ACCOUNT editable, TOTAL/MERCH/NET DUE are calcs."
+ }
+ }
+}
\ No newline at end of file
diff --git a/scripts/fix-invoice-102332.mjs b/scripts/fix-invoice-102332.mjs
new file mode 100644
index 0000000..651d1f8
--- /dev/null
+++ b/scripts/fix-invoice-102332.mjs
@@ -0,0 +1,67 @@
+// One-off: repair invoice #102332 (Shopify order #32494).
+// Usage: node scripts/fix-invoice-102332.mjs [--find-only] [--commit]
+// Default (no flags) = find the record and show a dry-run diff; --commit writes.
+
+import { readFileSync } from 'node:fs';
+import { fileURLToPath } from 'node:url';
+import { dirname, join } from 'node:path';
+
+const __dir = dirname(fileURLToPath(import.meta.url));
+for (const line of readFileSync(join(__dir, '..', '.env'), 'utf8').split('\n')) {
+ const m = line.match(/^([A-Z_]+)=(.*)$/);
+ if (m && !process.env[m[1]]) process.env[m[1]] = m[2];
+}
+
+const { findRecords, fieldMetadata, updateRecord, getRecord } = await import('../src/fm-client.js');
+
+const DB = 'invoice';
+const LAYOUT = 'Order Detail';
+const INVOICE_NO = '102332';
+
+const findOnly = process.argv.includes('--find-only');
+const commit = process.argv.includes('--commit');
+
+const meta = await fieldMetadata(DB, LAYOUT);
+const names = (meta.fieldMetaData || meta.response?.fieldMetaData || []).map((f) => f.name || f);
+console.log('FIELDS:', JSON.stringify(names));
+
+// locate the invoice-number field name on this layout
+const invField = names.find((n) => /invoice/i.test(n) && /#|no|num/i.test(n)) ||
+ names.find((n) => /^invoice$/i.test(n));
+if (!invField) { console.error('No invoice-number field found on layout — see FIELDS above.'); process.exit(2); }
+console.log('Using invoice field:', invField);
+
+const { records } = await findRecords(DB, LAYOUT, { [invField]: `==${INVOICE_NO}` }, { limit: 5 });
+if (!records.length) { console.error('Invoice not found.'); process.exit(3); }
+console.log(`Matches: ${records.length}`);
+const rec = records[0];
+console.log('recordId:', rec.recordId);
+console.log('fieldData:', JSON.stringify(rec.fieldData, null, 1));
+
+if (findOnly) process.exit(0);
+
+// --- corrections (ground truth: Shopify #32494 3×131.95 −59.37 +44.50 ship = 380.98 paid;
+// #32500 1×131.95 −19.79 free ship = 112.16 paid; total paid 493.14 = current PAID ON ACCOUNT, correct) ---
+// Line 1: show RETAIL unit price (131.95), qty 4 → TOTAL calc 527.80
+// Line 2: same qty, JS# "Discount at 15%", NET −19.79/unit → −79.16
+// 527.80 − 79.16 = 448.64 merch (unchanged) + 44.50 ship = 493.14 paid, NET DUE 0
+const fieldData = {
+ 'NET 1(1)': 131.95,
+ 'Q1(2)': 4,
+ 'Unit 1(2)': 'S/R',
+ 'DETAIL 1(2)': 'Discount at 15%',
+ 'NET 1(2)': -19.79,
+};
+
+const res = await updateRecord(DB, LAYOUT, rec.recordId, fieldData, { dryRun: !commit });
+console.log(commit ? 'COMMITTED:' : 'DRY RUN DIFF:', JSON.stringify(res, null, 1));
+
+if (commit) {
+ const after = await getRecord(DB, LAYOUT, rec.recordId);
+ const f = after.fieldData;
+ console.log('VERIFY:', JSON.stringify({
+ 'Q1(2)': f['Q1(2)'], 'DETAIL 1(2)': f['DETAIL 1(2)'], 'NET 1(2)': f['NET 1(2)'],
+ 'PAID ON ACCOUNT': f['PAID ON ACCOUNT'],
+ 'MERCHANDISE TOTAL': f['MERCHANDISE TOTAL'], 'NET DUE 2': f['NET DUE 2'],
+ }, null, 1));
+}
diff --git a/src/index.js b/src/index.js
index 9675589..101702d 100644
--- a/src/index.js
+++ b/src/index.js
@@ -35,6 +35,9 @@ server.tool('fm_find',
{ database: z.string(), layout: z.string().optional(), query: z.any(),
limit: z.number().optional(), offset: z.number().optional(), sort: z.any().optional() },
async ({ database, layout, query, limit, offset, sort }) => {
+ // some MCP clients deliver z.any() params as JSON strings — FileMaker needs the real object
+ if (typeof query === 'string') { try { query = JSON.parse(query); } catch { /* leave as-is */ } }
+ if (typeof sort === 'string') { try { sort = JSON.parse(sort); } catch { /* leave as-is */ } }
try { return ok(await fm.findRecords(database, layout, query, { limit, offset, sort })); } catch (e) { return fail(e); } });
// --- writes: dryRun defaults TRUE so a value never changes without an explicit second call ---
← 1f3eaf8 remove dead lib/invoice-core.js (orphaned from refactor; lib
·
back to Filemaker Mcp
·
test: dummy client+invoice demo scripts (999999/Testy McTest 2412a37 →