Formatting Allergens for Food Ingredients

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.

Prerequisites

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.

Applying formatting to allergens

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:

  1. Create a new label in Desktop Designer and open Dynamic Data Manager.

  2. Click Functions and select VBScript and type in the function Name.

  3. Open Script editor.

  4. Change the line Result = "0" into Result = and position the cursor after the equal sign.

  5. Under Available scripting elements expand Food Alergens and select FormatAllergensFromExcel

  6. 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 file DB.xls in the Product column in the sheet Products and apply bold formatting to all allergens within.

  7. Click OK.

  8. 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.

  9. Click OK.

  10. Select Rich Text Box object and add it ot the design surface.

  11. 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.

  12. You can use the controls available in the Rich Text Editor to format the font type, size and colors.

  13. Click OK.

Syntax of Allergen formatting functions

User provided list of allergens

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")


Allergens from Microsoft Excel spreadsheet

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.

Allergens from Microsoft Access database

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.

Allergens from Microsoft SQL Database

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.

Allergens from SQL Server

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.

Allergens from Tags

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.

Syntax of Allergen formatting functions with support for exclusions

User provided list of allergens

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")


Allergens from Microsoft Excel spreadsheet

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")


Allergens from Microsoft Access Database

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.

Allergens from Microsoft SQL Database

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.

Allergens from SQL Server

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.