Block item transactions, table, step index, sort order

[dis]

This is an interesting scenario that I’m trying to program but I’m not sure of the ‘best’ way to tackle this.

There are block transactions that populate a set of tags with recipe steps. The steps_ndx column is an auto number in the SQL database. Each step is a record with several fields. One of these fields is the recipe step number. However, this field is not unique and it’s also not an integer (float).

When a recipe is first created the auto number will work as desired.

1.01 auto number 1
1.02 auto number 2
1.03 auto number 3
2    auto number 4
3    auto number 5
4    auto number 6

However if the recipe is changed the auto number fields will not be in the same continuous order as the step number. Because the column index is the integer auto number field, the changed stepped is now located at the bottom.

1.01 auto number 1
1.02 auto number 2
1.03 auto number 3
3    auto number 5
4    auto number 6
2    auto number 35

Final order desired:

1.01 auto number 1
1.02 auto number 2
1.03 auto number 3
2    auto number 35
3    auto number 5
4    auto number 6

The solution I’m thinking of is to:

1. Perform block transactions (they are triggered by a button press)
2. Run a script that pulls the recipe steps tag structure into memory (~1000 tags)
3. Sort the tags by recipe step (float) tag
4. Write tags back out in the desired order

The reason being the tags are put into a table for display which also compares each recipe to the one in the PLC. Also, downloading the recipe from the Ignition tags to the PLC requires a script that transposes the data.

I don’t know Python very well so I’m thinking someone may have some “nifty” sorting code that would be better than my brute-force method.

[paul-griffith]

@dis

One of these fields is the recipe step number. However, this field is not unique and it’s also not an integer (float).

JOINing two tables in an INSERT INTO

Is it actually a REAL? That's almost certainly not what you want. Just because a reason code happens to be something like digits.digits, doesn't mean you should represent it with REAL. If you do, then how do you distinguish between 123.1 and 123.10 and 123.100?

This should probably be a string (if your intention is to store basically a top level + sublevel key)... though, really, there's an argument for two columns - step and substep or whatever. That also makes ordering trivial in either SQL or Python.

[dis]

I completely understand your reasoning and I agree.

Due to the design of the “recipe” system the float of 1.## is used to ensure the download order to the PLC. I.E. 1.01 goes in the first array index , 1.02 the second, etc. The number is arbitrary, it is chosen by the editor (me) when creating the recipe. I could easily be 1.1, 1.2, 1.3. “Corporate” wants the steps to show up in a certain order on the screen. When it gets sent to the PLC it is written as an integer.

step[1] = 1
step[2] = 1
step[3] = 1
step[4] = 2

The PLC logic handles activating all three steps at the same time. In the PLC the step number is compared to a counter. It’s a system I’m currently bound to but I’ve been slowly improving it before I decide enough is enough and completely re-write it. I will look into the idea of step, sub-step as this is something I can implement in SQL without the need for changing the PLC code and it wouldn’t need to be sent to the PLC.

BTW, the PLC code is inside a custom AOI. While I can change it, that would require a download of the PLC. That would require a shutdown. We had a shutdown scheduled, then the shutdown date got moved, then cancelled. A new date has not been chosen. It has been almost 2 years.