creating "report" pages Folgen
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.