Extract number out of tag to use in calculated roster

Ignition
1

I have a UDT set up that has 5 tanks. Each tank has a state value and if this value = 6 then the tank has a fault condition. Different people are responsible for taking care of different tanks and the users are all in a database. I want to use a calculated roster to dynamically send the user responsible for that tank a notification if that tank has a fault. In my database query I want to extract the UDT parameter containing the tank number and then use that in my calculated roster to say first get the tank number, then look into the database and determine who to contact if that tank has a fault.

How would I get this tank number in the calculated roster script?

2

You can read a parameter with a tag read, eg.

{tag/path/Parameter.TankNumber}

Could be plural parameters… Can remember off the top of my head. Also, you didn’t specify version so I assumed 8. It’s not the same for 7

3

Have a similar problem.
Did you find any solution to this @michael.black?

4

@pkhoshroo

Use Alarm Associated data at the bottom of the tag’s Alarm Setup. Send the tank number to the active pipeline.

Then construct your contact list dynamically using a calculated roster in your pipeline
Not sure where you’re storing users and credentials, so there’s some pseudocode below.

faultedTank = event.tank_number

for u in range(userSource): #pseudo

    uName = ...
    email = ...
    uTemp = {"username":uName,"email":[email]}

    roster.append(uTemp)

return roster
1 Like
5

Can you describe what you need to do ?

6

Thank you!
@pascal.fragnoud and @amarks So I have let’s say an associated data in my alarms called TankNum and then I bound that to Tank number. If I want to bring that into my calculated roster it would be something like the script below, correct?

	FaultedTank = event.TankNum

	data = system.db.runQuery ("SELECT X.contact, UserData.username FROM (
	SELECT * from UserContact) as X 
	LEFT OUTER JOIN UserData ON X.user_id = UserData.id
	WHERE UserData.id = (SELECT user_id FROM Tank where Tank_Number = FaultedTank) ")
	 
	for row in data:
	    builder.username(row["username"]).email([row["emailaddress"]]).add()
	userList = builder.build()
	return userList
7

I’ve not used the builder much, so I can’t comment on that, but I’d suggest using a named query and system.db.runNamedQuery rather than having the query in your roster script.

You can trigger an alarm and use the gateway’s Status > Logs log to see if it’s working properly and/or toggling the pipeline’s Test Mode.

1 Like
8

Thank you!
In terms of the alarm event, did I address that correctly? Would this one sentence be enough? Should I address the alarm and event too?

FaultedTank = event.TankNum
9

Yes, that’s how you access the associated data.

1 Like
10

Thank you very much!

11

I’m not sure if the builder works in the script console, but I typically test my scripts with it and print it out so I can verify it’s returning what I expect.

Also, you’ll need to do for row in range(data.getRowCount()) rather than for row in data as the returned data from the query is not iterable.

1 Like
12

I feel like my named query is not correct.
so FaultedTank = event.TankNum will return multiple numbers as a dataset, correct? As long as we have faulted tank it will return their numbers. So it can probably return tank numbers 3, 10, 15, …, right?

But the way I set up my query it will only accept 1 value.

13

if FaultedTank is indeed an array...
loop! :smiley:

	FaultedTanksArry = event.TankNum
	for FaultedTank in FaultedTanksArry:
		data = system.db.runQuery ("SELECT X.contact, UserData.username FROM (
		SELECT * from UserContact) as X 
		LEFT OUTER JOIN UserData ON X.user_id = UserData.id
		WHERE UserData.id = (SELECT user_id FROM Tank where Tank_Number = FaultedTank) ")
	 
		for row in data:
	    		builder.username(row["username"]).email([row["emailaddress"]]).add()
	userList = builder.build()
	return userList
1 Like
14

There are functions that you could use:
system.roster.getRosters()
system.user.getUsers()

1 Like
15

Thank you, Victor! None of them works I still get errors:
Unable to parse calculated roster.
Roster '<calculated>' has no users, notification for 1 alarm will go undelivered.

16

Not quite.

Each alarm will start in the pipeline with it's own associated data. So, let's say Tank 1 and Tank 3 are faulted. You'll have two alarms and two notifications to send. You'll have 2 separate, unrelated instances of event.TankNum one will be 1 and one will be 3.

Post your entire script so we can see.

1 Like
17

This is the code

	FaultedTank = event["TankNum"]
	data = system.db.runQuery("SELECT X.contact, UserData.username FROM (SELECT * from UserContact) as X LEFT OUTER JOIN UserData ON X.user_id = UserData.id WHERE UserData.id = (SELECT user_id FROM Tank where Tank_Number = FaultedTank) ")
		
	for row in data:  
	    builder.username(row["username"]).email([row["contact_value"]]).add()
	 
	userList = builder.build()
	return userList

It is working when I delete the first line and then make Tank_Number = (to a number like 10) but otherwise is not working I also tried:

	FaultedTanksArry = event.TankNum
	for FaultedTank in FaultedTanksArry:

and

FaultedTank = event.TankNum

None of them worked

18

Please share the results of the query via query browser and your alarm associated data setup.

1 Like
19

I just figured the issue. I had to use system.db.runPrepQuery and parameterized my query with question marks. Now it is working. Thank you very much!

1 Like
20

Great work! Can you give the line where you incorporate the question marks? I think I understand but would like to see. Thanks.