Making a query in a database table off of scanned data
Using BarTender Designer 2016 R8
I have a .btw file set up where I scan a barcode to read a string, where I use VBscript to parse a specific portion of the string to isolate a part number. I'd like to be able to use that part number parsed from my scanned information stored in a Text Input Box, to make a query in my connected Excel Database for a corresponding field of information.
Example Table:
Upon Printing, my Form prompts the user to scan a barcode into a text input box. The barcode contains a string of information, including the Part Number "B". I'd like to be able to query my connected Excel database to have the corresponding Working Life of Part Number "B" to display on a Text object in a Template.
I know how to access my scanned information in VB script and mess around with it to get the reference information I want. The part I'm struggling with is having full access to the database fields through VB script.
I haven't been able to figure out how to do this, as the database fields in VBscript seem to default to just the first row of information for the corresponding field (so when calling Part Number field, it defaults to "A", or when calling Working Life field, it defaults to "10"). When trying to use the filters or query prompts from the database connection setup, it seems like I'd only be able to print out a select database field by manually picking which record to use rather than going off of the information scanned by the user.
-
It sounds like you are doing it in reverse order.
In your database connection setup enable a filter/query prompt linked to the Part Number field and include your VB or truncation in the filter to strip out the buts of the scan you dont need.
Once done you can then link your label field to the Work Life column and at print time the part number will be selected which in turn populates the correct work life value
0 -
Hi Peter,
Thanks for the quick response.
I think the main issue is that I have several different things that I need to do with a single string. Let me explain my situation in a bit more detail.
The user is expected to scan a PDF417 barcode into a text input box during print time that contains a string with information similar to this, where delimiters are available either as circles or question marks:
{Prefix}○{Part Number}○{Receipt Number}○{Expiration Date}○{Batch Number}○{Suffix}
From this string, I need to have a bartender file that will do create two templates (labels) for the user:
- Create a label that duplicates the PDF417 barcode with the same string, and human-readable text displaying the part number, receipt number, and expiration date. I set my text input box to be linked to the barcode object so it auto-populates the barcode with the same string, and then referenced the barcode's value using Format.NamedSubStrings("").Value for the part number, receipt number, and expiration date.
- Create a label that displays the part number, current time, current date, and a corresponding working life to the part number. Similarly, I extracted the part number from the barcode object, used "Clock" type objects for the time and date, and then performed logical operations for the working life.
I've been able to get this working no problem by using a Split() function to parse my scanned string and create an array where I can call whichever portion of the string is needed. For the working life, I used a simple if-statement where if a specific part number was found in my string, I would then display a specific working life. The next step is to specifically improve that working life portion - where if this file was to support more part numbers, it wouldn't be efficient to have a large if-statement for every single part number possibility. This is where the database idea came as an opportunity to have an existing table that would be able to match a working life to the corresponding part number.
I've been able to get everything working so far, including setting up the filter/query prompt for the working life, but the last thing I'd like to figure out is how to set up the file to only require a single input. In order to populate all my information I have to perform a first scan for my query prompt, confirming the record on the Select Record window, and then a second scan for my text input box to populate the barcode, receipt number, and expiration date. I tried linking my text input box to my query prompt so that only one scan was necessary, but I lost my ability to link my scanned information into my barcode, receipt number, and expiration date as the query prompt input doesn't get stored as an object. Is there a way to streamline that final entry process down to a single scan and skipping the Select Records window?
0 -
So, you want to use the parsed/split Part Number value that was scanned in and printed on label 1 to perform a look into a database to populate some fields on label 2? If so:
On label 2 link the fields to the spreadsheet and include a filter/query that is looking for a value in Part Number column of the spreadsheet. Give this query a name (I used "ChooseMe". Save and close the label.
On label 1, go into the field that just contains your Part number that has been split from the the rest of the scanned code and use the Change Data Source Name button to make this a named field (I called mine "LookItUp").
Go to the Data Entry Form you are using on Label 1 and go to the Properties of the Form and click on the Actions button. Add a Print Document Action and on the Document tab browse to your label 2 file and select it. Then go to the Query Prompts tab and tick the Specify option and type in the name of your Filter added above. Now click into the Value box and the Insert Variable button should then become active. Click on this and select More Variables and then on the Events screen double click on your Named Data Source you configured above.
Close and save both labels and when you print label one the part number should then be used by label 2 to look up into the database.
0 -
Hi Peter,
I tried following your advice but I don't have an Actions section in my Form Properties window. Is this a version or settings issue?
0 -
Forgot the initial post where you said you were using 2016. I am not sure when the Actions were added, but I guess it must either be after that version or else the edition you are using doesn't include these.
0
Please sign in to leave a comment.
Comments
5 comments