SQL tags and binding with a component

Ignition
1

Hey guys, Is there anyway I can change the value of a tag based on a component on the windows? say I want to make a date tag “yyyy-mm-dd” and I want it to change depending on what day month or year I pick from the calendar component, am i missing something here?
The reason asking is I’m trying to grab certain cell values from a transaction group but they depend on over cell values in the table as you see in the picture bellow. I’m trying to pick the EOS_Count but i want it based on the t_stamp EOS_Name and OP. I want to put drop downs on the screen for each so based on what I pick I want them to change in my SQL tag that is querying the data from my transaction group.
AM I doing this the hard way or overthinking it? :scratch:

Thanks as always!!! :thumb_right: :thumb_left:

DB.jpg1034×382 118 KB

2

Ok so i found one way to do it and idk if it’s the most efficient way. I created few memory tags and called one “shift” and the other two “FromDate” and the other one to “ToDate” and another one “OP_Name”
I bind the shift memory tag to a drop down that have values Shift A Shift B and Shift C and i set it up as a bidirectional so anytime i change the drop down values it changes the shift tag, did the same thing with the OP tag and for the FromDate and ToDate i made the tags of DateTime type and bind them to two drop down calendars so whenever i change the date on the calendar it changes the date on the tag then i used those tags in my SQL query tag:

SELECT EOS_Count FROM OP5_EOS 
WHERE EOS_Name = '{[.]Shift}' AND EOS_OP = '{[.]OpName}' 
AND (t_stamp BETWEEN '{[.]FromDay}' AND '{[.]ToDay}');

Seems to be working fine, I had one concern which was the fact that you had to set designer to read/write mode in order for this to work but then found out(good old trial and error) that in client you can have the ignition designer set to read only and still able to write to your memory tags.

Now I guess my question is, what you guys think, is there a simpler version I can try?

3

[quote=“Mr.K001”]Now I guess my question is, what you guys think, is there a simpler version I can try?[/quote]It depends? Do these tags need to be in the gateway? Where are you running your script?
If you are only running this query in a window (power table, whatever), then you don’t need to put these criteria in tags at all. Just use custom properties of the table, root container, or whatever. If you are trying to generate a report, you should be able to define these criteria as properties of the report. Then when you trigger the report, pass those parameters (from the custom properties).

4

Ok I’m basically trying to create a window that its sole purpose is to show the previous results of all the different stations on a line, so say if some manager wanted to look at say 2 days ago what were the numbers for shifts A,B and C for all the stations, they can use this window.
This is mainly going to be used by the client.
I actually enjoy using tables and be able to take advantage of the custom properties and all but I’m trying to grab specific numbers and user them in other components so for example in this box i have 4 LED displays so you can easily look at different values for OP5 station in different days and different shifts.
Eventually there will be 11 boxes like this one a window each different stations so this way you can look at numbers for all of them in one place.

sql.jpg872×495 36.8 KB

5

Ok. I would create three custom properties on the root container:
[ul][li]A start date string[/li]
[li]An end date string[/li]
[li]A query result dataset[/li][/ul]
So the start date string would combine the date from your date picker and starting hour from your shift picker.
The end date string would compute from the start date + 8hours.
The result dataset would query all the stats you are interested in with a query like:

SELECT sum(EOS_Count) as EOS_Count, .... FROM existing_view_name WHERE t_stamp>='{Root Container.StartDate}' and t_stamp<'{Root Container.EndDate}';Note that I don’t recommend the ‘Between’ clause.
Then your individual readouts would have an expression like: {Root Container.Result}[0,'EOS_Count']

6

wow, that’s pretty awesome and useful, thank you. :thumb_right:

7

Hey pturmel, see if you could help me out here, so i have 10 tables for different ops station of a line, the tables are identical as far as the columns, only thing different is tables name and PK of course, I’m trying to query all these tables in one based on percentage late DESC and only want to see worse 5 offenders, What I was thinking was to use

