Normalization Scripts for Import Templates

Summary

Some COAs contain special characters (e.g., < > ? % *) or text results where numerical values are required. When this happens, normalization scripts standardize the data into a format compatible with TraceStation, ensuring consistency and proper formatting for seamless import. This article will guide you through common normalization scripts, including their functions, usage, and best practices.

Where to Use Normalization Scripts

You will only use normalization scripts in the Import Templates section of Supplier Compliance. Here’s how to get there:

Step 1: Hover over the Configuration Icon on the top right and select Import Templates under the Data Extraction section.

Step 2: Access the Zone File Editor by either: 

• Creating a new OCR Template: see Create a Blank Zone OCR Template.

• Editing an existing OCR Template: select the icon next to the desired Supplier/Item, choose the desired template from the 'Current Templates' dropdown, then select 'Zone File Editor'). 

Step 3: Click on the blue Edit Gear next to the attribute that you need to work on.

Step 4: The Normalization Scripts window will appear. You will see two tabs:

• Site Library: This is your personal library of normalization scripts stored on your enterprise site. Manage this library by navigating to Configuration > Normalization Scripts. From there, you can create new scripts or import normalization scripts from the Configuration Library and modify them as needed.
• Configuration Library: This is a centralized library containing all available normalization scripts. We regularly update this library to accommodate new instances requiring different normalization scripts. 

Step 5: Scroll through all available normalization scripts, or use the Search and 'Group' dropdown to filter the library. 

Step 6: Once you find the script you need, click Insert to add it to the Normalization Script field. You can edit the script as necessary. 

Step 7: Validate the Normalization Script:

• Enter the value exactly as it appears on the COA in the top entry field, including spaces and commas.
• Click Validate. A message will display either Success or Error: Script Failed, along with the resulting value.
• If validation fails, modify the script and validate it again. If the validation continues to fail, please try a different normalization script.  IDK

• If validation succeeds and the results value is correct, click Apply to Attribute.

Which Normalization Scripts to Use

Replace Function:

(REPLACE('##INPUTVALUE##', 'Text 1', 'Text 2'))

The Replace Function will replace exact phrases or characters with a desired value. For example: If the COA has a result that states Pass, and you need the result to be 0, you can use the Replace Function to replace 'Pass' with '0'. Put the text you wish replaced in the script where it says 'Text 1'. Put whatever it is that you want to replace it with in the 'Text 2' space.

DBO Function:

(dbo.TG_Micro_ReturnNumericValue('##INPUTVALUE##'))

We call this script the swiss-army-knife, because it is one script that does multiple things. This can remove all letters and special characters, and even convert certain phrases into numerical values. For example, the dbo script would convert:

Example: The value “6.37%” would present as 6.37.

Example: The value “≤3” would present as 2.999.

Example: The value “100 cfu/g” would present as 100.

Example: The value “Neg”, “Negative”, or “Absent” would present as 0.

Various Date Formats (DATEPART)

Suppliers can utilize a variety of date formats. If the supplier uses the traditional date formats such as "January 20, 2019" or "MM/DD/YY", no norm script is required. However, for all the other types of date format, we have some magical norm scripts that can help:

• YYMMDD -> MM/DD/YY

Example: If the date appears as 1900120, use the script below to put it in the proper format:

(SUBSTRING('##INPUTVALUE##',3,2) + '/' + RIGHT('##INPUTVALUE##',2) + '/' + LEFT('##INPUTVALUE##',2))

• YYYYMMDD

Example: If the date appears as 20190120, use the script below to put it in the proper format:

(SUBSTRING('##INPUTVALUE##',5,2) + '/' + RIGHT('##INPUTVALUE##',2) + '/' + LEFT('##INPUTVALUE##',4))

• DD/MM/YYYY -> MM/DD/YY

Example: If the date appears as 20/01/2019, use the script below to put it in the proper format:

(SUBSTRING('##INPUTVALUE##',4,2) +'/' + LEFT('##INPUTVALUE##',2))+ '/' + RIGHT('##INPUTVALUE##',4)

• YYYY/MM/DD -> MM/DD/YY

Example: If the date appears as 2019/01/20, use the script below to put it in the proper format:

(SUBSTRING('##INPUTVALUE##',6,2) +'/' + RIGHT('##INPUTVALUE##',2))+ '/' + LEFT('##INPUTVALUE##',4)

• MMDDYY -> MM/DD/YY

Example: If the date appears as 012019, use the script below to put it in the proper format:

(Substring('##INPUTVALUE##',1,2) +'/'+ Substring('##INPUTVALUE##',3,2)+ '/' + Right('##INPUTVALUE##',2))

• DDMMYY -> MM/DD/YY

Example: If the date appears as 200119, use the script below to put it in the proper format:

(Substring('##INPUTVALUE##',3,2) +'/'+ Substring('##INPUTVALUE##',1,2)+ '/' + Right('##INPUTVALUE##',2))

• MM1DD/YYYY-> MM/DD/YY (fixes slash dividers)

Example: If the date appears as 01120/2019, use the script below to put it in the proper format:

(Left('##INPUTVALUE##', 2) + '/' + Substring('##INPUTVALUE##',3,2)+ '/'+ Right('##INPUTVALUE##',2))