Temporal Data Patterns -- Effective Dates in FileMaker

Expert

Model time-varying data with effective dates, track record history, and query "as of" states using FileMaker relationships and calculation fields.

What you'll learn

How to model effective dates for slowly-changing values
How to find the record effective "as of" a given date using relationships
How to implement a Type 2 slowly-changing dimension pattern
How to display temporal history in a portal

Many real-world values change over time: prices, addresses, salaries, contract terms. A simple field stores only the current value, losing all history. Temporal data modeling adds effective date fields to track when each value was valid, enabling "as of" queries: "what was this customer's tier on the invoice date?" This pattern is essential for legal, financial, and audit-ready solutions.

1/4

The effective date pattern

Add EffectiveFrom and EffectiveTo dates to any table whose values change over time. A record represents the value during [EffectiveFrom, EffectiveTo). The "current" record has EffectiveTo = null (or a far-future date).

TEXT

// Table: PriceHistory
// Fields: PriceHistoryID, ProductID, UnitPrice, EffectiveFrom, EffectiveTo

// Example rows for ProductID = 42:
// PriceHistoryID  ProductID  UnitPrice  EffectiveFrom  EffectiveTo
// 1               42         99.00      2023-01-01     2024-06-30
// 2               42         109.00     2024-07-01     null (current)

// "Current" price: EffectiveTo is empty AND EffectiveFrom <= today
// Historical price as of 2024-01-15: EffectiveFrom <= 2024-01-15 AND
//   (EffectiveTo >= 2024-01-15 OR EffectiveTo is empty)

Relationship Design for Reporting Versioning Records via Relationships