Classic Chart Dynamic Filtering

Ignition
1

Hi Guys,

We have a testing machine and I am using a classic chart to scatter plot test data points. I also have a table in the window which shows a list of all orders.

I would like it such that if no order is selected in the table, it shows all data and if one (or more?) orders are selected, that it filters the data accordingly.

Would appreciate any advice on how to achieve this? I tried using an if statement in my named query but I don’t think you can use the if statement based on passed parameters - only on data columns.

2

I have done something very similar to this (prior to Named Queries), however, I think the way to achieve this is to structure the query in a way you can give a generic value to the query and it return all data.

I will use MS SQL because you haven’t specified and it’s what I am the most versed in as an example:

SELECT *
FROM OrderTable
WHERE OrderID LIKE ?

If you structure the query in this manner then setting the parameter to the wildcard character (in the case of MS SQL it’s ‘%’ will return all data from the table, but if you set the parameter to a specific value then only that data will be returned.

Obviously this is a simple example and not the only way to achieve what you’re looking for but with the information provided its about as good as I can do.

Another option would be to filter the database via script and manually loading the data in to the chart.

I’m still on 7.9.3 so named queries are not available for me yet. One day I’ll be able to convince them to let me upgrade it, but alas not yet.

Some information that will help others better answer your questions:

What version of Ignition are you using?
What database are you using (MS SQL, MySQL, etc…)

3

Hi Thanks for your reply,

Currently using V8.0.11, not updating to current release version (8.0.14) as I have seen several posts stating that some weird things happening - just FYI.

MS SQL Server.

I am trying to use named queries instead wherever I can.

I tried using LIKE as you have suggested but it is not returning anything if I don’t specify the order number…

4

I feel like there should be a simple solution to what I am trying to do and I am just going about things in a strange way…

5

It would probably want to fetch something as this :

SELECT *
FROM Order
WHERE id = CASE WHEN :id = -1 THEN id ELSE :id END

Here you would want to pass -1 to your named query when selecting all the orders or id for the one that is selected.
I haven’t tested building SQL tuple with IN function in Named query but I know it can certainly work with scripting.

1 Like
6

Oh cool, it sounds like I can use parameters in a case statement thanks I will give this a try!

7

Thanks, I got it working to where I don’t need to pass -1 if no row in the order table is selected:
WHERE [Order_Number] = CASE WHEN :lotId LIKE '' THEN [Order_Number] ELSE :lotId END