[object Object]

← back to Filemaker Mcp

Fix DW Reports Bot MTD undercount: query from month-start, not 10-day lookback

dafc8b080842b2aac8d4b22ea26b71d629f99403 · 2026-07-28 16:34:00 -0700 · Steve Abrams

MTD query window used the 10-day lookback date as its start, so from the
11th of each month onward it dropped every invoice before then. July MTD
read $26,526/80 orders; true value is $67,373/250. Window now = earlier
of month-start vs lookback.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Files touched

Diff

commit dafc8b080842b2aac8d4b22ea26b71d629f99403
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue Jul 28 16:34:00 2026 -0700

    Fix DW Reports Bot MTD undercount: query from month-start, not 10-day lookback
    
    MTD query window used the 10-day lookback date as its start, so from the
    11th of each month onward it dropped every invoice before then. July MTD
    read $26,526/80 orders; true value is $67,373/250. Window now = earlier
    of month-start vs lookback.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 scripts/sales-summary.mjs | 10 +++++++---
 1 file changed, 7 insertions(+), 3 deletions(-)

diff --git a/scripts/sales-summary.mjs b/scripts/sales-summary.mjs
index 677a251..b5cc24e 100644
--- a/scripts/sales-summary.mjs
+++ b/scripts/sales-summary.mjs
@@ -49,10 +49,14 @@ async function main() {
   const yesterdayFM = fmDate(ptYMD(new Date(now.getTime() - 864e5)));
   const todayISO = `${y}-${pad(m)}-${pad(d)}`;
 
-  // Pull window: month start (for MTD) or LOOKBACK_DAYS ago, whichever is earlier.
+  // Pull window: month start (for MTD) or LOOKBACK_DAYS ago, whichever is EARLIER,
+  // so MTD always sees the whole month AND we still catch backdated entries.
+  // Month-start (day 1) is earlier than the lookback UNLESS the lookback crossed
+  // into the previous month (i.e. we're within the first LOOKBACK_DAYS of the month),
+  // in which case the lookback is earlier and covers late-prior-month backdates.
   const lookback = ptYMD(new Date(now.getTime() - LOOKBACK_DAYS * 864e5));
-  const monthIsEarlier = y < lookback.y || (y === lookback.y && m < lookback.m);
-  const from = monthIsEarlier ? `${m}/1/${y}` : `${lookback.m}/${lookback.d}/${lookback.y}`;
+  const lookbackCrossedMonth = lookback.y !== y || lookback.m !== m;
+  const from = lookbackCrossedMonth ? `${lookback.m}/${lookback.d}/${lookback.y}` : `${m}/1/${y}`;
 
   const { records } = await findRecords(
     DB, LAYOUT,

← d91bd91 add scripts/fetch-clients.js — page entire Clients file to M  ·  back to Filemaker Mcp  ·  auto-save: 2026-07-28T17:01:56 (1 files) — scripts/post-mtd- 57f06a3 →