Recipe Group

Recipe Group

Watch the video

.

You can use Transaction Groups to create a recipe management system which will pull recipe information from the database and push it to the PLC when requested. With this system, the Transaction Group is what queries the database rather than writing scripts to handle it all.

Before the Transaction Group

Before we make the Transaction Group, we first need to make sure we have a table set up in our database that holds recipes. If you already have this, then you can skip to the next step on making the Transaction Group.

We will make a table in our database that will hold our recipes. Our recipes will be simple, containing a name, unique id, and two setpoints, so we will need a column for each of those values.

1. Verify the Designer's Comm Mode is set to Read/Write, and open up the Database Query Browser.

   

2. Execute the query below in the Database Query Browser to create the table we'll use in this example:

   CREATE TABLE recipes(
       id INT PRIMARY KEY,
       recipe_name VARCHAR(50),
       setpoint1 FLOAT,
       setpoint2 FLOAT)
   note

   This query was designed for an MSSQL database. If you are connected to a different database, the syntax on the CREATE statement may differ. Check your database's documentation for more details.

3. Next, we need to put some data into the table by using an insert statement. Execute the below query to insert a new record into our recipes table:

   INSERT INTO recipes (id, recipe_name, setpoint1, setpoint2)
       VALUES (1, 'Recipe 1', 10, 0)

   You can rerun this query as many times as you want, incrementing the id to give you a new unique id, changing the name, and providing different setpoints. Your table might look something like the one below.

   id	recipe_name	setpoint1	setpoint2
   1	The First Recipe	34.7	54.1
   2	The Wrong Recipe	12.8	42.3
   3	The Best Recipe	65.7	95.1
   4	The Other Recipe	49.8	112.2

Create the Transaction Group

Now that we have a recipe table in the database that is populated with some records, we can create the Transaction Group that will load a recipe from the table into our Tags. We will be using the recipes table that we put together previously, but if you already had a table, you can use that here instead.

1. Create a new Standard Transaction Group.

2. We have four columns in our database table, so we will need four Tags to use in the Transaction Group: an integer, string, and two floats for the id, name, and setpoints respectively. Add the four Tags to the Transaction Group.

   

3. Set the Table Name to 'recipes', the table that we created earlier.

4. We then need to ensure that our tags will be receiving the proper values from the database.

   1. Set the Target Names for each of the Tags: the string to 'recipe_name', the floats to 'setpoint1' and 'setpoint2'.

   2. Set integer to 'Read Only'. We don't need to set the integer to the id column, because we will not pull the the id from the database, but rather use the id as a trigger and in the where clause.

      

5. Now we can finish setting up the rest of the Transaction Group. Set the Update mode to DB to OPC.

6. Set the Table Action to Update/Select using Key/Value Pairs with the Column set to id, and the Value set to the Integer Tag you are using.

   

7. Set the Update Rate to 1 second. We want to query the values out of the database as soon as we ask for them, so we need the group to update quickly. However, we don't want the group to actually query the database every second, so we will need to set up the trigger.

8. Go to the Trigger tab, and select Execute this group on a trigger. Trigger on the item the int tag that is being used for the id. Specify the Trigger condition as Active on value change.

   

9. Finally, Enable the Transaction Group and save the project to get it started. The Transaction Group will now pull the recipe out of the database where the id matches the value of the int tag. The trigger also prevents it from running all the time, instead running only when the int tag value changes.

10. To test it out, simply change the value of id tag to an id of one of the recipes in the recipes table.