Filter table data

General Discussion
1

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

2

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.)

2 Likes
3

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
image1495×435 23 KB

2 Likes
4

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.

5

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:

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:

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

Table.props.data = filter

work?

6

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.

1 Like
7

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

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

or use + \ 

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.

1 Like
8

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.”

9

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.

2 Likes
10

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! :slight_smile:

11

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.

1 Like
12

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.

1 Like
13

I’ve got the sp working in the SSMS:

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:

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?

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?

14

[quote=“Mike_Demaris, post:13, topic:64087”]

	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.

1 Like
15

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.

1 Like
16

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…

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

Data type should be Int4, not Int8.

18

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 -

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

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.

1 Like
19

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

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.

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.

20

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:

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

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