# Filter table data

**URL:** <https://forum.inductiveautomation.com/t/filter-table-data/64087>\
**Category:** General Discussion\
**Tags:** perspective\
**Created:** [September 15, 2022, 12:50am UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087 "2022-09-15T00:50:48Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 15, 2022, 12:50am UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/1 "2022-09-15T00:50:48Z")

</div>

Hi all,

I have an inventory system created in MS Access that I would like to move into Ignition. I have a table, some text fields and a button. I’d like to filter the data set based on user input in the text fields and execute on the click of the button.

For instance, I want to see all the items who’s serial number starts with 1004, or contains 1004.

What is a good way to do this?

Do I need to create name queries for each type of filtering, and if so, how would I handle a null or zero value field in the parameters?

Thanks,  
Mike

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [September 15, 2022, 11:25am UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/2 "2022-09-15T11:25:05Z")

</div>

The DB’s `LIKE` operator is your friend.

(I recommend moving the data to a real database and using linked tables in MS Access to retain functionality while developing.)

---

<div class="post-metadata">

**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\
**Post date:** [September 15, 2022, 2:28pm UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/3 "2022-09-15T14:28:56Z")

</div>

The most trivial way to do this would be to enable filtering on the table itself… The problem with this approach is if you type in `1004` into the filter box, it’ll return all rows that contain `1004`, no matter the column. I’m hoping IA announces column filtering at ICC, that’s a feature we’ve been asking for for 2+ years.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/0/00f4379aa0eac8acd08e2b8514c63d1905a1400f.png)

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 15, 2022, 3:52pm UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/4 "2022-09-15T15:52:43Z")

</div>

Well, the data resides on a local instance of SQL Server, and the current front end is MS Access, however, I am attempting to replace Access with Ignition.

If Ignition is not a suitable replacement for Access, please stop me now, and I will drop this project and move on to the HMI.

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 15, 2022, 4:02pm UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/5 "2022-09-15T16:02:23Z")

</div>

I did find the table filtering option about 2 seconds after I posted the question, lol. However, that is not sufficient.

In the component event onActionPerformed, I have this:

```auto
def runAction(self, event):
	search = self.getSibling("Master Number").props.text
	search = '%' + search + '%'
	
	filter = system.db.runPrepQuery("Select i.CatalogNumber, i.MasterNum, i.Description, c.Category, i.Discontinued from Items AS i " & _
		"inner join Categories AS c on i.CategoryID = c.CategoryID " & _
		"inner join Item_Supplier as si on i.ItemID = si.ItemID_FK " & _
		"where i.MasterNum LIKE ?", search)

```

The code errors on the inner join words:

```auto
org.python.core.PySyntaxError
  File "<function:runAction>", line 6
    "inner join Categories AS c on i.CategoryID = c.CategoryID " & _
    ^
SyntaxError: no viable alternative at input '"inner join Categories AS c on i.CategoryID = c.CategoryID "'

```

Would this statement require parenthesis for multiple joins, or would that break the code execution?

Once I can get this to query, would something like

```auto
Table.props.data = filter 

```

work?

---

<div class="post-metadata">

**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\
**Post date:** [September 15, 2022, 4:03pm UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/6 "2022-09-15T16:03:33Z")

</div>

Access and Ignition are two completely separate tools. There is some cross-functionality, but it all depends on how you use Access now. If it’s just to enter data in a form that will then be inserted into the DB, that is fairly easy to do in Ignition.

---

<div class="post-metadata">

**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\
**Post date:** [September 15, 2022, 4:10pm UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/7 "2022-09-15T16:10:28Z")

</div>

What does the `&_ ` do? I’ve never seen that before… If you want a multiline string, use either triple quotes

```python
query = """
This is a super long
string that wraps on multiple
lines.
"""

```

or use `+ \ `

```python
query = "This is a super long" + \
"string that wraps on multiple" + \
"lines"

```

I’m curious as to why you have the query `onActionPerformed`, what are you going for there? How I would go about this is to create a named query (same as the one you have above) and add the parameters you want to filter on. Then on the table or view, create custom properties that hold the filter text. Then on the table, bind the `data` property to that query and pass in the custom property values into your query parameters.

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 15, 2022, 4:31pm UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/8 "2022-09-15T16:31:31Z")

</div>

We use Access for managing our spare parts inventory and as a type of internal ordering system.

