Sorting, Searching, and Filtering in Advanced Grid and Tree Grid
Sorting, searching, and filtering your grid data is handled through a standard platform query service with a single Filter event and a QueryFilter parameter. When the Filter event is triggered, whether to sort, search, or filter the grid data, the QueryFilter parameter ensures that the returned data meets all of the specified conditions.
You can query your data for sorting, searching, and filtering in the following ways:
• Set up a data table that contains your data and access it using the standard Platform QueryDataTableEntries API.
• If generating data dynamically, through a data service, use the Query InfoTable function to sort and search data in an infotable.
For more information about query parameters, see the
Query Parameter for Query Services section of the ThingWorx Help Center.
Implement Sorting
1. Set the EnableSorting property to true, either by clicking it in the properties panel of the Mashup Builder or defining it in the JSON script of a dynamic configuration service. The QueryFilter property and Filter event appears in the properties panel.
2. Bind the QueryFilter property to the query input parameter where the data to be sorted is located:
◦ If your data is in a table, bind the QueryFilter to the query parameter of the QueryDataTableEntries service.
◦ If you are generating data through a data service, bind the QueryFilter to the queryFilter parameter of the data service.
3. Bind the Filter event to the service that is triggered when sorting begins:
◦ If you are using a data table, bind the Filter event to QueryDataTableEntries service.
◦ If you are generating data through a data service, bind the Filter event to the data service.
The following example shows a query parameter with the sort columns name and title applied:
{"maxItems":100000,"query":{"sorts":[{"fieldName":"name","isAscending":true},{"fieldName":"title","isAscending":true}]}}
When these binding steps are complete, the Bindings panel should look like the following:
Implement Searching
Searching provides the ability to find a string value in any column in a grid.
1. Set the EnableGridSearch property to true, either by clicking it in the properties panel of the Mashup Builder or defining it in the JSON script of a dynamic configuration service. The GridSearchLocation property, the QueryFilter property, and Filter appear in the properties panel.
2. Use the GridSearchLocation property, either in the Mashup Builder properties panel or in a JSON script, to configure a location for the Search field in the grid. Available options include: top right, top left, bottom right, and bottom left.
3. Bind the QueryFilter property to the output query parameter where the data to be searched is located:
◦ If your data is in a table, bind the QueryFilter to the query parameter of the QueryDataTableEntries service.
◦ If you are generating data via a data service, bind the QueryFilter to the query Filter parameter of the data service.
4. Bind the Filter event to the service that is triggered when searching begins:
◦ If you are using a data table, bind the Filter event to QueryDataTableEntries service.
◦ If you are generating data through a data service, bind the Filter event to the data service.
The following example shows a search query that searches for an event, Rain, in all columns:
{"maxItems":100000,"query":{"filters":{"type":"OR","filters":[{"fieldName":"id","type":"LIKE","value":"%Rain%"},{"fieldName":"date","type":"LIKE","value":"%Rain%"},{"fieldName":"max_temp","type":"LIKE","value":"%Rain%"},{"fieldName":"min_temp","type":"LIKE","value":"%Rain%"},{"fieldName":"cold","type":"LIKE","value":"%Rain%"},{"fieldName":"visibility","type":"LIKE","value":"%Rain%"},{"fieldName":"wind","type":"LIKE","value":"%Rain%"},{"fieldName":"precipitation","type":"LIKE","value":"%Rain%"},{"fieldName":"events","type":"LIKE","value":"%Rain%"},{"fieldName":"image","type":"LIKE","value":"%Rain%"},{"fieldName":"key","type":"LIKE","value":"%Rain%"},{"fieldName":"location","type":"LIKE","value":"%Rain%"},{"fieldName":"source","type":"LIKE","value":"%Rain%"},{"fieldName":"sourceType","type":"LIKE","value":"%Rain%"},{"fieldName":"tags","type":"LIKE","value":"%Rain%"},{"fieldName":"timestamp","type":"LIKE","value":"%Rain%"}]}}}
Implement Filtering
To implement filtering in a grid, add a Data Filter widget to the mashup where you are building the grid. You can only add a Data Filter widget to a grid that is bound to a data table based on an underlying Data Shape.
1. From the Widgets tab on the left side of the Mashup Builder, select the Data Filter widget and drag it into your mashup.
2. Bind the output query parameter of the Data Filter widget to the
QueryFilter property of the Advanced Grid. In this scenario, the
QueryFilter property is serving both an input and an output function. It receives input from the Data Filter, which is automatically combined with any active sorting and searching input, and generates a single output for the query parameter.
3. Bind the QueryFilter property to the query input parameter of the QueryDataTableEntries service of the data table being filtered, sorted, or searched.
The following example shows a Data Filter query with a single filter parameter, an event value of Rain:
{"maxItems":100000,"query":{"filters":{"fieldName":"events","type":"LIKE","value":"Rain*"}}}
A filter query can become much more complex when multiple filters are applied, or when filter input is combined with search and sort parameters. The following example shows a combination of sort, search, and filter parameters in a single output query:
{"maxItems":100000,"query":{"sorts":[{"fieldName":"id","isAscending":true},{"fieldName":"min_temp","isAscending":true}],"filters":{"type":"And","filters":[{"type":"And","filters":[{"fieldName":"events","type":"LIKE","value":"Rain*"},{"fieldName":"cold","type":"EQ","value":false}]},{"type":"OR","filters":[{"fieldName":"id","type":"LIKE","value":"%21%"},{"fieldName":"date","type":"LIKE","value":"%21%"},{"fieldName":"max_temp","type":"LIKE","value":"%21%"},{"fieldName":"min_temp","type":"LIKE","value":"%21%"},{"fieldName":"cold","type":"LIKE","value":"%21%"},{"fieldName":"visibility","type":"LIKE","value":"%21%"},{"fieldName":"wind","type":"LIKE","value":"%21%"},{"fieldName":"precipitation","type":"LIKE","value":"%21%"},{"fieldName":"events","type":"LIKE","value":"%21%"},{"fieldName":"image","type":"LIKE","value":"%21%"}]}]}}
In the advanced grid, when filtering is in use along with sorting and/or searching, the bindings should look like the following diagram when complete: