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.

thanx,

ron

rbrown@echodata.com