I have a table that the data is bound to a query. I have a few questions that I don’t know if are even possible:
Is it possible to create my own “filter” using a text entry field? If I use the built-in filter option on the table and then say I have another text entry field that the user can add more data to filter the table by? Is it possible to have these two “filters” work together?
Is it possible to filter the table using data from a column that is set to be “hidden”?
I don’t have the solution to this question, but it’s something I wondered for a while… The filtering options for the Perspective Table component are lackluster, to put it mildly. It’s also a component that I would imagine is heavily used in higher level applications (MES, psuedo-ERP, etc).
One thing I’ve done in the past is to enable filtering on the table, use the CSS injection trick to hide the default filter bar at the top of the table, and then bind the table’s filter text property to my own custom text field. This helps with making the table look prettier but it doesn’t add anything functionally…
It isn’t possible to filter on more than one column. What would be neat is to build out filters using text fields and other components and then OR or AND them together (on more than one column). But afaik this isn’t possible, sadly.
Regarding your last point, you can filter when a column is not visible. Again, the same limitations I explained above apply (that you can only filter on one column). The steps to accomplish this are to add your table’s column objects and name the columns properly. At this point, if you uncheck the visible checkbox on a column, you won’t be able filter on that column. The trick is to keep visible turned on, but to also set strictWidth to true and the width to 0.
Not possible with the Table components filter prop but you can always create a custom prop where the unfiltered dataset is held, and then based on filtering options you provide filter the data in a script transform on the binding.
I should’ve been more clear - I was trying say that it isn’t possible with the Table component, not that filtering isn’t possible at all. One could take the approach you described, or filter using the database query (pass parameters to named query), or some other approach… It just feels clunky doing it that way - the Vision Power Table had amazing filtering capabilities.
Instead of binding the table’s data prop to whatever fills the table, bind that to a custom binding instead.
Now, you can add a form to your view (text fields, numeric fields, etc.) and use the values entered there to filter the dataset, then use the result as the table’s props.data.
You actually don’t need a custom prop for this, using a structure binding on your props.data is usually enough.
What I ended up doing was writing two named queries: one for the unfiltered table data, and the second one for filtering. Filtering is done via a button on event action: script - which takes the values/text in the numeric and text fields, passes those into the query's parameters, which queries the server.
Then set the table's data prop to the filtered query.
Although,
reading pascal's reply...if I were to use a custom prop array as the table's data source, and changing that array to the filtered data set... I'm not sure how an expression structure binding would work tho.
We needed the capability to filter based on multiple selections in multiple columns. We landed on a gateway script that did a system.db.runQuery with our custom "filters" added into the query string. You have to get creative on forming the string to pass into the script, but if you send 'Show All' or something when there is no filter, and '(val1,val2,val3)' with the values to filter, this works pretty well actually. Saves some overhead since you are weeding out everything on the return and not having to loop back thru all the data.
You should look at using runPrepQuery instead so you don't have to process your variables.
E.g.
system.db.runPrepQuery("""
SELECT *
FROM
table
WHERE
StartTime < EndTime
AND
(filtered.Area IN ? OR 'Show All' IN ?)
...
""", [AreaFilter, AreaFilter])
We had issues with the prep query adding quotes where SQL didn't want them. It's possible I was handling the variable wrong, but the runQuery fixed the issues I was having with incorrect syntax.
The whole point of using "Prep" queries and named queries is to never have to worry about quotes and proper escaping of strings. Question mark and colon-delimited parameters are never quoted. The data for the parameter is transferred in original binary form through JDBC to the database without ever converting to/from strings.
Go back and fix your SQL to use Prep queries. Do not use string conversions and concatenation to build SQL from user input. That way lies madness and security breaches.
I'll have to revaluate how i was sending them for this script. I agree the prep queries are the way to go, as we use them in literally every other scripted query and am aware of the advantages. It was only a problem when we tried to send it a list "('val1','val2'l)". It seemed like it was adding unnecessary " ' " around it and breaking the query in sql.
With this would i ever be able to not have any values / no filter? If so, i may have to give this a shot. My sql knowledge is fairly new so am always open to advice.
The IN clause does always need values to return anything from the query. For example, if you had the query SELECT * FROM table WHERE col IN ('') then it would only ever return rows where col == ''.
If you want to return all rows if the IN value is '' (i.e. no value filters are supplied), then you'd have to use something like:
query = """
SELECT
*
FROM
table
WHERE
(col IN ({colValsCSV}) OR '' IN ({colValsCSV}))
""".format(colValsCSV=colValsCSV)
Hmm, just note that that method doesn't work for integer values in the CSV, only for strings. I'll have a think how to handle ints and other values as well (if only to satisfy my own curiosity).
Edit: it's not simple to handle all data types...
No! You still should use runPrepQuery. Where you are stringifying the actual values, instead inject the correct number of comma-separated question marks. Then merge the list of values with any other parameters you need.
As noted, generic JDBC doesn't permit sending a list as a single value. Some JDBC drivers support it with vendor-specific types, but Ignition doesn't expose the vendor classes.
