Question
· Apr 1, 2016

How to extract column sorting of %ZEN.tablePane with or without snapShot

Hi all,

I've been looking for a way to extract the sorting  from the  ^CacheTemp.zenData global that contain the tablePane snapshot data in order to use it on a report following the current UI sorting criteria.

I could see that if the column is sorted by one column, new entries with this structure are created:

^CacheTemp.zenData(<sessionId>,<snapShotId>,<tablePaneId>,"index",<columnId>,<value>,<dataId>)=""

These are created the first time that the sort criteria is applied (asc) and has the same amount of entries as the data being displayed. But then if I change the criteria to descentant this data dont change and then I cannot differ whether the sorting order.

 

Is there a way to get the data of the ^CacheTemp.zenData in the same sorting displayed on the page?

 

Best regars

Discussion (6)1
Log in or sign up to continue

There is no easy way to achieve this on the server side. But you can easily extract the currently displayed data set on the client. Here is a code snippet that works with the tablePane demo ZENTest.TableTest.cls in the SAMPLES namespace:

var t = zen('table')
for (var i = 0;i < t.pageSize; i++) { console.log(t.getRenderedRowData(i)) }

The code snippet will render each individual row, respecting the current filtering and order.

Presumably, if you're showing the results in a report sorted based on the currently-selected column (in the currColumn property of the tablePane), you could also look at the sort order for the tablePane (sortOrder property, "asc" or "desc") and then $order over the index global in reverse if it's "desc".

Here's a class query/example that could help - you can modify the ROWSPEC to fit your purposes.

/// Queries snapshot data for a Zen tablePane, optionally sorted.
/// QuerySnapshotExecute returns an error if the snapshot or a required index (for sorting) is missing.
/// 
/// sessionId : CSP session ID of the user whose tablePane will be shown
/// snapshotId : snapshotId property of the tablePane
/// tablePaneIndex : index property of the tablePane
/// sortColumn : (optional) currColumn property of the tablePane: the column name of the column to sort by
/// sortOrder : (optional; default is ascending) sortOrder property of the tablePane (asc/desc)
Query QuerySnapshot(sessionId As %String, snapshotId As %Integer, tablePaneIndex As %Integer, sortColumn As %String = "", sortOrder As %String = "") As %Query(ROWSPEC = "col1:%String,col2:%String,col3:%String,col4:%String,col5:%String,col6:%String,col7:%String,col8:%String,col9:%String,col10:%String,col11:%String") [ SqlProc ]
{
}

ClassMethod QuerySnapshotExecute(ByRef qHandle As %Binary, sessionId As %String, snapshotId As %Integer, tablePaneIndex As %Integer, sortColumn As %String = "", sortOrder As %String = "") As %Status
{
    Set tDataGlobal = "^CacheTemp.zenData("""_sessionId_""","_snapshotId_","_tablePaneIndex_",""data"")"
    Quit:'$Data(@tDataGlobal) $$$ERROR($$$GeneralError,"Invalid reference to tablePane snapshot.")
    Set tIndexGlobal = $Case(sortColumn,"":"",:"^CacheTemp.zenData("""_sessionId_""","_snapshotId_","_tablePaneIndex_",""index"","""_sortColumn_""")")
    Quit:'$Data(@tIndexGlobal) $$$ERROR($$$GeneralError,$$$FormatText("tablePane snapshot index not populated for property %1",sortColumn))
    Set qHandle = $ListBuild(tDataGlobal,tIndexGlobal,$Case(sortOrder,"desc":-1,:1),"","")
    Quit $$$OK
}

ClassMethod QuerySnapshotFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = QuerySnapshotExecute ]
{
    Set $ListBuild(tDataGlobal,tIndexGlobal,tSortOrder,tSub1,tSub2) = qHandle
    If (tIndexGlobal = "") {
        // Not sorting by any column.
        Set tSub2 = $Order(@tDataGlobal@(tSub2),tSortOrder)
        If (tSub2 = "") { Set AtEnd = 1 }
    } Else {
        // First $order over values of the indexed column
        Set:tSub1="" tSub1 = $Order(@tIndexGlobal@(tSub1),tSortOrder)
        If (tSub1 '= "") {
            // There may be multiple matches for a single key in the index. Get the next one for this key.
            Set tSub2 = $Order(@tIndexGlobal@(tSub1,tSub2),tSortOrder)
            // If we previously were on the last value for the index key, move on to the next index key.
            If (tSub2 = "") {
                Set tSub1 = $Order(@tIndexGlobal@(tSub1),tSortOrder)
                Set:tSub1'="" tSub2 = $Order(@tIndexGlobal@(tSub1,tSub2),tSortOrder)
            }
        }
        If (tSub1 = "") && (tSub2 = "") { Set AtEnd = 1 }
    }
    
    If 'AtEnd {
        Set Row = @tDataGlobal@(tSub2)
        Set $List(qHandle,4) = tSub1
        Set $List(qHandle,5) = tSub2
    }
    Quit $$$OK
}

ClassMethod QuerySnapshotClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = QuerySnapshotExecute ]
{
    Quit $$$OK
}

Sample use, against /csp/samples/ZENTest.TableTest.cls (and, in my case, with the class query defined in App.TablePaneUtils):

call App.TablePaneUtils_QuerySnapshot(<your session ID>,<your snapshot number>,23)
call App.TablePaneUtils_QuerySnapshot(<your session ID>,<your snapshot number>,23,,'desc')
call App.TablePaneUtils_QuerySnapshot(<your session ID>,<your snapshot number>,23,'Title','asc')