Block Transaction Group with Hour Meter and Event Meter

Ignition
1

Hey guys,
I’m running into a design problem and I’m sure it’s something I’m over complicating and was hoping someone had some good advice. Here are the details:

I have one SQL Tag which is called State and is of type INT4. This value is read from a PLC and can be any integer between 1 and 8. These represent the machine’s state, ie, Running, Blocked, Starved, Faulted, etc.

I’m trying to find a way to store the values in a SQL table by using Transaction Groups. I’ve successfully done this with a Standard Transaction Group and have used SQL queries to calculate duration and number of occurrences. This gets pretty tricky and could be a pain to manage as the number of machines increases. I’d really like to let Ignition do the work for me with the Hour Meter and Event Meter. It would all be in the same spot and easily modified and scaled later.

The problem is, I cannot figure out how to do this…I must be missing something.

I found this post by Pat and it looks like exactly what I want to do but I’m checking for more than a 1 and 0…:
viewtopic.php?f=70&t=8377&st=0&sk=t&sd=a

[quote]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]

My SQL table has the following columns: ndx, t_stamp, state_value, state_timer, state_count

Does anyone have any ideas on how to do this with a Block Transaction Group? Or should I just go back to SQL queries with the SUM and COUNT and build it that way?

Thanks

2

I think I’m getting closer and having a really hard time describing what I think is “ideal.”

So far, I have created 8 Expression SQL Tags of type boolean where the Expression looks to see if the State tag is 1,2,3,4,5,6,7,8. I named the 8 Expression SQL Tags: Running, Blocked, Starved, Faulted, etc. Now I have a boolean value for each that tells me what the machine is doing.

From that I have created a Block Transaction Group and I’m updating the following columns in the database:
ndx, t_stamp, state_active, state_timer, state_tagname, row_id, block_id

I can’t figure out what to Trigger or set the “Table Action” to so it only increases the timer for the SQL record that represents the current machine’s state.

Let me try to explain what I’d like to see happen:
Every time one of the 8 Expression SQL Tags is true, I’d like to start the hour meter for that particular state and time the event until one of the other Expression SQL Tags goes true.

Ultimately, I want to present a table that shows each occurrence of the different machine states with the duration the machine was in each state.

I hope that was clear :scratch:

3

I have given up hope trying to reset individual Hour Meters within a Block Item of a Block Transaction group.

I changed my attack a little bit and now have the following:

SQL table with table_index, station_id, state_value, state_timer

*One Block Transaction Group with 2 block items:

  1. Station ID
    • 200 SQL Tags with static Station IDs
  2. State Value
    • 200 SQL Tags each one representing a station’s state and can have values 1-8
      No Triggers and Insert Changed Rows

*200 Standard Transaction Groups, each with:

  1. An Hour Meter based off the State Value SQL Tags
    Bi-Directional, DB Wins
    Update Custom with a SQL query that selects the top 1 record (descending index) and the StationID for that particular Transaction Group.

This appeared to work awesome. Any time any of 200 station’s State changed, it was written to the database with a 0 for the Hour Meter, which would reset the Hour Meter for the current state and begin updating the timer/accumulator until the machine changes state again.

The problem I have is the data isn’t that accurate. I’m counting in seconds with most machines States between 5-50 seconds. The Hour Meter is usually 1 second slower and can be as much as 4 or 5 seconds slower than the actual tag changing values. It almost looks as though the longer the Hour Meter runs, the less accurate it is.

So…I thought I would start changing the Execution Timer. I found if I change the Execution Timer from 1 second to 500 milliseconds, the Hour Meter runs twice as fast which is weird. I’m running out of options to try but I feel like I’m still making progress.

Does anyone have any input? All I really want to do is reliably log how long a machine is in one of 8 states based off of one SQL Tag’s value (don’t have a timer in the PLCs).

4

You could look into using Tag Change event scripts.

5

Thanks adamaustin. We currently use some pretty complex Tag Change scripts for other projects. I was just hoping there was a way to use the Hour Meter because it’s a lot easier and cleaner to maintain than the complex scripts…especially if multiple people are involved with adds/changes.

If only I could crank down the Scan Class for the Tag and have the Transaction Group execute more frequently than once a second and not have the Hour Meter run faster. I wonder if this is by-design??? Does anyone else see this occurring

Also, I was thinking about using the LastChanged property of the SQL Tag with a now() expression to derive an Expression Tag that would be similar to the Hour Meter. I haven’t thought through this completely. Does anyone do anything like that instead of Tag Change scripts?