The European Union’s mandatory food labeling legislation, formalized in Regulation (EU) No. 1169/2011 not only specifies what information must be shown, but also how that specific information is presented, including font, color and contrast. The regulation deals in detail with font size, but leaves the exact type of allergen highlighting to the user. The ingredient allergens must be printed using a font that distinguishes them from the rest of the list (such as bold, italic, underlined, inverse, different color).
NiceLabel offers a built-in support for allergen formatting using custom functions. These are predefined functions that you can use to highlight the allergens in the list of ingredients. To highlight them, you can use bold, italic, underline, CAPS formatting or combination of this formatting. The function result is RTF-formatted data you can use with the Rich Text Box object.
The allergen word might appear inside a certain ingredient that is not categorized as an allergen. In this case you can use the "WithExceptions" type of functions. These functions will accept another input parameter – a list of exceptions. These are words or sentences that must not be formatted even if they contain the allergen word. For example, if you define the word "milk" as allergen, it will also be highlighted in the ingredient "coconut milk", which is incorrect. You can define "coconut milk" as an exception and Desktop Designer will not highlight the word "milk" when used in combination "coconut milk".
Note
For samples and additional how-to documentation see NiceLabel web page: EU Food Allergen Labeling Regulation.
The Food Allergens functions connect to the provided data source and read the included allergens. To open the data source, the appropriate database drivers (ODBC drivers) must be installed on the computer.
-
For Excel and Access data sources. If you have Microsoft Office installed on the same computer, you already have the database drivers. If not, install the drivers separately. Visit Microsoft Download Center to download and install Microsoft Access Database Engine 2010 Redistributable or Microsoft Access 2013 Runtime.
-
For MS SQL data sources. The ODBC driver has been released within the Microsoft SQL Server Feature Pack. You must have installed support for the provider SQLSQLNCLI11. Visit Microsoft Download Center and download the driver.
To format allergens in the list of ingredients, you have to execute the formatting function and provide the input parameters. The formatting functions are executed as VBScript function and will provide result in the output variable, which can be used directly in the Rich Text Box object. The function that you would use must match the data location, where you keep the list of allergens, such as CSV string, Microsoft Excel spreadsheet, or Microsoft Access database.
To format the allergens from a list in Excel, do the following:
-
Create a new label in Desktop Designer and open Dynamic Data Manager.
-
Click Functions and select VBScript and type in the function Name.
-
Open Script editor.
-
Change the line
Result = "0"
intoResult =
and position the cursor after the equal sign. -
Under Available scripting elements expand Food Alergens and select FormatAllergensFromExcel
-
In the Script editing field category, define the function parameters, for example:
Result = FormatAllergensFromExcel([Products.Ingred],"c:\Data\DB.xlsx","Allergens","Allergen","bold,italic,underline")
This will read the list of ingredients provided in the variable
Products.Ingred
, read the list of allergens from Microsoft Excel fileDB.xls
in the Product column in the sheetProducts
and apply bold formatting to all allergens within. -
Click OK.
-
In Output variable name, define the variable name, where the formatted allergens will be saved.
Note
Make sure the variable begins with the prefix RTF_, such as RTF_FormattedIngredients. This will instruct Rich Text Object to honor the control codes specified in the variable.
-
Click OK.
-
Select Rich Text Box object and add it ot the design surface.
-
In Rich Text Editor, select your variable in the list and add it into the content.
You can add additional variables or fixed text if necessary.
-
You can use the controls available in the Rich Text Editor to format the font type, size and colors.
-
Click OK.
This function accepts the list of ingredients and list of allergens in two variables and creates the RTF code with highlighted allergens. All words from the Allergens
that are matched in the Ingredients
will be formatted by the Highlight
specification.
Syntax:
FormatAllergens(Ingredients,Allergens,Highlight)
Parameter |
Description |
---|---|
Ingredients |
The CSV list of ingredients. |
Allergens |
The CSV list of allergens. |
Highlight |
The CSV list of formatting switches you want to apply to the allergen. You can use "bold", "italic", "underline", "caps", text color and/or background color. The colors are formatted in hex syntax for RGB scheme, for example "#FF0000" for red. For text color just provide the color code, for the backround, prefix the color code with "bg:", such as "bg:#FF0000". This is an optional parameter. If provided as empty value (""), bold format is used. |
Example 139. Example
FormatAllergens("wheat flour,salt,veg fat,hazelnuts","wheat,hazelnuts","bold,italic")
FormatAllergens(Ingredients,"wheat,hazelnuts","bold,italic")
This function accepts the list of ingredients and location of the Microsoft Excel spreadsheet. The function reads the allergens from the spreadsheet and creates the RTF code with highlighted allergens. All words from the spreadsheet that are matched in the Ingredients
are formatted by the Highlight
specification.
Syntax:
FormatAllergensFromExcel(Ingredients,ExcelFile,Spreadsheet,Column,Highlight)
Parameter |
Description |
---|---|
Ingredients |
The CSV list of ingredients. |
Excel file |
The full path and filename to the Microsoft Excel file containing the allergens. |
Spreadsheet |
The name of the spreadsheet containing the list of allergens. |
Field |
The name of the field (column name) containing the allergens. You can also provide the column index number containing the list of allergens. Column A must be provided as "1", column B as "2", etc. |
Highlight |
The CSV list of formatting switches you want to apply to the allergen. You can use "bold", "italic", "underline", "caps", text color and/or background color. The colors are formatted in hex syntax for RGB scheme, for example "#FF0000" for red. For text color just provide the color code, for the backround, prefix the color code with "bg:", such as "bg:#FF0000". This is an optional parameter. If provided as empty value (""), bold format is used. |
This function accepts the list of ingredients and location of the Microsoft Access database. The function reads the allergens from the table and creates the RTF code with highlighted allergens. All words from the database table that are matched in the Ingredients
are formatted by the Highlight
specification.
Syntax:
FormatAllergensFromAccess(ingredients,AccessDb,table,field,highlight)
Parameter |
Description |
---|---|
ingredients |
The CSV list of ingredients. |
AccessDb |
The full path and filename to the Microsoft Access database file containing the allergens. |
table |
The name of the table containing the list of allergens. |
field |
The name of the field (column name) containing the allergens. You can also provide the column index number containing the list of allergens. Field1 must be provided as "1", Field2 as "2", etc. |
highlight |
The CSV list of formatting switches you want to apply to the allergen. You can use "bold", "italic", "underline", "caps", text color and/or background color. The colors are formatted in hex syntax for RGB scheme, for example "#FF0000" for red. For text color just provide the color code, for the backround, prefix the color code with "bg:", such as "bg:#FF0000". This is an optional parameter. If provided as empty value (""), bold format is used. |
This function accepts the list of ingredients and location of the Microsoft SQL Server database. The function reads the allergens from the table and creates the RTF code with highlighted allergens. All words from the database table that are matched in the Ingredients
are formatted by the Highlight
specification.
Syntax:
FormatAllergensFromMSSQL(ingredients,sqlserver,dbusername,dbpassword, dbname,table,field,highlight
Parameter |
Description |
---|---|
ingredients |
The CSV list of ingredients. |
sqlserver |
The full path and filename to the Microsoft SQL Server database file containing the allergens. |
dbusername |
User name for accessing the database. |
dbpassword |
Password for accessing the database. |
dbname |
The name of the database which contains the list of allergens. |
table |
The name of the table containing the list of allergens. |
field |
The name of the field (column name) containing the allergens. |
highlight |
The CSV list of formatting switches you want to apply to the allergen. You can use "bold", "italic", "underline", "caps", text color and/or background color. The colors are formatted in hex syntax for RGB scheme, for example "#FF0000" for red. For text color just provide the color code, for the backround, prefix the color code with "bg:", such as "bg:#FF0000". This is an optional parameter. If provided as empty value (""), bold format is used. |
This function accepts the list of ingredients and location of the SQL Server database. The function reads the allergens from the table and creates the RTF code with highlighted allergens. All words from the database table that are matched in the Ingredients
are formatted by the Highlight
specification.
Syntax:
FormatAllergensFromSQL(ingredients,connectionstring,table,field, highlight)
Parameter |
Description |
---|---|
ingredients |
The CSV list of ingredients. |
connectionstring |
Connection string which is used to connect to the SQL Server database. |
table |
The name of the table containing the list of allergens. |
field |
The name of the field (column name) containing the allergens. |
highlight |
The CSV list of formatting switches you want to apply to the allergen. You can use "bold", "italic", "underline", "caps", text color and/or background color. The colors are formatted in hex syntax for RGB scheme, for example "#FF0000" for red. For text color just provide the color code, for the backround, prefix the color code with "bg:", such as "bg:#FF0000". This is an optional parameter. If provided as empty value (""), bold format is used. |
This function reads the formats enclosed in custom tags to highlight the allergens among ingredients. The function reads the allergens from the table and creates the RTF code with highlighted allergens. All words which include matching tags under Ingredients
are formatted by the Highlight
specification.
Syntax:
FormatAllergensFromTags(ingredients,tag,highlight)
Parameter |
Description |
---|---|
ingredients |
The CSV list of ingredients. |
tag |
Tag used to identify the ingredient as an allergen. |
highlight |
The CSV list of formatting switches you want to apply to the allergen. You can use "bold", "italic", "underline", "caps", text color and/or background color. The colors are formatted in hex syntax for RGB scheme, for example "#FF0000" for red. For text color just provide the color code, for the backround, prefix the color code with "bg:", such as "bg:#FF0000". This is an optional parameter. If provided as empty value (""), bold format is used. |
This function accepts the list of ingredients and the list of allergens in two variables and creates the RTF code with highlighted allergens. All words from the Allergens
that are matched in the Ingredients
are formatted by the Highlight
specification. The last parameter provides the CSV list of sentences that must not be highlighted even if they contain the allergen words.
Syntax:
FormatAllergensWithExclusions(Ingredients,Allergens,Highlight,Exclusions)
Parameter |
Description |
---|---|
Ingredients |
The CSV list of ingredients. |
Allergens |
The CSV list of allergens. |
Highlight |
The CSV list of formatting switches you want to apply to the allergen. You can use "bold", "italic", "underline", "caps", text color and/or background color. The colors are formatted in hex syntax for RGB scheme, for example "#FF0000" for red. For text color just provide the color code, for the backround, prefix the color code with "bg:", such as "bg:#FF0000". This is optional parameter. If provided as empty value (""), bold format is used. |
Exclusions |
The CSV list of words & sentences that will not be highlighted even if they contain allergen words. Milk is an allergen so the word "milk" must be highlighted, but not when used in context "coconut milk". In this case "coconut milk" must be defined as exception. |
Example 140. Example
FormatAllergensWithExclusions("wheat flour,salt,veg fat,hazelnuts, coconut
milk","wheat,hazelnuts,milk","bold,italic","coconut milk")
This function accepta the list of ingredients and location of the Microsoft Excel spreadsheet. The function reads the allergens from the spreadsheet and creates the RTF code with highlighted allergens. All words from the spreadsheet that are matched in the Ingredients
are formatted by the Highlight
specification. The last parameter provides the CSV list of sentences that must not be highlighted even if they contain the allergen words.
Syntax:
FormatAllergensFromExcelWithExclusions(Ingredients,ExcelFile,Spreadsheet,Field,Highlight,SpreadsheetEx,FieldEx)
Parameter |
Description |
---|---|
Ingredients |
The CSV list of ingredients. |
Excel file |
The full path and filename to the Microsoft Excel file containing the allergens. |
Spreadsheet |
The name of the spreadsheet containing the list of allergens. |
Field |
The name of the field (column name) containing the allergens. You can also provide the column index number containing the list of allergens. Column A must be provided as "1", column B as "2", etc. |
Highlight |
The CSV list of formatting switches you want to apply to the allergen. You can use "bold", "italic", "underline", "caps", text color and/or background color. The colors are formatted in hex syntax for RGB scheme, for example "#FF0000" for red. For text color just provide the color code, for the backround, prefix the color code with "bg:", such as "bg:#FF0000". This is optional parameter. If provided as empty value (""), bold format is used. |
SpreadsheetEx |
The name of the spreadsheet containing the list of word & sentences to be excluded from allergen formatting. |
FieldEx |
The name of the field (column name) containing the strings to be excluded from the formatting. You can also provide the column index number containing the list of allergens. Column A must be provided as "1", column B as "2", etc. |
Example 141. Example
FormatAllergensFromExcelWithExclusions("wheat flour,salt,veg fat,hazelnuts","c:\files\data.xlsx","Sheet1","1","bold,italic","Sheet2","2")
FormatAllergensFromExcelWithExclusions(Ingredients,"c:\files\data.xlsx","Sheet1","1","bold,italic","Sheet2","2")
This function accepts the list of ingredients and location of the Microsoft Access database. The function reads the allergens from the table and creates the RTF code with highlighted allergens. All words from the spreadsheet that are matched in the Ingredients
are formatted by the Highlight
specification. The last parameter provides the list of strings that must not be highlighted even if they contain the allergen words.
Syntax:
FormatAllergensFromAccessWithExclusions(ingredients,accessdb,table_with_allergens,field_with_allergens,highlight,table_with_exclusions,field_with_exclusions)
Parameter |
Description |
---|---|
Ingredients |
The CSV list of ingredients. |
accessdb |
The full path and filename to the Microsoft Access database file containing the allergens. |
table_with_allergens |
The name of the table containing the list of allergens. |
field_with_allergens |
The name of the field (column name) containing the allergens. |
table_with_exclusions |
The name of the table containing the list of word & sentences to be excluded from allergen formatting. |
field_with_exclusions |
The name of the field (column name) containing the strings to be excluded from the formatting. |
This function accepts the list of ingredients and location of the Microsoft SQL Server database. The function reads the allergens from the table and creates the RTF code with highlighted allergens. All words from the database table that are matched in the Ingredients
are formatted by the Highlight
specification. The last parameter provides the list of strings that must not be highlighted even if they contain the allergen words.
Syntax:
FormatAllergensFromMSSQLWithExclusions(ingredients,sqlserver,dbusername,dbpassword,dbname,table_with_allergens,field_with_allergens,highlight,table_with_exclusions,field_with_exclusions)
Parameter |
Description |
---|---|
ingredients |
The CSV list of ingredients. |
sqlserver |
The full path and filename to the Microsoft SQL Server database file containing the allergens. |
dbusername |
User name for accessing the database. |
dbpassword |
Password for accessing the database. |
dbname |
The name of the database which contains the list of allergens. |
table_with_allergens |
The name of the table containing the list of allergens. |
field_with_allergens |
The name of the field (column name) containing the allergens. |
highlight |
The CSV list of formatting switches you want to apply to the allergen. You can use "bold", "italic", "underline", "caps", text color and/or background color. The colors are formatted in hex syntax for RGB scheme, for example "#FF0000" for red. For text color just provide the color code, for the backround, prefix the color code with "bg:", such as "bg:#FF0000". This is an optional parameter. If provided as empty value (""), bold format is used. |
table_with_exclusions |
The name of the table containing the list of word & sentences to be excluded from allergen formatting. |
field_with_exclusions |
The name of the field (column name) containing the strings to be excluded from the formatting. |
This function accepts the list of ingredients and location of the SQL Server database. The function reads the allergens from the table and creates the RTF code with highlighted allergens. All words from the database table that are matched in the Ingredients
are formatted by the Highlight
specification. The last parameter provides the list of strings that must not be highlighted even if they contain the allergen words.
Syntax:
FormatAllergensFromSQLWithExclusions(ingredients,connectionstring,table_with_allergens,field_with_allergens,highlight,table_with_exclusions,field_with_exclusions)
Parameter |
Description |
---|---|
ingredients |
The CSV list of ingredients. |
connectionstring |
Connection string which is used to connect to the SQL Server database. |
table_with_allergens |
The name of the table containing the list of allergens. |
field_with_allergens |
The name of the field (column name) containing the allergens. |
highlight |
The CSV list of formatting switches you want to apply to the allergen. You can use "bold", "italic", "underline", "caps", text color and/or background color. The colors are formatted in hex syntax for RGB scheme, for example "#FF0000" for red. For text color just provide the color code, for the backround, prefix the color code with "bg:", such as "bg:#FF0000". This is an optional parameter. If provided as empty value (""), bold format is used. |
field_with_exclusions |
The name of the field (column name) containing the strings to be excluded from the formatting. |