Thinking about sumations

I’ve got my RUNNING tag in my UDT set up with history enabled… 1 minute scan class. I’m looking at creating a tag within the UDT that can sum the historical values of this (a bool value tag) so that i could return a value that keeps track of how many minutes a motor has been on.

Should I be doing this directly using a query? and if so, what kind of coding are we dealing with?

You won’t want to query the database directly. Instead, use a system.tag.queryTagHistory() inside of an expression with the runScript() expression function.

I was able to do this with a memory tag and a client tag. The memory tag would be the boolean for the motor on/off, the client tag would be your runtime. And this is the expression that I put on the client tag:

if({Memtag}, dateDiff(toDate({Memtag.LastChange}), toDate(now()), "minute"), 0)

I think I may be better off using a historical transaction group in this case and executing once a minute and then inside of my window, I can indirectly reference my tag with the appropriate column in my database and just query it directly with a sum.

Historically trending for my tags isn’t really necessary in this case although the redundancy wouldn’t kill my project with latency issues, would it? Also, I would rather log it all into my db so if a random person wants to look at the data directly, it’s easier to view it.

Can I add db columns on the fly for new group items or do I have to redesign my db table to take the new column and then assign a designation?
I may have over 200 motors I want to track history for every minute and if I have to add or remove some, I’d like to balance flexibility with simplicity.
I’m logging the data now but I’m not sure if I am organizing my db in the most efficient way to make room for changes to the transaction group.
Would it be crazy to make 200 different tables and transaction groups just so I won’t have to edit db table columns?
Any thoughts on this?

1 Like

This sounds like an entirely different question from the original post.

I’m just trying to get an idea of how someone else might set this up… I am doing it now completely different then I thought I was going to, yes.

Hi,

I think you’re right that transaction groups would be the easiest way to go. We need to beef up our tag history query capabilities over time, and SUM definitely needs to be added soon.

With groups, you have a few options:

  1. You can do exactly what you had in mind: log every second, query with SUM(), maybe set the group(s) to delete after 1 month or something.
  2. Items in groups can actually be “Hour Meters”, which are pretty much what you want. You take your on/off tag, set its data type to float (or int), and set it to be an hourmeter “on 1”. While the tag is 1, it will accumulate value (the group can run at any rate). When it goes to 0, it will reset or not, depending on whether it’s “retentive”. With 7.5, there’s an additional option for when to reset the value. So, instead of logging history and calculating a sum, you could have hourmeters set up to record to the last row of a table. If you want to really get tricky, have the group set up as “bi-dir, db wins”. Then, you can just insert a new row with 0s in the columns to reset the values and start over (for shifts, or per day, for example).

Now, for your group questions:

  • Overhead for logging probably won’t affect your device communication much. You already have the items subscribed elsewhere, so it won’t be a big deal.
  • You can definitely have 200 groups, though I prefer to minimize the number of groups when possible, as it can get a tad cumbersome to work with so many. But, 200 isn’t too bad, 1000 is rough. Still, if you have 200 motors, it would probably just be better to make a “motor_runtime” group with 200 columns.
  • You can definitely just add new items to a group. It will just add the columns onto the table for you.

Hope this helps,

Alternative approach.

I presume you are using a PLC to derive your running status

Rather than datalogging every minute while you are running, how about just log run events (or down time events)? i.e. timer in the plc keeps track of up time (or downtime) in minutes. When a running or downtime event finishes, the plc sets a boolean tag to true, loads the downtime count into a tag and a transaction group logs the minute count (and a time stamp) into a database.

We have done it this way for downtime tracking on some of our attached plant.

You can then sum minute counts between different dates and times to get downtime in a day, downtime in a month etc, as well as being able to allocate reasons for downtime and some other follow on stuff relatively easily. It becomes an easy matter to build reports, charts etc off this.

Note that we are not using the downtime and OEE module.

Just throwing it out there. Good luck! :thumb_left:

