Dropdown list SQL query problem

Ignition
1

Hi!

Sorry if this has already been asked and answered, but I tried to search an answer on the forums.

I’m having a small problem with the Dropdown list.
I have created a table which has one column for users roles and other one for a plain language name for the Project that specific role responses to. I.E. Role User1 is for project 1 and user2 is for project 2 and so on.

I’m using the following SQL query in the dropdown list: “SELECT project FROM project_userroles WHERE role IN (’{[System]Client/User/RolesString}’)”

This works when the User has only one role, but the list is empty if there are Two or more roles for the user. I tried to replace the RolesString with RolesDataSet but that just gives me a plank list in all occasions.

How can i get this to work?

What I’m trying to do is make a dropdown list with all of the users Projects and the user selects the project he wants and the is redirected to the selected project. This works when the user has only one Role, as i mentioned above.

2

Assuming that the client tag has comma separated roles in it, the actual SQL query would be similar to this:

SELECT project FROM project_userroles WHERE role IN ('Role_1,Role_2,Role_3')

It needs to be:

SELECT project FROM project_userroles WHERE role IN ('Role_1','Role_2','Role_3')

Possible solution - add a custom property to your root container and populate it with the user roles from the client tag formatted in the correct way. Then use the custom property in the binding instead of the client tag.

3

Try using this expression in the custom property as suggested:

replace({[System]Client/User/RolesString},",","','")
4

Hi!

Thank You!
I got it to work.
I used the replace-expression for a custom property.
Then I added the property to the SQL-query.

Works nicely!