Peter Schmedling made an interesting comment on an earlier posting about custom events, in which he suggested linking worksheets using events. Thus one might get a pivot table to update automatically, rather than being manually refreshed. The point of using events is that the the updates are ‘pulled’ by the Views, not ‘pushed’ by the Model (the holder of the master data).
It occurred to me that one application of this would be dynamic filtering of data:
- a Data worksheet would hold the full set of records
- one or more Filter worksheets would present filtered views of the Data records, perhaps for an attribute range, such as a date range (the Filtered subsets might or might not be disjoint)
- a Data form would be used to add, edit or delete data records
- such changes would be propagated automatically to the filtered views.
Unlike doing a standard Advanced Filter, this would be completely automatic, and incremental (after an initial filtering pass).
Here’s some simple data:
To keep things simple, we’ll go for filtering Value by interval. The mapping of intervals to worksheets can be set up on an auxiliary worksheet:
This specifies that worksheet ‘Filter A’ will hold records with Value between 1 and 300, worksheet ‘Filter B’ will hold records with Value between 301 and 1000 (any > 1000 will appear only on the Data worksheet). The yellow cells are VLOOKUPs of the bounds for a particular filter.
Here’s the data form, which is similar to ones described in earlier postings:
Like the earlier forms, this is based on a ‘data cursor’ object that provides access to the Data worksheet. Both the form and the cursor are a bit more complex, as we have to support additions, edits and deletions, as well as previous/next moves.
So, for example:
- if I add (id5, 555), it appears on both the Data and Filter B worksheets
- if I delete (id2, 222), it disappears from both the Data and Filter A worksheets
- if I change (id3, 333) to (id3, 123), it is updated on Data and migrates from Filter B to Filter A.
I’ll talk about the design in the next posting, but the key points are:
- the Data Cursor object generates ItemUpdate and ItemDelete events (the former includes additions)
- both types of event pass a record Id as a parameter
- these events are received by one or more Filter objects
- each Filter object decides whether the notification is relevant, and makes any necessary changes to its filtered worksheet.