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.

2 thoughts on “Filtered Relations, part 4

  1. ASTRA solutions

    Hello,

    Great series of articles; Good job!

    One question: The demo file link nº 4 not works. Can you fix it?

    Thank you.

    Eduardo

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s