Skip to main content
M
MA873117416-Pearl
16-Pearl
February 12, 2024
Solved

QueryDataTableEntries is not working expectedly Thingworx 9.3.7

  • February 12, 2024
  • 2 replies
  • 16356 views

 

it has to match with the serial Number and return the entry where it matches the serial number but it  returns all the entries of datatable. sometimes it works fine but again after sometime if i run the service again it shows me all the entries. it works fine until i give the serialnumber to get the entries and then when i add wrong serialnumber then it keeps showing me the old output with all the entries. I have never seen such a wrong behaviour before. Anyone can please help me in this regard

 

const SERVICENAME = "evaluateExistingSerialNumber";
logger.info(me.name +" :: "+ SERVICENAME +" :: Start Service");


//let result;
let maxEntriesToFilter 	= 40;

let latestEntriesInfoTable = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
 infoTableName: "InfoTable",
 dataShapeName: "ReUse.LatestEntries.DS"
 });
let singleEntryInfoTable = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
 infoTableName: "InfoTable",
 dataShapeName: "ReUse.LatestEntries.DS"
 
});


//try {
 
latestEntriesInfoTable = Things["ReUse." + lineName + "-" +Location +".DT"].QueryDataTableEntries({
	maxItems: maxEntriesToFilter /* NUMBER */,
	query: {
 "type": "And",
 "filters": [{
 	"type": "IN",
 	"fieldName": "serialNumber",
 "values": [serialNumber]
 	},
 
 
 
 
 ],
 
}
});

//DEBUG STARTS
// For debugging (select OUTPUT as an infotable latestentriesDS- first 3 lines for latest entry and last line for all the entries)
//let resultRow = latestEntriesInfoTable.getRow(0);
//singleEntryInfoTable.AddRow(resultRow);
//let result = singleEntryInfoTable;

let result = latestEntriesInfoTable;

 

  

Best answer by Rocko

The syntax needs another "filters" on top level, that is missing in your example. On your other question, yes, that is what the "And" filter is for.

Check lines 3 and 5 in this example and compare with yours.

var query =
{
 "filters": {
 "type": "And",
 "filters": [
 {
 "type": "GT",
 "fieldName": "Duration",
 "value": "12"
 },
 {
 "type": "Tagged",
 "fieldName": "tags",
 "tags": "MaintenanceIssues:PowerOutage"
 }
 ]
 }
};

2 replies

D
DanZ15-Moonstone
15-Moonstone
February 12, 2024

That is strange, I can reproduce the issue. But it seems to be related to the (in this case) unnecessary filter composition with the "AND" type. Try this:

{
 "filters": {
 "type": "IN",
 "fieldName": "serialNumber",
 "values": ["serialNumber"]
 } 
}
M
MA873117416-PearlAuthor
16-Pearl
February 13, 2024

i have removed it but thats not an issue actually. If i add in fitler another query with fieldName then the behviour gets weired.

My question: if i add two fields in filter to filter and i want only that entry where the both fields present with the value i give. is it possible to get only that entry? OR QueryDatatableentries gives even though one field filter will be fullfilled.??

Rocko
Rocko19-TanzaniteAnswer
19-Tanzanite
February 13, 2024

The syntax needs another "filters" on top level, that is missing in your example. On your other question, yes, that is what the "And" filter is for.

Check lines 3 and 5 in this example and compare with yours.

var query =
{
 "filters": {
 "type": "And",
 "filters": [
 {
 "type": "GT",
 "fieldName": "Duration",
 "value": "12"
 },
 {
 "type": "Tagged",
 "fieldName": "tags",
 "tags": "MaintenanceIssues:PowerOutage"
 }
 ]
 }
};
    M
    MA873117416-PearlAuthor
    16-Pearl
    February 14, 2024

    @Rocko I want the entry where the serialnumber and productcategory have the values which i will give and i want to match it as it is. Can you please see the code what mistake i am doing? Because it is showing me NO DATA. but if i execute the commented query for serialNumber only then it shows the entries only with that serialnumber which is good but i want only those entries which will match with both the serialNumber and productCategory.

     

     

     

    const SERVICENAME = "evaluateExistingSerialNumber";
logger.info(me.name +" :: "+ SERVICENAME +" :: Start Service");



let maxEntriesToFilter 	= 30;

let latestEntriesInfoTable = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
 infoTableName: "InfoTable",
 dataShapeName: "ReUse.QualityData.DS"
 });



try {
 logger.info("ReUse." + lineName + "-" +Location +".DT");
latestEntriesInfoTable = Things["ReUse." + lineName + "-" +Location +".DT"].QueryDataTableEntries({
 
	maxItems: maxEntriesToFilter /* NUMBER */,
//	query: {
 
// "filters": {
// 	"type": "IN",
// 	"fieldName": "serialNumber",
// "values": [serialNumber]
// 	},
 query: {
 "filters": {
 "type": "And",
 "filters": [
 {
 "type": "IN",
 "fieldName": "serialNumber",
 "value": [serialNumber]
 },
 {
 "type": "IN",
 "fieldName": "productCategory",
 "value": [productName]
 }
 ]
 }
 }

 // }
});

} catch(e){
logger.info("the error is here"+e);
}

let result = latestEntriesInfoTable;

     

     

     

    Rocko
    Rocko19-Tanzanite
    19-Tanzanite
    February 14, 2024

    I can check later, but when you compare to only one value and not multiple, is there a reason why you use IN and not EQUALS in your filters?

      Terms & ConditionsAccessibility statement