FindDataTableEntries Service do not work and returns No data
So first of all in my datatable i have entries and the datashape for each entry is as below:
qualityCheckTimestamp [DateTime]
serialNumber [DateTime]
MaterialNumber [String]
userIdentification [String]
SData [InfoTable]
status [Boolean]
ProDate [DateTime]
affix [String]
qualityCheckTimestamp and serialNumber are my primarykeys.
With FindDataTableEntries first of all i have set an index in configuration of the datatable. i have selected userIdentifcation field as my index in Configuration. I have set an index then restart my thing then reindex service i have executed. Now in FindDataTableEntries service in parameters(maxItems and values) in values parameter i have written in useridentification field for eg JHON and other all fields i have left empty because i need the data where the useridentification field will match to JHON but it returns no data.
Can someone please give me details about my problem. Why i am unable to get the data? I have tried to set the serialNumber also as my index in Configuration and still it returns no data.
My usecase is that i have in my datatables the data from last 3 years and the entries are 100 thousand in each datatable. i want to get the entries stored in 2021 then in 2022 and then in 2023 that i can get with the qualityCheckTimestamp field ...the total numbers like for eg: 2021 we had 70 thousand entries. So that i can show the number on the chart thats it. how can i achieve it in a best way as for now my query is taking so long to get this count of data with year. any good idea? Currently i am doing like that but this query is taking so long for me to get all the entries and then distribute them with the year. I thought of using FindDataTableEntries but there is a limitation that you can only get the data which would be equal to your value thats it and NOT like between or greater then, lesser then can be used. How can i get the data from the datatables fast and categorized them in a year and have a numberCount for each year. ANY IDEA 🙂
AllMaterialDatasetThings.forEach(row => {
let jamalData= row.jamalData;
let field = getFieldFromQualityData(jamalData);
let maxItems = Things[jamalData].GetDataTableEntryCount();
if (field && Things[jamalData]!=null) {
// Modify the query to include a date filter, reducing the amount of data fetched
let dataEntries = Things[jamalData].QueryDataTableEntries({
maxItems: maxItems,
query: {
filters: {
type: "BETWEEN",
fieldName: "timestamp",
from: "2020-01-01T00:00:00Z",
to: "2024-12-31T23:59:59Z"
}
}
});
// Process the filtered entries
dataEntries.rows.toArray().forEach(entry => {
let year = entry.qualityCheckTimestamp.getFullYear();
entriesCount[field][year] = (entriesCount[field][year] || 0) + 1;
});
}
});
// Populate the result with the aggregated data
for (let year = 2020; year <= 2024; year++) {
let newRow = { Year: year };
for (let field in entriesCount) {
newRow[field] = entriesCount[field][year] || 0;
}
result.AddRow(newRow);
}
logger.info("Entries Count by Year: " + JSON.stringify(entriesCount));
I have also used GetDataTableEntries which would get all the entries first and then i can categorize the entries with the year and show them in result but the time it takes the same as with QueryDataTableEntries. There is no time difference in getting results both are taking long. I want all the entries which are in the datatable. Currently we have data from 2020 till 2023. So my goal is to get all the entries from the datatable and then categorize it with the numbercount and show on the chart.