I am trying to create a simple downtime OEE table using one word from a PLC, when the word is greater than zero a transaction group inserts a new row and another transaction group,set as a hour meter, inserts the duration the value was above zero. My problem is that the “update” overwrites the previous duration before the new row is inserted. So say the last duration was 20 seconds, the new alarm overwrites the 20 with a 1 or a 2 THEN a new row is inserted and then the duration is updated to this last row. Is there a documented simple method of achieving downtime tracking? Ideally I would like to count the duration when the value is zero as well (machine running) i.e. a duration for every change of the value.
Hi-
Yes, this is a common problem that people run into. There are a variety of ways to handle it, but the one I commonly recommend is to simply have the insert group and the update group trigger off of opposite values of the boolean. That is, have the insert execute when the boolean goes to 0. Keep your update group the same. In that way, the next row is inserted at the end of the previous event. The inconvenience is that when you view the data you’ll need to filter out the last row, but usually this isn’t too hard.
The other common solution is to use run-always action items in the group to perform some logic ultimately ending up in a call to ‘executeUpdateQuery(“insert into hourmetertable(col) values(0)”)’ to insert the row before the group executes. It usually also involves the “StoreVariable/GetVariable” functions. If you want more info on this, let me know, but I would recommend trying the first method to start with.
Regards,
I wanted to follow up that after writing the response to your question, it got me thinking- since this is something that comes up very often, it would be nice to have a better response. The reason that I didn’t go too much into the second option is because it involves a number of expression items in the group, and the use of the “storevariable/getvariable” functions, which I don’t like to promote.
After thinking about this, though, I realized that a “hasChanged” function that simply took a value and returned whether it was different than the last time the function was called could go a long way towards helping this (as well as other situations where people want to build complex triggers). So, in 7.1.8, there will be a new group-level expression function by that name that does exactly that. That would let you do the following:
- In your hour-meter group, in addition to your hour meter, add a copy of the source tag, either as a boolean or integer. You can make this “read only” by not specifying a target column. Let’s say this item is called “Trigger”.
- Create a new expression item in the group, and set it to “ignore trigger” (in other words, “run always”). Set it to be an expression, and set the expression to:
if({Trigger}>0 && hasChanged({Trigger}), executeUpdateQuery("insert into grouptable(hourmetercolumn) values(0)"), 0)- Set your group to execute on a trigger, with the trigger being your “Trigger” item, “>0”.
There would now be no need for your insert group. The run-always expression item will execute before the rest of the group, will detect if your hour meter source bit has transitioned from 0 to 1, and will insert a new row. While the bit is high, the group will be triggered and will update the last row.
This will be available in 7.1.8 beta 3. If the original proposed method works for you, then please go for it, but this would let you do everything in one group. I want you to know that you are by far not the first to do this- but each time it comes up, I keep thinking there should be an easier way. We will hopefully someday make it more of a built-in group feature, but for now this should help people implement it in a bit cleaner manner than before.
Regards,
suppose I want to execute multiple insert querys instead of one… How would I structure that inside of the if?
What do your multiple queries look like? Is each query inserting different values into different tables?
different values into the same table… I made a new post about this in design help.
I need to make my table insert the itemname and the value of that item in the table.
Currently I can only get the value to store.
I am using the insert query to make new blank rows with only a timestamp and then updating the last few rows with the transaction group.
Travis posted a solution to getting the tag names in the table, does that solve your issue?