Help me configure a block transaction group

Ok, so I have a chunk of data in a SQL database that I want to send to the PLC. Seems like the block transaction is what I want.

Here is the setup, in the PLC I have an array of tags where the SQL data needs to end up:

PLC_Tag[0]
PLC_Tag[1]
PLC_Tag[2]
.
.
.
PLC_Tag[10]

In the SQL table I have 3 columns of data, I want the VALUE to populate the PLC tag, and the INDEX to point to the array element of the PLC tag. So the index is what aligns the data.

Here is a crude example:

PLC, INDEX, Value
1, 0, 1.23
1, 1, 1.24
1, 2, 2.54
.,.,.
.,.,.
.,.,.
1, 10, 9.99

Goal:

PLC_Tag[0] = “SELECT Value WHERE PLC = 1 AND INDEX = 0”
PLC_Tag[1] = “SELECT Value WHERE PLC = 1 AND INDEX = 1”
PLC_Tag[2] = “SELECT Value WHERE PLC = 1 AND INDEX = 2”
PLC_Tag[.] = “SELECT Value WHERE PLC = 1 AND INDEX = .”
PLC_Tag[.] = “SELECT Value WHERE PLC = 1 AND INDEX = .”
PLC_Tag[.] = “SELECT Value WHERE PLC = 1 AND INDEX = .”
PLC_Tag[10] = “SELECT Value WHERE PLC = 1 AND INDEX = 10”

For testing, I just have the trigger going every 1 second.

I tried to setup the “Table Action” to “update/select” and include a Where statement, but the transaction will fail w/o much detail as to why it failed, and I’m not sure of how the where statement should be formatted since I want more than 1 row of data to be returned.

If I set “Table Action” to “update/select” - “first” the transaction works and data is received. However, the data does not align, and I’m not sure of the order the data is being queried and sent to the PLC. Data that is in my table gets to the array tags in the PLC, just not in the correct location.

I assume the “where” statement needs some sort of place holders to ensure the indexing aligns with the data, but just doing a fixed where statement “PLC = 1 and INDEX = 1” produces an error. I would have expected all the PLC tags to be populated with the same data with that statement, but no go.

I haven’t found much reference on “block” groups anywhere so if there is a video or thread please point me to it!

“Update Mode” is set to DB to OPC

Data Source is correct

Table Name is correct, I have unchecked all options to crate tables/rows and store timestamps…ect

Here is how I would set it up.

I added the PLCTags item to the Block Items. Then I added a trigger Chris_Test (bool) to the Basic OPC.

Capture4.JPG1432×777 115 KB

Then I setup the Block Segment as a Pattern.

Capture3.JPG623×553 40.4 KB

Then I setup the Trigger on my Chris_Test bool.
Checked only execute once and Reset trigger.

I am not certain how you could indirectly address each PLC, but you could create separate transaction groups for each PLC or add another Block Item for each PLC, if they run off the same trigger.

Hope that helps.

Cheers,
Chris

Thanks I actually got it figured out.

I did use the “pattern” feature, and I also had to “check” the option to “use custom index column” so I could get the data to align correctly. I also had to bring in a tag that would index to the proper recipe in my SQL where expression.