Skip to main content
J
jwilliams1110-Marble
10-Marble
October 26, 2018
Solved

Grid Widgets

  • October 26, 2018
  • 1 reply
  • 4228 views

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

Best answer by wposner-2

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; 
}

 

1 reply

W
wposner-21-Visitor
1-Visitor
October 29, 2018

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.

J
jwilliams1110-MarbleAuthor
10-Marble
October 29, 2018

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

    W
    wposner-21-Visitor
    1-Visitor
    October 29, 2018

    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.  

    Terms & ConditionsAccessibility statement