Use FILL-WHERE-STRING to assign queries prior to FILL

Save PDF

Last Updated: October 18, 2024
2 minute read

OpenEdge
Version 12.2
Documentation

If you want to make a change dynamically to modify how FILL loads records into a dataset, you specify the FILL-WHERE-STRING attribute.

Work with the FILL-WHERE-STRING attribute on a data-source

The query for each buffer has a FILL-WHERE-STRING attribute. This attribute helps you determine the query that is in effect at any given time. It also can be used to customize the WHERE clause of the query prior to a FILL operation.

The AVM sets the FILL-WHERE-STRING value when it prepares the query. During FILL, the AVM creates a default WHERE clause based on the defined query. For example, the query might be on Order using a WHERE clause:

     WHERE Order.ShipDate < TODAY.

Customize FILL-WHERE-STRING

You also might want to supply more criteria to the WHERE clause. You can modify the FILL-WHERE-STRING attribute to reflect new criteria; this attribute opens the query again to change the scope of records retrieved.

This technique is useful whenever you want to re-assign a new query before FILL. For example, you could create a number of different reports using the same dataset, but change the query each time to retrieve a different set of records.

For example, assume your query gets all Orders for a Customer. The FILL-WHERE-STRING value would be:

     WHERE Order.CustNum = Customer.CustNum.

If you want to change the query to get only orders that are > $1,000, you can add to the FILL-WHERE-STRING as follows:

     myString = DATA-SOURCE srcOrder:FILL-WHERE-STRING
       + " AND orderTotal > 1,000".
     DATA-SOURCE srcOrder:FILL-WHERE-STRING = myString.

Steps for customizing the FILL-WHERE-STRING

To modify the FILL-WHERE-STRING, follow these steps:

Add to the FILL-WHERE-STRING.
Define a query to use the new criteria.
FILL the dataset.

Example using a dynamic query

DEFINE VARIABLE cMyString AS CHARACTER NO-UNDO.
DEFINE VARIABLE hOrderQuery AS HANDLE NO-UNDO.

{include\dsOrderOrderLine.i}

BUFFER ttOrder:ATTACH-DATA-SOURCE(DATA-SOURCE srcOrder:HANDLE).
BUFFER ttOrderLine:ATTACH-DATA-SOURCE(DATA-SOURCE srcOrderLine:HANDLE).
DATA-SOURCE srcOrder:FILL-WHERE-STRING = "WHERE ShipDate < TODAY".

/* Add to the FILL-WHERE-STRING */
cMyString = "For Each Order " + DATA-SOURCE srcOrder:FILL-WHERE-STRING
  + " AND OrderDate > 1/1/05".

MESSAGE cMyString VIEW-AS ALERT-BOX.

/* Define a dynamic query and prepare the query. */
/* Create a query for the Order */
CREATE QUERY hOrderQuery.
hOrderQuery:SET-BUFFERS(BUFFER Order:HANDLE).
hOrderQuery:QUERY-PREPARE(cMyString).
DATA-SOURCE srcOrder:QUERY = hOrderQuery.

/* Fill the dataset using the new FILL criteria. */
DATASET dsOrderOrderLine:FILL().

FIND FIRST ttOrder NO-ERROR.

IF AVAILABLE (ttOrder) THEN 
  MESSAGE ttOrder.OrderNum ttOrder.ShipDate VIEW-AS ALERT-BOX.

Datasets