Script filter a query tag on perspective page from a dropdown value

Ignition
1

I want to compose a filtering script for a transform on a query tag binding.

My script so far, is working.

valueHeaders = ['Line','Year Month', 'Stops','Attempted','Palletized']
data=[]
for row in range(value.getRowCount()):
	Line= value.getValueAt(row,'Line')
	yearMonth= value.getValueAt(row,'Year_Month')		
	stops= value.getValueAt(row,'Stops')
	Attempted =value.getValueAt(row,'Attempted')
	Palletized=value.getValueAt(row,'Palletized')		
	data.append([Line,yearMonth,stops, Attempted, Palletized])	 
	
data = system.dataset.toDataSet(valueHeaders, data)

filterLine=self.parent.parent.getChild("Dropdownrow").getChild("Dropdown").props.value
	
filterCode =self.parent.parent.getChild("Dropdownrow").getChild("NumericEntryField").props.value
	
return data

But I have two dropdowns to filter with.

In a query, I usually write:

where (t_stamp>DATEADD(month, -6,EOMONTH( GETDATE()))) and (Code<1002) and (:code=0 or :code=code) and ( :machine=-1 or 
	Line=Case 	when :machine=1  then 'Brm1' 
				When :machine=2  then 'Brm2'
				When :machine=3  then 'Brm3'
				When :machine=4  then 'Brm4'
				When :machine=5  then 'Brm5'
				When :machine=6  then 'Brm6'
				When :machine=11 then 'Cym1'
				When :machine=12 then 'Cym2'
				When :machine=13 then 'Dyg3'
				When :machine=14 then 'Dyg7'
				When :machine=15 then 'Eeg5'
				When :machine=16 then 'Eeg6'
	else null end )

I think I figured it out as I was writing it.
I think I say like if Line = filterLline and code=filter code then all this stuff and append.

2

I wrote a dictionary pairing numbers with machines

thisDictionary = { 1: 'Brm1' }

if filterLine == 0 or Line ==thisDictionary["filterLine"]:

says key error filterLine?

3

Take the quotes off of filterLine. It is a variable, and you want its value as the key into the dictionary.

1 Like
4

Thanks

My script isn’t showing errors now, but when I change the dropdown, nothing changes in my table.

My data binding is to the query tag, then I have this transform

filterLine=self.parent.parent.getChild("Dropdownrow").getChild("Dropdown").props.value	
filterCode =self.parent.parent.getChild("Dropdownrow").getChild("NumericEntryField").props.value
thisDictionary ={    
                "1"  : 'Brm1' ,
				"2"  : 'Brm2',
				"3"  : 'Brm3',
				"4"  : 'Brm4',
				"5 " : 'Brm5',
				"6" : 'Brm6',
				"11" : 'Cym1',
				"12" : 'Cym2',
				"13" : 'Dyg3',
				"14" : 'Dyg7',
				"15" : 'Eeg5',
				"16" : 'Eeg6'   }

valueHeaders = ['Line','Year Month', 'Stops','Attempted','Palletized']
data=[]
for row in range(value.getRowCount()):
	Line= value.getValueAt(row,'Line')
	yearMonth= value.getValueAt(row,'Year_Month')		
	stops= value.getValueAt(row,'Stops')
	Attempted =value.getValueAt(row,'Attempted')
	Palletized=value.getValueAt(row,'Palletized')		
	if filterLine == 0 or Line ==thisDictionary[filterLine]:
			data.append([Line,yearMonth,stops, Attempted, Palletized])	
	
data = system.dataset.toDataSet(valueHeaders, data)
	
return data

OH NO

It kind of works. I removed the quotes on the keys.
It only seems to update once though, and not when I move the dropdown

what trickery do I need to perform?

I set the query tag as a tag binding on a custom property.
Then I set the data binding to the dropdown value in property binding.

The table is changing on the dropdown selection, but I also need the numbericfield to dynamically change the table.

The stop code filter can’t be used here like this I realized. (numbericfield)

The stop code filter was used in a subquery of the original named query.

I would need to write a query tag to be the original subquery, then make the rest of the query in the script in order to use it.

So I am done for now on this one.

Thanks for your help Phil.
The table is showing the stops per month for all machines or per machine.
Each perspective session is not running a query now.

*I edited the title from filtering on two dropdowns to on a dropdown

1 Like
5

If you want things to happen when you change values somewhere, you need to bind on those values and not get them in the script.
Use a structure binding.

1 Like
6

I thought this was finished.

However, when I load the page in a browswer, it says that line 21 has a nonetype attribute for getRowCount. Goes away when I click on the dropdown.

I have never seen structure bindings before that I recall. Would they fix that?

trying it

image
image583×798 38.2 KB

I am doing something wrong.

doesn’t update at all or doesn’t put the value into the script properly?

image
image754×820 55.9 KB

I can’t get a value though the structure binding. What am I doing wrong?
Says the values are null?

if I pass just the one dropdown and not the query, I get a value

image
image548×831 36.3 KB

7

Works perfectly fine for me:

image
image874×591 50.2 KB

I’m guessing your ‘query’ binding is incorrectly configured.

1 Like
8

image
image978×590 15.9 KB

I can’t seem to get the query tag dataset into the structure binding.
Says null when I try to. I thought if I could get that query into the structure binding, then I might not get the error.

If I opt out of the overlay, the dataset is empty to begin with.
How do I get the table to populate when the page loads first time though?

9

what’s the value of custom.query ?

10

it is bound to a query tag

11

I used the structure binding to get the dropdown value for which machine or all machines.

I added this line of code to try to make sure the script told everything that query is a dataset before it got to the line throwing the error for not having a getRowCount attribute.

query2 = system.dataset.toPyDataSet(query)

I closed my browser and opened it.
Seems like the error is gone.

Opened on another pc, and on first view of the page, was showing the data and no error.

The error came back randomly.

I am not sure why it happens.

I edited something else on the page and saved, then the error was back again.
I think the query tag is being populated, or else the custom property is being populated.
Seems like, using the page once or twice, the error goes away and only comes back if I do a save.

12

Instead of this awkward case statement, you should consider adding your machines into a sql table and joining onto that to get your machine names

2 Likes
13

joining is a slower query though

14

Meh I don’t know about that. The table would be less than 15 rows. Index the right column and it will be an O(1) operation. You’re probably right that it would be slower, but I can’t imagine it would be by more than a handful of milliseconds.

It’s up to you - would you rather write a join statement, or a case statement with 15 lines every time you need to get this relationship? I think this is the real benefit of putting this into a table.

2 Likes
15

It is a script that runs when someone clicks the dropdown, and the script is run on a query tag that runs once every 8 hours.

So I don’t want to add another query, or it would defeat the optimization I think.

16

Ok, but this is exactly what relational databases are for…

2 Likes
17

I think this is a case of pre-mature optimization on your part, probably the Original Sin of programming if there is one.

You wouldn’t be adding another query just modifying your existing one.

Also, since it sounds like that is driven by user interaction (or just three times a day on a schedule), I don’t think you would notice milliseconds difference, and it would clean up your code significantly.

I think the aim should be to write clean maintainable code first, and then when optimization issues arise - if you need to hardcode a case statement - sure. But from what you’ve said, I don’t think you need to optimize for milliseconds here.

2 Likes
18

You are confusing me.

About a year ago, I wrote a query to get this information.

Now, I am optimizing it.

19

What is the criteria you are optimizing? How fast the query runs?

1 Like
20

I am making it so not every session has to run a query.

I thought getting the query tag data would be better.

I am trying to test it in the database query browser.