It is currently Wed Aug 20, 2014 10:31 am




Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 24 posts ]  Go to page Previous  1, 2
 Thinking about sumations 
Author Message
Moderator
Moderator

Joined: Sun Apr 02, 2006 3:43 pm
Posts: 2491
Post Re: Thinking about sumations
Quote:
how is it not resetting my hour meter every second

The hour meter reset is performed on the rising edge and latched. It won't reset again until it sees a 0 first.

For the query, just use a SQL Query binding on the label text:
Code:
SELECT TOP 1 "MOTOR  {Root Container.MotorID}" FROM dbo.motorrun ORDER BY t_stamp DESC

So, what you're doing there is ordering the results by the timestamp (in reverse), and limiting it to 1 result. As for the column name, you can just bind in references directly in the query. In this case it's made a tad more complicated by the fact that there appears to be a space between "MOTOR" and the number in the column name... so you have to quote the column name in the query.

Hope this helps,

_________________
Colby Clegg
Inductive Automation
Software Development


Wed Jun 27, 2012 7:44 am
Profile
General
General

Joined: Wed Apr 25, 2012 5:51 am
Posts: 388
Location: Terryville, CT
Post Re: Thinking about sumations
The select query works perfectly for me now.
Thanks very much for that.. My column names and motor IDs have white space which has caused problems for me since the beginning but I wanted to learn how to manage them.

How could I reset my tag that is the reset condition for my group items... this is how I am triggering the change on a button:

system.db.runUpdateQuery("INSERT INTO motorrun (t_stamp) VALUES (CURRENT_TIMESTAMP)" )
system.tag.writeToTag('ShiftReset', 1)


I was thinking that I could use a tag change client script that would simply reset the value to 0 after a minute of it being changed to a 1. Is this a good way to do it?
Is there a way to avoid all the settings for each item.. like the reset conditions and the hour settings. Could I just set up all of my items similarly to a UDT architecture so I am not repeating settings 200 times?


Wed Jun 27, 2012 8:43 am
Profile
Moderator
Moderator

Joined: Wed Apr 18, 2007 11:00 am
Posts: 645
Location: Sacramento Ca.
Post Re: Thinking about sumations
You can reset the trigger value in a Transaction group by selecting the 'Write handshake on success' (or failure) checkboxes in the Trigger tab.

_________________
Robert McKenzie
Inductive Automation
Technical Support


Wed Jun 27, 2012 1:26 pm
Profile WWW
Moderator
Moderator

Joined: Sun Apr 02, 2006 3:43 pm
Posts: 2491
Post Re: Thinking about sumations
One thing you can do is to set the group to be "bi-dir, db wins". Then you no longer need to specify reset conditions on each tag- instead, when you insert the row with default values, the 0 that goes into the db will be written to the tag, resetting it.

In regards to setting up hourmeters, unfortunately I think you'll have to do this on each item. It may be possible to export to XML and search & replace. The hourmeter condition is defined under the element "ITEM_EXEC_FLAGS", and has the value of 1. If you export a group with an hour meter, you'll see what I mean. Since this field is a bit-flag field, you may not have exactly 1, but the 0th bit should be high. Here are all of the item exec flags:
Code:
FLAG_HOURMETER = 1;
FLAG_EVENTMETER = 2;
FLAG_RETENTIVE = 4;
FLAG_ON_ZERO = 8;
FLAG_RESET_METER_ENABLED = 16;


Regards,

_________________
Colby Clegg
Inductive Automation
Software Development


Wed Jun 27, 2012 2:03 pm
Profile
General
General

Joined: Wed Apr 25, 2012 5:51 am
Posts: 388
Location: Terryville, CT
Post Re: Thinking about sumations
Colby.Clegg wrote:
One thing you can do is to set the group to be "bi-dir, db wins". Then you no longer need to specify reset conditions on each tag- instead, when you insert the row with default values, the 0 that goes into the db will be written to the tag, resetting it.

