Select record from Database at print time, filter database base on selection, print 2 different template design.
Hello All,
I would like some help how to handle my problem.
We use Bartender 2016 automation and i would like the following template to setup but missing some understanding and can't find what i am looking for.
1st I have one Access database with 2 tables.
1 table will be the selection table for the part number selection.
The 2nd table is joined with the first table over an ID number. The second table will have all data for the labels and is filter by the ID number. Record are filtered and will have x labels to print when i select from the dropdown list the part number i would like to have the labels.
This i can setup for the first label design.
Now i have a 2nd design label that will have the same data source but only a summery page with the part number printed in the list. I can use the same function as above and work fine but i require to use a Page template so i can't use both (x single labels & summery page) in the same template design.
Page template is like this:
with the single record template like:
Now i would like to have both labels combined into 1 query at print time that would be used at a print station.
Basically operator run print station when print label select the part number and x single labels plus summery page are printed.
I do not know how to do this and if i need to setup a global database or the batch maker know that both will select the same part number with selecting at print time.
I also need to define a qty of the batch at print time as i would like to have it sorted in batch during print time.
I try to use a global data field but i am not able to link the query prompt and enter the selected part number from a dropdown list to this global data field.
Somehow i miss how this works.
Can somebody point me in the direction if this possible or what objects i need to use to make this possible.
Thank for anybody who is looking into this.
-
I do not think that is possible the way you are doing it however if you created the summary label first then as you have the Automation edition there is a way to use the data from there to generate your item labels,
You would need to amend your summary label so as to add any extra fields that are in your database on to the product template label (not the page template) but move these fields off the side of the label so they do not print.
Once you have done that use the Administer > Log Setup and configure it to create a text file at print with just the data only (ie, delete all the header and footer options). You want the file to just the information for the fields you need on your item labels only and not all the fields as you probably wont need any or all of the Page Template information I expect. In BarTender 2021 there is an option in the log setup to just specify which fields you want the data from in there, but, although I no longer have access to 2016 I do not recall this feature being in that version and I think there is a tick box on one of the screens in the Properties of the data fields which you tick/untick if you want them to be logged or not.
Print a test label to create a sample log file output and then use replace the existing database connection in your existing item labels with that file as the database and relink the fields to the new text database.
You will then need to configure Integration Builder to use the log file from the Summary label to trigger the production of the item labels with the Integration using the trigger file as the active database
1 -
Hello Pete
Thank you for this suggestions.
I am looking into this and let you know if it works.
I was not aware of this log possibility.
Now i need to check if this works from the log i get.
Will keep you update.
It looks promising at the moment.
0 -
Hello,
I manage to setup the log, file detection and printing. I still have to tweak all to work on the network but i think i should manage.
But i have one issue not solved yet to get a prompt to enter qty of batches at the print station.
My summery page must have a batch qty. I can't select the identical copies in the template that will repeat the first record x times on the label and start the next one. The summery must print all records first and repeat all records again. I can't find an option in the print station or the batch maker to ask this before print start. Only when i open the batch maker i can see the batch repeat counter.
Any way to access the batch repeat counter?
thank you.
0 -
If this is the QTY value for each line in the accessory list, make sure that is output in the log file and in Integration Builder you can link the Copies on the Print Options screen to the appropriate field in your data file
0 -
Hello Pete,
No this is the Batch qty. The single qty per record is set and not a problem. I need the Batch repeat counter.
We have a product that will be build for example 5 times so i need to print 5 identical summery label and every single single parts in the summery label. so i get the label as
label 1:
item 1
item 2
item 3
label 2:
item 1
item 2
item 3
If i use the copies per record it will print like this:
only 1 label:
item 1
item 1
item 2
item 2
item 3
item 3
But i don't think the batch repeat counter can be request at the print station. I don't want that the operator need to count how often he print the batch. The Operator may need to work with the batch maker instead. :(
Is their maybe a printer script i can use? You have experience with that. I have none, so i am not sure and have not study it yet.
0 -
Hello Pete
You may can help to go into a different direction as i have problem to make this functional with the several level of connections between the labels and the different setups. I also face problem with the connection to a network folder, where i can't make the integration can access the files in anyway, so i have to abundant the approach.
My last idea's would before i start setting each label and batch manual, is the label read to part number from the file name. I have solved this per VBscript. But i would like to disable the disable the query prompt show up at print time as the data is already process per VBscript.
If this is not possible, i would like to have the database filter based on the file name.
Do you have any suggestion or links i can refer to further improve this functionality.
0 -
I do nto think the latter two things are possible,
As for the Integration and network folder this will be a permissions issue as the integration will be running under a Local System account and so may not have access to network drives and so you may need to adjust the account that is running to service or else (and probably better) adjust who has read/write access to that folder
0 -
Hello Pete,
Thank you for your feedback and try to help.
I set to give read and write for everyone and all other user account that are found on this folder, with no luck. I try to add several other accounts (services, local services, administrators..) that was able to find and give access rights, also no working. Their is something i am missing, but i am not familiar with server setup, network access setup and user accounts and so i am not going to try around before i mess something up by accident :)
But last night i figure out how to print and define the query prompt with Excel VBA. Means i can also define how many times the labels are printed. So i will work on an Excel VBA Macro to fulfill our setup and use bartender SDK or how you name that.
Will keep you updated if i can success with this approach. This will avoid the read and write issue i have with Integration, The issue with bartender batch copy define not able to define in the print station and only 1 query prompt required. I also had issue with 64 and 32 bit bartender/Windows version that blocked my work on the design computer and the actual work station.
Maybe the newer version 2021 of Bartender designer, Integration, Printstation are more flexible and advanced, but i have to work with what i have, 2016 :(.
0 -
Dear all,
Find the Excel VBA macro below if anybody have a similar difficult setup required in their setup.
- In the label I setup a filter and a query prompt in the database.
- On the print, do not request a entry form as data is entered by the macro. For testing the label you can request to query form at print time, but for the macro this is not required anymore if you get the query prompt from the excel.
Regards
Sub PrintLabel()
Dim BarTender As Object
Dim Label1 As String
Dim Label2 As String
Label1 = "C:\Bartender\Accessory\Label\AccSum_log.btw" ' This also work with any file location on the network
Label2 = "C:\Bartender\Accessory\Label\AccLabTemp.btw" ' This also work with any file location on the network
Dim btApp As BarTender.Application
Dim btFormat1 As BarTender.Format
Dim btFormat2 As BarTender.Format
Dim BatchQty As Variant'Enter Batch Qty and check if it is a valid Qty or if enter was cancel or empty
ReEnter:
BatchQty = InputBox("Batch QTY?", , 1)
If BatchQty = "" Or BatchQty < 1 Then MsgBox "Qty = 0 or Batch Qty canceled! No Labels printed", vbOKOnly: GoTo subexit
If Not (IsNumeric(BatchQty)) Then GoTo ReEnter
If IsNumeric(BatchQty) And BatchQty >= 1 Then BatchQty = Round(BatchQty, 0)' This will print the label
Set btApp = New BarTender.Application
btApp.Visible = False 'open the bartender designer in the background
Set btFormat1 = btApp.Formats.Open(Label1)
Set btFormat2 = btApp.Formats.Open(Label2)
For i = 1 To BatchQty
'Label 1
btFormat1.Databases.QueryPrompts(1).Value = Range("C4").Value2 'get the prompt value from Cell C4
btFormat1.RecordRange = btFormat1.PrintOut(False, False) 'print label and don't show the print selection
btFormat1.RecordRange = "1..." ' reset the database selected range to all, otherwise have issue for the second batch.'Label 2
btFormat2.Databases.QueryPrompts(1).Value = Range("C4").Value2
btFormat2.RecordRange = btFormat2.PrintOut(False, False)
btFormat2.RecordRange = "1..."
Next ibtApp.Quit ' Close the bartender designer from the background
subexit:
End Sub0
Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.
Kommentare
9 Kommentare