Consultor Eletrônico

Status: Unverified

GOAL:

How to manipulate Dynamics queries

GOAL:

How to manipulate the query of a Dynamic SDO with the available functions and API calls.

FACT(s) (Environment):

Dynamics

FIX:

There are several different ways that Dynamics SDO queries can be changed using API's and function calls. Which method
is chosen depends on the result that is required, the property that needs to be set, and whether the new query should
be preserved.

To begin with the QueryString property is the primary query property. When opening the SDO query, QueryString is used.
If QueryString is blank, then the QueryWhere property is used, and if QueryWhere is blank the BaseQuery property is used.
The following is a list of API's that can be used to manipulate the dynamic SDO query. The SDO used in the examples
below has a design time base query of:


FOR EACH Order NO-LOCK,
EACH Customer OF Order NO-LOCK,
EACH Salesrep OF Order NO-LOCKThe API's are being executed from the super procedure of a dynamic viewer having obtained the handle of the source SDO.
But these could also be used in an InitializeObject override in the SDO Data Logic Procedure.


setQueryWhere - Updates the QueryWhere property and blanks the QueryString property. Passing in a blank parameter value to
setQueryWhere results in assigning the property equal to the baseQuery property.
DYNAMIC-FUNCTION('setQueryWhere':U IN h_SDO,
"Customer.Name BEGINS 'B'":U).


Results in a QueryWhere property of:
FOR EACH Order NO-LOCK,
EACH Customer OF Order WHERE (Customer.name begins 'B') NO-LOCK,
EACH Salesrep OF Order NO-LOCK


setBaseQuery - Updates the BaseQuery property
DYNAMIC-FUNCTION('setBaseQuery':U IN h_SDO,
"FOR EACH order NO-LOCK":U).


Results in a baseQuery property of 'FOR EACH order NO-LOCK'.


addQueryWhere - Update QueryString and QueryWhere properties.
If the following two calls to addQueryWhere were made:
DYNAMIC-FUNCTION('addQueryWhere' IN h_SDO,
"customer.name begins 'B'",
'customer',
'').

DYNAMIC-FUNCTION('addQueryWhere' IN h_SDO,
"customer.name begins 'C'",
'customer',
'OR').


Results in QueryString and QueryWhere properties of:
FOR EACH Order NO-LOCK,
EACH Customer OF Order WHERE
(customer.name begins 'B') OR (customer.name begins 'C') NO-LOCK,
EACH Salesrep OF Order NO-LOCK


The same result can also be achieved with:
DYNAMIC-FUNCTION('addQueryWhere' IN h_SDO,
"Customer.name begins 'B' OR Customer.name begins 'C'","","").


setQuerySort - Updates the QueryString, QueryWhere and QuerySort properties.
DYNAMIC-FUNCTION('setQuerySort' IN h_SDO,
'Customer.Address':U).


Results in QueryString and QueryWhere properties of:
FOR EACH Order NO-LOCK,
EACH Customer OF Order NO-LOCK,
EACH Salesrep OF Order NO-LOCK BY Customer.Address


and QuerySort property of 'Customer.Address'.


assignQuerySelection - Updates the QueryString, QueryWhere and QueryColumns properties.
If the following two calls to assignQuerySelection were made:
DYNAMIC-FUNCTION('assignQuerySelection' IN h_SDO,
'customer.name,customer.custnum','B' + CHR(1) + '1473', 'BEGINS,=').


DYNAMIC-FUNCTION('assignQuerySelection' IN h_SDO,
'customer.salesrep','BBB', '=').


Results in QueryString and QueryWhere properties of:
FOR EACH Order NO-LOCK,
EACH Customer OF Order WHERE
(customer.name BEGINS 'B' AND customer.custnum = '1473') AND
(customer.salesrep = 'BBB') NO-LOCK,
EACH Salesrep OF Order NO-LOCK


and a QueryColumns property of 'Customer:name.BEGINS,46,3,custnum.=,73,6,salesrep.=,106,5'.


removeQuerySelection - Is designed to remove query selection criteria added by assignQuerySelection. Updates the
QueryString, QueryWhere and QueryColumns properties.
DYNAMIC-FUNCTI.ON('removeQuerySelection' IN h_SDO,
'Customer.name,customer.custnum,customer.salesrep', 'BEGINS,=,=').


When applied to the above query modified by assignQuerySelection, results in QueryString and QueryWhere properties of:
FOR EACH Order NO-LOCK,
EACH Customer OF Order WHERE

EACH Salesrep OF Order NO-LOCK


and a blank QueryColumns property.


.