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.