Using the COPY-DATASET method for successive FILLs
This second COPY-DATASET example provides a variation on the earlier PickOrder procedure. As it stands, PickOrder.w and its support procedure OrderSupport.p retrieve a set of Order headers, and then in follow-on calls, the OrderLines for those Orders. All the items are retrieved along with the first set of OrderLines. When you select a different set of Orders, the target ProDataSet is emptied and you start over again.
To show a possible use for COPY-DATASET, you start by making some changes to the way PickOrder works, which will cause a problem that must be corrected.
To update your code:
1. Create a copy of PickOrder.w called PickOrderCopy.w.
2. Create a variation on dsOrder.i called dsOrderNoRepos.i, which does not have the REPOSITION keyword on the Item relation, as shown:
/* dsOrderNoRepos.i -- include file definition of DATASET dsOrder with
no REPOSITION qualifier. */
DEFINE DATASET dsOrder FOR ttOrder, ttOline, ttItem
DATA-RELATION OrderLine FOR ttOrder, ttOline
RELATION-FIELDS (OrderNum, OrderNum)
DATA-RELATION LineItem FOR ttOline, ttItem
RELATION-FIELDS (ItemNum, ItemNum).
This will be used in the OrderSupport procedure when it fills the ProDataSet, so that rather than retrieving all Items regardless of their relationship to the OrderLines (which is what REPOSITION does on a FILL), it will retrieve only those Items that match one of the OrderLines being filled at the same time.
3. Create a copy of OrderSupport.p called OrderSupportCopy.p. Include dsOrderNoRepos.i in place of dsOrder.i, as shown:
/* OrderSupportCopy.p -- FILL events for OrderDset.p */
{dsOrderTT.i}
{dsOrderNoRepos.i}
4. Edit the internal procedure fetchOrderDetail in OrderSupportCopy.p to eliminate the second parameter, which is the flag indicating whether Items have been retrieved already or not. In this variation, you will always retrieve Items for the current OrderLines (and only those Items). The FILL-MODE for the ttItem table needs to be changed to MERGE. APPEND mode is correct for the ttOline table, because there will not be any duplicate OrderLines for a given set of Orders (although MERGE mode would work just as well). MERGE mode is needed for the ttItem table in case the same Item is used in more than one OrderLine.
MERGE mode eliminates the duplicates. For example:
PROCEDURE fetchOrderDetail:
DEFINE INPUT PARAMETER piOrderNum AS INTEGER NO-UNDO.
/* DEFINE INPUT PARAMETER lFillItems AS LOGICAL NO-UNDO. */ DEFINE OUTPUT PARAMETER DATASET FOR dsOrder BY-VALUE.
hDataSet:EMPTY-DATASET.
cSelection = "OrderNum = " + STRING(piOrderNum).
/* We need to reset the FILL-MODE from its initial setting of
NO-FILL for these two tables. */
hDataSet:GET-BUFFER-HANDLE(2):FILL-MODE = "APPEND". /* ttOline */
hDataSet:GET-BUFFER-HANDLE(3):FILL-MODE = "MERGE". /* ttItem */ /* IF lFillItems THEN "APPEND" ELSE "NO-FILL". */ hDataSet:FILL().
END PROCEDURE.
5. Back in PickOrderCopy.w, change the Main Block to run OrderSupportCopy.p instead of OrderSupport.p
6. Change the SalesRep LEAVE trigger in PickOrderCopy.w to run the new version of fetchOrders, with only two parameters, as shown:
RUN fetchOrders IN hOrderProc (INPUT cSelection, OUTPUT DATASET dsOrder).
7. Change the MOUSE-SELECT-DBLCLICK trigger for the ttOrder browse, likewise eliminating the second argument to fetchOrderDetail, as shown:
RUN fetchOrderDetail IN hOrderProc
(INPUT iOrderNum, OUTPUT DATASET dsOrder APPEND).
8. Now try running the window procedure. Enter 1 for the Customer and tab through the other fill-in fields. You will see Orders for Customer 1 as before.
9. Double-click on the second Order (number 36). You see that only its Items have been retrieved to be displayed in the Item browse:
10. Now double-click on the next Order, number 79. Its OrderLines and Items are retrieved and added to those already in the window procedure's ProDataSet. So far so good.
11. Next, double-click on the next Order, number 177. The following error appears:
What went wrong? Because the APPEND mode on the ProDataSet parameter in the Order browse trigger does not eliminate duplicates, the AVM attempted to add an Item to the window procedure's ttItem table that was already used in another OrderLine, and was therefore already there. This violates the unique index on the ttItem table, and the AVM complains accordingly.
Since you cannot get the APPEND parameter mode to do a merge for you, eliminating duplicates, how can you accomplish this?
One way is to use the COPY-DATASET method to combine the data from two ProDataSets, one which is already on the client, and the other which is retrieved into a separate ProDataSet from the support procedure.
12. To do this, insert the contents of the include files dsOrderTT.i and dsOrder.i into PickOrderCopy.w's definitions section, and edit them to give unique names to the second copy of the ProDataSet and its temp-tables. The temp-tables should be named ttOrder2, ttOline2, ttOlineBefore2, and ttItem2. The field and index names can stay the same. The new ProDataSet definition should look like this:
DEFINE DATASET dsOrder2 FOR ttOrder2, ttOline2, ttItem2
DATA-RELATION OrderLine FOR ttOrder2, ttOline2
RELATION-FIELDS (OrderNum, OrderNum)
DATA-RELATION LineItem FOR ttOline2, ttItem2
RELATION-FIELDS (ItemNum, ItemNum).
Remember that there is no way to have multiple instances of the same static ProDataSet or temp-table in a single OpenEdge procedure, so the second ProDataSet and its temp-tables need distinct names. You could, of course, also use a dynamic ProDataSet as the second ProDataSet, as shown in the previous example.
13. Now re-edit the MOUSE-SELECT-DBLCLICK trigger on the Order browse to remove the code that empties the target ProDataSet, and to receive the additional Orders, OrderLines, and Items into a second ProDataSet that is then copied into the first one. For example:
/* DELETE ttOrder.
FOR EACH ttOline WHERE ttOline.OrderNum = iOrderNum:
DELETE ttOline.
END. */
RUN fetchOrderDetail IN hOrderProc (iOrderNum, OUTPUT DATASET dsOrder2).
DATASET dsOrder:COPY-DATASET(DATASET dsOrder2:HANDLE, TRUE).
The second argument to COPY-DATASET, the merge-flagTRUE, tells the AVM to merge data from ProDataSet dsOrder2 into dsOrder rather than emptying dsOrder first. Now when you rerun the window, retrieve Orders for Customer 1, and select Orders 36, 79, and 177 in turn, the buffer handle is gone, because new data is being merged into the original ProDataSet and duplicates are automatically eliminated. For example:
