Can someone tell me if database view variables work in service desk procedures, and if so how to use them?
Yes but it is tricky.
Here is an example of one that I have created.
I wanted to be able to grab more information from the Organization Staff members table. Specifically what is entered in the Staff Function field.
First create a SQL Query and save it as an XML in this specific location on the Kaseya server.
Here is the details of the Query : “staff_function.xml “
<?xml version="1.0" encoding="utf-8" ?>
<queryDef label="staff_function" sql="SELECT StaffFunction FROM vSystemOrgStaff WHERE staffName = '[=who=]' " />
In the screenshot of the Sub-procedure I get the Variable of “who” which is the Staff name in line 1
In line 2 I run my SQL Query with is using the variable “who” and it then returns the results into the variable “ID”
In line 3 to test I just output the details of “ID” as a note
The other thing to be careful with is ensuring your SQL query used in the xml file will return only 1 single row.
So I also was add "Select top 1" to my queries
e.g. as in Shawns example I'd change the select to read
SELECT top 1 StaffFunction FROM vSystemOrgStaff WHERE staffName = '[=who=]'
This way even if there are more than 1 rows that match staffname = '[=who=]' you will only get 1 returned.