Use a Block Transaction Group and have Ignition do the timing calculation for you, no mess no fuss. As Colby mentioned you can set up a transaction item to be an “Hour Meter”, so you could create a Standard Group with 200 items that correspond to 200 columns in some table, this is probably easiest for most users. But, as Colby also mentioned, it can be cumbersome working with a table with so many columns, plus it’s not good database design. It’s better to have a long table instead of a wide table and there are two ways to accomplish that in this case, 1) create a Standard Group for each of your motors or 2) use one Block Group for all your motors.

A simple table would be something like motor_id, timer, counter, t_stamp (although you didn’t mention anything about having a counter, it’s simple to add and it’s valuable information). From there you could set-up a Standard Group for each of your motors, where each of the groups would have the following items - a motor id item, a running status item set it to be a timer, and a second running status item set it to be a counter. The drawback with doing it this way, it can be a little hectic to manage and it’ll take some time to implement. Enter the Block Group, create one block group, add three block items MotorID, Timer, Counter, and add your tags to the block items. About the hardest thing about using the block group is making sure all your items are in the same row, but the Block View tab allows you to easily view the data. It’s easy to manage, clean, and probably has a faster execution time than using multiple standard groups. Block Groups are great for doing historical logging as well.

[quote=“Colby.Clegg”]If you want to really get tricky, have the group set up as “bi-dir, db wins”. Then, you can just insert a new row with 0s in the columns to reset the values and start over (for shifts, or per day, for example).
[/quote]

I would like to do this.
I am updating the last row in my group.
How can I insert a new row without doing an insert into. … and naming all of my columns?

Different databases will behave a little differently (and it probably depends on the table setup a bit), but in general there’s no rule about having to specify each column. For example, in mysql you could do something as simple as:

INSERT INTO yourtable() values()

to just use the default values for all columns.

Regards,

anyone know how to do this with MS SQL server?
So far, I can’t find anywhere where it even says it is possible to do this

Well, according to the documentation for INSERT for SQL Server, it looks like you should be allowed to do:

INSERT INTO yourtable DEFAULT VALUES

If nothing else, you should also be able to insert with just the timestamp:

INSERT INTO yourtable(t_stamp) VALUES(CURRENT_TIMESTAMP)

Regards,

using the timestamp as a placeholder worked for me…
Thanks a bunch… SQL is very new to me.
Where might I use this SQL code lets say if I wanted to push a button to call the insert query.

On button action I am writing to a tag called shiftreset a value of 1.
Inside my group, my items are set to reset when this tag is = to 1
It looks like it is working how I had wanted it to work, resetting when I click the button and creating a new row in my table.
However, I never reset the tag to 0… how is it not resetting my hour meter every second when the tag value is always a 1?

Looking a little further down the line here,
I would like to pick the last item in one of these columns in my table out and display it in a window.
I would like to indirectly reference which motor I need to value for as well…
Where could I implement code and how?
I have something like this now:

Label Text binded to:

DECLARE @MyVar VARCHAR(50) = ‘dbo.motorrun.’ + ‘{Root Container.MotorID}’
select (@MyVar) from dbo.motorrun
where last(timestamp)

This won’t work… Even if I take out the Where, it only displays dbo.motorrun.MOTOR 101 instead of a value in the column

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:

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,

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?

You can reset the trigger value in a Transaction group by selecting the ‘Write handshake on success’ (or failure) checkboxes in the Trigger tab.

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:

FLAG_HOURMETER = 1; FLAG_EVENTMETER = 2; FLAG_RETENTIVE = 4; FLAG_ON_ZERO = 8; FLAG_RESET_METER_ENABLED = 16;

Regards,

[quote=“Colby.Clegg”]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:

FLAG_HOURMETER = 1; FLAG_EVENTMETER = 2; FLAG_RETENTIVE = 4; FLAG_ON_ZERO = 8; FLAG_RESET_METER_ENABLED = 16;

Regards,[/quote]

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)