Summary
Some COAs will have results that contain special characters such as < > ? % *. Likewise, sometimes our suppliers will give us text results when we need numerical ones. When any of these show up, they require the use of "normalization scripts" so that TraceGains can interpret the data. Below you will find several of our common norm scripts with examples of what they do, how to use them, and when to put them to use.
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: Build either an OCR or Digital template in the ZON File Editor.
Step 3: Click on the blue Edit Icon 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: In the Normalization Scripts screen, select the Configuration Library tab to find the script you need. You can use the search bar, filter by group, or scroll through all available normalization scripts.
You may not see all scripts due to data type conflicts. The system filters scripts to match the data type of the selected attribute to avoid errors. For example, if you are looking for a script for the Manufacturing Date attribute (data type: date), only compatible scripts will be displayed.
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.
- If validation succeeds and the results value is correct, click Apply to Attribute.
We recommend saving the normalization script to your site library for quick and easy access, and to preserve any changes you make. After validating the script, click Save Normalization Script at the bottom of the screen. To access saved scripts, go to Configuration > Normalization Scripts.
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))