Start and End data values

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

1 Like

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

Do I need to format the returned value somehow?

That is a tag path you are trying to select.

Generally, you select from columns in a DB table.

I think you need to sketch out on paper what you are trying to achieve (post it here).

It seems you are going around the world a bit to try and get this done, between Historian, a table, tag paths, and then an expression.

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
1 Like

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.

I really appreciate everyone’s efforts here!

Sorry, you still need the single quotes around the date, so ‘{Root…}’

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)

Sorry, but this is the same as Chart > Scripting > Extension Functions > configureChart ?

Does the .getValueAt(0,1) reference the data by (row,column)? I can't seem to get it to print that cell.

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.

if event.propertyName == 'startDate':
1 Like

Or to include multiple properties, like so:

if event.propertyName in ('startDate', 'endDate'):
2 Likes

Maybe this will help to clarify what is happening. I’ve got the tables showing the values for the tank volume at start and end dates of the chart, the third table “Tank 1 Volumes” should be showing the difference between the two readings, thus providing the net loss of volume. For some reason the script from @dkhayes117 is always calculating the same value, despite changing the start and stop dates. I think I’m very close to figuring this out, I just don’t know how to read the value of the power table cells into the Starting volume and Ending volume fields. I’ve tried a few different ways, but keep getting errors like:
File “event:propertyChange”, line 8, in
TypeError: can’t convert Dataset [1R ⅹ 2C] to double

I thought the tag value was a double already, from TagHistorian.

Lets try this a little differently. Change the aggregationMode to MinMax. (Assuming your volume measurement is cumulative) This will return two rows where index 1 is your volume measurement. Row 0 is max, and row 1 is min.

history = system.tag.queryTagHistory(paths= [tagPath],startDate=startDate,endDate=endDate,returnSize=1,aggregationMode='MinMax')
startVolume = history.getValueAt(1,1)
endVolume = history.getValueAt(0,1)

event.source.parent.getComponent('Start Volume').value = startVolume
event.source.parent.getComponent('End Volume').value = endVolume

You then can make your volume change label an expression binding subtracting the other two numeric label values. This is unnecessary if you only want the volume change value, but if you want to display the start and stop values too, then use MinMax like I just described.

As far as your error, it is telling you exactly what the problem is. You cannot assign a dataset to a numeric label value. You need to index the value from the dataset, then assign it to the label.

The script maybe running faster than the chart is updating its values. In that case, you should invokeLater the script.

PS: I tested the Range aggregation on my system, and it worked as expected

I think this is what I'm trying to do, but keep getting the error I mentioned above. I'm trying to use: Root Container.Tank 1 Volumes Start.data(0,1) as the value, which I thought would be (row,column) from the table "Tank 1 Volumes Start", I used the properties browser to find it.

I do want to display the Start volume and End volume for the user to see, and for my own learning. Is there anything other than the docs site? Something that can give more beginner-friendly information? I feel like I'm way over my head here.

Post your exact script, and I will help you work it out.

The only other resource for Ignition that might be more introductory is www.inductiveuniversity.com

The IT lords here have broken the videos at inductiveU, but I have read the transcripts on many areas.

This is from the power table "Tank 1 Volumes" in the PropertyChange scripting window:
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('Tank 1 Volumes').data = history

Sorry, I don't know how to format it to look like code.

This is how
image

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,aggregationMode='MinMax')

    'putNumericLabelValuePathHere' = history.getValueAt(1,1)  //something like Root Container.Start Volume.value = 
    'putNumericLabelValuePathHere' = history.getValueAt(0,1)

I really appreciate everyone suffering through this with me!

I’ve tried with a few different aggregation modes, still getting strange results. Currently using this script on the power table:

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,aggregationMode='Range')

    event.source.parent.getComponent('Starting Volume').value = history.getValueAt(1,1)
    event.source.parent.getComponent('Ending Volume').value = history.getValueAt(0,1)

I get different values in @dkhayes117 power table compared to when I use a historical tag query for the other power tables above. I don’t know why, since they seem to be using the same variables for start and end dates,

How do I get the value from a cell in the power table into the numerical fields “Starting Volume” and “Ending Volume”? I’ve tried using properties and linking to the table using “Root Container.Tank 1 Volumes Start.data(0,1)” but I get error " The type of the property you chose ( Dataset ) is not assignment-compatible with the type of the property you are binding to ( double )." I thought the data in the dataset WAS a double, since it’s just the same values the tag history is keeping. Doesn’t the (0,1) reference to the cell data, not the whole data set? Or do I need to index the whole dataset and then use the (0,1) to reference within that set? If only tag History was available on the numerical input properties! Or is it, and I just don’t know how?

Ignore the value in the Starting Volume, Ending Volume, and Preservative in Charge, I don’t know why they aren’t zeroed out.

I left out the returnSize=1 part, add that back.

Range aggregation will return the difference between the min and max values over your date range. If your values at start and end are not the min and max values then Range isn’t what you want. You would use tagHistoryQuery twice using LastValue aggregation. For start value, use the startDate for both query start and end dates. For end value, use the endDate for both start and end dates. Assign these values along with the difference between them to your labels from the event handler.

You are better off not using property bindings on the table, do everything from the event handler so everything is in one spot. I would never use an index binding to a table’s dataset, use scripting to get the values you want.