Use of a DataFilter widget

[etc6]

Hello guys,

I have an external Oracle database and it's no problem to show queried information (using a service for that of course)  inside a grid in a mashup. But how do I filter the information in this grid (InfoTable)?

I would like to know how to use a DataFilter widget on an InfoTable.

Could someone provide code (inside the different services) plus bindings?

I found tutorials for the bindings and different code snippets but never a complete, well explained, tutorial.

Thanks in advance

[vangne]

@etc6 Have you checked this information? It also contains a video tutorial which may help.

1. Which Thingworx version you are using?

2. Also, did you try filtering the information in the grid and received any error?

Thanks,

Vibhuti

[etc6]

Hey,

the provided information doesn't help a lot and the tutorial is good for the bindings but it does still not work.

1. ThingWorx 8.2.6-b456

2. When I try filtering I get this error:

org.json.JSONObject cannot be cast to java.lang.String

Here is a screen of my service and my bindings:

[vangne]

@etc6 

1. Have you tried replacing the [[...]] with <<...>>>?

2. Have you tested with the INPUT parameter of type String instead of JSON?

You may refer this article to get an idea on opType / dataType

Thanks,

Vibhuti

[etc6]

Hey @vangne 

1. Yeah I tried but this gives me an new error:

15:27:16 ERROR - Runtime exception handling data update for binding {"Id":"1be0e3bb-3c1c-4246-9ff7-d2fa4605991d","SourceArea":"Data","SourceSection":"Things_POC_ATBB_CHILD_CONNECTOR","SourceId":"QueryImplementingThingsWithData","SourceDetails":"AllData","TargetArea":"UI","TargetSection":"","TargetId":"dhxgrid-51","PropertyMaps":[{"SourceProperty":"","SourcePropertyType":"InfoTable","SourcePropertyBaseType":"INFOTABLE","TargetProperty":"Data","TargetPropertyType":"property","TargetPropertyBaseType":"INFOTABLE"}],"isBoundToSelectedRows":false}
Exception: Cannot read property 'length' of undefined
15:27:16 ERROR - Error in invoking service Things_POC_ATBB_CHILD_CONNECTOR, QueryImplementingThingsWithData, Things, POC_ATBB_CHILD_CONNECTOR, Services, QueryImplementingThingsWithData. Error from the server: "Unable to Invoke Service QueryImplementingThingsWithData on POC_ATBB_CHILD_CONNECTOR : Execute Query failed: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
"

2. Yeah I tried that too but the Input Parameter has to be of type Query (as in the tutorial video). If it is not of type Query I can not do the bindings as specified.

Thanks already but do you have any other ideas? Or perhaps a working example? Can't believe that this is so difficult to accomplish. 

[rjanardan · Accepted solution]

As I understand you already have a service that returns all data in an Infotable(as expected) from an external DB. 
One approach would be to not bind this data directly to a grid and instead feed this as input to a second service which will do the actual filtering. 

Features for this new service 

1. Input:Two. 1 of type infotable based on same data shape of the infotable in your existing service and 2 of type query

2. Output: Infotable based on same data shape of the infotable in your existing service

3. Code: Use snippet Resources -> Infotable functions -> Query. Edit the code with your Input variable names chosen (infotable and query)

In mashup,

1. Add new service but it should not be called on mashup loaded
2. bind:

• service invoke completed event of your service to new service
• query from Data Filter widget to query parameter of new service
• returned data from your first service to data of data filter widget & input infotable of new service
• output infotable of new service to Grid
• changed event of Data Filter widget to new service

[etc6]

Hello,

I appreciate your help and I think your given solution

led me to the right direction but I still have a little problem.

I've created the new sevice and put the script inside as you said to do.

But the problem is that I don't know how I can acces the Inputparameters with the script.

I changed the names and tried various thing but I either get an NullPointerException

(08:48:15 ERROR - Error in invoking service Things_POC_ATBB_CHILD_CONNECTOR, GetPRwithoutCCR, Things, POC_ATBB_CHILD_CONNECTOR, Services, GetPRwithoutCCR. Error from the server: "Error executing service GetPRwithoutCCR. Message :: Wrapped java.lang.NullPointerException - See Script Error Log for more details.")

or my table in the mashup remains empty when I do the filtering.

I also tried to acces the properties direct but then I get this error

09:24:42 ERROR - Error in invoking service Things_POC_ATBB_CHILD_CONNECTOR, GetPRwithoutCCR, Things, POC_ATBB_CHILD_CONNECTOR, Services, GetPRwithoutCCR. Error from the server: "Error executing service GetPRwithoutCCR. Message :: TypeError: Cannot read property "RPT" from undefined - See Script Error Log for more details." 

which tells me again that I have no acces to the Infotableparameter.

The filter has problems to get basic data it needs for filtering. 

As I said I tried various thing (changed bindings, changed code) but I don't get this filtering done.

Any ideas?

[rjanardan]

If your initial service is returning data properly as an infotable there is no reason for this approach to not work. 

I hope you have not set the new service(GetPRwithoutCCR?) to be invoked upon Mashup load(Mashup loaded checkbox). Your new service has to be triggered only after your service to fetch data from DB has completed execution. Otherwise, the infotable input would be null. 

[etc6]

Ah I figured it out.

I hadn't the first neither the second service to be invoked upon Mashup load. 

But actually the first service has to be invoked upon Mashup load.

My bad that I hadn't checked this. Thanks for your help guys.