Reading A Trigger File, Using Data For Excel Record Lookup (Bt 2016)
I'm brand new to BarTender, using 2016.
I have an Excel file. Each record has approx 10 fields. The file can have many records.
My system will get a single alphanumeric string, then have to use that string to search the Excel file. When a matching record is found ( the string matches the corresponding field - part number, for example), I need to print a label that contains the fields in that record. My system will not be opening and searching that Excel file, I need to let BT do that.
I have created a label, have assigned field and file names so that I can properly fill the label. When I print the label manually, I can select the record to print and it does print properly.
I've created an Integration Builder project that uses trigger files. Right now, my system can push a file that will trigger the print.
What I want to do:
1) Put the part number inside the trigger file.
2) Write the trigger file (it will go onto a network location somewhere)
3) Let the Integration trigger on the file
4) Let the integration read the key out of the trigger file
5) Let the integration search the Excel file for the record to print
6) Print the label.
I am stuck at #4 and #5.
At least one of the help videos explains how to create a variable and read an element from a file to put into that variable. I haven't been able to get that to work yet. And then I still need to be able to select which record to print once I do.
I'd appreciate any help or guidance.
Thanks.
--
edit:
Follow-up: I have been able to read the text and put it into a variable %EventData%. Now I am at the point where I have a variable, and I have a spreadsheet. How can I get BT Integration Builder to find the record with a field that matches that variable and then print only that record?
-
I have been working with it a little more, and think I almost have it:
1) Integration builder detects the file
2) integration builder reads a text file containing a text string
3) integration builder loads variable %EventData% with that text string
3a) integration builder then writes that string into another text file so I can verify it is being read properly
--
4) BarTender label database setup - I added a filter called "AND Part# Contains ?QueryPrompt1"
When I manually select "Print" and "Print Preview", I can enter the same string that appears in %EventData% (in step 3) and I get the record that I want, formatted, and printed.
--
5) Integration Builder - I set up action Print Document, check "Specify Values for Query Prompts" then set Name = QueryPrompt1" and Value = "%EventData%"
I am expecting the BT document to query for a part number, then Integration Builder to fill that query and print.
What I get is "BarTender: Error Message #3... No records found."
-
Is this the proper way to pass a variable from Integration Builder into a BT document? It appears that the query is being executed, but I can't figure out why the record is not found.
0 -
When the event message is detected then the content of the message is automatically written to the %EventData% integration variable.
When you do a print job in BT manually, supplying the same query prompt data, does it find any records?
Why use the "contains" operator in the filter and not equals?
As a test, put a literal value in the Integration for the query, to see if that still returns a record. If not then something else might be going wrong. Perhaps the action needs to execute under a user account or something.
0 -
Thanks for the tips.
I eventually got it to work and I'm not exactly sure what happened. I know I was getting the Integrator to interfere with the BT document. Some of the print settings I had in the Integrator were over-ruling the settings inside the BT document.
There was something else that was happening, too. I was reading the trigger file, the string was being put into %EventData%, and then being passed into "QueryPrompt1" the way it's supposed to work. Somehow, I had gotten the system into the mode where it would not find a matching record if I had the filter set to "equals". If I had the filter set to "contains" then it would work. I don't know how it happened, but apparently the string field I was reading from an XLSX file contained trailing spaces after the part number (this is just my guess). When I passed the part number in QueryPrompt it would fail to find a match if I was filtering on "equals". So I had to use "contains", but that led to other problems. If the %EventData% read an empty trigger file, it would pass along an empty QueryPrompt that would cause it to print either the first record or all records depending on other settings.
I don't know what I did to fix this, mostly trial and error, but it is now working the way I wanted it to work.
The interesting thing is, even though it is working now, I can still recreate the behavior.
If I go into the filter and set it to "equals", then Apply Filter, I can type in a part number and no records will be found. If I change the filter to "contains", then "Apply Filter", and type in the same part number the correct record will be found.
But, if I go into "Record Browser", the "equals" filter works properly.
I don't understand the difference in behavior between "Apply Filter" and "Record Browser" and why one returns a record using "contains" and "equal", and one returns a record only with "contains".
0 -
I would imagine this is to do with data types, which is why the use of Excel as a database should be avoided if possible. When using equals the data type would need to match and not just the characters. When doing a contains comparison, I guess this implies a string data type which is why you get a match. We have a knowledge base article on the website detailing the perils of Excel.
0 -
Just heard from BarTender tech support. They recommend downloading R2 (just released) that has a bug-fix for the problem I was having.
0
Please sign in to leave a comment.
Comments
5 comments