Notion helper formulas

notion-helper-formulas

Add formula properties to Notion DBs so that complex dashboard filters become one-click checkbox checks instead of multi-condition filter wiring.

Why this skill exists

Notion view filters become unwieldy when they have 4+ conditions (e.g. "Status = Pending AND Type = Invoice AND Date < today-7 AND Stage != Closed"). Each filter has to be re-wired per view. Solution: push the boolean logic into a formula property on the DB. The formula returns a checkbox-like boolean; views just filter Is Overdue = checked.

CRITICAL gotcha — cross-formula-reference limit

Notion formulas cannot reference another formula property when doing date comparisons. Returns 400 validation_error "Type error with formula".

❌ Fails: not empty(prop("Feedback Due Date")) and prop("Feedback Due Date") < now() (where Feedback Due Date is itself a formula dateAdd(Issued Date, 4, "days"))

✅ Works: not empty(prop("Issued Date")) and dateAdd(prop("Issued Date"), 4, "days") < now()

Rule: when you'd reference a date formula prop in a comparison, inline the underlying date column computation instead. Direct date columns (type=date) work fine in all comparisons.

Prerequisites

• NOTION_API_KEY env var
• ids.json (or DB IDs in hand)

Steps

1. Schema-first: invoke notion-schema-dump to confirm property names + types are current.
2. Draft formulas. For each:
   • Boolean output → users filter = checked
   • Date output → use directly (subject to the cross-ref limit above)
3. Inline any date math instead of referencing date formulas.
4. Run:

   from notion_os_toolkit import auth, client, helper_formulas
   nc = client.NotionClient(auth.load_token())
   helper_formulas.patch(nc, {
       "<deliverables_db_id>": {
           "Needs Client Approval": 'prop("Status") == "Client Review" and prop("Requires Client Approval")',
           "SLA Breach": 'not empty(prop("Issued Date")) and dateAdd(prop("Issued Date"), 4, "days") < now() and prop("Status") != "Approved"',
       },
   })
   

5. Verify by regenerating SCHEMA-REFERENCE.md and confirming each formula appears.
6. If any formula fails with "Type error": isolate by substituting literals for prop refs one by one. The one that swaps to literal-and-passes is the offender — usually a cross-formula date reference.

Example formula library (interior design vertical)

Deliverables.Needs Client Approval
  = prop("Status") == "Client Review" and prop("Requires Client Approval")

Deliverables.SLA Breach
  = not empty(prop("Issued Date"))
    and dateAdd(prop("Issued Date"), 4, "days") < now()
    and prop("Status") != "Approved" and prop("Status") != "Issued"

Finance.Is Overdue
  = prop("Record Type") == "Invoice Issued"
    and prop("Status") != "Received" and prop("Status") != "Written Off"
    and not empty(prop("Date"))
    and dateAdd(prop("Date"), 7, "days") < now()

Projects.Is Active
  = prop("Project Stage") != "Handover" and prop("Client Sign-off") == false

Tasks.Is Open
  = prop("Is Template") == false and prop("Status") != "Done"

Leads.Needs Follow-up
  = not empty(prop("Next Action Date"))
    and prop("Next Action Date") <= now()
    and prop("Lead Stage") != "Won" and prop("Lead Stage") != "Lost"

Input/Output contract

Input: {db_id: {formula_name: expression}} mapping Output: added formulas on each DB; failure count if any rejected