Iterate Datatable

[VaibhavShinde]

Hi ,

I have Datatable with some records. I want to get the specific rows or field values by using Timestamp, and later I have to send the result through the mail. how we can get it? can someone has done it?  I am expecting pseudo code in javascript using datatable.

Thank you

[rosharma]

i think you need to create datatable with datashape having one datetime field as primary key.

store data to this datatable using service adddatatableentries.

now fetch the data from this datatable using getdatatableentrybykey ( where key is your timestamp field)

this will return one row in form of infotable. then you use infotable functions primarily for loop to iterate through infotable , get the values and store it in variables.

later you can send those values in email using sendmessage or other services from your mail server thing.

[VaibhavShinde]

Hello rosharma,

first of all thank you for quick response, I have already mahineKey as Primary key and suppose I want to get last week report by using timestamp field of Datatable. how we can do it ? by using query? by using any javascript code? 

Thank you 

[rosharma]

i think you can use QueryDatatableEntries on datatable passing in query parameter using "Between" keyword to generate the data specific to users inputs.

query would be like 

var query = 
{
    "filters": {
        "type": "Between",
        "fieldName": "Duration",
        "from": "2",
        "to": "12"
    }
};

And more on query parameter you can refer thingworx help center link 

https://support.ptc.com/help/thingworx_hc/thingworx_8_hc/en/index.html#page/ThingWorx/Help/Composer/Things/ThingServices/QueryParameterforQueryServices.html

[VaibhavShinde]

I want to send report using timestamp and i have scheduler with daily weekly n monthly configuration, now you got overall scenario.
Thanks

[rosharma]

i think you need to set schedulers for your timings like daily, weekly and monthly.

on these times, subscription script will run to call getdatatableentriesbykey(passing it the timestamp) as input

once you have the data then you can sent that through email using mail server thing;

[VaibhavShinde]

Hi rosharma,

The key parameter for getDatatableEntryByKey(key) of Datatable should be primary key and here we will have duplicate timestamp , how we can ? and here for timestamp we can't make primary key because we will have multiple records with same Date still if we pass timestamp as parameter its not taking. What is solution for this?

Thank you

[rosharma]

ok

in this case you can use QueryDataTableEntries( * or any kind of query data services) on datatable where you can pass query as input

query you can pass filters using that Between keyword for timestamps that i sent you earlier in this thread

[VaibhavShinde]

Here one more thing is user is not entering any date value , it means user not selecting Date values manually , we need to calculate it based on last week , last month etc

thank you

[rosharma]

That calculations part to get the dates you need to do on your own above the snippet service before passing it to query inputs. like below

and if these are date values are coming from another services then , in this service you can add 2 inputs on service and map these

....................................................................................................

// do your calculations here for your date value input to query. get values from last week or month
//like var fromDate & var toDate and pass to query inputs below

var query =
{
"filters": {
"type": "Between",
"fieldName": "timestamp",
"from": "fromDate",
"to": "toDate"
}
};

// result: INFOTABLE dataShape: ""
var result = me.QueryDataTableEntries({
maxItems: undefined /* NUMBER */,
query: query /* QUERY */,
values: undefined /* INFOTABLE */,
source: undefined /* STRING */,
tags: undefined /* TAGS */
});

[VaibhavShinde · Accepted solution]

yes, got it now , major query resolved!! now I am looking for filter data by avoiding time in timestamp. It should work with matching only Date without time. how can we filter timestamp field?

thanks