Level: Intermediate, SQL, Version: FM 8 or later

Filtered Relations, part 3

Today we’re going to take a third look at the challenge we encountered in part 1 of this series: Given a simple database consisting of Products, Invoices and Invoice Line Items, how can we show total sales per product filtered by date range?

Reminder: what makes this problem challenging is the fact that the date field lives in the Invoices table, but the product foreign key (fk_product) lives in the Line Items table.

To briefly recap, we solved this problem in part 1 at the cost of adding a couple fields and table occurrences, i.e., by leveraging the relational model. And in part 2 we solved this problem by displaying a related summary field inside a one-row filtered portal. Continue reading “Filtered Relations, part 3”