Filtered Relations, part 4

In parts 1, 2 and 3 of this series, we looked at various methods for dealing with a relational challenge (see the first two paragraphs of part 3 for a succinct description). My aim was to explore some outside-the-box ways to generate filtered totals while respecting the normalized data structure of the three tables.

However if WAN performance is a primary consideration, then denormalizing the data model by redundantly storing invoice dates in the Line Items table is worth considering. (You can follow along in this article’s demo file, filtered-relations-04, if you are so inclined.)

a) create a new relationship from Products to Line Items using the following predicates…

b) define a calculated field in Products to sum across this relationship.

c) implement an airtight methodology to ensure that the dates in Line Items will always be in synch with their parent Invoice.

1. use an autoenter calc on the date field in Line Items to pull the date from Invoices

2. attach a script trigger to the date field in Invoices, to update related Line Item dates as necessary

As long as you don’t allow users to directly interact with the Line Items table, this approach will be multiuser friendly, and the WAN performance will be blazingly fast.