FindDataTableEntries Service do not work and returns No data

[MA8731174]

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.

@CarlesColl  @Tudor  can you guys please share your knowledge. @PaiChung 


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.

[Rocko]

You might run into CS363928.

If so, try with an updated version or create a case on your own,

I also want to mention that you are maxing out datatables with 100k rows, so performance will not be too great. I would recommend to use SQL tables instead if you have this amount of rows.

For reference:

https://support.ptc.com/help/thingworx/platform/r9/en/index.html#page/ThingWorx/Help/Composer/DataStorage/SizingLimitsofDatatables.html#

https://support.ptc.com/help/thingworx/platform/r9/en/index.html#page/ThingWorx/Help/ModelandDataBestPractices/when_to_store_data_outside_the_platform.html#

https://support.ptc.com/help/thingworx/platform/r9/en/#page/ThingWorx/Help/ModelandDataBestPractices/UsingJDBCasthePersistenceProvider.html

If you are on postgres, you could also use SQL directly to search in the data_table DB table, because postgres allows querying JSON attributes,  although this would be unsupported, i.e. when the schema changes or so, you'd be on your own. I'd recommend creating your own properly indexed SQL tables, move the data there and access them with performant queries.

[Rocko]

Quick follow-up due to some learnings:

1) make sure your Datashape does not have default values, or you provide values for them, as suggested per

https://www.ptc.com/en/support/article/CS306221 and https://www.ptc.com/en/support/article/CS292316

2) You probably don't have this, but it's a good reminder, make sure your Datashape does not contain field names already used in DataTables (key, location, source, sourceType, tags and timestamp)

[MA8731174]

This is below the fields of my Datashape which does not include (key, location, source, sourceType, tags and timestamp) and i am adding serialNumber in service FindDataTableEntries to find the entry but it is not working. It returns no data. Please help me in this regard

[Rocko]

Do any of those fields have default values defined? If so, FindDataTableEntries does not work and you will have to use QueryDataTableEntries with a query parameter (or remove the default value definitions)).

[MA8731174]

@Rocko  I have uploaded a full picture now. Please have a look there is no default values for these fields. 

[MA8731174]

look thats how i am using findDatatableentries. i am just giving the serialNumber of the entry and it returns me no data. I even have set an index in configuration of the datatable then i executed the service reindex also and restart thing also. but no it is not returning the entry with this serialnumber.

[Rocko]

Ok, just to be sure also check that the embedded qualityCheck DS does not have a default value defined. Not sure what is happening here, but I wonder if you can/should have empty key fields. You defined the key to have two columns, serialNo and qualityCheckTimestamp, but qualityCheckTimestamp is empty. This could be issue. If you can't make it work, just use QueryDataTableEntries.

[MA8731174]

I have tried to use another field (other then primary key) to find the entry but it is still not working and there is no default value for the embedded qualitycheck datashape. I want to use this service because it is fast to find an entry with it as it uses an index to find the entry but it does not work. i have no idea that why it is not working. QueryDataTableEntries service is slow because it does not use index so it searches for all the field in an entry. 

[Rocko · Accepted solution]

But you would still have the situation of a null value in a PK field. You might have to change something on the definition side of the DT, not the query side. (EDIT: Sorry, I was assuming the date value is null in the table, but it was just in the query, which should work)

At this point, if you want to investigate further, I'd suggest to create a fresh new DataTable for testing, using a simple datashape. Enter just two or three records and check if and how it works there - because FindDataTableEntries does work in general. Then work yourself forward and create another DataTable, using the qualityCheck DataShape, and repeat the testing. Maybe adding other indexes and reindexing causes the issue here.

To rule out you're calling FindDataTableEntries with spaces in the string argument I recommend writing a service that calls FindDataTableEntries instead of calling the service interactively.

Other than that, you could create a ticket with support.

[MA8731174]

Thanks for info. Its working,. I have written a service and use it there and not trying directly from the datatable thing. Now the question is it is not fast as compared to QueryDataTableEntry. Its almost same performance. I thought it would be fast but i have not seen a major difference. any idea?

[Rocko]

Hard to say without seeing what you did in the service. Datatables in general are not particularly fast, as all row data go into one JSON column in the DB. So filtering is done no application side, not DB side.