So there are permissions and roles. We use the full CRUD statements, creating temp tables where necessary, ad hoc queries, etc.

What caught out eye about Ignition was this: already using it for our machine interfaces, storing/viewing machine data. It’s compatible with SQL Server and then we got the Perspective license. So we thought “Let’s move our (essentially) CMMS database functions to Ignition.”

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [September 15, 2022, 4:38pm UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/9 "2022-09-15T16:38:56Z")

</div>

> [@mdemaris](#):
>
> If Ignition is not a suitable replacement for Access, please stop me now, and I will drop this project and move on to the HMI.

I would drop Access in favor of Ignition in a heartbeat. And have helped customers do so. The first hurdle was usually to get the data stored in the Access file itself moved to a shared resource. If you are already over that hurdle, carry on.

Do note that Ignition doesn't provide you with GUI tools for query design. You need to be comfortable with SQL already. Ignition also does not have built in tools that bind form components to recordsets the way Access does. You will need separate row query, row insert, and row update operations to support your forms. Ignition's Named Queries are the way to go--avoid constructing query strings where you can.

Also, VBA string concatenation and line continuation punctuation do not work in python/jython.

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 15, 2022, 8:56pm UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/10 "2022-09-15T20:56:46Z")

</div>

Ok, now I’m looking at using a named query bound to the table. The query will need to accept four parameters, and in most use cases, three of the four parameters will be null - meaning the user did not enter anything in those search fields.

To make this work more like Access, I am converting the integer fields in the DB to strings, which means I will have to create some sort of function/stored proc to generate and convert numbers into a string to insert into the DB when new records are added. (side note)

When the named query is called, the button will take the text, add % wild cards around it, and feed that to the correct parameter for the chosen field/column. Hopefully returning all records containing the search string, aka %1004% as I think I mentioned earlier.

And, as Phil mentioned in someone else’s thread, IA doesn’t charge extra for more named queries! 🙂

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 15, 2022, 9:07pm UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/11 "2022-09-15T21:07:25Z")

</div>

Here is the thread I mentioned, and after reading it again, perhaps using a stored proc might be the way to go as SQL Server does allow using NULL.

> [@Pass Wildcard Parameters on Named Queries](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/6):
>
> LIKE doesn’t work for integer columns, only for string columns. I would make another named query. IA isn’t charging you for extra ones, after all. smile

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [September 15, 2022, 9:10pm UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/12 "2022-09-15T21:10:05Z")

</div>

Avoid using LIKE on numbers. Numeric fields should be exact matched (on the value). If you have something that looks like a number but isn’t really, then the DB column should be char() or varchar() and LIKE is fine. Also avoid using the wildcard `%` at the beginning–that crushes DB performance.

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 16, 2022, 1:36am UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/13 "2022-09-16T01:36:51Z")

</div>

I’ve got the sp working in the SSMS:

```auto
CREATE PROCEDURE [inv].[usp_FilterItems] 
	-- Add the parameters for the stored procedure here
	@catalog int = null, 
	@master int = null,
	@description varchar(255) = null
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT CatalogNumber, MasterNum, Description 
	FROM inv.Items
	WHERE (CatalogNumber = @catalog OR ISNULL(@catalog, '') = '')
	OR (MasterNum = @master OR ISNULL(@master, '') = '')
	OR (Description LIKE '%' + @description + '%' OR ISNULL(@description, '') = '');
END
GO

```

From Ignition Docs, it looks like I can call it via:

```auto
exec inv.usp_FilterItems

```

However, (using the onActionPerformed, Script Action), it does not like me using the SP’s parameter name (`@master`) to pass a value. There are three params, and if only one value or string is passed, how do I tell Ignition which one is which?

```auto
exec inv.usp_FilterItems , , value
exec inv.usp_FilterItems value
exec inv.usp_FilterItems null, null, value

```

I get messages stating no viable input or mismatched input. If I use the second line, I should get an error regarding incorrect data type.

Well, except that Ignition says ‘inv’ is not a valid name, which is the schema name and part of the SP’s name.

Side question:  
If I make changes to the DB, do I need to refresh the project, and if so, is there another way than closing and reopening it?

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [September 16, 2022, 12:29pm UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/14 "2022-09-16T12:29:20Z")

</div>

[quote=“Mike\_Demaris, post:13, topic:64087”]

