This Instruction Article explains how to use formula in conjunction with GridField in CODESOFT with non-printable GS1 codes.
You should have some familiarity with database integration and GridField in CODESOFT and an understanding of GS1 standards.
The function of GridField is to take a large set of organized data from a particular data source (database or table lookup) and populate it on a label template. If the data for the GridField is pull directly from a database, the procedure is fairly straight forward and this article does not cover it.
Pulling Data from a Database
There will be times where you need to manipulate the data from the database before using it in GridField. An example is, if the data from the database contain unprintable characters, those characters will be substituted with a hex readable format ‘_xNNNN_’ where NNNN is the hex equivalence of the character. In order to properly use the data, the hex readable data needed to be converted back to the non-printable characters. (Note: the non-printable characters are very common in 2D barcodes).
One thing to keep in mind. When CODESOFT retrieves data from a database or table lookup, it comes in a form of one giant data stream. This is why you have to designate Field separator and Record separator strings in the database or table lookup properties. CODESOFT uses these two strings to parse the data stream into multiple fields and records. We tend to think that CODESOFT reads the data one record at a time. That is incorrect. The interface that CODESOFT uses for Table lookup pulls all the data into one long string and then parse it into multiple records.
You want to read data from an Excel sheet to be used in a DataMatrix (2D) barcode. The data in Excel contains Record Separator [RS], Group Separator [GS] and End of Transmission [EOT] values used by the GS1 standards.
- [RS] = ASCII 30 HEX = 1E
- [GS] = ASCII 29 HEX = 1D
- [EOT] = ASCII 4 HEX = 04
In Excel, you may have the field defined as
=”[)>”&CHAR(30)& “06” & CHAR(29)& “Q”& CHAR(29)& “4L”&CHAR(4)
- CHAR(30) represents the [RS] record separator value
- CHAR(29) represents the [GS] group separator value
- CHAR(4) represents the [EOT] end of transmission value
In the cell, it shows [(>06Q4L. Since Char(30), Char(29) and Char(4) are not printable, you do not see them. However, when CODESOFT pulls this data into the label, the data comes in as
The non-printable values are unfortunately converted to Hex digit representations. If this data is fed directly into a DataMatrix, the barcode will show all the hex representations…not the [RS], [GS], and [EOT] values.
To fix this, we need to convert the Hex digit representations back to a format what will allow CODESOFT to insert them correctly as not printable ASCII. We can use the ReplaceString function to substitute _x001E_ with char(30), _x001D_ with char(29) and _x0004_ with char(04).
Method #1 – Nested Function
FixString – ReplaceString(ReplaceString(ReplaceString (INPUT1,”_x001E_”,char(30)),”_x001D_”,char(29)),_”x0004_”,char(4))
Note easy to read, is it? In the GridField, you will use FixString as the data
Method #2 – Multiple Function
Replace30 – ReplaceString(INPUT1, “_x001E”,char(30))
Replace29 – ReplaceString(Replace30, “_x001D”,char(29))
Replace4 – ReplaceString(Replace29, “_x001D”,char(4))
Much easier to read and follow
In the GridField, you will use Replace4 as data