creating "report" pages Folgen


Hi Folks,

   Still working on a solution. Want to produce a "report" from BT 2016, where there is a header record and multiple detail records, such as an invoice, shiplist, packing slip, etc. The data comes from mysql, using 2 views. 1 view returns the single header record, the other returns multiple detail records, from 1 to 999. I want to be able to print x number of detail records per page, and move to the next page, until all detail records have been used up.

   I have looked at a lot of other posts, and have not found anything that sounds like it will work across multiple labels. It is not possible to use additional templates, as the number of pages is truly unknown, and does not lend itself to doing 'reports'.

   Here is my proposal, so far, with questions I need to get answers for.

   The two views are joined by the sales order number. I am using a queryPrompt to pass the sales order number to the label format.

   I am getting the required data from sql, as I do not want to pass xml, with the associated xml tags, which will cause the file being sent to be huge. Same is true of sending CDATA. Having the data selected once, at the beginning of a new 'label' means no data passed, and only a single select being executed.

   I have created a 8.5 x 11 label, with header and footer fields, getting populated from the database fields from the header data. This data will repeat across as many pages as required, with the only exception being the page number.

   I have created multiple sets of fields, with each set representing 1 of 6 detail records to be printed on each page. They have names such as PartNbr_1, Desc_5, etc. All of the fields for the first detail set on the page end with _1, all for the 5th set end with _5, etc.

   LineNbr_1   PartNbr_1 Desc_1 ShipQty_1 PickLoc_1

   LineNbr_2   PartNbr_2 Desc_2 ShipQty_2 PickLoc_2

   LineNbr_3   PartNbr_3 Desc_3 ShipQty_3 PickLoc_3

   LineNbr_4   PartNbr_4 Desc_4 ShipQty_4 PickLoc_4

   LineNbr_5   PartNbr_5 Desc_5 ShipQty_5 PickLoc_5

   LineNbr_6   PartNbr_6 Desc_6 ShipQty_6 PickLoc_6

   I have code behind each field, which will use the current page number, and the set nbr from the field name, to determine which detail record to use to populate the Value for that field. The following code will support this..

   ItemsPerPage = 6 - need to set this as a global or public variable, so it only gets coded once

   thisPageNbr = someBTVariable - need to know how to get this data from BT

   thisSetNbr = right(PartNbr_2,1) - can use vbs code to get the current field name, then extract the 'item set number', change this to handle multi-digit set nbrs

   detailStartPtr = (thisPageNbr * ItemsPerPage) - (ItemsPerPage -1)

   detailEndPtr = (thisPageNbr * ItemsPerPage)

   thisSetPtr = detailStartPtr + (thisSetNbr - 1)

   example, we are on page 2, there are 6 possible items per page to print, and we are working with the 3rd item set on the page

       thisPageNbr = 2

      thisSetNbr = 3, extracted from the field name PartNbr_3

      detailStartPtr = (2 * 6) - (6 - 1) = 7

      detailEndPtr = (2 * 6) = 12, thus we will work with detail records 7 thru 12

     thisSetPtr = 7 + (3 - 1) = 9 is the detail set we use to populate this field

   To make this happen, I need to know how to get the data from detail set number 9. This data is gathered at the time the header and detail records are selected from the sql db. What would the syntax look like to get data from the detail records in memory.

   Once each of the detail item sets on the page are populated, using the above code, then a decision must be made. If any of the fields come across as null, then we know we are done with this sales order, and the page can be printed, and we move on to the next sales order requested. Could possibly use EOF on the dataset, to determine we are at the end of the line.

   If there are no null fields or not at EOF, then we know that we need to go to a new page, and continue this sales order. So, we need to preserve the dataset records built by the original select, reuse the header records on the next page, bump the page nbr, and let the item sets continue to get populated by the above code, which uses the page number and number of itemsPerPage to calculate which detail records to extract.

Finally, if this is possible, then here are the things I need to know about...

   How to get the current page number - could not find anything in the code editor syntax helper

   how to set ItemsPerPage as a variable that is available each time a new page is created, possibly use PUBLIC, but, need to know where this would be set in code. Is there a set of code that applies to the entire label, making it global, rather than code that resides behind individual fields.

   how to carry over the dataset records to new pages

   possibly, how to tell BT that we are done with a page, and need to go to the next - this might be handled automatically, once all fields have been populated or EOF has been reached

   bumping the page number...seems to me that this happens automatically, but might be something I need to handle

   the syntax to the storage of the detail record sets, so that those data fields can be extracted on a field by field basis


Does any of this make sense in the current version of BT 2016?

Does anyone have suggestions, better ideas, a cliff to jump off of?

Any help would be appreciated, and, should lead to a solution that the others trying to solve this problem can use.




2 Kommentare

Ron Brown
Aktionen für Kommentare Permalink

Addendum - getting the data for each field will require using the calculated detail record ptr, and changing the recordset being referenced by the standard database sources available in the vb editor, thus I would have to change the recordset to be recordset 9 before referencing the field data using sod_shiplist_view.Part_Nbr to get the Part Number associated with the 9th record set. Don;t know how to change the record set reference to do this, though.

Pete Thane
Aktionen für Kommentare Permalink

Quite a few years ago we did something like this but output the data directly out of the database/ERP system into a flat text file. 

The BarTender "label" was a basically an A4 sheet with about 200 odd fields on it  including the header and footer and allowed for multiple lines of product data.

The printing system was set to automate when a data file was produced (you would now use Integration Builder to perform this although I think we used the fore-runner of the old Commander application as it was that many years ago) and populate the matching label fields with the data in the trigger file.

We had calculated how many lines of product information would fit on each label and so if more records were needed an additional line of data was added into the trigger file. The new line(s) included repeating whatever header/footer details were needed together with the extra data records. Where parts of the header/footer weren't needed for the subsequent sheets, these were left blank in the data file (ie, field 1 data, field 2 blank, field 3 blank, field 4 data etc).

Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.