```auto
	SELECT CatalogNumber, MasterNum, Description 
	FROM inv.Items
	WHERE (CatalogNumber = @catalog OR ISNULL(@catalog, '') = '')
	OR (MasterNum = @master OR ISNULL(@master, '') = '')
	OR (Description LIKE '%' + @description + '%' OR ISNULL(@description, '') = '');

```

I would just make this a named query instead of a stored procedure with `@master` and `@description` becoming your parameters.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [September 16, 2022, 6:30pm UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/15 "2022-09-16T18:30:20Z")

</div>

> [@mdemaris](#):
>
> If I make changes to the DB, do I need to refresh the project, and if so, is there another way than closing and reopening it?

No need to refresh the project if you make changes in the DB. Depending on the change you may need to make changes in the project to accommodate, but any changes you make in the DB side will be effective as soon as they are published in the DB.

Don't worry about making stored procedures for simple tasks such as general Select, Update, and Insert queries.

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 16, 2022, 6:37pm UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/16 "2022-09-16T18:37:38Z")

</div>

This task does not feel simple, lol.

I’m testing this to try and pass a null value into a named query. Data type is Int8. I’ve tried using a \*, % as wildcards, but that evaluates to a string. NULL appears to be evaluated before it reaches the SQL statement and won’t process.

If someone has a solution to passing nulls to a named query or how to pass nulls to a stored proc via the event config script, please do share. The docs don’t quite cover it all…

```auto
SELECT CatalogNumber, MasterNum, Description
FROM Items
WHERE (CatalogNumber = :myValueX OR ISNULL(:myValueX, '') = '')

```

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 16, 2022, 6:38pm UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/17 "2022-09-16T18:38:26Z")

</div>

Data type should be Int4, not Int8.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [September 16, 2022, 6:43pm UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/18 "2022-09-16T18:43:32Z")

</div>

So if I am undertanding correctly, if a parameter is is not passed for (ie the end user doesn’t want to filter by Catalog Number) you skip it, otherwise you want to filter by it? I do this with a slight reversal of your logic -

```auto
SELECT CatalogNumber, MasterNum, Description
FROM Items
WHERE (:myValueX IS NULL OR CatalogNumber = :myValueX)

```

Though often, with Ignition components, you only rarely actually get a None (which translates to a null) and often have to do a different kind of comparison. If CatalogNumber is say selected from a dropdown you might have something like

```auto
SELECT CatalogNumber, MasterNum, Description
FROM Items
WHERE (:myValueX =-1 OR CatalogNumber = :myValueX)

```

Since a dropdown that doesn’t have a selection has selectedValue of -1 (which is what you would be passing in to the parameter).

I’d also suggest renaming your SQL parameters to match your column names a little more but purely personal preference. Good for maintenance reasons imo.

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 17, 2022, 12:31am UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/19 "2022-09-17T00:31:23Z")

</div>

Ok, so how do you change the query binding of the table to reflect the filtered data?

If I set the initial query binding to the named query with

```auto
SELECT CatalogNumber, MasterNum, Description
FROM Items
WHERE (:catalogValue =-1 OR CatalogNumber = :catalogValue)

```

this, there is a null data set, and using this in the button’s event scripting, does nothing for the table.

```auto
catalog = self.getSibling("Catalog Number").props.value

	namedQuery = "item_search_filter"
	params = {"catalogValue":catalog}
	system.db.runNamedQuery(namedQuery, params)

```

I don’t understand why it seems so hard to:

- check the values in the text/numeric boxes
- if there is a value or values, send that value(s) to the query
- use that query or resulting data set to filter the table’s data

Seems like there should be a way to change the table’s query, i.e. record set, to a different one by clicking a button.

Ideally, I would like to filter on five different columns, have one search field default to a certain value, and be able to filter on all or none of the columns. None would return the full data set.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [September 17, 2022, 3:09am UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/20 "2022-09-17T03:09:09Z")

</div>

You haven’t shown how you have your binding set up. You can assign property and tag values to the parameters in the binding.

As for the script you have to take the dataset returned by `system.db.runNamedQuery()` and assign it to the table, something like:

```auto
catalog = self.getSibling("Catalog Number").props.value

namedQuery = "item_search_filter"
params = {"catalogValue":catalog}
self.getSibling("yourTableName").props.data = system.db.runNamedQuery(namedQuery, params)

```

[Next page](https://forum.inductiveautomation.com/t/filter-table-data/64087.md?page=2)