Designing Labels With 1:many Relationships
Any discussion would be helpful, examples fantastic. Thanks BarTender community.[/font]
-
The multiple records on a single label feature is something that BarTender does not support directly at the moment. As you suggest this sort of feature is ideal for reports, receipts and things such as ingredient or package content lists for a single product. This sort of functionality is of interest to us, so will probably get implemented some time in the future.
Meanwhile, you can get the result you want by using an event based VB script inside the label format to data source the related (sub-component) records, using a query in the VB script that uses the Product ID value from your main BarTender database connection as the query criteria value.
Your VB script should take this form:
1. Make a connection to the database/table containing the component items.
2. Use the Product ID field from BarTender's database connection to the main "Products" table as the criteria value in your SQL query.
3. Loop through the record set of data that is returned, setting the value of a paragraph text object on the label with a line of field data per record. Terminating with a carriage return ready for the next row of data to be set.
4. Close the database connection.0 -
Thanks Ian,
That was what I needed to get it done. I posted some sample code on this in the VB Scripting section if anyone is interested...
[url="http://seagullscientific.invisionzone.com/index.php?/topic/79-populating-child-records-from-parent-db-record/"]DB VB Script example of 1:many relationship[/url]0 -
That's great, thanks Shane. I'll take a look at the code and let you know if I have any comments. 0 -
I came looking for the same feature. We're writing a warehouse management system and need it to print carton labels to display the contents of the carton. I think it would be a great feature to include.
[quote name='Ian C - Seagull Support' timestamp='1309767051' post='158']
That's great, thanks Shane. I'll take a look at the code and let you know if I have any comments.
[/quote]0 -
Hello Kyle,
Yes it would be a good feature; we fully agree. Certainly this sort of functionality is as of much interest to us as it is to our customers. Be sure to post a request into the "Feature Request" forum for BarTender to help give it more priority with development and management.
http://seagullscientific.invisionzone.com/index.php?/forum/147-bartender/0 -
Ok, I will do that right now. We're just evaluating the software right now and we're very happy with it so far. Unfortunately, this is a critical feature for one of our most important label types. But we're going to try and go with Shane's suggestion for now (thanks Shane).
[quote name='Ian C - Seagull Support' timestamp='1312376815' post='318']
Hello Kyle,
Yes it would be a good feature; we fully agree. Certainly this sort of functionality is as of much interest to us as it is to our customers. Be sure to post a request into the "Feature Request" forum for BarTender to help give it more priority with development and management.
http://seagullscientific.invisionzone.com/index.php?/forum/147-bartender/
[/quote]0 -
I am working with an Exact database where data as batchnumbers is in.
I would like to print an entry label of new products with several batchnumbers on 1 page. I would like to use this kind of VB script, is this possible?0 -
[quote name='Shane Sullivan' timestamp='1309554453' post='148']
Thanks Ian,
That was what I needed to get it done. I posted some sample code on this in the VB Scripting section if anyone is interested...
[url="http://seagullscientific.invisionzone.com/index.php?/topic/79-populating-child-records-from-parent-db-record/"]DB VB Script example of 1:many relationship[/url]
[/quote]
I can see your code, how to add this into BarTender? Which steps to take? Can you give some more info on your code also?
=======
I edited the script:
[code]dim sServer, sConn, oConn,oRS
sServer="SERVERxxx"
sConn="provider=sqloledb;data source=" & sServer & ";initial catalog= xxx"
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn, "xxx", "xxx"
Set rstComponents =CreateObject("ADODB.Recordset")
sSQL = "SELECT itemcode"
sSQL = "SELECT SBNumber"
sSQL = "SELECT ItemDescription"
sSQL = "SELECT quantity"
sSQL = "SELECT V-eenheid"
sSQL = "SELECT Location"
sSQL = sSQL & " FROM sp.vwLabelComponentDetail where CabSerialNum = '" & Field("vwLabelCabinetView.CabSerialNum") & "'"
sQQL = sSql & "Order by itemcode asc"
Set rstComponents = oConn.execute(sSQL)
itemcode1=""
SBNumber1=""
ItemDescription1=""
quantity1 = ""
V-eenheid1=""
Location1 = ""
...
itemcode50=""
SBNumber50=""
ItemDescription50=""
quantity50 = ""
V-eenheid50=""
Location50 = ""
dim i
i= 1
Do until rstComponents.EOF
Select Case i
Case 1
itemcode1 = rstComponents("itemcode")
SBNumber1 = rstComponents("SBNumber")
ItemDescription1 =rstComponents("ItemDescription")
quantity1 = rstComponents("quantity")
V-eenheid1 =rstComponents("V-eenheid")
Location1 = rstComponents("Location")
Case 2-49 ...
Case 50
itemcode50 = rstComponents("itemcode")
SBNumber50 = rstComponents("SBNumber")
ItemDescription50 =rstComponents("ItemDescription")
quantity50 = rstComponents("quantity")
V-eenheid50 =rstComponents("V-eenheid")
Location50 = rstComponents("Location")
Case else
End Select
i = i+1
rstComponents.MoveNext
Loop
oConn.close
[/code]
however i don not understand what you mean in the from and where section. where is this info coming from? I also get an error: id is missing where ... is standing now.0 -
bhdijkstra,
I noticed a couple of things that might help or have an effect on your implementation. The first is that the From statement in the SQL string still looks like mine and I think you'll want that converted to your own. My "From" statement is a view in the DB that I'm querying and the where clause is how I linked the parent record (stored in a field in the label) to the query that i was running. Basically I was asking for all the children records where the parent = aSerialNumber. When this runs, the label prompts the user for the parent serial number. On the production floor, they scan the barcode of the unit, that then (through this label in BarTender) puts all of the components on the label and prints the label to complete the unit's assembly process. That parent information is coming from the same DB, but a different table and handled through BarTender's DB connection. Thus the call for
[code]Field("vwLabelCabinetView.CabSerialNum")[/code]
is a call to the DB field CabSerialNum in the configured DB connection.
The second is that you'll need to name your labels in BarTender. It isn't enough that the labels are called "itemcode1" or "SBNumber1", you'll need to actually go into the properties panel of those items and set the Share/Name to SBNumber1 or itemcode1 in the data source tab. I think that might be where your "error:id is missing" is coming from. My last comment is that I put this code in its own non-printing component in the label. I have it fire in the OnAutoSelectedEvent. Which event I picked mattered significantly in how I expected it to run.
Having said all of that, this may be very sub-optimal. I have only this experience in coding a VBScript solution in BarTender so there may be some better ways to handle this. We did take this into production though and it has held up so far. Good luck and I'll keep an eye on this string to continue the discussion if has been helpful.
~S
[quote name='bhdijkstra' timestamp='1314346144' post='458']
I can see your code, how to add this into BarTender? Which steps to take? Can you give some more info on your code also?
=======
I edited the script:
[code]dim sServer, sConn, oConn,oRS
sServer="SERVER02"
sConn="provider=sqloledb;data source=" & sServer & ";initial catalog= 920"
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn, " AKGICT\administrator", " Akg7609Es3"
Set rstComponents =CreateObject("ADODB.Recordset")
sSQL = "SELECT itemcode"
sSQL = "SELECT SBNumber"
sSQL = "SELECT ItemDescription"
sSQL = "SELECT quantity"
sSQL = "SELECT V-eenheid"
sSQL = "SELECT Location"
sSQL = sSQL & " FROM sp.vwLabelComponentDetail where CabSerialNum = '" & Field("vwLabelCabinetView.CabSerialNum") & "'"
sQQL = sSql & "Order by itemcode asc"
Set rstComponents = oConn.execute(sSQL)
itemcode1=""
SBNumber1=""
ItemDescription1=""
quantity1 = ""
V-eenheid1=""
Location1 = ""
...
itemcode50=""
SBNumber50=""
ItemDescription50=""
quantity50 = ""
V-eenheid50=""
Location50 = ""
dim i
i= 1
Do until rstComponents.EOF
Select Case i
Case 1
itemcode1 = rstComponents("itemcode")
SBNumber1 = rstComponents("SBNumber")
ItemDescription1 =rstComponents("ItemDescription")
quantity1 = rstComponents("quantity")
V-eenheid1 =rstComponents("V-eenheid")
Location1 = rstComponents("Location")
Case 2-49 ...
Case 50
itemcode50 = rstComponents("itemcode")
SBNumber50 = rstComponents("SBNumber")
ItemDescription50 =rstComponents("ItemDescription")
quantity50 = rstComponents("quantity")
V-eenheid50 =rstComponents("V-eenheid")
Location50 = rstComponents("Location")
Case else
End Select
i = i+1
rstComponents.MoveNext
Loop
oConn.close
[/code]
however i don not understand what you mean in the from and where section. where is this info coming from? I also get an error: id is missing where ... is standing now.
[/quote]0 -
What is the status of the new feature proposed here (multiple records on one label). Was this implemented ?
We've just purchased the software.
Calin
0 -
Calinp: No this has not yet been implemented (as of v10.1 SR1), and is likely to be a while yet.
0 -
hi Ian
we are about to move over to Bartender , one of the most important areas for us too is (multiple records on one label). shipping department is demanding it.. has this implemented in v10.1 SR2?
if not,, how far away is it? weeks, months , years?
0 -
petergsi: Such a feature would be part of a major version release, and not simply service release which is intended to fix bug, improve performance, and add small features that didn't quite make it for the major release deadline. Unfortunately I cannot give you any ETA as I don't know our exact plans, and it's against company policy to do so anyhow. I suggest you work on a backup contingency, see below, if your shipping dept cannot wait.
Although we don't support it directly at the moment, there are ways an means that you can get the sort of results you want already. It just means that you have to go about things in a more complex way to achieve it. For example, controlling BarTender via Automation to feed multi-record data directly into the data source of a single text object in order to get the table of data effect. The use of document level VB scripting can also be used to dynamically modify the layout of the label to suit the data you're passing it. Lastly, multiple templates and/or layers can be used to further extend the layout variation possibilities for your multi-record labels.
Much depends on what sort of multi-record label it is that you want, which thus decides the level of complexity you have to go to in order to achieve it.
0 -
If it were me, I would probably design a .NET app to run my database query, and then generate my label from that point. But I'm not a very experienced programmer, so I'm sure there are lots of ways to do it like Ian describes.
0 -
Hello,
Is there any news about this feature?
Last update was from 2014. is there any update in the 2016 release?
0 -
BarTender v10.1 supports multiple records per label through the use of the Page Template, with a multi-label layout being set for defining the line items. See the attached document example. BarTender 2016 also brings support for multiple database connections that don't require the tables/view to be joined. In other words database/datafile look-up connections. The use of both of these features should help you achieve the end result you want.
0 -
Hi Ian,
Our company just recently purchased the BarTender 2016, and I am having difficulty connecting to multi-databases in one label.
Can you explain on your last comment how is that done? " BarTender 2016 also brings support for multiple database connections that don't require the tables/view to be joined."
How is connecting to multiple database connections can be done without joining them?
Thanks.
0 -
williamchond: This is for populating data entry form controls using database connections that are unrelated to the main database connection.
0 -
BarTender v10.1 supports multiple records per label through the use of the Page Template, with a multi-label layout being set for defining the line items. See the attached document example. BarTender 2016 also brings support for multiple database connections that don't require the tables/view to be joined. In other words database/datafile look-up connections. The use of both of these features should help you achieve the end result you want.
Is there any more documentation that shows a better example of how to use Page Templates? The document you posted here is a start but the database file it is tied to is not there. Without the database, the Packing List Page Template does not show the multi-records on the same page.
0 -
Just to summarize our journey into placing child records on a label to help anyone else down the road that might stumble on this thread:
1. Page Templates was our first choice but soon found out that wouldn't work. Page templates seem like they would work just fine if you only have one area on your label that you want to place child records. Or more than one area where the template is the same size. We needed two areas filled with child records but the areas were not the same size. Page templates seem as though you can only have one unique size in the page setup dialog. Maybe we were missing something.
2. VBScripting worked great for us and wasn't too difficult to implement. Using the example above as a start we were able to place all of our child data into the two areas that we needed it. We were able to list child records in a vertical fashion by place a carriage return between each child record. Here are some introductory steps we needed to figure out ourselves when getting our feet wet with VBScripting and not being fluent in VB but rather C#.
a. Place a regular single or multiline text box on your label
b. Drag a Visual Basic Script object into that text box
c. Write your script code (my example is below and similar to code above)
d. When writing VBScript code and looking for code references online make sure you are searching VBScript and not just 'VB' or 'VB.Net'. The latter two terms have a lot more calls and functions available than just the VBScript language. An example is VBScript does not have native Floor and Ceiling functions where VB.Net does.
e. VBScript option works pre-2016 version.
Scripting allowed me to not just list child records but list them in two columns
dim sServer, sConn, oConn sServer="serverName" sConn="provider=sqloledb;data source=" & sServer & ";initial catalog= dataBaseName" Set oConn = CreateObject("ADODB.Connection") oConn.Open sConn, "username", "password" Set rstComponents = CreateObject("ADODB.Recordset") sSQL = "SELECT SampleTest, a.TestName, " sSQL = sSQL & " (SELECT Count(*) FROM Printing.CartTagTests WHERE CartID = '" & Field("CartTag.CartID") & "') AS TotalTestsInCart " sSQL = sSQL & " FROM Printing.CartTagTests AS a " sSQL = sSQL & " WHERE CartID = '" & Field("CartTag.CartID") & "'" sSQL = sSQL & " ORDER BY LabCount, TestID" Set rstComponents = oConn.execute(sSQL) dim fieldText dim i i = 1 dim totalRecords totalRecords = rstComponents("TotalTestsInCart") dim floorOfHalfOfRecords floorOfHalfOfRecords = Int(totalRecords / 2) Do until rstComponents.EOF ' If there is more than 21 total records than split them up between two columns and place the first 21 here If (totalRecords >= 21) Then If (i <= floorOfHalfOfRecords) Then fieldText = fieldText & rstComponents("SampleTest") & vbCrLf End If Else ' Less than 21 recrods - place them all in this box fieldText = fieldText & rstComponents("SampleTest") & vbCrLf End If rstComponents.MoveNext i = i + 1 Loop oConn.close ' The following line is needed for the object to take the value generated by code value = fieldText0 -
jaredbaszler: You can download the sample database file from here: https://goo.gl/uYA49I
0
Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.
Kommentare
21 Kommentare