SELECT a,b,c FROM tableA WHERE t_stamp>=c and t_stamp<d UNION ALL
SELECT a,b,c FROM tableB WHERE t_stamp>=c and t_stamp<d UNION ALL
SELECT a,b,c FROM tableC WHERE t_stamp>=c and t_stamp<d UNION ALL
SELECT a,b,c FROM tableD WHERE t_stamp>=c and t_stamp<d UNION ALL
SELECT a,b,c FROM tableE WHERE t_stamp>=c and t_stamp<d UNION ALL
SELECT a,b,c FROM tableF WHERE t_stamp>=c and t_stamp<d UNION ALL
SELECT a,b,c FROM tableG WHERE t_stamp>=c and t_stamp<d UNION ALL
SELECT a,b,c FROM tableH WHERE t_stamp>=c and t_stamp<d UNION ALL
SELECT a,b,c FROM tableI WHERE t_stamp>=c and t_stamp<d UNION ALL
SELECT a,b,c FROM tableJ WHERE t_stamp>=c and t_stamp<d

ORDER BY b DESC

Now as far as results I get results, I just want to know if there is a more efficient way of doing this? and I know LIMIT doesn’t work with SQL and TOP does, but how can you implement that? with multiple SELECT statements like this? Thanks

8

[quote=“Mr.K001”]What I was thinking was to use

[code]
SELECT a,b,c FROM tableA WHERE t_stamp>=c and t_stamp<d UNION ALL
SELECT a,b,c FROM tableB WHERE t_stamp>=c and t_stamp<d UNION ALL
SELECT a,b,c FROM tableC WHERE t_stamp>=c and t_stamp<d UNION ALL
SELECT a,b,c FROM tableD WHERE t_stamp>=c and t_stamp<d UNION ALL
SELECT a,b,c FROM tableE WHERE t_stamp>=c and t_stamp<d UNION ALL
SELECT a,b,c FROM tableF WHERE t_stamp>=c and t_stamp<d UNION ALL
SELECT a,b,c FROM tableG WHERE t_stamp>=c and t_stamp<d UNION ALL
SELECT a,b,c FROM tableH WHERE t_stamp>=c and t_stamp<d UNION ALL
SELECT a,b,c FROM tableI WHERE t_stamp>=c and t_stamp<d UNION ALL
SELECT a,b,c FROM tableJ WHERE t_stamp>=c and t_stamp<d

ORDER BY b DESC
[/code][/quote]Blegh! Consider adding an ‘opstation’ column to your tables and recording all in one.
In the meantime, you probably want to wrap that query in an outer query:SELECT TOP 5 * FROM ( SELECT a,b,c FROM tableA WHERE t_stamp>=c and t_stamp<d UNION ALL SELECT a,b,c FROM tableB WHERE t_stamp>=c and t_stamp<d UNION ALL SELECT a,b,c FROM tableC WHERE t_stamp>=c and t_stamp<d UNION ALL SELECT a,b,c FROM tableD WHERE t_stamp>=c and t_stamp<d UNION ALL SELECT a,b,c FROM tableE WHERE t_stamp>=c and t_stamp<d UNION ALL SELECT a,b,c FROM tableF WHERE t_stamp>=c and t_stamp<d UNION ALL SELECT a,b,c FROM tableG WHERE t_stamp>=c and t_stamp<d UNION ALL SELECT a,b,c FROM tableH WHERE t_stamp>=c and t_stamp<d UNION ALL SELECT a,b,c FROM tableI WHERE t_stamp>=c and t_stamp<d UNION ALL SELECT a,b,c FROM tableJ WHERE t_stamp>=c and t_stamp<d) alltables ORDER BY b DESC;

9 
Blegh! Consider adding an 'opstation' column to your tables and recording all in one.

what you mean recording all in one? I do have a column that holds the OP station names.

You sure ALLTABLES is a SQL syntax? ALL TABLE seems to be but still doesn’t work, keeps giving me an error

On: SQLTagTesting.Root Container.Table.data
caused by GatewayException: Incorrect syntax near the keyword 'ORDER'.
caused by SQLServerException: Incorrect syntax near the keyword 'ORDER'.

UMMM NVM, I take that back alltables doesnt lit up blue so thats why i thought it might be an error and didn’t try (stupid me :stuck_out_tongue:) I just tried it and it works… but how? if alltables is not a syntax how does get used?

10

[quote=“Mr.K001”]what you mean recording all in one? I do have a column that holds the OP station names.[/quote]Then put all of that data in one table. The opstation column provides the information to separate the data when needed.[quote=“Mr.K001”]You sure ALLTABLES is a SQL syntax?
NVM, I take that back alltables doesnt lit up blue so thats why i thought it might be an error and didn’t try (stupid me :stuck_out_tongue:) I just tried it and it works… but how? if alltables is not a syntax how does get used?[/quote]When using nested queries, aka subqueries, in a FROM clause, you assign the subquery an alias. ‘alltables’ is the alias I picked because it contains data from all of your tables.
I primarily use PostgreSQL, which has pretty good documentation on this.

