DataGrid Filter example - with sorting

The other day I posted an example of how you can add a filter to a DataGrid in Revolution. At the end of that blog post, I remarked how I had deliberately turned off column sorting. Fear not, it is straightforward to add support for this, again thanks to the DataGrid lessons available on the runrev.com website.

Just as a reminder, here's a screenshot of the example stack:



So what do we need to do to allow the user to click on the column header and apply the sort and filter at the same time? It's quite a simple bity of scripting, but before we get to that, you need to make some minor modifications to the datagrid columns.
- Switch to the 'pointer' tool, and select the datagrid, then open the Object Inspector, and switch to the 'Columns' panel
-> select the column 'index' and set its Sort by column type to 'Numeric'
-> select the column 'name' and make sure its Sort by column type is 'Text'

Now edit the script of the datagrid, and set it to:

local sOriginalData
local sIndexMap
local sFilter

setProp dgData pData
  put pData into sOriginalData
  RebuildMap
end dgData

getProp dgData
  return sOriginalData
end dgData

setProp dgFilter pFilter
  put pFilter into sFilter
  RebuildMap
end dgFilter

getProp dgFilter
  return sFilter
end dgFilter

--> datagrid callbacks

on GetDataForLine pLine, @pData
  local tKey
  -- retrieve the original line of data
  put sIndexMap[pLine] into tKey
  put sOriginalData[tKey] into pData
end GetDataForLine

on SortDataGridColumn pColumn
  SortMap pColumn
  HiliteAndStoreSortByColumn pColumn
  dispatch "RefreshList" to me
end SortDataGridColumn

--> private commands and functions

private command RebuildMap
  local tKeys, tKey, tIndex
  put empty into sIndexMap
  put 0 into tIndex
  put the keys of sOriginalData into tKeys
  sort tKeys numeric
  repeat for each line tKey in tKeys
    if sFilter is empty \
    or sOriginalData[tKey]["name"] contains sFilter \
    then
      add 1 to tIndex
      put tKey into sIndexMap[tIndex]
    end if
  end repeat
  set the dgNumberOfRecords of me to tIndex
  SortMap
  dispatch "ResetList" to me
end RebuildMap

private command SortMap pColumn
  local tSortDirection, tSortType, tSortCaseSensitive
  local tSortCommand
  local tKeys, tKey, tNewIndex, tNewIndexMap
  -- works whether a column is specified or not
  if pColumn is empty then
    -- find the current sort column
    put the dgProps["sort by column"] of me into pColumn
    if pColumn is empty then
      exit SortMap
    end if
  end if
  -- assemble and do the sort command
  put the dgColumnSortDirection[pColumn] of me \
    into tSortDirection
  put the dgColumnSortType[pColumn] of me \
    into tSortType
  put the dgColumnSortCaseSensitive[pColumn] of me \
    into tSortCaseSensitive
  put "sort lines of tKeys" && tSortDirection \
    into tSortCommand
  switch tSortType
    case "numeric"
      put " numeric" after tSortCommand
      break
    case "international"
      put " international" after tSortCommand
      break
    case "datetime"
    case "system datetime"'
      put " dateTime" after tSortCommand
      break
  end switch
  put " by ColumnValueForLine(pColumn, each)" \
    after tSortCommand
  set the caseSensitive to \
    (tSortCaseSensitive is true)
  set the useSystemDate to \
    (tSortType is "system datetime")
  put the keys of sIndexMap into tKeys
  do tSortCommand
  -- rebuild the index map
  put 0 into tNewIndex
  repeat for each line tKey in tKeys
    add 1 to tNewIndex
    put sIndexMap[tKey] into \
      tNewIndexMap[tNewIndex]
  end repeat
  put tNewIndexMap into sIndexMap
end SortMap

private function ColumnValueForLine pColumn, pLine
  local tKey
  -- retrieve the original line of data
  put sIndexMap[pLine] into tKey
  return sOriginalData[tKey][pColumn]
end ColumnValueForLine

What's changed in comparison to the last implementation? Well, the bulk of the necessary work is done in the new private command SortMap, which reads the column sort properties and uses a custom sort function to get the filtered lines in the right order on the basis of the column value in the original lines of data. Then it builds a new IndexMap to replace the old one, and the regular refresh routines of the datagrid will "just work."

So what happens when we sort the data descending on the name, without applying a filter?



Looking good - does the filter still work? Let's type 'url' into the filter field.



You may have noticed that I also added getProp-handlers for dgData and dgFilter, making the above script easily reusable. With just a bit of lateral thinking and coding, you too can build just about anything, thanks to Revolution.

DataGrid Filter example

One of the powerful additions to Revolution 3.5 is the DataGrid - a custom control group that allows you to display any type of data with astonishing flexibility. Recently, I had to add filtering capabilities to a data grid for one of my projects. In this post, I will explain how I accomplished it, thanks to the DataGrid lessons available on the runrev.com website.

Here's a screenshot of the example stack:



