Skip to main content
Jamal Ashraf
Jamal Ashraf16-Pearl
16-Pearl
Feb 12, 2024 9:07 AM UTC
Solved

QueryDataTableEntries is not working expectedly Thingworx 9.3.7

 

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;

 

  

Rocko
RockoAnswer
19-Tanzanite
Feb 13, 2024 11:03 AM UTC

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"
 }
 ]
 }
};
View in thread

18 replies

DanZ15-Moonstone
15-Moonstone
Feb 12, 2024 2:59 PM UTC

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"]
 } 
}
Translate
Jamal Ashraf
Jamal Ashraf16-PearlAuthor
16-Pearl
Feb 13, 2024 5:21 AM UTC

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.??

Translate
Rocko
Rocko19-TanzaniteAnswer
19-Tanzanite
Feb 13, 2024 11:03 AM UTC

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"
 }
 ]
 }
};
Translate
    Jamal Ashraf
    Jamal Ashraf16-PearlAuthor
    16-Pearl
    Feb 14, 2024 9:55 AM UTC

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

     

     

     

    Translate
    Rocko
    Rocko19-Tanzanite
    19-Tanzanite
    Feb 14, 2024 9:59 AM UTC

    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?

    Translate
      Terms & ConditionsAccessibility statement