Is the block_id from a block transaction group always unique? Or are there certain things I could have done which would have caused it to reset to 1? For instance, does modifying a group and then saving it cause the block_id to reset? What about changing the group name?
Here is my situation. I have a block transaction group which writes to table_staging in the database. Then I have a job on the SQL server that does some calculations and modifications, verifies these new rows have not already been moved, and moves that data to table_final and deletes the rows from table_staging.
Yesterday afternoon, it stopped moving the data to table_final, and it turns out that it is because the block_ids already exist there, so it thought they would be duplicated. I can go through and fix the data this time, but I need to know if it is going to reset the block_id again so I can stop assuming that is going to be unique.
When inserting, the block group select the max(block_id), and then adds 1 to it. If you are moving rows out of that table, it might be a bit unpredictable as to what you’d end up with. If the move and clear happened before the group ran, you would/should end up with 1 for the next execution.
Two solutions come to mind: 1) Mark your blocks as transferred, and when you delete, don’t delete the most recent… or 2) instead of using the “store block id” option, use 2 Query items in the group: one that selects the current “block id” from an outside table that you’ve created and is set to write to the “block_id” column, and and one that then runs an update query to update that value in the outside table.
Actually, you could probably achieve that 2nd id with Tags as well- create a memory tag that you then reference in the group as a “read only” item… execept I think you can then use it as the write target of an expression item whose value is “{BlockId}+1”. Since it’s a memory tag in an actual tag provider, the value will persist across group and gateway restarts.