Execute the following steps, if you want to follow along:
- Create a new stack
- Drag a button to the topleft of the card and set its name to "Fill"
- Drag a text field to the topright of the card
- Drag a label field next to the text field and set its content to "Filter:"
- Drag a data grid onto the card and configure it using the Object Inspector
-> In the 'Basic Properties' panel, turn off the checkbox "Allow Text Editing"
-> Switch to the 'Columns' panel, and add 2 columns "index" and "name"
-> Edit the script of the DataGrid, and set it to the following:

local sOriginalData
local sIndexMap
local sFilter

setProp dgData pData
  put pData into sOriginalData
  RebuildMap
end dgData

setProp dgFilter pFilter
  put pFilter into sFilter
  RebuildMap
end dgFilter

private command RebuildMap
  local tKeys, tKey, tIndex
  put 0 into tIndex
  put the keys of sOriginalData into tKeys
  sort tKeys numeric
  repeat for each line tKey in tKeys
    if sFilter is empty \
    or sFilter is in sOriginalData[tKey]["name"] \
    then
      add 1 to tIndex
      put tKey into sIndexMap[tIndex]
    end if
  end repeat
  set the dgNumberOfRecords of me to tIndex
  dispatch "ResetList" to me
end RebuildMap

on GetDataForLine pLine, @pData
  local tKey
  put sIndexMap[pLine] into tKey
  put sOriginalData[tKey] into pData
end GetDataForLine

on SortDataGridColumn pColumn
  -- prevent column sorting
end SortDataGridColumn

The above data grid script will take care of filtering the data. Now it's just a matter of wiring up the text field.
-> Edit the script of the text field and set it to the following:

local sUpdateMsg

on keyUp pKey
  ScheduleUpdate
  pass keyUp
end keyUp

on deleteKey
  ScheduleUpdate
  pass deleteKey
end deleteKey

on backspaceKey
  ScheduleUpdate
  pass backspaceKey
end backspaceKey

on pasteKey
  ScheduleUpdate
  pass pasteKey
end pasteKey

on cutKey
  ScheduleUpdate
  pass cutKey
end cutKey

command ScheduleUpdate
  if sUpdateMsg is empty then
    send "UpdateFilter" to me in 100 milliseconds
    put the result into sUpdateMsg
  end if
end ScheduleUpdate

on UpdateFilter
  set the dgFilter of group "DataGrid 1" \
    to the text of me
  put empty into sUpdateMsg
end UpdateFilter

By trapping the different key events, we can update the data grid filter as we go along. Note that I used the ScheduleUpdate helper command to try and minimize the number of refreshes for quick typers. Play around with the interval until you find the sweet spot of responsive user interface.

Finally, it is time to fill the data gird with some data for testing, so edit the script of the button and set it to the following:

on mouseUp
  local tData, tIndex, tName
  put 0 into tIndex
  repeat for each line tName in the functionNames
    add 1 to tIndex
    put tIndex into tData[tIndex]["index"]
    put tName into tData[tIndex]["name"]
  end repeat
  set the dgData of group "DataGrid 1" to tData
end mouseUp

This simple script will fill our data grid with a list of the built-in functions that revTalk has to offer. Click the button, and type away in the filter text field to see the effect. Here's what happens when I type "cos":


Looks pretty good, doesn't it? It certainly was good enough for my use, but there are a few things to bear in mind about this implementation:
- I didn't have to edit the data in the grid.
- I didn't have to support sorting for my particular project.
Both of these limitations can be overcome, but I'll leave that for another time...

Behavior on the Grid

Today, the good people of Runtime Revolution Ltd. presented an impressive webinar, showcasing the brand new Data Grid control that is a salivating new feature of Revolution 3.5 - in fact, this new control has given us a lot more than what most people would want from a table control. You can setup everything from a straightforward table with multiple alignments, to a spectacular form with dynamically resizing rows containing arbitrary controls with judicious use of template groups.

This crown jewel was developed using the other new big technological addition to Revolution: behaviors. This allows you to write a script once and apply it to several other controls at once - and when you update the original behavior script, all those controls adopt the change in behavior automatically.
Of course, if you're used to object-oriented programming, this sounds like simple inheritance. The xCard paradigm, pioneered over 20 years ago by Apple's HyperCard is traditionally object-based but not fully object-oriented; there's a limited set of built-in controls and you can 'specialize' these by adding a script.
Another typical OO design pattern, Chain of Responsibility, was the basis of the xCard message path, which allows developers to place handlers for events, command and function calls in a central place - if you needed to know where the event originated, you could use 'the target' property to work your magic from there.

Strictly speaking this was enough to build something like the Data Grid in the past, but the new 'behavior' feature makes it a lot easier. The 'me' object points to the 'target' rather than the button that contains the 'behavior' script - so we no longer have to subject ourselves to 'long id' shenanigans to make sure we're modifiying the right controls.
Experiments with the new 'behavior' scripting methods have me pondering how I can adopt this for all my Revolution projects and which controls I can rebuild and put together in a single library for reuse - and perhaps for others to use. Ah, if only there was a function to conjure a Time-Turner so I could do everything I want to do but fail to find the time for...