Using the QueryDataTableEntries method prompt [" value "] not found

[lliangjb]

var values = Things["SearchMapDT"].CreateValues();

values.type = type; //NUMBER [Primary Key]

values.province = province; //STRING

values.customer = customer; //STRING

values.country = country; //STRING

values.city = city; //STRING

values.county = county; //STRIN

var query1 =

{

    "filters": {

        "type": "OR",

        "filters":[

            {

                "type":"EQ",

                "fieldName":"type",

                "value":type

            },

            {

                "type":"EQ",

                "fieldName":"province",

                "value":province

            },

            {

                "type":"EQ",

                "fieldName":"customer",

                "value":customer

            }

     ,

            {

                "type":"EQ",

                "fieldName":"country",

                "value":country

            }

     ,

            {

                "type":"EQ",

                "fieldName":"city",

                "value":city

            },

            {

                "type":"EQ",

                "fieldName":"county",

                "value":county

            }

        ]

    } 

}

var params = {

  maxItems: undefined /* NUMBER */,

  values: values /* INFOTABLE*/,

  query: query1 /* QUERY */,

  source: undefined /* STRING */,

  tags: undefined /* TAGS */

};

// result: INFOTABLE

var result = Things["SearchMapDT"].QueryDataTableEntries(params);

LOG MESSAGE:

[context: com.thingworx.webservices.context.HttpExecutionContext@40c66c25][message: Execution error in service script [SearchMapDT myquery] : Wrapped java.lang.RuntimeException: org.json.JSONException: JSONObject["value"] not found. Cause: org.json.JSONException: JSONObject["value"] not found.]

[liliu]

Hi Mr. Guo,

I don't think we shold query the data twice.

values or query one should be undefined. I suggest to set values to undefined.

var params = {

  maxItems: undefined /* NUMBER */,

  values: values /* INFOTABLE*/,

  query: query1 /* QUERY */,

  source: undefined /* STRING */,

  tags: undefined /* TAGS */

};

Best Regards,

Lily

[wposner-2]

Actually, you need:

var values = Things["SearchMapDT"].CreateValues();

But you don't need to set any of the properties.

When you get to the params for the QueryDataTablleEntires you simply do:

var params = {

  values: values /* INFOTABLE*/,

  query: query /* QUERY */,

};

// result: INFOTABLE

var result = Things["SearchMapDT"].QueryDataTableEntries(params);

[liliu]

Hi Wayne,

Thanks for your suggestion. It works now.

Lily

[liliu · Accepted solution]

Hi Mr. Guo,

For your case, I suggest using Data Filter widget.

BR,

Lily

[wposner-2]

How is this the correct answer?  The data filter widget has nothing to do with correctly using the QueryDataTableEntries service.

[liliu]

Hi Wayne,

Mr. Guo wants to filter data table data with textbox values and customized service. It OK, but I suggest using DataFilter widget, which does not need customized service. It would be easier.

By binding data filter's query to the service QueryDataTableEntities's parameter query.;

Best Regards,

Lily

[wposner-2]

Hi Lily...

That's fine to suggest using the data filter widget, but in the explicit context of the original poster's question, this is not the correct answer.  If, as a relatively new user to TWX, I make the same mistake in formatting the parameters for the query service and get the error from the first message in the thread, instructing me to use the data filter widget is not going to answer my question or fix my problem.  The right answer is to explain how to properly set the parameter values to avoid the error. 

And I don't get any indication based on the original message that exclusively indicates the OP wants to filter a data table based on some value(s) from a text box. Making an assumption is one thing, but there are many instances where I need to perform a data table query and a data filter widget is not a viable solution because the data I need to perform the query is being passed in to my service from multiple sources.

[liliu]

Hi Wayne & Mr. Guo,

First of all, sorry to make this thread a little bit confused.

In fact, Mr. Guo sent emails to me and described detail issue of this thread. That's why I know that Mr. Guo was using textboxes to pass parameters to customized service. And I know he wanted to filter the columns which end users give values and ignore these textboxes without values. It's will involve lots of customized coding, not convenience enough. That's why I introduce Data Filter widget.

I will add additional information if the thread owner communicates with me by using email.

Here I'd like to share the answer of Mr.Guo's original question:

Or operator: (query out the records parentname=="Shanxi" || name=="Xian")

var query1 =

{

    "filters": {

        "type": "OR",

        "filters":[

            {

                "type":"EQ",

                "fieldName":"parentname",

                "value":"Shanxi"

            },

            {

                "type":"EQ",

                "fieldName":"name",

                "value":"Xian"

            }

        ]

    } 

};

var params = {

    maxItems: undefined /* NUMBER */,

    query: query1 /* QUERY */,

};

var result = me.QueryDataTableEntries(params);

And operator: (query out the records parentname=="Shanxi" && name=="Xian")

var values = me.CreateValues();

values.parentname = "Shanxi"; //STRING

values.name = "Xian";

var params = {

    maxItems: undefined /* NUMBER */,

    values: values/* INFOTABLE*/,

};

var result = me.QueryDataTableEntries(params);

Should you have any concern, please feel free to let me know.

Best Regards,

Lily