Configuring Ignition SQL Recipes with Recipe ID Filters

After the fix, selecting a recipe loads only that recipe's ordered ingredient steps while one transaction group remains connected to one stable table structure. The key change is to select rows by recipe ID instead of treating the dropdown text as a SQL table name.

Reject the quick table-copy fix

The first approach usually tried is to delete every row from sample, copy the selected recipe table into it, and let the transaction group read the replacement data:

DELETE FROM sample WHERE id IS NOT NULL;

INSERT INTO sample (ID, ItemNumber, ProductName, Setpoint)
SELECT ID, ItemNumber, ProductName, Setpoint
FROM selected_table;

This can move data, but it creates avoidable failure modes. The delete and insert leave an empty or partially loaded staging table unless they execute inside one database transaction. A transaction group can read during that interval. Two recipe selections arriving close together can also overwrite each other's staging data.

Replacing selected_table with {Root Container.Dropdown.selectedStringValue} is another weak point. SQL parameters represent values such as a recipe ID; they generally do not represent identifiers such as table names. A component query binding may perform project-level substitution before sending SQL, while a database query browser sends SQL in a different context and cannot resolve a component property path. Directly concatenating dropdown text into SQL also exposes an identifier-injection path.

Do not add a tag-change script merely to preserve this table-per-recipe design. A Boolean edge can start a script, but it does not correct the data model, eliminate the copy window, or make a UI component property available to a Gateway-scoped event.

Check: Stop here if production logic requires the transaction group to read a copied table at a precisely coordinated instant and that coordination has not been defined. Otherwise, remove the copy operation from the normal recipe-selection path.

Set one row structure for every recipe step

Store recipe identity separately from step data. Use a recipe header table for one row per recipe and a recipe-step table for one row per ingredient or process step. This avoids a wide record containing Ing1, Code1, SP1 through 20 or 30 repeated groups.

Link RecipeSteps.RecipeId to the selected recipe. Link RecipeSteps.StepId to StepCodes.StepId. Use StepNumber as the ingredient index presented to the PLC. A key or index covering RecipeId and StepNumber supports selection in process order.

This design keeps the transaction-group payload narrow. The PLC still receives the current ingredient, code, and setpoint rather than 60 or 90 parallel values. Adding a recipe inserts rows; it does not require another physical table, another query shape, or another transaction group.

Check: Select one RecipeId manually in the database and confirm that its step rows are unique and ordered by StepNumber.

Load existing recipe tables into the shared structure

Migrate each current recipe table once. Create or locate its header row, obtain the corresponding RecipeId, then insert that table's ingredient rows into RecipeSteps. Map the existing row ID to StepNumber only if it already represents the required process order. Otherwise, define the order explicitly during migration.

1. Back up the recipe tables and record their row counts.
2. Create one row in Recipes for every recipe name.
3. Map each legacy ingredient row to its recipe ID, step number, step definition, and target value.
4. Insert the mapped rows into RecipeSteps.
5. Insert reusable descriptions and item numbers into StepCodes, then reference them through StepId.
6. Compare counts and ordered values before retiring any legacy table.

Do not turn the old table name into the long-term recipe key. Names change and may contain characters unsuitable for SQL identifiers. Use the database ID internally and keep RecipeName as display data.

Check: For every migrated recipe, compare the ordered ingredient, code, and setpoint values with the old table. Do not proceed when counts differ or a step number is duplicated.

Bind the dropdown to recipe IDs

Configure the dropdown so operators see RecipeName while the selected value is RecipeId. The query must pass the ID as a value parameter, not substitute the displayed recipe name into a table identifier.

If the selection must survive client closure or be visible to the Gateway, store the selected ID in a controlled shared location. Validate that it identifies an enabled recipe before permitting a load request. Keep the Boolean request separate from the selected ID so the Gateway can read one unambiguous selection when the trigger changes.

Check: Change the dropdown and inspect the selected value. It must be the intended RecipeId, while the operator still sees the recipe name.

Query the selected steps with a value parameter

Join the step rows to the step definitions and filter on the recipe ID. The evidence supplies this query shape; correct the field spelling to match the actual database schema:

SELECT
    RecipeSteps.StepNumber,
    StepCodes.StepDescription,
    StepCodes.ItemNumber,
    RecipeSteps.TargetValue
FROM RecipeSteps
INNER JOIN StepCodes
    ON RecipeSteps.StepId = StepCodes.StepId
WHERE RecipeSteps.RecipeId = :SelectedRecipeId
ORDER BY RecipeSteps.StepNumber;

:SelectedRecipeId is a value placeholder. Bind it to the dropdown's selected recipe ID for the display query and to the shared selected-ID value for Gateway or transaction-group execution. Keep the table names fixed.

The result is the same useful shape as the proposed holding table: one row per step and only the fields needed at that point in the process. Recipe length becomes a row-count issue rather than a column-count issue. The PLC can continue indexing through rows, subject to the configured maximum number of allowable steps in the PLC and database-to-OPC path.

Check: Run the query for two known recipe IDs. Confirm that each returns only its own rows, in step order, with the expected item number and target value.

Connect the transaction group to the selected recipe

Keep the transaction group on the shared recipe-step structure and apply a WHERE condition using RecipeId. This preserves the one-table constraint without copying records into sample or hold.

1. Expose the selected RecipeId to the transaction group through the project's approved shared value.
2. Filter the group's database rows by that ID.
3. Use StepNumber or the established row key for PLC-controlled indexing.
4. Map only the current step fields required by the PLC, such as ingredient or item, code, and setpoint.
5. Reject a load when the selected recipe returns zero rows, duplicate step numbers, or more steps than the configured PLC capacity.
6. Acknowledge completion only after the selected rows and first transferred values have been verified.

Use a Gateway script when the transfer requires sequencing, validation, acknowledgements, or an atomic handoff that the transaction group cannot express cleanly. A tag-change event may detect a Boolean transition, but read both the current request state and selected recipe ID at Gateway scope. Make the action edge-driven or state-controlled so repeated tag evaluations cannot reload the recipe unintentionally.

Check: Trigger one controlled load and verify that the transaction group reads the selected recipe ID, starts at the intended step, and does not transfer rows belonging to another recipe.

Stage rows only when the interface requires it

If an existing production interface can read only sample or hold, use that table as a temporary compatibility layer. Treat replacement as one database transaction so readers never observe the gap between delete and insert.

1. Validate the requested RecipeId.
2. Begin a database transaction.
3. Delete the old staging rows.
4. Insert the selected rows from the fixed shared tables using a recipe-ID value parameter.
5. Verify the inserted row count and step sequence.
6. Commit the transaction; roll it back if any check fails.
7. Publish the completion acknowledgement only after commit.

Do not obtain a source table name from selectedStringValue. If legacy tables temporarily remain unavoidable, map each approved selection to a hard-coded allowlist of known identifiers in Gateway code. Never pass free-form component text into the SQL statement.

Run the end-to-end test with two recipes of different lengths. Confirm the screen dataset, filtered database rows, transaction-group selection, PLC step index, transferred item or ingredient, code, and setpoint. Then repeat the request, change selections between loads, and confirm that no stale tail rows from the longer recipe remain after loading the shorter one.

Check: Production is ready only when a completed load identifies the selected recipe, reports the expected row count, preserves step order, and leaves no data from the prior selection.

FAQ