Return the block_id from transaction group after execution

Ignition
1

I would like to set up some relational tables , and preferably use Ignitions transaction groups to do so.
But i’m running into a bit of a problem with the conceptual idea.
I would like to insert a row into a table that represents a production item. I would like to be able to use the auto incrementing column of the table as a unique ID for this item, which i can then use in other transaction groups that may provide relational data.
I don’t see a way to extract this though.
I will need this value to be used in a PLC which is compiling the relational data.

Is the only option to use an SQL query tag which reads the MAX(block_id) out? How could i write that to the PLC though?

2

I would use a stored procedure group. Have your stored procedure return the new ID as an OUT parameter.

3

Thanks Phill I’ll look into using a stored procedure.

I’ve acually got a slightly different use case pop up which i’m not sure i can achieve with a stored procedure.

I have a Block Transaction group which writes a block of data to SQL on a trigger. There are 500 rows, 3 columns. Each trigger is inserting a new block and hence generating a new block_id.
I would like to get this new block_id into the PLC somehow.

It doesn’t look like Stored Procedure groups support block inserts though.

4

At this stage i’m thinking of having two tags to achieve this.
SQL tag to read MAX(block_id) from table.
OPC tag to write to PLC.

Add an on-change script to the SQL tag, if value changes, write it to the OPC tag.
Then the PLC can detect the new block_id and act appropriately.