Use individual dataset values in a transaction group?

General Discussion
1

Hello,

I have about 100 values in a dataset which I’d like to move into specific cells in an SQL database using a transaction group. When I try dragging and dropping a value from the dataset into transaction group it doesn’t work, it seems like I can only drag and drop the entire dataset.

I was going to create tags and reference each cell of the dataset, but I can’t seem to reach the individual cell values in the dataset to get that to work either.

Expression tags with this expression are erroring out: system.tag.read(“default]TagPath/Tablename”).value.getValueAt(0, 1)

Is there a better way to do this? I could try to set up a script to iterate through the dataset to return all the values and then write 100 lines of code to move them to the correct tags, and then move those 100 individual tags into the transaction group, but I assume there are better ways of doing this. Any tips are greatly appreciated.

Thanks

2

How are you creating the dataset tag? If it is in a table, have you thought about writing it back to the database from the table when a change is done? If you want to do it individually though a transaction group, I think you will have to do an expression using either the lookup() function.

3

I am using a system.tag.queryTagCalculations() function to aggregate min, max, avg and deviation values from the tag historian. Once I gather that information (right now saving it to a dataset memory tag), I need to move it into the appropriate cells in a separate production run log SQL DB, which has one row per production run.

4

Personally, if your pulling it out of the historian once per production run, I would put it into the database you want to store it in, through the same script. Are you looking at the values in your dataset tag anywhere? If your not, there isn’t a reason for the middle step of writing it to a tag. If your already running a script to gather the data though, I would use system.db.runNamedQuery() to insert the data into the production run log you mentioned. You can easily pull the data out using the .getValueAt() that you tried to use above. Then store it in the variables that you need to write back into the production log.

5

I guess I’m not sure how to do that. When I use the system.tag.queryTagCalculations() function, the result is a dataset, so I have to store it to a dataset, right? Once it is a dataset, how can I get the individual values mapped to individual memory tags?

6

I could technically map it with a case function like this:

table = event.source.parent.getComponent("Table") 
MMADdata = system.dataset.toPyDataSet(table.data) # convert to Python table

i = 0

for row in range(MMADdata.getRowCount()):
    for col in range(MMADdata.getColumnCount()):
        #print MMADdata.getValueAt(row, col) # Will print out every item in our MMADdata dataset, starting on the first row and moving left to right.
        case(
        	i,          
        	0,          
        	system.tag.write("[default]Tagpath/Tag1", value),      
        	1,          
        	system.tag.write("[default]Tagpath/Tag2", value),
        	2,         
        	system.tag.write("[default]Tagpath/Tag3", value),
        	forceQuality("!BAD STATE!",0)) // default
        i = i+1

But this returns an error that “case” is not defined:
NameError: name ‘case’ is not defined

Why doesn’t it know the case function?

7

Something like this could do what I think your looking for.

hist = system.tag.queryTagCalculations(paths=path, calculations = ['Minimum','Maximum','Average','StdDev'], startDate = start, endDate = end)
# this pulls your individual values, you would want to check it, I can't test it right now but I don't think there are any other columns in the dataset returned.
min = hist.getValueAt(0,0)
max = hist.getValueAt(0,1)
avg = hist.getValueAt(0,2)
dev = hist.getValueAt(0,3)

# if you wanted to do a named query to insert your data it would look similar to this
params = {"MinColName":min, "MaxColName":max, "AvgColName":avg, "DevColName":dev}
system.db.runNamedQuery("projName","QueryName",params)


# for writing to memory tags it would be similar to this.
paths = ["MinTagPath","MaxTagPath","AvgTagPath","DevTagPath"]
vals = [min,max,avg,dev]
system.tag.writeBlocking(paths,vals)
1 Like
8

The way you did the case is from the expression language, it isn’t the same for python scripting.

1 Like
9

Thank you @bpreston for your help and explanations, I think your code suggestions will work. I’ll give them a try and let you know the results.

10

No problem, with the last little bit of code you sent. As long as your columns are the same everytime, you could even do something like:

table = event.source.parent.getComponent("Table") 
MMADdata = system.dataset.toPyDataSet(table.data)
tags = []
vals = []

for col1,col2,col3,col4 in MMADdata:
	if col1:
		tagPath = "[default]Tagpath/Tag1"
		tags.append(tagPath)
		vals.append(col1)
	if col2:
		tagPath = "[default]Tagpath/Tag2"
		tags.append(tagPath)
		vals.append(col2)
	if col3:
		tagPath = "[default]Tagpath/Tag3"
		tags.append(tagPath)
		vals.append(col3)
	if col4:
		tagPath = "[default]Tagpath/Tag4"
		tags.append(tagPath)
		vals.append(col4)
		
system.tag.writeBlocking(tags,vals)

It checks for nulls in the columns, as long as they aren’t null then it would write the value.

1 Like
11

This isn't an expression. It is python. An expression would be something like this:

{[default]TagPath/Tablename}[0,1]
1 Like
12

Thank you for clarifying. As I am somewhat new to both Ignition expressions and Python, I’m not always sure which is used where. Is it correct to assume the only Ignition expressions can be used in the expressions and that Python goes in Ignition scripting?

13

Both the system.tag.queryTagCalculations() solution and the for/ if case type statement solution from @bpreston and the expression tag solution @pturmel are all valid solutions. Thank you very much for your quick and accurate support!