In regards to setting up hourmeters, unfortunately I think you'll have to do this on each item. It may be possible to export to XML and search & replace. The hourmeter condition is defined under the element "ITEM_EXEC_FLAGS", and has the value of 1. If you export a group with an hour meter, you'll see what I mean. Since this field is a bit-flag field, you may not have exactly 1, but the 0th bit should be high. Here are all of the item exec flags:
Code:
FLAG_HOURMETER = 1;
FLAG_EVENTMETER = 2;
FLAG_RETENTIVE = 4;
FLAG_ON_ZERO = 8;
FLAG_RESET_METER_ENABLED = 16;


Regards,


My default values are NULL.. It doesn't seem to work like this for me and it won't let me write to a SQL tag on handshaking, only group items it looks like.
I have retentive checked and the only way I can reset the items with the creation of a new db row is to force my tag on and then off (if i want to repeat)


Thu Jun 28, 2012 4:28 am
Profile
Moderator
Moderator

Joined: Sun Apr 02, 2006 3:43 pm
Posts: 2491
Post Re: Thinking about sumations
Ah, yeah, it won't work if the defaults are null. I had a thought that that might be the case, but was hoping for the best. In that case, you'll either need to change the defaults, or have your insert query specify each column and a 0 value, which I know is a bit of work.

Do you need to reset each motor individually, or all together? Because here's something I've worked out that seems decent, but would really be best if you were logging and resetting all of them at the same time:

1) Create a "log and reset" SQLTag for the group (or if it was based on an expression, like time of day, it could just be an item in the group). It will be a boolean, or 0/1 integer.
2) Have each hour meter set to reset on 0 of that tag.
3) Have the group set to insert new records (and make sure your mode is opc->db), and have it set to trigger on that item, on 1, with "reset trigger" and "only execute once" enabled.

So, this works because the hour meter reset is only on the edge of the value change. When everything starts, the trigger will be 0, all hour meters will start at 0, and begin counting, according to their values. When you trigger it to log (the trigger tag goes to 1 for whatever reason), the group will log according to the trigger, and then "reset" the trigger by writing a 0. On the next execution, all of the items will see the 0 and reset the hour meters.

Setting this up for all of your motors shouldn't be too bad, since you're using the same settings for all of them. Bring them all into the group, then select them all, right click, and hit "edit". You can multi edit them to set up the hour meter mode and reset condition. Be careful not to touch the "target" property, or you'll wipe out the unique column names.

Hope this helps,

_________________
Colby Clegg
Inductive Automation
Software Development


Thu Jun 28, 2012 8:10 am
Profile
General
General

Joined: Wed Apr 25, 2012 5:51 am
Posts: 388
Location: Terryville, CT
Post Re: Thinking about sumations
I decided to set this up using a tag and reset the tag on success.
Now I've got the same setup but I'm looking at recording history to a database for 512 tags.

I had tried to setup a block group since I don't think the number of items is going to change,

Image


Here I'm looking at the 2 tables created by using the method above and the new table I'd like to look and function just like the motorrun table.
It seems like I'm locked into creating block items as table rows. Can I change this so it will be more manageable ?


Fri Jun 29, 2012 7:46 am
Profile
Moderator
Moderator

Joined: Wed Apr 18, 2007 11:00 am
Posts: 645
Location: Sacramento Ca.
Post Re: Thinking about sumations
Most modern databases aren't good at handling table with many columns (on the order of a few hundred). The point of the Block Group is for handling cases like yours that would have too many datapoints to efficiently store them into columns. You can create a new column that is the name or number of the tag and then use that in your where clause to filter out the results.

If you really want each data point to be a new column, then you have to use the standard group. Beware using this method for more than 100 columns though, queries against it become very memory intensive.

_________________
Robert McKenzie
Inductive Automation
Technical Support


Fri Jun 29, 2012 8:53 am
Profile WWW
General
General

Joined: Wed Apr 25, 2012 5:51 am
Posts: 388
Location: Terryville, CT
Post Re: Thinking about sumations  Topic is solved
Thanks for walking me through Transaction groups..
I think Ive got everything exactly how I wanted and I definitely appreciate the help.


Fri Jun 29, 2012 10:38 am
Profile
Display posts from previous:  Sort by  
Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 24 posts ]  Go to page Previous  1, 2


Who is online

Users browsing this forum: Google [Bot] and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to: