Dynamic Filtering (using events) – Part 2

In Part 1, we looked at the idea of having worksheets that offer dynamically updated views of some data on a ‘master’ worksheet. Editing events on the master are notified to the various filters, which update their worksheets accordingly. Here’s the overall design:


The arrangement on the left is pretty much as we had it in the multi-form example. Here, the DataCursor (was RecordCursor) needs to handle edit operations. Handling deletions is slightly tricky, as it has to maintain a map of Ids to positions, so that the appropriate row cells can be deleted. As before, the form receives CursorMove events and updates its contents.

The DataCursor raises ItemUpdate and ItemDelete events, which are handled by a FilterCursor. This has a private FilterItem function which tests whether an added/updated record falls into its filtered set; it can also find out whether a deleted Id is in its set. If a change event is relevant to a FilterCursor, it calls its own Add/Change/DeleteItem methods, which in turn call a DataCursor for the filtered worksheet (details omitted in the diagram).

The reason why a FilterCursor is a cursor at all is that it has to perform an initial filtering pass through the Data worksheet. So it needs position and value properties, Move* methods, and a reference to the worksheet. Unlike the DataCursor, access to the worksheet is read-only. Maybe in a full OO language we could have separated the filter and cursor aspects into different classes.

You might think that the filter could use the master DataCursor to get its data. However, the filtering pass would change the DataCursor’s position (from start to finish of the records), which would thus cause the DataForm to update  repeatedly (and very quickly!).

The initial filtering is done afresh on Workbook_Open, clearing out any previously saved filtered data. Other approaches that make use of saved filtered data might be appropriate where the size of the filtered sets is very small in comparison with the size of the data set.

When the FilterCursor initializes itself, it gets the value minimum and maximum from the Filters worksheet:


The yellow cells contain VLOOKUPs into the Filters table. Obviously, this isn’t completely general with regard to filtering criteria. At the other extreme, we could simply hand-code various filtering functions into our FilterCursors, which might be okay if those functions are a fixed part of our application.

If you want to update the min/max values, you need to reset the cursors (that is, re-filter); there’s a command button for this. However, we could pick up Worksheet_Change events for those cells, and do the resetting automatically.

So when might all this be useful? I can envisage a ‘work flow’ scenario, where transactional objects (orders, jobs, etc) are received, added to a master list, and then updated to reflect status changes (waiting, inProgress, completed, suspended, etc). The filter worksheets partition these objects according to status (and maybe date range). Status changes cause objects to ‘migrate’ between the various worksheets. There’s no need to do repeated manual filtering: the filtered sets are always up-to-date.


0 Responses to “Dynamic Filtering (using events) – Part 2”

  1. Leave a Comment

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

June 2009
« May   Jul »

%d bloggers like this: