How to perform a wildcard search and filter across a whole table with Named Query Binding

Ignition
,
1

I would like to filter the test table below in Perspective by Text Field Input referencing UnitType and IssueType primarily, using a Named Query Binding or other method.

screen4
screen41438×497 141 KB

This MYSQL query using LIKE works fine in the console but I can't figure out how to reference the wildcard value in Perspective:

SELECT * FROM testPivot 
WHERE  CONCAT(UnitType, IssueType) 
LIKE '%Comms%' ORDER BY UnitType;

How do I dynamically reference the wildcard '%???%' in my named query?

I have tried several different methods with no luck, I basically want this:

SELECT * FROM
testPivot
WHERE CONCAT(UnitType, IssueType) LIKE '%TextFieldInput%'

I can filter the table by one input field such as this dropdown but adding additional parameters linked to the same input field doesn't work:

screen2
screen21362×645 43.6 KB

I have tried this also:

SELECT * 
FROM testPivot
WHERE IssueType =:IssueType OR UnitType =:UnitType
2

Any reason you don't want to use the table's built-in filter ?
It will perform a like %foo% filter on all columns, so it will filter on both unit type and issue type.

1 Like
3

It was just a layout idea I was trying, the built in filter is definitely the cleanest way to do it though thanks

4

I'm not totally sure I understand your question, but ....

You can just replace the search string passed to LIKE with a parameter:

SELECT * FROM testPivot 
WHERE  CONCAT(UnitType, IssueType) 
LIKE :SearchString ORDER BY UnitType;

And then call the named query with "%Comms%", for example. If you don't want to have to remember the "%" on the caller, you can use LIKE CONCAT('%',:SearchString,'%') in the query and just pass "Comms" instead.

2 Likes
5

That's exactly what I wanted thank you, I just couldn't figure out how to reference the search string

6

I found a work around for what I wanted it's not very elegant but it works. I added a hidden 'Select All' column to my table and referenced that in my SELECT query. User can select a distinct issue type or 'Select All' in the drop down to filter the table.

SELECT *
FROM testPivot
WHERE (IssueType) LIKE :SearchString
OR (SelectAll) LIKE :SearchString
7

I'm still not sure why you're not just using the built-in filter.. What's the issue you needed to solve exactly ?

8

I am using the built in filter as well for additional filtering, I need to export data afterwards and the dropdown filter is for this

9

You can get the built-in filter results back for exporting too, take a look at the filter.results.enable and filter.results.data property on the table.

2 Likes
10

Thank you very much, I'll have a play around with that