Why do Ignition block transaction rows sort out of order?

The recipe rows land out of order because the block transaction group places rows by the auto-number index column (steps_ndx). It does not place them by the recipe step number. When you edit step 2 and it is re-inserted as auto number 35, it moves to the bottom of the block. Fix the order in the database, where the data is still rows. Add integer step and substep columns, or an explicit sequence column, and read in that order. Sorting ~1000 tags in a script after the transaction is a fallback only, and it corrupts data if it writes back into tags the group owns. The PLC AOI needs no change for either approach.

Symptom patterns in database-backed recipe step tables

A block transaction group maps a set of database rows to an array-like block of tags, one row per block position. Every ordering symptom below comes from one of two faults: the row index is used as the order, or the step key is stored as a floating-point number.

Row order, auto-number keys, and REAL step values

A SQL table is an unordered set. The only order you can rely on is an explicit ORDER BY in the query that reads it. An auto-number primary key identifies a row. It says nothing about sequence. It increases with insertion time, so any edit that deletes and re-inserts a row, or appends a replacement row, gives that step the highest index in the table. A block group keyed on steps_ndx then fills block positions in index order:

The step number column is a second, separate problem. 1.01, 1.02, 1.03 is a two-level key: top-level step 1, substeps 01 through 03. A REAL cannot represent that. It stores 1.1, 1.10, and 1.100 as the same number. It orders 1.10 ahead of 1.9. Most decimal fractions also have no exact binary representation, so equality tests on the column are unreliable. With the current data, numeric ordering happens to give the right answer, because the editor used a consistent two-digit substep. That holds only as long as nobody enters 1.1 next to 1.01.

Constraints imposed by the PLC recipe AOI

The PLC side fixes what the database may change:

• The download script transposes the recipe tags into PLC arrays. The array index is the download order, so the row order in Ignition becomes the execution array order.
• The PLC receives only the integer step. The example recipe downloads as step[1] = 1, step[2] = 1, step[3] = 1, step[4] = 2.
• The AOI compares each element's step number against a counter. All elements that share an integer step activate together, so 1.01, 1.02, and 1.03 run as one step 1.
• The fractional part exists only to set display and download order. Its value is arbitrary and chosen by the recipe editor.
• Changing the AOI requires a PLC download and therefore a shutdown. No shutdown date is scheduled.

Any fix must therefore keep three outputs unchanged: the integer step value per element, the element order, and the array layout the transposition script produces. Splitting the step into major and minor integer columns meets all three. The major value is exactly what the PLC already receives, and the minor value never leaves SQL.

Step and substep columns with an ordered sequence

Two integer columns make ordering trivial in both SQL and Python. A third column, a sequence number (a dense 1..N position within one recipe), gives the block group a stable key that already matches the display order. The column names below are examples. The SQL shown is Microsoft SQL Server syntax, so adapt it for your database engine.

1. Add the columns:

   ALTER TABLE recipe_steps ADD step_major INT NULL, step_minor INT NULL, seq INT NULL;

2. Backfill from the existing REAL column. This assumes every stored substep uses exactly two decimal places, as in 1.01. Check for any values that break that assumption before you trust the result.

   UPDATE recipe_steps
   SET step_major = FLOOR(step_no),
       step_minor = CAST(ROUND((step_no - FLOOR(step_no)) * 100, 0) AS INT);

3. Renumber the sequence within each recipe. Put steps_ndx last in the ordering so ties resolve the same way every time:

4. Run the renumber statement on every recipe save, as part of the same button-triggered action that performs the edit. Run it before the block transaction reads the rows.
5. Point the block group's row index at seq instead of steps_ndx. Leave steps_ndx as the primary key. Never renumber or reuse the auto-number key to force order.
6. If the group cannot key on a custom column, bind the display table and the download script to a query with ORDER BY step_major, step_minor. The block tags then stop being the source of order.
7. In the download transposition, send step_major as the PLC step value. For the example recipe this gives the array 1, 1, 1, 2, 3, 4, which is identical to today's download.
8. Change the recipe editor to write step_major and step_minor directly. Keep the REAL column only until nothing reads it.

Script-side sort of the recipe tag structure

Sort in a script only when the database schema is frozen. Keep three rules. Read the whole block in one bulk call, not ~1000 individual reads. Sort rows, not individual tags. Write the result to a separate display/download tag set, never back into the tags the block group is bound to. If the group is bidirectional, writing sorted values into its tags pushes step 2's data into the row keyed steps_ndx 5, and so on. That overwrites database records with the wrong content. If the group runs on a timer, it also overwrites the sorted order on its next execution.

If the step is later stored as a string such as "1.10", replace the float key with a split key: major, _, minor = s.partition("."), then return (0, int(major), int(minor or 0)). That key orders 1.9 before 1.10. Trigger the script from the group's completion or handshake signal, not from the same button press. Otherwise the script can read the block before the transaction has written it.

Checks on the reordered recipe and PLC download

1. Query the edited recipe with ORDER BY seq. Expected: 1.01, 1.02, 1.03, 2, 3, 4, with seq 1 through 6 and steps_ndx 1, 2, 3, 35, 5, 6.
2. Check step_major/step_minor against the REAL column for every row. Expected: (1,1), (1,2), (1,3), (2,0), (3,0), (4,0). A row showing (1,10) where you expected (1,1) marks a stored 1.1 that needs re-keying.
3. Look for duplicate sequence numbers within one recipe. Expected: zero rows returned by a GROUP BY recipe_id, seq HAVING COUNT(*) > 1 query.
4. Trigger the block transaction and read the block tags. Expected: block position 4 holds the step 2 data from row 35.
5. Trigger the transaction a second time without editing anything. Expected: the order is unchanged and the database row for steps_ndx 35 has the same content as before.
6. Run the download transposition to a test copy of the arrays, or compare before a live write. Expected: PLC step array 1, 1, 1, 2, 3, 4, the same as a download of the unedited recipe.
7. Open the recipe-vs-PLC comparison table for a recipe that matches the controller. Expected: no mismatch flags on any row.

Recurring errors in recipe ordering on SCADA-to-PLC systems

• Treating the primary key as a sequence. Any edit, delete-and-reinsert, or restore from backup changes the order. Order comes from a column that you control.
• Floating-point hierarchical keys. 1.1 and 1.10 collapse to one value, and 1.10 sorts before 1.9. The editor's freedom to pick 1.1, 1.2, 1.3 or 1.01, 1.02, 1.03 guarantees that both conventions will eventually appear in one recipe.
• Implicit REAL-to-integer conversion. If the integer step is ever derived inside the controller instead of the script, note that a Logix REAL-to-DINT move rounds rather than truncates. A step of 1.5 or higher would become 2. Derive the integer explicitly on the SCADA side (step_major, or int(math.floor(step))).
• Sorting tags the group writes back. A bidirectional block group turns a display sort into a database corruption. A timer-driven group undoes the sort. Write sorted data to its own tag set.
• Positional comparison against the PLC. A positional row-by-row compare reports false mismatches whenever the order changes. Once order is deterministic, positional compare is valid, because the PLC array order is the download order.
• Renumbering in the wrong place. Running the seq update after the block transaction leaves the tags one edit behind. Verify the fix by editing a step, triggering the save, and confirming that the block shows the edited step in its new position on the first transaction, not the second.

FAQ