Populating Power Table WHERE column IN({property})

[danieltalbot]

Hey,

I’m having a bit of trouble figuring out how to write this query. I’m trying to get it so that I can pass multiple ID’s, separated by commas, into the search bar and populate the Power Table with each row belonging to that ID. I’ve had limited success. For example, I can get it to work when I have multiple ID’s in the search bar, but then the table errors when I clear the values from the search bar or add a comma to the end of the search bar string. And when I get the Power Table working again without any ID’s in the search bar, then it only takes at most 1 ID from the search bar and returns only that row.

The problem seems to focus around the last line in the WHERE clause…
(status IS NULL AND cl.id IN(’{Root Container.Work Request.Search.text}’)) ← with single quotes around the property. This way only populates the table from the first ID listed in the search bar.
(status IS NULL AND cl.id IN({Root Container.Work Request.Search.text})) ← without single quotes around the property. This way allows for multiple ID’s to be entered into the search bar, and the rows belonging to those ID’s will populate the table. However, this will give me an error if I add a comma to the end of the search string or if I clear the search string.

Does anyone know how to do what I’m trying to accomplish?

Big thanks,
Daniel

[oparra]

Hi Daniel,

I replicated the issue you are experiencing on my Ignition installation and isolated for only the last WHERE clause in the query that you have. To address your first question:

“the table errors when I clear the values from the search bar or add a comma to the end of the search bar string.”

This is because the SQL syntax is incorrect, namely, when there is no text in the search bar the SQL query will execute as c.id IN () where the value to be checked for is NULL. This is will throw a SQL query error. Similarly, the issue with the extra comma happens because the query is expecting another value and there isn’t one, also a syntax error.

To address your question regarding expression statements:

(‘{Root Container. Work Request.Search.text}’)

Is not the same as:

({Root Container. Work Request.Search.text})

Given the values from the first picture: 168, 86, 84, 83, when the SQL query is executed for the first statement it would evaluate to:

(‘168, 86, 84, 83’)

And in the second statement it would be:

(168, 86, 84, 83)

In the first statement there is only 1 value – the string : ‘168, 86, 84, 83’
In the second statement there is a list of values for the c.id to match 168, 86, 84, 83

Hope this helps, let me know if you have any questions.

SQL IN statement article