# Calculated roster throwing an error

**URL:** <https://forum.inductiveautomation.com/t/calculated-roster-throwing-an-error/54600>\
**Category:** Ignition\
**Created:** [January 3, 2022, 1:58pm UTC](https://forum.inductiveautomation.com/t/calculated-roster-throwing-an-error/54600 "2022-01-03T13:58:05Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [January 3, 2022, 1:58pm UTC](https://forum.inductiveautomation.com/t/calculated-roster-throwing-an-error/54600/1 "2022-01-03T13:58:05Z")

</div>

Hello all,

Happy New Year! 🎉

I am trying to use an alarm event property when using a calculated roster, the script is:

```auto
    value = event[“propertyname”]
		
	data = system.db.runPrepQuery("query")

	for row in data:  
	    builder.username(row["username"]).email([row["contact_value"]]).add()
	 
	userList = builder.build()
	return userList

```

I’m trying to use the value as a parameter in my database query and if I replace the parameter with a static number it seems to work fine. If I try using the script above, I get the error:

`Roster '<calculated>' has no users, notification for 1 alarms will go undelivered.`

I would appreciate your help/suggestions in this regard. Thank you!

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [January 3, 2022, 2:20pm UTC](https://forum.inductiveautomation.com/t/calculated-roster-throwing-an-error/54600/2 "2022-01-03T14:20:36Z")

</div>

We’ll need to see your query, or an example of it if the actual query has sensitive information.

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [January 3, 2022, 2:33pm UTC](https://forum.inductiveautomation.com/t/calculated-roster-throwing-an-error/54600/3 "2022-01-03T14:33:26Z")

</div>

Thank you @lrose!

This is the query:

```auto
value= event["propertyname"]
	data = system.db.runPrepQuery("SELECT contact, username FROM UserContact where location_id= ?)",[value])

```

“location\_id” is not necessary a single value and can be multiple values.

Any idea how I can type cast my location\_id variable to the correct data type?  
I have to mention that in the alarm property, the location\_id parameter is an integer.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [January 3, 2022, 3:19pm UTC](https://forum.inductiveautomation.com/t/calculated-roster-throwing-an-error/54600/4 "2022-01-03T15:19:54Z")

</div>

> [@pkhoshroo](#):
>
> “location\_id” is not necessary a single value and can be multiple values

I take this to mean that you are passing in a list of location ID's. If that is the case then your query syntax is incorrect and you will need to dynamically modify the query to accept multiple values.

SQL does not understand a List of integers in the same way that python does, so you need to give it a little help.

The query should look something like:

```auto
"SELECT contact, username FROM UserContact WHERE location_id IN (?)"

```

Where the number of parameters changes based on the number of "id's" your looking for.

Something like this:

```auto
value = event['propertyname']

query = 'SELECT contact, username FROM UserContact WHERE location_id IN (%s)' % (','.join(['?'] * len(location_id)))
data = system.db.runPrepQuery(query,location_id)

for row in data:
    builder.username(row['username']).email([[row'contact_value']]).add()

userList = builder.build()
return userList

```

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [January 3, 2022, 3:45pm UTC](https://forum.inductiveautomation.com/t/calculated-roster-throwing-an-error/54600/5 "2022-01-03T15:45:45Z")

</div>

You are exactly correct!  
So then if I modify my query to:

```auto
value = event['propertyname']
data = system.db.runPrepQuery ("SELECT contact, username FROM UserContact WHERE location_id IN ?)",[value]) 

for row in data:
    builder.username(row['username']).email([[row'contact_value']]).add()

userList = builder.build()
return userList

```

Even tried the 2nd version and they should both work, but they don’t, I still get the same error.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [January 3, 2022, 3:51pm UTC](https://forum.inductiveautomation.com/t/calculated-roster-throwing-an-error/54600/6 "2022-01-03T15:51:04Z")

</div>

That isn’t the same as the code I supplied.

1. Is value a python list? If not then there is no need to use IN, you can just use standard syntax  
`'SELECT contact, username, FROM UserContact WHERE location_id = ?'`

2. Your SQL syntax is incorrect, the `()` arround the parameters are requiered when using the IN Operator.

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [January 3, 2022, 4:16pm UTC](https://forum.inductiveautomation.com/t/calculated-roster-throwing-an-error/54600/7 "2022-01-03T16:16:19Z")

</div>

You are correct! That was a typo from my side.

I have tried both versions:  
1)

```auto
locationid= event['locationid']
query='SELECT contact, username, FROM UserContact WHERE location_id = ?'
data = system.db.runPrepQuery (query,locationid) 

for row in data:
    builder.username(row['username']).email([[row'contact_value']]).add()

userList = builder.build()
return userList

```

1. 

```auto
locationid= event['locationid']
query='SELECT contact, username, FROM UserContact WHERE location_id IN (?)'
data = system.db.runPrepQuery (query,locationid) 

for row in data:
    builder.username(row['username']).email([[row'contact_value']]).add()

userList = builder.build()
return userList

```

I get the same error.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [January 3, 2022, 4:22pm UTC](https://forum.inductiveautomation.com/t/calculated-roster-throwing-an-error/54600/8 "2022-01-03T16:22:44Z")

</div>

Is value a list?

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [January 3, 2022, 7:16pm UTC](https://forum.inductiveautomation.com/t/calculated-roster-throwing-an-error/54600/9 "2022-01-03T19:16:49Z")

</div>

You were correct @lrose. I had some subquery syntax issues and that’s why I did not get the correct result. Appreciate your help as always.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [January 5, 2022, 9:22am UTC](https://forum.inductiveautomation.com/t/calculated-roster-throwing-an-error/54600/10 "2022-01-05T09:22:09Z")

</div>

> [@pkhoshroo](#):
>
> `.email([[row'contact_value']])`

That doesn't look right. `.email(row['contact_value'])` maybe ?