Because the caller is selecting a subset of the fields and rows, in this case the new ProDataSet has to copy data from the original one, rather than simply assigning new buffers to the existing temp-tables in their entirety.
To update the code:
1. Create the new procedure fetchCustomTable in CodeSupport.p. It takes the table name, field list, and selection where-clause as input parameters and returns the new dynamic ProDataSet, as shown:
PROCEDURE fetchCustomTable:
DEFINE INPUT PARAMETER pcTable AS CHARACTER NO-UNDO.
DEFINE INPUT PARAMETER pcFields AS CHARACTER NO-UNDO.
DEFINE INPUT PARAMETER pcSelection AS CHARACTER NO-UNDO.
DEFINE OUTPUT PARAMETER DATASET-HANDLE phFilterData.
2. Add the variables the procedure uses:
DEFINE VARIABLE cField AS CHARACTER NO-UNDO.
DEFINE VARIABLE hTable AS HANDLE NO-UNDO.
DEFINE VARIABLE iField AS INTEGER NO-UNDO.
DEFINE VARIABLE hNewBuf AS HANDLE NO-UNDO.
DEFINE VARIABLE hOldBuf AS HANDLE NO-UNDO.
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
3. Create a dynamic ProDataSet and a dynamic temp-table to put into it, with the fields the caller requested:
/* Create a new dynamic ProDataSet based on the table and fields passed
in. */
CREATE DATASET phFilterData.
CREATE TEMP-TABLE hTable.
DO iField = 1 TO NUM-ENTRIES(pcFields):
cField = ENTRY(iField,pcFields).
hTable:ADD-LIKE-FIELD(cField,pcTable + "." + cField).
END.
4. Create a dynamic query for the temp-table in the original ProDataSet and prepare it using the where-clause passed in:
/* Next create a dynamic query for the selection criteria passed in. */
CREATE QUERY hQuery.
hOldBuf = DATASET dsCode:GET-BUFFER-HANDLE(pcTable).
hQuery:ADD-BUFFER(hOldBuf).
hQuery:QUERY-PREPARE("FOR EACH " + pcTable + " WHERE " + pcSelection).
5. Open the query and buffer-copy all the rows that satisfy the selection into the new temp-table:
hQuery:QUERY-OPEN().
hQuery:GET-FIRST().
DO WHILE NOT hQuery:QUERY-OFF-END:
hNewBuf:BUFFER-CREATE().
hNewBuf:BUFFER-COPY(hOldBuf).
hQuery:GET-NEXT().
END.
Or, you can create a dynamic Data-Source for the temp-table in the original ProDataSet and attach that Data-Source to the new temp-table buffer in the new ProDataSet. This shows how one ProDataSet table that has already been filled can be used as a Data-Source for a table in another ProDataSet.
As in this example, this is appropriate if the original ProDataSet is filled with some set of generally useful data (and possibly, data that is expensive to regenerate and that needs to be used as a cache for the session), and if the second ProDataSet only wants a subset of its rows or fields. Remember that this approach does involve copying data from one ProDataSet to another.
To use this method, remove or comment out the lines in the previous code section and replace them with this code:
CREATE DATA-SOURCE hCodeSource.
/* NOTE: hOldBuf is the source temp-table buffer, and the KEYS list is
not needed */
hCodeSource:ADD-SOURCE-BUFFER(hOldBuf, ?).
/* Because there is a specific query for selecting a subset of the rows
in the source temp-table, the procedure uses the dynamic query defined
above. Otherwise it could leave off the query and get all rows automatically. */
hCodeSource:QUERY = hQuery.
/* Now when it attaches the Data-Source and fills the new ProDataSet it
gets rows from its Data-Source, which is the table in the original
ProDataSet. */
hNewBuf:ATTACH-DATA-SOURCE(hCodeSource).
phFilterData:FILL().
hNewBuf:DETACH-DATA-SOURCE().
/* This is the end of the alternative code to use the original ProDataSet
as a Data-Source for the custom subset. */
6. Delete the dynamic objects the procedure uses. Note that it's OK to delete the ProDataSet before returning because the AVM delays the actual delete until the parameter has been returned. For example:
7. Return to the window procedure CodeWindow.w to create a user interface for the custom ProDataSet.
8. Add these variables to the Definitions section:
DEFINE VARIABLE hCustomQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE hCustomBrowse AS HANDLE NO-UNDO.
DEFINE VARIABLE hCustomSet AS HANDLE NO-UNDO.
You are going to add some Customer fields to the window along with a Region combo box that lists the four regions in the US. When the user selects a region, the procedure runs fetchCustomTable to request a list of state codes and state names for that region. To reduce the size of the example somewhat, you will use fields from the Customer table directly rather than a Customer ProDataSet, which would be the proper way to do things.
9. From the AppBuilder palette, select the DB Fields icon and drop the fields CustNum, Name, and City from sports2000.Customer onto the window.
10. Select the combo box from the palette and create a combo box. It has the Object name cRegion, the LabelRegion, 5Inner Lines, and the set of List-Items<select>,East,West,Central,South. The <select> choice prompts the user to select a region before seeing any SalesReps for it, as shown
11. Create another combo box called cState, with a Label of State and 5Inner Lines as well. It has no initial List-Items. The design window should now look something like this:
12. Code a VALUE-CHANGED trigger for the Region combo box.
If the user makes a region selection, the trigger runs fetchCustomTable, requesting a ProDataSet with the ttState table, two of the three fields from the table, and only those SalesReps where the Region matches the one chosen. For example:
DEFINE VARIABLE hStateBuf AS HANDLE NO-UNDO.
IF cRegion:SCREEN-VALUE NE "<select>" THEN DO:
RUN fetchCustomTable IN hCodeSupport
(INPUT "ttState",
INPUT "State,StateName",
INPUT "Region = '" + cRegion:SCREEN-VALUE + "'",
OUTPUT DATASET-HANDLE hCustomSet).
It empties the State combo in case this is not the first request, creates a dynamic query for it, and adds each StateName that came back in the ProDataSet from fetchCustomTable to the List-Items for the State combo. It makes the first one the current choice, and deletes the query now that it is done with it. For example:
cState:LIST-ITEMS = "". /* Empty the old list if any. */
Now your procedures are finished. When you run the window, the standard AppBuilder-generated code opens a Customer query and retrieves the first Customer for you because you added fields from that table to the window. You can then select a Region and see a list of all the States in that region to choose from, as shown:
This illustrates how a ProDataSet that is filled with a set of useful data can be divided in many ways by other procedures that need various subsets of the data in the same session or another session. Data held in a ProDataSet in a client session can act as a cache for visual objects or client-side business logic that needs to view or use the data or a subset of the data. Any object in the same session can define its own query to browse or otherwise use a subset of the rows in the data.
