Filtering on calculated fields

Use-case: A calculated field (ePatKost.Kostnumkurz) was used to reformat a DB field (ePatKost.kost_num) in the business entity temp-table. Users demand to filter on this calculated field as it was just used to reformat a database field. We need to check the query string for the calculated field and use the provided for the calculated field and filter on a database field.

The default behavior of the data access classes is to ignore calculated fields in the selection portion of the provided query string (calculated fields in the sort portion of the query string cause a runtime error). This allows developers to use the query string to provide customizable selection behavior from the client.

The entry point is an override to the method FetchData in the actual Data Access class (child class of DataAccess):

Override for method FetchData

METHOD OVERRIDE PUBLIC VOID FetchData (poFetchDataRequest AS Consultingwerk.OERA.IFetchDataRequest):
 
    DEFINE VARIABLE cQueryString AS CHARACTER NO-UNDO.
      
    ASSIGN cQueryString = poFetchDataRequest:Queries 
 
           /* Call into method ManipulateQueryString */
              cQueryString = THIS-OBJECT:ManipulateQueryString(cQueryString)
           
           /* Assign potentially new Query String to the Queries property.
              As the IFetchDataRequest interface defines the property as 
              read-only, we need to CAST to the FetchDataRequest class. */
              CAST (poFetchDataRequest, Consultingwerk.OERA.FetchDataRequest):Queries = cQueryString .
       
    /* Call into the super method in the Data Access object to fetch data from DB */
    SUPER:FetchData (poFetchDataRequest).
 
END METHOD.

The method ManipulateQueryString in the Data Access classes uses the QueryParser to parse the Query String. The Query Parser returns an IQueryExpression instance (typically a ListQueryExpression instance). The static method GetExpressionForField of the QueryExpression class can be used to return the query expression (ePatKost.KostnumKurz = "value").

When this query expression is valid, the second operand returns a CharacterHolder with the filter value for the field ePatKost.KostnumKurz. As in this sample we simply need to add a constant value to the query value and change the name of the filter in the query expression.

We return the query string from this method. Therefor we return the (modified) expression of the oQueryExpression instance. We also replace the calculated field in the BY clause with the related DB field.

Method ManipulateQueryString

METHOD PROTECTED CHARACTER ManipulateQueryString (pcQueryString AS CHARACTER):
       
    DEFINE VARIABLE oQueryParser AS Consultingwerk.QueryParser NO-UNDO . 
    DEFINE VARIABLE oQueryExpression AS Consultingwerk.IQueryExpression NO-UNDO . 
    DEFINE VARIABLE oFieldExpression AS Consultingwerk.QueryExpression NO-UNDO . 
                
    oQueryParser = NEW Consultingwerk.QueryParser () .
    oQueryExpression = oQueryParser:ParseQueryString (pcQueryString) .
    oFieldExpression = Consultingwerk.QueryExpression:GetExpressionForField ("ePatKost.Kostnumkurz", 
                                                                             oQueryExpression) .                    
                
    IF VALID-OBJECT (oFieldExpression) THEN DO:
            
        CAST (oFieldExpression:FirstOperand, Consultingwerk.BufferFieldName):FieldName = "kost_num" .
        CAST (oFieldExpression:SecondOperand, Consultingwerk.CharacterHolder):Value = "0214" + CAST (oFieldExpression:SecondOperand, Consultingwerk.CharacterHolder):Value .  
            
        RETURN "FOR EACH ePatKost " + 
               oQueryExpression:GetExpression() +
               REPLACE (oQueryParser:ByClause, " BY ePatKost.Kostnumkurz", " BY ePatKost.kost_num") /* substitute the calc field in the BY clause */.  
            
    END.
    RETURN REPLACE (pcQueryString, " BY ePatKost.Kostnumkurz", " BY ePatKost.kost_num") /* substitute the calc field in the BY clause */.  
        
END METHOD.