11

Thanks, that definitely helped.
As for as having them all in one table, I’m using Transaction groups to make each OP table, at first I really wanted to put all the End of the shift data in one table to make it more convenient but wasn’t quite sure how to do it since If I wanted to put all my tags(all the ops) in one group, when the trigger occurs then all those tag values get written in one row instead of multiple rows based on their OP name. Is there a way around that?
Since I couldn’t figure this out I just decided on making multiple tables and and use a table component and query to it to get the result I want. Another reason I figured it won’t be that bad of an idea was because each table gets 3 rows of data a day so over course of a month its only 90 rows and I’ve set it to delete after 6 months. so figured that query won’t take that long due to the low number of rows.

12

No, keep individual transaction groups for each operator station. But include an expression item in each one that is just the operator station name or ID, targeted at that column. Then all of the transactions can use the same table.

13

Gotcha, Thanks for the help. Something else came up, there are times that errors occur such as when a NULL value pops out, whats the best way of doing error handling, I know you can use error scripting such as

system.gui.errorBox(message [, title]) or

system.gui.warningBox(message [, title])

but how would you use them.
Say I have a tag and I want to have an error pop when a value goes NULL, do I use that component that that tag is bind to and do scripting or is there a way to directly write something for the tag?

14

I don’t use messageBox() and friends in asynchronous events. Only in operator-initiated events like a button’s actionPerformed event. Those functions block that script’s execution and I’m leery of possible side effects and/or future compatibility problems. { It’s a long-standing rule-of-thumb that foreground events in any gui should complete in 0.1 second or less. }
So, for gateway tags that get a null, I would use the alarm system to flag that. Those tags are global, so all users should see those alarms.
I suspect you mean nulls that are values in a dataset property. For those, I use the try() expression function to supply an alternate value, and if necessary, the isNull() expression function to drive a style change.

15

I see, I did some reading on it in the Ignition Manual and I think try() and isNull() will definitely come handy. You were correct as for as the Null issue. My main worry is when working with a DB, dataset and when for some reason some value ends up coming back Null, the tag shows broken. Now I would know what it means and can investigate it but as far as plant managers and most higher ups it looks as the software is broken, you know how that goes.

Thanks a lot for all the help. I’m sure I’ll have more as get stuck :scratch: :thumb_right: :thumb_left: .

16

Well, I thought I had this figured out till I opened a client to check and make sure everything works correctly… and I had an issue… So I have a live table going for each OP so it the station can be observed as it works, I made a drop down on top and in the dataset I added all the OP names (each with their own value), basically want to use that to jump from window to window instead of go back once and then go in the OP window you want to see. I guess I could not worry about it but at this point I need to know what I did wrong, so I took this approach:
I used this if else statement to check on the selectedValue , I could have used the selectedStringValue as well I suppose.

if event.source.selectedValue == 5:
	system.nav.swapTo("Main Windows/Assembly/OP5")
elif event.source.selectedValue == 10:
	system.nav.swapTo("Main Windows/Assembly/OP10")
elif event.source.selectedValue == 18:
	system.nav.swapTo("Main Windows/Assembly/OP18")
elif event.source.selectedValue == 20:
	system.nav.swapTo("Main Windows/Assembly/OP20")
elif event.source.selectedValue == 30:
	system.nav.swapTo("Main Windows/Assembly/OP30")
elif event.source.selectedValue == 30:
	system.nav.swapTo("Main Windows/Assembly/OP60")
elif event.source.selectedValue == 30:
	system.nav.swapTo("Main Windows/Assembly/OP65")
elif event.source.selectedValue == 30:
	system.nav.swapTo("Main Windows/Assembly/OP70")
elif event.source.selectedValue == 30:
	system.nav.swapTo("Main Windows/Assembly/OP75")
elif event.source.selectedValue == 30:
	system.nav.swapTo("Main Windows/Assembly/OP80")
elif event.source.selectedValue == 30:
	system.nav.swapTo("Main Windows/Assembly/OP90")
else: 
	system.nav.swapTo("Main Windows/Assembly/OP190")

