Grid Widgets

[jwilliams11]

I would like to give my users the capabilities available in the Grid-advanced widget, like multiple column sorting and saving grid layouts, but I'm having trouble using the Grid-advanced widget with my external SQL Server data source.  I've been advised by PTC support that a data table is required to use a Grid-advanced widget, but I'm inexperienced in utilizing a data table to manage data from a mashup to an external SQL Server database, and it doesn't seem to be a valid approach.  It appears that you have to transition completely to using a data table instead of an external SQL Server database in order to make use of the Grid-advanced widget.  My questions are:

1.  Is there any way to use a Grid-advanced widget without having to put your externally-stored data in a data table?

2.  I see a DynamicColumnsGrid widget listed in Composer, but can't find any documentation on it, either within Composer or on the Marketplace.  Is there any documentation available?  I'm curious as to whether or not that widget might provide us some of the features we're looking for with the Grid-advanced widget.

Any/all advice is appreciated!

Thanks,

Jeff

[wposner-2]

The functionality of the advanced grid widget is enabled primarily through cookies.  The filtering is simply a JSON object representation of a query.  All you need to use the advanced grid widget is an infotable which you get when you query your SQL DB.  If you need to use the filter, you just need service to parse the query JSON and construct your SQL query accordingly.

[jwilliams11]

Thank you for your response.  I was told by the PTC help desk this morning that, currently, you have to store your data in a data table in order to use the sorting, searching, and filtering capabilities of the Grid Advanced widget.  However, the capability to sort/filter the Grid Advanced widget via an infotable has been accepted by R&D as an enhancement request (JIRA PSPT-4106), though no implementation date for the enhancement has been set.

The above being said, is your suggested approach to using JSON to sort/filter infotable-based data bound to a Grid Advanced widget still valid?  If so, would you be so kind as to provide some example script of sorting data via this method?

Thanks,

Jeff

[wposner-2]

If you take a look at the JSON object passed as a result of the filter event you get something like this:

{"query":{"filters":{"type":"OR","filters":[{"fieldName":"id","type":"LIKE","value":"%ad%"},{"fieldName":"sourceType","type":"LIKE","value":"%ad%"},{"fieldName":"location","type":"LIKE","value":"%ad%"},{"fieldName":"name","type":"LIKE","value":"%ad%"},{"fieldName":"center","type":"LIKE","value":"%ad%"},{"fieldName":"source","type":"LIKE","value":"%ad%"},{"fieldName":"boundaries","type":"LIKE","value":"%ad%"},{"fieldName":"key","type":"LIKE","value":"%ad%"},{"fieldName":"timestamp","type":"LIKE","value":"%ad%"},{"fieldName":"tags","type":"LIKE","value":"%ad%"}]}}}

This is based on the datashape used by the infotable used to drive the data grid.  So you can easily iterate through this object and construct you sql query to pass to your SQL service.  

Similarly, when you do a sort, something like this gets passed back to your service (depending on the row you just tired to sort on):

{"query":{"sorts":[{"fieldName":"name","isAscending":false}]}}

So all you need is a service with a "query" parameter that is defined as a Query type.  This service would parse the query object, construct your SQL query including the sortBy function if you're sorting on a column, and then return the results back to your grid.  

[jwilliams11]

Thank you - making progress!  My service now sorts the Grid-advanced widget when sorted by one column, but I'm having trouble figuring out how to parse the JSON query parameter to sort on multiple columns.  Can you please share with me how you would parse the JSON query parameter when sorting by multiple columns in the Grid-advanced widget?  Up until now, I've only dealt with InfoTables, so dealing with JSON objects is still foreign to me, and any additional guidance you can provide is much appreciated!

[wposner-2 · Accepted solution]

Based on what I can see from watching the service calls my my browser dev tools, you can only sort on a single column at a time.  If you have two columns: name and timestamp, and first sort on name, if you next try to sort on timestamp you will lose your name sort.  

For the following object:

{"query":{"sorts":[{"fieldName":"name","isAscending":true}]}}

your service parameter name is "query" and it contains {"sorts":[{"fieldName":"name","isAscending":true}]}.

sorts is an array, but since in this case, it will only ever contain single row we can just access it with something like this:

var fieldName=query.sorts.fieldName;

var ascending=query.sorts.isAscending;

If you are filtering and sorting you get something like this:

1. {"query":{"sorts":[{"fieldName":"name","isAscending":true}],"filters":{"type":"OR","filters":[{"fieldName":"id","type":"LIKE","value":"%william%"},{"fieldName":"sourceType","type":"LIKE","value":"%william%"},{"fieldName":"location","type":"LIKE","value":"%william%"},{"fieldName":"name","type":"LIKE","value":"%william%"},{"fieldName":"center","type":"LIKE","value":"%william%"},{"fieldName":"source","type":"LIKE","value":"%william%"},{"fieldName":"boundaries","type":"LIKE","value":"%william%"},{"fieldName":"key","type":"LIKE","value":"%william%"},{"fieldName":"timestamp","type":"LIKE","value":"%william%"},{"fieldName":"tags","type":"LIKE","value":"%william%"}]}}}

"query" now contains a "sorts "array and a "filters" array.  You can still access sorts exactly the same as the previous example.

For your fieldNames you'll want to use a for each loop over the filters array:

sql = sql + " where ";
for (var field in filters) {
    sql = sql + field.fieldName + " LIKE " + field.value; 
}

[jwilliams11]

Thank you for your detailed follow-up!  With your feedback I was able to parse the sort array from the JSON object, which got me significantly closer to the desired result than where I started, and I learned some tips that could be very useful in the future.  However, it looks like using the Grid-advanced widget with an infotable is a little too cumbersome at present for the benefits received.  I'll just hold off for the enhancement that'll allow the widget to easily work with an infotable.

Thanks again for all your help and time!!