Transaction group update multiple rows

Hi,

I've been going around in circles trying to get this working with transaction groups, but i'm not sure its possible with how they are designed to work.

I have a table of Maintenance tasks, with the following columns:
UUID, MC_ID, CurrRunMins

I would like the transaction group to be set up to update the MachineRunTime column for ANY row that has a MachineID as specified in the transaction group.
So no matter how many tasks (rows) i have, one transaction group will update machine run time for that specified machine, for any tasks that match.

I have tried using a standard group, but i noticed that when using a custom update, it does a query like:

SELECT UUID FROM MaintTasks WHERE MC_ID = ID LIMIT 1

Clearly i don't want the "LIMIT 1" part here.

So i tried using a Block Transaction group, but can't get that to work either.

I originally used a script but it took quite a long time to execute because it required looping through tags, so i don't really want to have to revert to that.
Any tips would be greatly appreciated.

One expression item, concatenating a string together to build a query. Example (and completely swagging column names and group tags):
concat("UDPATE MaintTasks SET CurrRunMins = '", toStr({[~]CurrRunMins}), "' WHERE MC_ID = '", {[~]ID}, "'")

One expression item to run the query:
executeUpdateQuery({[~]Update String})

You could eliminate a step by using an SQL Query Expression type, but I like to see the query being used. Maybe it’s just the control freak in me. :slight_smile:

1 Like

Maybe you should revisit this. You aren't looping through tags in a Transaction Group -- I don't see why you'd have to loop through tags in a scripted replacement.

Sorry i left a bit of information out. What i'm trying to do with this question is update ANY tasks that are assigned to ONE machine area.
I have built an Ignition UDT for a machine area, so my original script was searching my tags for UDT of type machine area before looping through and running an SQL query for each machine area.

To do this in transaction groups i had actually built a transaction group per Machine Area, but now i've discovered they don't update multiple rows.

Consider writing a single gateway tag change event script that can handle one of those UDTs, and subscribe it to all of the UDTs trigger tags. Note, not a tag event function.

For even better performance and reliability, if you don’t use all of the UDT contents for vision windows, don’t define non-UI elements in the UDT. Then, in the event script, use system.opc.read* to snapshot all of the non-trigger values (UI and non) when a trigger is seen, ensuring that none of the recorded values are stale.

1 Like

Jordan - Not sure what you mean about expression item, is that for the transaction group?

I suppose I can just use a Query Tag? Add it as part of my Machine Area UDT to update any tasks it can find that match the Machine ID.
I’ve just tested a temporary Query tag and it seems to work pretty well.

Yep! Standard transaction groups have two areas for expression items. Triggered Expression Items run whenever the group is executed and trigger conditions are met. There are also Run-Always Expression Items the run each time the group is executed, ignoring any trigger condition.

Expression items can be configured to use either an expression or an SQL query.

I tried using two expression items. The concat seemed to produce a valid query, but the execution expression tag is giving me an “Evaluation Error”.
But using an SQL query instead seems to work.

I think I’ll stick to an SQL query tag inside my Machine UDT though. Building/updating ~ 20 transaction groups is not particularly enticing.

But thanks for all the tips! I’m glad to finally have a reliable solution to this particular problem.