Named query in Expression, with Params

Ignition
1

Hi all,

I have a view with many text fields that will indicate which users put their fingers in the pie. The underlying data stores the users as numbers, as a reference to a "User ID".

The view pulls the data via a named query, which is bound to a view.custom.array. The array elements are pulled into a view.custom.object.value- for the most part. This enables the components to have a bi-directional binding on the view.custom.object.value for updating.

For many components, I run an expression binding: try({view.custom.arr_Item_Req_Data}[0, 'POnumber'], ''). However, I need to basically run a query to get the user's name to display in the text field instead of their ID number. I was reading this post: https://forum.inductiveautomation.com/t/sql-runscript-in-expression-binding/64505 and am thinking I could do something like this, but I need to pass in a parameter to the query. Might it look something like:
runScript("system.db.runNamedQuery(QueryName, params = {"p_UserID":UserID })
OR
runScript("system.db.runScalarQuery('SELECT UserName FROM Users WHERE UserID = {p_UserID}', UserID)")?

Here is a pic of the view.custom:
image

Thanks

Edit:
I don't have any user fields in the custom props yet, as I'm trying to figure out how I would run the queries.
Total user name fields are five (5). In MS Access I have used combo/drop down boxes and just disabled them from edits. But that looks, idk, chincy.

2

I'd keep the id as is in the custom props, and put a query binding on the label, using the id there as parameter.

edit: Or, if you have more than just one scalar value to pull from the query, put that binding on another custom prop that you can then use on your different labels/fields.
Do you want the user to be able to make changes to those ?

3

Is there any particular reason you can't use subqueries in the main NQ to pull these names into their own columns? So there's only one round trip to the DB instead of four (or more)?

2 Likes
4

The main query will contain five fields either NULL or not. How would I use a subquery for each field?

SELECT F1, F2, F3, F4, F5, F6 FROM Table1 WHERE F1 = 100. 
F2 = 63
F3 = 63
F4 = 70
F5 = 75
F6 = NULL

If I could use a subquery for each field (F2 - F6) like: SELECT UserName FROM UsersTable WHERE UserID = Table1.F2

It's been a long time since I've looked at subqueries...
Well, this SQL statement is going to be long, LOL. A SELECT stmt for each user field.

Sample code, from a table with over 20 columns...

select ReqID, (select FirstName from adm.[User] where UserID = r.User_FK) AS Originator, (select FirstName from adm.[User] where UserID = r.ReviewedBy) AS ReviewedBy from rfp.Request as r

@pascal.fragnoud The User will not be changing these fields directly, there will be code that adds the appropriate user's name to the appropriate box.

5

Your DB won't care. Grabbing all this in one go will be faster than any other approach.

Consider writing your select fields one per line, indented. The DB won't care about the extra whitespace. Something like this:

SELECT
	F1,
	F2,
	(SELECT FirstName From adm.User Where UserID = F2) As Name2,
	F3,
	(SELECT FirstName From adm.User Where UserID = F3) As Name3,
	F4,
	(SELECT FirstName From adm.User Where UserID = F4) As Name4,
	F5,
	(SELECT FirstName From adm.User Where UserID = F5) As Name5,
	F6,
	(SELECT FirstName From adm.User Where UserID = F6) As Name6
FROM Table1
WHERE F1 = :someParam
1 Like
6

Finished SQL

SELECT ReqID, 
	(SELECT FullName FROM adm.v_UserFullName WHERE UserID = r.User_FK) AS Originator, 
	(SELECT FullName FROM adm.v_UserFullName WHERE UserID = r.ReviewedBy) AS ReviewedBy,
	(SELECT FullName FROM adm.v_UserFullName WHERE UserID = r.approvedby) AS ApprovedBy, 
	(SELECT FullName FROM adm.v_UserFullName WHERE UserID = r.OrderedBy) AS OrderedBy,
	(SELECT FullName FROM adm.v_UserFullName WHERE UserID = r.ReceivedBy) AS ReceivedBy, 
	(SELECT FullName FROM adm.v_UserFullName WHERE UserID = r.DeliverTo) AS DeliverTo,
	ReqNumber, User_FK, Status_FK, Option_FK, POnumber, createdAt, ReviewedAt, ApprovedOrNotAt, GLCode, WantedBy, ExpectedBy, RequestType_FK, Notes, Title, Website, SelfOrdered,
	Quote, LastUpdatedOn, HasProject, ProjectNumber
FROM rfp.Request AS r
WHERE r.ReqID = :p_ReqID
2 Likes