Understand MySQL Link To Vision Dropdown - Recipe Database

Ignition
, ,
1

I'm struggling to understand how to properly build out a recipe database tying Ignition and MySQL. I have run into so many roadblocks that I figured it might be worthwhile to ask for some help.

My basic design is as follows:

image
image1280×661 21 KB

I have a recipe dropdown menu on the HMI that allows the user to select the "Active Process". This selector is referencing a MySQL table called "Master Processes". Each row of this database contains a specific process name ( Customer A, B, C).

Customer A's recipe is a series of 0-150 rows of data, 7 parameter columns.

image
image382×459 3.64 KB

All customer recipes start as a child duplicate of a MySQL table called Master Reference. This is an empty table that contains the 0-150 rows and named columns. Upon creating a new recipe, this duplication of the master is performed, and the table is renamed to the Customer's name.

This leaves me with a chart like:

image
image1164×724 25.5 KB

Push "NEW" and there is a NQ which inserts a row into the Master Processes Table. There is a default name added into this row "New Process". The Ignition dropdown now lists that New Process for the operator to select.
After this, the Master Reference MySQL table is duplicated and named "new_table"

Where my logic is failing me:

  1. I ultimately want the user to be able to select a process out of the dropdown menu, and load it into a Vision table. How can I pair the newly created MySQL ("new_table") to the specific dropdown Customer name? MySQL tables are automatically formatted to lower case and no spaces. Ignition does not have these rules, so I can't 1:1 name these two variables the same. This is why I said a new table needs to be duplicated and renamed, but is that necessary? How can I link them otherwise?
  2. Is there another way to do this? The remaining functions of the recipe database need to interact with this road map. "NEW", "LOAD", "DELETE", "SAVE". So if I'm building this recipe system on a bad design, these functions will only be harder to design.

Any input is GREATLY appreciated.

2

You could simplify this greatly by storing all data in one table. You'd have one table row for each line of each recipe. The columns could be,

    id             auto-incremenent, unique row identifier
    customer       string
    recipe_number  integer
    recipe_name    string
    recipe_line    integer
    ingredient1    string  (name)
    ingredient1q   float   (weight)
    ...

You would then retrieve a recipe with Named Queries | Ignition User Manual,

SELECT
    ingredient1,
    ingredient1q,
    etc.
FROM recipetable
WHERE
    customer = :customer
    AND
    recipe_name = :recipe_name    <-- or use recipe_number here
ORDER BY
    recipe_line

You would populate the dropdown with another named query binding on the options property,

SELECT 
    UNIQUE recipe_number AS value,
    recipe_name AS label
1 Like
3

Thanks @Transistor

Is there any limitation to doing it this way?

150 rows of data per recipe... 100 different recipes.

That's 15,000 rows of data inside one table, would that fare well?

4

We have tables with 4.5 million rows with minimal issue fetching large chunks at once as part of join statements. Just make sure you have indexes on the proper columns(at the least, id, recipe_number, and customer)

Building off Transistor's idea, personally I would separate out customer, and recipe_name into a separate table and have the id column of that separate table drive the value of the recipe_number column in the main table. Smaller table for searching recipes or customers and a decently fast left join when fetching the entire recipe (or no join if you don't need the name/customer name)

1 Like
5

Decently spec-ed SQL databases can easily handle millions of rows.

Truly powerful systems get into the tens to hundreds of millions.

1 Like
6

Very interesting idea.

The final solution would essentially be a populated Vision table with rows 1-150 illustrated, and only the dropdown menu showing the current customer's name.

7

That's OK if each customer has only one recipe. You either need the dropdown to display the customer and the recipe or you need two dropdowns; one to display a list of customers and the other their recipes. The second dropdown would be populated by a named query which would filter all the UNIQUE recipes for the customer selected in the first dropdown.

1 Like