Building Recipes

I have two tables:
One table with the raw materials that are used in a recipe. (code & description of the material)
The second one has the detailed mix of products.

The operator in charge of preparing the recepies has to fill in the rows with the materials and quantities of each.
How can the code of the raw material be placed on the detailed mix of products table so that the operator in charge of defining the recipe does not have to key in the values?

The behavior required is similar to a value tranlation list but it is based on a list of raw material defined in inventory at the ERP level. The ERP does not produce BOMs that could be read from it nor any decent grouping method.

Has anyone done this with Ignition?

I’ve done something vaguely similar. Basically the database has a matrix table with a listing of all possible recipes that was built using a script. Everything is abstracted away from Ignition using stored procedures in the database so we just have to call something like “EXEC spGetRecipe @Product={event.source.Dropdown.1.selectedValue}”. Let the database do the work for you and just present a result set to Ignition.

I was hoping to find something easier. Althoug putting the load on the SQL Server could really help preserve data integrity. Unfortunately it becomes less portable since not all SQL is the same.