Concat Number Of Records Into String 关注

0
Avatar
Legacy Poster

Hi all,

 

i'm not sure if this can be done at all, but the helpful folks from this forum will sure be able to help me here!

I have this file in our ERP system that i connect viad ODBC to:

 

item1

item2

item3

item4

....

item????

 

The list can contain anything from 1 to a few hundred items.

 

What i want to do is print a Datamatrix code like this:
item1<<HT>><<HT>><<HT>>item2<<HT>><<HT>><<HT>>item3<<HT>><<HT>><<HT>>.....

 

This barcode should always contain 18 items. So if the table has 60 entries, i need 4 barcodes, 3 with 18 items, 1 with 6 items in it.

 

 

I know that changing the source file would be the easiest way to achieve this, but maybe there is also a way to make this work in Bartender.

So basically the whole file needs to come into an array i guess and a loop needs to take always 18 items. But this is still beyond my skills...

 

Edit:  we cannot change the source file format.

Is there the possibility to create a string out of multiple records?

6 评论

0
Avatar
Ian Cummings
版主
评论操作 固定链接

Are these items that you speak of in different fields in the same record, or the same field in X number of records?  And you intend to connect BarTender to your ODBC database at print time to source this data and print all four (potentially) barcodes on the same label?

0
Avatar
Legacy Poster
评论操作 固定链接

Hi Ian,
 
the table has just one field and X number of records. I have connected it via ODBC. The file is updated once per day in the ERP system.
 
So it looks like this:
pjitem
item1
item2
item3
...
 
 
Ideally it would print 1 barcode per label. So every 18 records 1 barcode is generated and printed on 1 label.
 
The connection and printing in general is working, this i do a lot with our ERP system and Bartender in other places. But usually there i have 1:1 relation between a record in the file and a label
The problem here is of course that i need to put 18 records from the file onto 1 label.
 

0
Avatar
Legacy Poster
评论操作 固定链接

>>the table has just one field and X number of records. I have connected it via ODBC. The file is updated once per day in the ERP system.
 
So you're saying that the file is generated from the ERP database table daily?

Do you have a way to just query the data from the ERP table instead of the flat file?

You could write a pivot type query or something similar to turn the "rows into columns"...

 

Just throwing an idea- that's a strange requirement.

 

Out of curiosity, what's special about the number 18? :)

0
Avatar
Legacy Poster
评论操作 固定链接

No i'm not allowed to directly connect to the tables. I can only run a report that generates that file.

I now all of this may sound confusing, therefore now some pictures :)

 

I want to go from that:
http://www.schoelle.de/img/table.png

 

To that:
http://www.schoelle.de/img/table2.png
 

I know that this would be best done in the data source, but that is not possible.

 

I'm just thinking of loading the table into excel, trying to do the transition there and then connecting the excel table to Bartender. But ideally there would be not step inbetween.

Thanks for any help and input!

The barcode with the 18 items will be scanned into another system by workers that are absolutly not tech-savvy. And we need 18 items in there per batch.
I know this may sound confusing a bit, but the systems we have there are as they are :)

 

 

Edit: Not so easy to include pictures here...

0
Avatar
Legacy Poster
评论操作 固定链接

OK with Excel inbetween i was able to achieve it.

 

1) Get the data from the ERP file with an ODBC Connection to Excel

2) Run this routine in Excel:

Sub TestIt()
   JoinOnNthLine Worksheets("Tabelle1").Range("A1:A100"), Worksheets("Tabelle2").Range("a1"), 18
End Sub
Sub JoinOnNthLine(rngSrc As Range, rngDst As Range, n As Integer)
   Dim i As Long     ' Zeile Quelle
   Dim j As Integer  ' Spalte Ziel
   Dim k As Long     ' Zeile Ziel
   
   For m = 0 To rngSrc.Rows.Count - 1
   
   For i = 0 To rngSrc.Columns.Count - 1
      rngDst.Offset(k, j).Value = rngSrc.Offset(m, i).Value
      If j = n - 1 Then
         k = k + 1
         j = 0
      Else
         j = j + 1
      End If
   Next
   Next
End Sub

 

3) Save as .csv file with a delimiter  (item1; item2; item3; ...)

4) open label that is connected to the csv file

5) on the barcode object set search ";"  and replace to "<<HT>><<HT>><<HT>>"

 

Now i have 1 barcode for 18 items which is the goal.

 

Now the question is how to make this easier, stable and without the use of Excel :)

 

0
Avatar
Ian Cummings
版主
评论操作 固定链接

The problem here is that BarTender doesn't support multiple records per label currently.  Normally for such a process you would be creating your items as different fields in a single record rather than the other way around.  This forces you to use this Excel workaround.  I'm afraid there's not much we can do about that at the moment.

请先登录再写评论。