Transaction group help

Ignition
1

Hello, I am trying to use transaction groups to store a tag value which is coming from a solar array to my database (ultimately to make a nice chart) but the logic i would like to achieve is to just store the amount generated over the course of the hour, for example at 10am the tag reads 3kw and at 11am it reads 4kw i would like it to output the difference of 1kw made throughout the hour to my database so i can display what was produced at each hour throughout the day.

I have watched the university videos on transaction groups which has given me the idea that this is possible (particularly the hour meter setting) but i cannot seem to find the specific solution i need.

I would be grateful for advice on this or if I am barking up the wrong tree with trying to sort this logic through transaction groups, thank you.

2

The usual method is the store the raw data in the database, then do the calculations via a query, or query-plus-script.

There are a few topics on the forum for such things. Look up lead lag window. :slight_smile:

2 Likes
3

Just a technical tip:
Be clear about whether you are recording power (kW) or energy (kWh). It sounds like your meter is reporting energy generation so it’s kWh (and the capital ‘W’ is significant).

I agree with Jordan. Record the actual data presented. Manipulate it later.

1 Like
4

Ok thank you both for your responses i will try to pursue a solution through stored procedures in sql

5

There’s generally no need for stored procedures. Just run a simple SQL query - preferably a named query. You can then bind your chart dataset to that query and add a script transform to calculate the difference between consecutive readings. It’s much easier to write and debug Python scripts than SQL and everything will be visible in the project rather than “stored” in the database. (Think of the guy who has to maintain your project.)

2 Likes
6

Hey, I have one more question I’m trying to reference a tag in my query and usually i would be able to bind a parameter to the tag but since I’m running the query through a gateway scheduled script there is no option to set the parameters, my brain is melting, i used a stored procedure to get the exact functionality i want but this is the last step and it is not working.
this is what is in my named query

EXEC [dbo].[sp_UpdateMeter1_kW] @Meter1_kW = system.tag.readBlocking([default]_Sample_Device_/Sine/Sine0)

(im just using sine tag for now because im not connected to the solar currently)

EXEC [dbo].[sp_UpdateMeter1_kW] @Meter1_kW =

This part works fine and if i give it a value manually it works but referring to the tag gives the error incorrect syntax near ‘.’ which isnt helpful

7

Like @Transistor suggests, you would probably be better off not using a SP, but you should be able to pass parameters to your named query regardless. I don’t have many examples handy, but this is one where i am forced to used a SP for another application’s DB.

Named Query:
image

Where I run the named query ( not in a timer script but you get the idea)

	params = {"LOCID":self.getSibling("LOCID").props.text,
			"DESC":self.getSibling("DESC").props.text,
			"PHONE":self.getSibling("PHONE").props.text,
			"USER":self.getSibling("USER").props.value,
			"TENANT":self.getSibling("TENANT").props.value,
			"NAME":self.getSibling("NAME").props.text
			}
	system.db.runNamedQuery("WR_TEST", params)
8

i feel like im close to a breakthrough here as i have the script

path = system.tag.readBlocking(["[default]_Sample_Device_/Sine/Sine0"])
param = {"value":path}

system.db.runNamedQuery("storedproc", param)

but the error is that is cannot coerce the the entire value ‘[[-36.49571, Good, Fri May 06 17:13:44 BST 2022 (1651853624735)]]’ into type: class java.lang.Float

what is the method to tell it to only read the value and not any other properties of the tag, i have tried putting .value after as many part of it as i could but it hasnt worked

9 
qualifiedValue = system.tag.readBlocking(["[default]_Sample_Device_/Sine/Sine0"])[0]
param = {"value": qualifiedValue.value}

system.db.runNamedQuery("storedproc", param)

Try this. readBlocking returns a list of qualified value objects, so you need to get the first element ([0]) and then extract the value subproperty. See QualifiedValue.

1 Like
10

Thank you sir i love you this worked i was about to snap its been a long day.

1 Like