Can I use a query within a Tag event script?

Ignition
, ,
1

I have a measuring tool that pushes measurements to tags in my system and I need to check them against a database. What is the syntax to put my query in this Tag event script? The query has a job name that will be sent to the system and then the upper and lower bounds (the 1.98 and 1.19 values) should change based on the job title. Is there a way to do this in ignition.
Here is my Tag Event Script
If this prompt returns 0 it is out of bounds, if it returns 2 it is within regulation.
radii = system.tag.read("[.]Radii")
width = system.tag.read("[.]Width")
if currentValue.value >= 1.98 or currentValue.value <= 1.19:
system.tag.write("[.]Proxy",0)
elif width.value >= 1.98 or width.value <= 1.19:
system.tag.write("[.]Proxy",0)
elif radii.value >= 1.98 or radii.value <= 1.19:
system.tag.write("[.]Proxy",0)
else: system.tag.write("[.]Proxy",2)

2

Create a named query and use system.db.runNamedQuery()

3

You can, but note that any delays or heavy traffic can bog down your whole tag system. I would use a query tag to hold the current applicable bounds and reference that in the actual value script.

1 Like
4

Within the query tag is it possible to select only the data connected to my job name tag? Im having trouble with the proper syntax. Something like

SELECT heightmin FROM rec_profiles
WHERE name = tag name value

5

I would pull all of the currently needed measurement limits into a single query tag with a column for the tag names that they correspond to. Then your tag value script picks the correct row and column from that dataset.

1 Like
6

sorry to bother again but how do I bring the correct query row into my tag value script? Do I use SQL syntax within this statement?

7

Please show your “rec_profiles” profiles table structure. And describe how you need to use it and for how many tags. That will make specific advice possible.

It is also possible that an expression tag is more appropriate than a script. But let’s see everything involved.

1 Like
8

I would do it with 2 queries.

ForMax

SELECT MAX(height) FROM rec_profiles WHERE name = tag name value

ForMin

SELECT MIN(height) FROM rec_profiles WHERE name = tag name value

Or maybe in a single query which hopefully is small by x Rows and 1 Col dataset, you can try

data = datasetQuery.getData()
min = min(i for i in data[0])
max = max(i for i in data[0])
9

image
image945×148 14.4 KB

Here is the table.
There are 31 different jobs each with their own min and maxes.
The job name will be reported to the tag “name”

This is the script on the height tag
radii = system.tag.read("[.]Radii")
width = system.tag.read("[.]Width")
if currentValue.value >= HEIGHTMAX FROM QUERY or currentValue.value <= HEIGHTMIN FOR QUERY:
system.tag.write("[.]Proxy",0)
elif width.value >= WIDTHMAX FROM QUERY or width.value <= WIDTHMIN FROM QUERY:
system.tag.write("[.]Proxy",0)
elif radii.value >= CORNERMAX FROM QUERY or radii.value <= CORNERMIN FROM QUERY:
system.tag.write("[.]Proxy",0)
else: system.tag.write("[.]Proxy",2)

Proxy is the tag in which this is writing too. 0 is not within, 2 if within

10

So, I’d create a query tag(with named query), named RecProfile perhaps, that executes in event driven mode that simply pulls the entire row that matches the current job. Something like this:

SELECT * FROM rec_profiles WHERE name = :job

In the tag setup, supply {[.]job} for the :job query parameter.

Then, I’d change the Proxy tag to be an expression, with this:

if({[.]Height} >= {[.]RecProfile}[0, 'heightmin'] &&
	{[.]Height} <= {[.]RecProfile}[0, 'heightmax'] &&
	{[.]Width} >= {[.]RecProfile}[0, 'widthmin'] &&
	{[.]Width} <= {[.]RecProfile}[0, 'widthmax'] &&
	{[.]Radii} >= {[.]RecProfile}[0, 'cornermin'] &&
	{[.]Radii} <= {[.]RecProfile}[0, 'cornermax'],
	2, 0)

No script.

1 Like
11

Thank you so much, that worked perfectly!

12

Your proxy expression worked perfectly but I need to modify it to accommodate a close to being bad. I am not the best with python and I understand the logic behind && but I do not understand the [0, ‘heightmin’] part. I assume the heightmin is telling the {[.]RecProfile} which part of the query to address. Could you help me on this?

The close to bad will be
Height >= min <= (HeightMax - (.1 * (Max - Min))
close to good will be
Height <= max >= (HeightMin + (.1 * (Max - Min))

13

Output should be
2 for good
1 for close
0 for bad

14

So, this isn’t python. It is Ignition’s expression language. The square brackets after a dataset reference access the content of that dataset, by row and column. Described here:

https://docs.inductiveautomation.com/display/DOC81/Expression+Language+and+Syntax#ExpressionLanguageandSyntax-DatasetAccess

Simply take the {dataset}[subscripts] construct and substitute into your updated equations.

15

I would recommend altering your “RecProfile” dataset query to supply the “warning” boundaries so they don’t have to be recomputed for every comparison.

1 Like
16

Here is the expression I ended with after adding the warning labels for those of who want to see.
if({[.]Diameter} >= {[.]CirProfile}[0, ‘warningmin’] &&
{[.]Diameter} <= {[.]CirProfile}[0, ‘warningmax’],
2,if({[.]Diameter} >= {[.]CirProfile}[0, ‘diametermin’] &&
{[.]Diameter} <= {[.]CirProfile}[0, ‘diametermax’]
,1,0))