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.
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.)
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.
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.
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.
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.
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?
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.
2 of 2 people found this helpful
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:
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.
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.
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 convert the Numbers to a CSV and import because the Datamatrix will not import,
I can only get the import from a Numbers to Excel conversion.
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.