8 Replies Latest reply on Aug 8, 2017 4:11 PM by mcintyre2

    Trouble Extracting a Date for the numbers from a QR code

    mcintyre2

      I’m having a problem with the Middle Function.  I might not have used the right

      function to extract numbers from the middle of a string of integers.

       

      I scanned a QR code from a breast implant box.

       

      Numbers Scanned From a QR Code are

       

            0110888628007284172108242121592096

       

       

      The breakdown is as follows

       

      0110888628007284 17      210824   21          21592096

                18 integers      ?         date(08/24/2021)          ?          Serial Number

       

      The first 18 integers are numbers that come before the date. The date is

      08/24/2021.  It is the expiration date for a breast implant still in a box.

       

      The last 8 digits are the serial number on the box.

      The 17 and the 21 seem to be numbers common to all the QR codes on the boxes.

       

      (I’m calling them

      QR codes because they look like one. )

       

       

      I tried extracting the date from the QR code by using

      the Middle function and structure it as a date Month/day/Year  i.e. 08/24/2021. My original calculation was

       

      Expiration_Date = Middle(QRScan;21,2) & “/“ & Middle(QRScan;23,2) & “/“ & “20” & Middle(QRScan; 19,2)

       

      However this resulted in “21/08/2017”

       

      When I looked at the code number, it appears that, using the middle function, off sets the

      results by 2 characters to the left.  i.e. 17 is called.

       

       

       

      The correct calculation is

       

      Middle(QRScan;23;2) & "/" & Middle(QRScan;25;2) & "/" & "20" & Middle(QRScan; 21;2  )

       

      It returns 08/24/2021

       

      Did I use the wrong function? Why would it offset the results using 2 characters to the left?

      Should I have used the Right Function?

       

      Thank, Dr. B

        • 1. Re: Trouble Extracting a Date for the numbers from a QR code
          philmodjunk

          Might your results be caused by leading space or other non printing characters?

           

          you might also want to use those values with the date function to get a value of type date instead of text.

          • 2. Re: Trouble Extracting a Date for the numbers from a QR code
            mcintyre2

            Interesting you mentioned leading spaces and nonprinting characters.  When i import the data into a spreadsheet before importing into filemaker pro.   (The iPhone software "Scan to SS" captures the data then it is imported into a spreadsheet before importing to filemaker pro.)

             

            The number imported into the QR scan column in the spread sheet is 0110888628007284172108242121592096.  The number that appears in the cell viewer is

            0110888628007284 1721082421 21592096.  (When I copied and paste the number in this paragraph it transferred as 0110888628007284172108242121592096.  I put those spaces in where the spaces showed up in the viewer.)Screen Shot 2017-08-03 at 10.08.55 PM.png

             

            However if these were leading spaces then neither formulas would work, so there doesn't appear the spaces in the viewer actually transfer.  I tried to account for the spaces in the working the calculation but they weren't the problem. 

            • 3. Re: Trouble Extracting a Date for the numbers from a QR code
              philmodjunk

              I suspect that those "spaces" are other nonprinting characters than actual spaces. And it looks to me like such characters could be affecting your results. You might run your text through the Filter Function and specify only the visible characters that you see in your data to clean out such suspect characters and then see if you get the expected results.

              • 4. Re: Trouble Extracting a Date for the numbers from a QR code
                mcintyre2

                philmodjunk, Thank you for your advice. 

                 

                I worked on the problem yesterday evening and I have discovered the problem.

                 

                First the code is a Data Matrix Code.

                Second when it is scanned and then placed in a spread sheet there are 3 leading spaces that are introduced that can only be seen

                in the cell viewer and not the qr_code Column. 

                The first leading space is before the first 0.

                 

                When I removed the first space the segment of the code before the second leading space was imported into the Filemaker Pro Database.

                 

                 

                Screen Shot 2017-08-03 at 10.08.55 PM.png

                 

                When I removed the second and third leading space the cell was converted to  leading space and the third

                The cell number was converted to 1.10889E+32. I could not recover the original number until of used the “undo”

                feature of the Edit Tab.

                 

                Screen Shot 2017-08-03 at 10.52.38 PM.png

                So finally I tried converting the qr_code column into text then removed the leading spaces.

                The qr_codes were then imported into the FMP. 

                 

                Its a lot of work to go thru each cell and remove the spaces.  But I got it done. The problem is that  I have to do it for more than this number of records to be imported.

                 

                Any why to filter the spreads sheet column with a macro, that first converts the column to text the filters out the nonprinting spaces or characters?

                • 5. Re: Trouble Extracting a Date for the numbers from a QR code
                  philmodjunk

                  Why not clean the text after import into FileMaker?

                   

                  I recommended the filter function earlier:

                   

                  Filter ( YourFieldHere ; 1234567890 )

                  as an example, would remove all characters except the digits 0 thru 9

                   

                  Filter ( YourFieldHere ; "ABC" )

                   

                  would remove all characters except the capitalized A, B and C characters.

                   

                  It can be tedious to build a long enough filter string to accept all acceptable characters, but you don't have to identify those mystery characters like you would if you used the substitute function to remove them.

                   

                  I'm not an expert in Excel or Numbers so you'd need to check the help or with someone who knows more about them if you wanted to filter this in the spreadsheet.

                  • 6. Re: Trouble Extracting a Date for the numbers from a QR code
                    jbante

                    This looks like GS1 data. GS1 is a standardized schema and format that gets used a lot for encoding information in a barcode or RFID tag about products being moved between trading partners. The Wikipedia page on GS1-128 has some good introductory information on it, including a list of the fields ("application identifiers").

                     

                    In the value from your example:

                     

                    0110888628007284172108242121592096

                     

                    The fields/AIs are:

                    01 - Global Trade Item Number: 10888628007284

                    17 - Expiration Date: 2021-08-24

                    21 - Serial Number: 21592096

                     

                    If you're going to stick with parsing out separate fields with the Middle function presuming the same information will be in the same place every time, you may want to monitor the validity of that assumption on an ongoing basis. Folks can and do change the ordering of GS1 data without feeling the need to tell anyone. For example, the exact same information could just as easily be encoded as: 1721082401108886280072842121592096

                     

                    The non-numeric characters you found are probably group separator characters (Char ( 29 )). Many barcode scanners use that character to indicate a "function code" in a scanned barcode. GS1 uses function code 1 to mark that one field/AI is ending and the next is starting. That's optional for fixed-length fields in GS1, but required for the ends of variable-length fields. If your scanned data started with the serial number (21), we wouldn't be able to say where the other data starts without the group separator character in the scanned result.

                     

                    Barcode Creator includes some scripts to help with parsing out GS1-formatted data if you'd rather not.

                    2 of 2 people found this helpful
                    • 7. Re: Trouble Extracting a Date for the numbers from a QR code
                      mcintyre2

                      Philmodjunk, The filter idea is good.  However, the problem with filtering data after importing into FMP is that I can't import any data into FMP from Excel until the non-visible characters are removed within Excel.     If I remove the first non-visible character only the data between the first and second non-visible character would be imported.  If I remove the first and second only the data following each of the non-printing characters will be imported. I have to remove all 3 of the non-visible character to import the entire string.  So the filter would only be applicable to Excel and not FMP with my current approach. The barcode function in FMGo16 will not read GS1-Datamatrix, it seems to read others.  The Scan to SS does read GS1-Datamatrix and will import into Excel but not into FMP until the non-vis characters are removed.  I need a GS1-Reader that removes the non-vis characters and make the read into text string.  

                      • 8. Re: Trouble Extracting a Date for the numbers from a QR code
                        mcintyre2

                        Well I figured it out.  I don't know why it works but it does. 

                        The GS1 Datamatrix was the type of barcode to be scanned.  It isn't supported by FMGo 15 or 16. (can't read).  I used Scan to SS (Spreadsheet) by Andy Berry at Berrywing.com  (Mr. Berry was very supportive in trying to figure this problem out.  I like the app and will continue to use it.  GS1-Datamatix are require on medical devices.) 

                         

                        First I used Scan to SS to log the BIN, Serial number and the DataMatrix Number.  The only one that would not import was the DataMatrix Number.  It would just be blank.  The original number, from Scan to SS, was exported to Excel then from Excel imported into FMP 16.  The Datamatrix did not transfer at all.  In reviewing the Cell Viewer for a Excel cell in the DataMatrix column (At first I thought the Datamatix was just a QRScan so that is why the import column is QRScan)  It appeared that non-viz character were in the viewer but not in the cells in the column.  Jbante pointed that out in his reply.  If i manually delete the three non-vis character and convert the number to a text it would import.  If I deleted only the first then only the first 16 numbers would be imported since after the first 16 another non-vis character occurred.  I went through several scenarios but this one works.

                         

                        First scan the BIN, Serial Number  and the Datamatrix into the Scan to SS app.

                        Next export the data into the Numbers Spreadsheet App on the Phone.

                        Next export from the Numbers Spreadsheet to make an Excel Spreadsheet. Email the Spreadsheet to my email. 

                        Once I have the Excel Spreadsheet then I imported all Characters including non-viz in to the DataMatrix Field (QRScan).  For here I can parse the information out of the Datamatrix Field into other fields. 

                         

                        I can't import the data strictly from Numbers because the Source fields do not show up in the import dialog box.Screen Shot 2017-08-08 at 4.33.36 PM.png

                        I can't convert the Numbers to a CSV and import because the Datamatrix will not import,

                         

                        Screen Shot 2017-08-08 at 4.35.07 PM.png

                         

                        I can only get the import from a Numbers to Excel conversion.

                         

                        Screen Shot 2017-08-08 at 4.36.13 PM.png

                         

                        Why Numbers could not be imported I don't know

                        Why a Numbers to CSV could not be imported I don't know

                        Why a Numbers to Excel conversion CAN BE IMPORTED, I don't know

                        I have what I need now and can filter (like philmodjunk suggested) and utilize the Datamatrix info.

                         

                        My thanks to jbante and philmodjunk for your help.

                         

                        Dr. B