Originally I had this Under the mouseClicked event handler but there was a glitch, I could pick the OP from the list but it wouldn’t switch windows right away, I had to click the drop down box one more time for it to work. So I decided to change it and use the code under propertyChange event handler. It all seemed to work in the designer fine till I opened the Client, when I went in one of the cycle time windows, the client started acting all weird, and it wasn’t functioning so had to close it. Am I doing this right? as far as the code yes I can make it better by maybe using some kind of switch statement but as far as the event handler, would you do it any other way?

dropdown.jpg911×364 107 KB

17

[quote=“Mr.K001”]Well, I thought I had this figured out till I opened a client to check and make sure everything works correctly… and I had an issue… So I have a live table going for each OP so it the station can be observed as it works, I made a drop down on top and in the dataset I added all the OP names (each with their own value), basically want to use that to jump from window to window instead of go back once and then go in the OP window you want to see. I guess I could not worry about it but at this point I need to know what I did wrong, so I took this approach:
I used this if else statement to check on the selectedValue , I could have used the selectedStringValue as well I suppose.

if event.source.selectedValue == 5: system.nav.swapTo("Main Windows/Assembly/OP5") elif event.source.selectedValue == 10: system.nav.swapTo("Main Windows/Assembly/OP10") elif event.source.selectedValue == 18: system.nav.swapTo("Main Windows/Assembly/OP18") . . . elif event.source.selectedValue == 30: system.nav.swapTo("Main Windows/Assembly/OP80") elif event.source.selectedValue == 30: system.nav.swapTo("Main Windows/Assembly/OP90") else: system.nav.swapTo("Main Windows/Assembly/OP190") [/quote]I see many selectedValue == 30 in this list :frowning: [quote=“Mr.K001”]Originally I had this Under the mouseClicked event handler but there was a glitch, I could pick the OP from the list but it wouldn’t switch windows right away, I had to click the drop down box one more time for it to work. So I decided to change it and use the code under propertyChange event handler. It all seemed to work in the designer fine till I opened the Client, when I went in one of the cycle time windows, the client started acting all weird, and it wasn’t functioning so had to close it. Am I doing this right? as far as the code yes I can make it better by maybe using some kind of switch statement but as far as the event handler, would you do it any other way?[/quote]Well, propertyChange events fire for every change to a bindable property, including the assignments to those properties when the window opens. So your script would be firing many times on window open, triggering other window opens, which trigger other window opens, ad infinitum.
And mouseClicked isn’t appropriate, either, as you discovered, since its timing is wrong for what you are attempting. Finally, if … elif … elif … is an unmaintainable mess. Do these op station windows have the same structure? If so, use just one window, with indirect binding based on this drop-down. Organize all of the tags for these operator stations into folders, where the tag names are the same. Just the folder name is different. The the dropdown picks which folder to connect to the display.

18

oops, sorry, I grabbed a line and copied and pasted a bunch of lines and totally forgot to change the 30s to their proper values. I knew my if elif way was a mess, I just wanted to get it to work the way I wanted it then try to come up with better way of implementing it.
As far as the windows, they do have the same structure since I make one then just copied and pasted the same one and changed the name and bind different tags to it for different OPs. Also kept the tag folders and windows organized so I’m going to give your idea a try and see how that goes. Thanks.

19

[quote=“pturmel”]
Do these op station windows have the same structure? If so, use just one window, with indirect binding based on this drop-down. Organize all of the tags for these operator stations into folders, where the tag names are the same. Just the folder name is different. The the dropdown picks which folder to connect to the display.[/quote]

Well finally got this figured out and wow… what the heck man, I used to do this the hard hard hard way lol. This is so much easier and more convenient!
Did a Indirect bind the 6 component on the bottom and even did the same thing with the text field of the header! One thing I was worried about was my SQL Query for the table but considering I Named the tables all the same with only the OP name being different I bind my FROM clause to the drop down and walla!!! You’re the man! thanks for such a great suggestion.

One other thing( i know right… :open_mouth: ), so I have that refresh button so every time I click it it refreshed the table data to show the new date from the DB i used

system.db.refresh(event.source.parent.getComponent('Table'),"data")

Which works just, but is there a way to fire this up automatically say every 5 seconds? Instead of the user having to click refresh, it automatically does it every so many seconds.
Originally I thought maybe I can use the property change to fire it up say the drop down so whenever a new value changes in drop down the table gets refreshed but then what if someone leaves that OP open for 10 min you know.

window.jpg863×760 60.3 KB

20

Look at the binding for your query – at the bottom are choices on refreshing or not, and how often.