Objective: To display the volume used based on an Easy Charts StartDate and EndDate.
So far I've figured out how to get the charts start and end dates, pass that to a table query and display a listing of all the data points. This is a lot of extra data, as I only need to know the tag's value at the start and end of the time selected on the Easy Chart. I've found a few similar questions on the forum, but none of them look to exclude data. Reading Reading and Writing to Tags - Ignition User Manual 8.0 - Ignition Documentation in the docs, but it will return the values, quality, and timestamp; I want to supply the timestamp to read the value at.
I'm still really new to this, so maybe I'm just not aware of something simple. I think I could get this solved, but at the cost of a lot of unnecessary queries.
Clarification: I only need the volume at the StartDate and at the EndDate, to calculate the difference. The data in between is useless for this purpose, as the level can change due to outside factors.
Iām not for sure exactly what youāre looking for. You state you want to display the volume used between the StartDate and EndDate. To me this sounds like you want to aggregate the values.
If thatās the case, then it should be as simple as using and aggregate function in the query.
Sorry I wasnāt clearer, I want to know the values at the Start and the End of the chart, which will be subtracted to determine volume lost to the process. The values in between Start and End can fluctuate as product is pumped between vessels during the process. I only need to know the actual quantity at Start and at End, in between is not necessary. Iāll look further into the Tag History Binding.
If you are pulling from tag history, youāll use the Range aggregation in system.tag.queryTagCalculations. If you are using data from a regular DB table, then it would be something like this,
SELECT max(volume) - min(volume)
FROM myTable
where t_stamp between 'yourStartDate' and 'yourEndDate'
SELECT max(āWork Tank 1 Imperial Gallonsā) - min(āWork Tank 1 Imperial Gallonsā)
FROM group_table
WHERE t_stamp between āStartDateā and āEndDateā
You're supplying the literal text "StartDate" here instead of the actual start date and end dates. You need to use the property selector to select these properties from your easy chart. You should end up with something like {Root Container.EasyChart.StartRange} or similar
Iāve made some changes, I think if I do two separate queries to get the values, then use my expression to get the difference, it will be less traffic.
Trying to get the value of the tag at the startdate of the chart using:
SELECT {[default]Work Tank 1/Work Tank 1 Volume Imperial Gallons.value}
FROM charge_reports
WHERE t_stamp = {Root Container.Easy Chart.startDate}
but getting an error:
Exception: Error running query:
SQLQuery(query=SELECT 31990.16
FROM charge_reports
WHERE t_stamp = 2021-02-02 08:00:00.000, database=Charge_Reports)@0ms
On: Charts Testing.Root Container.Starting Volume.value
caused by GatewayException: Incorrect syntax near ā08ā.
caused by SQLServerException: Incorrect syntax near ā08ā.
This is what I'm trying to accomplish. I think the reason it's confusing is that there is probably a few different ways to go about it. I think the easiest would be to use the chart's Start and End dates to query the DB for the values at those times, then use my expression to calculate the value. Alternatively, if I could do a query and then just return the first and last values (perhaps in two separate queries?) would also work.
First thing.
Where is your volume values stored? In a regular database table, or are you using tag history to log the volume. It seems that you are using tag history therefore you should use something like this (used in the easy charts propertyChange extension function)
if event.propertyName == 'startDate':
startDate = event.source.startDate
endDate = event.source.endDate
tagPath= '[default]Work Tank 1/Work Tank 1 Volume Imperial Gallons'
history = system.tag.queryTagHistory(paths= [tagPath],startDate=startDate,endDate=endDate,returnSize=1,aggregationMode='Range')
event.source.parent.getComponent('Power Table').data = history
You are correct, I am using Tag History, to work with the Easy Chart.
This thread Read a historical tag value is very similar to what Iām trying to do. I can get the dates for the start and end of the chart, but Iām not having much luck getting the historical value of the tag at those dates.
The code I gave you will give you what you asked for. No need to get the value at start and end then subtract manually. The Range aggregation will do that for you.
If you want just the value and not the dataset returned from queryTagHistory() then just add
print history.getValueAt(0,1)
(I think that would be the right index for the value)
I said extension function, but I meant event handler propertyChange. I recommend placing a temporary power table on your window so you can see the dataset that tagHistory is returning (my original script shows this). Then once you verify that the info is correct you can index the row value you want and assign the result where ever you want.
Yes that is correct. Note that print displays output to the Output Console under Tools>Console or ctrl + shift + C.
Also, the script I wrote will only fire when the start date of the chart changes, but this can be changed to other properties.