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…:
[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?