Introduction

It’s a common requirement for Business Entities and Data Access classes to limit the available data based on session attributes or security related key values. This may be required for filtering records based on the current login company or filtering the customer records a certain user is allowed to retrieve from the database based on the Country or Customer Category or a list of Countries or customer Categories or both.

In the SmartComponent Library DataAccess classes the DsQueryString implementations are responsible for manipulating the data access query when retrieving data from a Business Entity.

Developers can override the NewDSQueryString method in the DataAccess class that returns a querystring instance. The SmartComponentLibrary uses this object instance to manipulate the query the on the database and populate your dataset with result data.

In this method developers can return a customized instance as required. For the above mentioned purpose we have implemented a new class FilteredDSQueryString specialized to handle the query manipulation required for those use cases in a standardized way.

Sample implementation

Here’s an example about how to implement this feature in a specific data access class implementation.

METHOD PROTECTED OVERRIDE IDSQueryString NewDSQueryString (pcQuery AS CHARACTER, 
                                                           pcBuffer AS CHARACTER, 
                                                           plFilterUsingInnerJoin AS LOGICAL):

    DEFINE VARIABLE oFilteredQueryString AS FilteredDsQueryString NO-UNDO .
    DEFINE VARIABLE oLoginCompanies      AS CharacterList         NO-UNDO .

    oLoginCompanies = NEW CharacterList() .

    /* add one or more values to the list 
       multiple values will be added with an OR to the query  */
    oLoginCompanies:Add (SessionManager:LoginCompanyKey1) .
    oLoginCompanies:Add (SessionManager:LoginCompanyKey2) .
    oLoginCompanies:Add (SessionManager:LoginCompanyKey3) .

    oFilteredQueryString = NEW FilteredDsQueryString (pcQuery,
                                                      THIS-OBJECT,
                                                      pcBuffer,
                                                      plFilterUsingInnerJoin) .

    /* now add the filter to the temp-table of your dataset 
       and tell for which field in the datasource the restriction
       applies. */
    oFilteredQueryString:AddFilter ("eTable1":U, "Table.Field":U, oLoginCompanies) .
    oFilteredQueryString:AddFilter ("eTable2":U, "Table.Field":U, oLoginCompanies) .

    RETURN oFilteredQueryString.

END METHOD.

The default query as defined in the BusinessEntityDesigner will be respected . For example

In the corresponding data access class two default filter option are added on Salesrep:

oSalesreps = NEW CharacterList ().

oSalesreps:Add ("BBB":U) .
oSalesreps:Add ("HXM":U) .

oFilteredQueryString = NEW FilteredDsQueryString (pcQuery,
                                                  THIS-OBJECT,
                                                  pcBuffer,
                                                  plFilterUsingInnerJoin) .

oFilteredQueryString:AddFilter ("eCustomer":U, "Customer.Salesrep":U, oSalesreps) .

The following query is used during execution: