Changing data types dynamically depending on database cell
I work at a cannabis facility where we need to include third party test results on our labels that will be sent to our retail locations to be sold. We have a spreadsheet including all of our test results, and for my BarTender template I have my data types set to 'number' so that I can shorten the decimal places. However, in certain situations some batches do not test above a certain threshold to contain an actual number, and instead will be listed as 'ND' or '<LOQ' on our sheet. Obviously this is not a number, so when loading data onto my template it shows up blank. Due to regulations in my state, there needs to be data listed in that spot. Is there any way to have it so that if there is text in a cell it changes the data type to 'text' automatically? Thanks!
-
This sounds like an old issue that you would sometimes see where the ODBC driver (that links the spreadsheet to the label) would ignore the formatting in the spreadsheet and just look at the first few rows of data. If it saw all numeric data then it would decide the whole column is numeric and if there were any none numeric fields later on then the would be sent through as blank.
The simplest way to get round this is add an extra record at the top of the spreadsheet and set the contents of all the fields as "dummy" or "sample" etc and then ODBC will then see the data is alphanumeric and will pass both through number only, letter only and mixed data fields to the label to print.
0 -
I don't think this would be the case, as the columns and rows have alphanumeric characters in them already (i.e THC, CBD, test results, etc.). I'm just not sure how I would get around the fact that sometimes a row will include numbers, while for other batches it will contain text. Perhaps there's a way to use VBScript to identify if a field has only text (plus the < sign), but if it includes numbers only to shorten the decimal to 2 places? I can include some screenshots of my data and template if that could potentially help at all.
Basically how this is supposed to work is that those fields on the template that read 1,234.00 will be automatically filled according to what record is chosen from the database.
0 -
Sorry I may have misunderstood your initial message.
So for the fields on the label you have set the Type to Database field and then on the Data Type tab to numeric? This field however can also contain numeric data in which case you do not want the formatting to be applied.
The simplest way to achieve this is to add a second substring to the field and have this as a general Text type object but include, from the Transforms tab, Suppression so that the string does not print when the numeric string has a value greater than zero.
This would give you something like this where the top line is the raw value from the the Database
This is made up like this with the ringed button being how you add a second substring.
My first string is the none numeric option and the Transforms>Suppression for this is configured:
1 -
edit: I figured it out! I added a character filter to the new text data source I made, since the cells of my sheet will only ever include ND, <LOQ, or a number. That looks like it fixed my issue. Thanks for the help!
That seems like it worked! I appreciate it so much. But of course now it seems that another issue has popped up. It looks like the Data Builder is formatting some numbers from my spreadsheet into scientific notation, and since that contains a letter, your method won't work for those specific situations. This isn't an issue with every row of my sheet, but I will need to figure out a workaround for that.
0 -
Another update: it was working for a while, but out of nowhere I keep receiving an error #2606. Not sure what's causing it as my transformation/suppressions seem like they should be working fine. The template is also correctly showing what I want it to show, but when I go to print it spams me with errors. Even when I try loading the specific record that is giving me the error it shows it correctly on the template.
This image is showing the highlighted field the error references, but clearly is displayed correctly in the template. Very confusing.
0
Vous devez vous connecter pour laisser un commentaire.
Commentaires
5 commentaires