How to search for or filter items within a recipe database

[AaronS]

Introduction:

Within this post, we demonstrate how to use SQL within a Macro to search or “filter” records within a recipe database. To save time, the author of this post has assumed that a recipe database already exists within your project.

Software Version:

EasyBuilder Pro 6.03.02.393+

Demo:

v1.0
*requires 6.09.01.322+

Related Recipes:

How to make a recipe drop-down list
How to export or import a recipe as a .csv from a USB
How to Install and Configure MySQL Server and Workbench on a Local PC
Video - Recipe Database Basics in EasyBuilder Pro
Video - SQL Sync, how to set up a Local SQL Database
Video - Sync Data to a central database with a SQL Query object
Video - SQL Query Object: Advanced Mode

Instructions:

Simple

This method is suitable for those that wish to provide machine operators with an efficient method for searching through recipe records. It is not suitable for situations in which it is necessary to search a specific column, validate entry, or as a means to retrieve, process, and distribute returned results.

1. Within this example, I have designed and will reference components of a recipe called “Mixer” defined below:
   

   

2. The easiest way to “search” for a key-string within a recipe is to select the “Filter enabled” checkbox when creating a “Recipe View” object:
   

   

3. While “Filer enabled” is selected, a “Filter” keyword can be entered just above the view object:
   

   

4. This keyword will “Filter” items in the recipe and display only those with matching alphanumeric strings:
   Note: This feature will find matching “keywords” within every column.
   

   

Advanced

This method is suitable for those that wish to have the ability to parse, modify, validate, or transfer values within returned records.

1. Within this example, I have designed and will reference components of a recipe called “Mixer” defined below:
   

   

2. To search within our “Mixer” recipe, we’ll define a “search” macro. To create a macro, select the “Macro” icon within the project tab:
   

   

3. Within the following menu click “New”:
   

   

4. Within our macro workspace, just under “macro_command main()”, we’ll define three char arrays:
   Note: That “SELECT * FROM” indicates that we will select all items returned by this query. The reicpe name “Mixer” is specified, followed by “WHERE Code” which denotes the “Code” column of our recipe. “LIKE” is an operator used to denote a specific pattern.
   

   

5. To search for a specific “Code” we can initialize a char variable to the desired code as shown below:
   Note: the ‘[6]’ specifies a char array that is 6 characters in length.
   

   

6. However, it may be more useful to obtain a variable from a device register. To do this, select the “GET/SET FN…” button on the bottom right:
   

   

7. Within the following window, select the “GetData” function from the “Function name” drop-down list:
   

   

8. Configure the “Variable” name and the “Array index” which specifies where data will be inserted into this array. This option may be left at ‘0’. Then, define the “Read” address and “Data count”, which should be equivalent to the length of your array:
   

   

9. Click “Ok” to add this statment to your macro workspace:
   

   

10. To “build” your query, we will need to append the desired search “string” to the “sql” statement. This can be accomplished using the “StringCat” function as in this example:
    Note: The array index ‘[0]’ must follow the variable name. The StringCat sequence below will form a query similar to “SELECT * FROM Mixer WHERE Code LIKE ‘%CODE%’” where CODE is some value retrieved from LW-0 on line 8.
    

    

11. Within step 10, we form our query but in order to send this query we must use the “RecipeQuery” command, which requires the char array that consists of our query as a parameter, as well as a short integer that denotes the number of records found:
    Note: Some possible data types within our macro syntax are char, short, int, float, and long. These data types are 8, 16, 32, 32, and 64 bits in length respectively.
    

    

12. When finished, we can check the result of n_records to determine if any records match our search string using an if statement:
    

    

13. To parse each returned record, we will use a “For” statement, the “RecipeGetData” function, and the “RecipeQueryGetRecordID” function as in this example:
    Note: The “recordID” is the index of the returned record. We retrieve this index using the “RecipeQueryGetRecordID” function and then use this index within the “RecipeGetData” function to determine “Get” data from the specified recipe column. As an example, on like 27 we “Get” data from the “Red” column of our “Mixer” recipe and assign it to the first element within the “rgb” array. I’ve added the “SetData” statements to demonstrate how recipe records may be transferred to a device register.
    

    

Notes:

• To declare a “RecipeQueryGetRecordID” or “RecipeGetData” command, please use the “GET / SET FN…” button as mentioned in step 6 of the “Advanced” section:
  

  

• To search for an integer equivalent to a value contained within a specific column, use the equals operator in front of the column name as in line 3. Then, the integer value in “ASCII” format may be concatenated to the “sql” query statement using the “StringCat” function:
  

  

Keywords

sql, macro, filter, recipe, database, record, records, operator, query, recipequery, recipegetdata, recipequerygetrecordid