Using the CalculatedFieldHelper
Introduction
The Consultingwerk.OERA.CalculatedFieldHelper can be used to get the values for calculated fields that are concatenated values of other temp-table fields and modify the query string to sort and filter by the calculated field.
Concatenate field values GetConcatenatedFieldValue
The method Consultingwerk.OERA.CalculatedFieldHelper:GetConcatenatedFieldValue can be used to get a list of different source (temp-table) fields. This would commonly be done in the ReceiveData method of a BusinessEntity.
Parameters
It expects the following parameters:
Name | Type | Purpose |
|---|
Name | Type | Purpose |
|---|---|---|
phBuffer | handle | This parameter expects the handle of the temp-table buffer. It is used to read the values of the source fields from which the calculated field’s value is constructed. |
pcSourceFields | character | This parameter expects the list of source fields from which the calculated field is constructed. They are expected in the order that they should be in the calculated field and separated by |
pcDelimiter | character | This parameter expects the delimiter to be used for the calculated field. When |
plSkipEmpties | logical | This parameter can be used to control whether empty entries should be added ( |
Return value
This method returns the value for the calculated field, it is not assigned to the temp-table buffer.
Examples
The following examples would be called from within a for each over the temp-table in a BusinessEntities ReceiveData method.
Constructing a qualified database table name
The TableBusinessEntity uses the CalculatedFieldHelper to construct a qualified table name from its fields DatabaseName and TableName with . as its delimiter.
eSmartTable.QualifiedTableName = CalculatedFieldHelper:GetConcatenatedFieldValue (buffer eSmartTable:handle,
"DatabaseName,TableName":U,
".":U,
false) .Combining City and Country
If a BusinessEntity for the table Sports2000.Customer has a field (e.g. CityCountry) that should have values formatted as <City> / <Country>, the CalculatedFieldHelper can be used like this to generate the values.
eCustomer.CityCountry = CalculatedFieldHelper:GetConcatenatedFieldValue (buffer eCustomer:handle,
"City,Country":U,
"/":U,
false) .Full Employee name
To have a combined name (<FirstName> <LastName>) of an employee as a calculated field FullName, the CalculatedFieldHelper can be used like this:
eEmployee.FullName = CalculatedFieldHelper:GetConcatenatedFieldValue (buffer eEmployee:handle,
"FirstName,LastName":U,
" ":U,
false) .Modify query string FilterByConcatenatedValue
While concatenating field values could also be done through a simple substitute call or string concatenation, modifying the query string to allow sorting and filtering by the calculated fields source fields could be more complex. The method CalculatedFieldHelper:FilterByConcatenatedValue can be used to update a query string in a way that replaces the calculated field with its source fields in both the where and by parts of the query string. This would commonly be done in the CustomizeFetchDataRequest method in a DataAccess class. Unlike the BusinessEntity:ReceiveData method in the previous chapter, this would require an override of the method.
Parameters
It expects the following parameters
Name | Type | Purpose |
|---|
Name | Type | Purpose |
|---|---|---|
pcQueryString | character | A query string that sorts or filters by a calculated field of concatenated values. |
pcTableName | character | The name of the temp-table that has the calculated field. |
pcCalculatedFieldName | character | The name of the calculated field (without the table prefix). |
pcTargetFields | character | A comma separated list of the temp-table fields that the calculated field was constructed from (without the table prefix). |
pcDelimiter | character | The delimiter used between the different concatenated field values. |
Return Value
The method returns the modified query string (as character) that now does not filter/sort using the calculated field but those fields from which the calculated field was assigned.
Examples
The examples here correlate to those in the GetConcatenatedFieldValue chapter. They would usually be implemented in the CustomizeFetchDataRequest method of a DataAccess class (requires an override). To not needlessly process query strings that don’t contain the calculated field, they include a check for whether the current query contains the calculated field.
Filter/Sort on qualified database table name
This example corresponds to Using the CalculatedFieldHelper | Constructing a qualified database table name and modifies a query string to filter and sort by the DatabaseName and TableName fields of the eSmartTable temp-table that were concatenated with "." as delimiter.
if poFetchDataRequest:Queries > "":u and poFetchDataRequest:Queries matches "*QualifiedTableName*":u then do:
poFetchDataRequest:Queries = CalculatedFieldHelper:FilterByConcatenatedValue (poFetchDataRequest:Queries,
"eSmartTable":U,
"QualifiedTableName":U,
"DatabaseName,TableName":U,
".":U) .
end.Filter/Sort on combined City and Country fields
This example corresponds to Using the CalculatedFieldHelper | Combining City and Country and modifies the query string to filter and sort by the City and Country fields of the eCustomer temp-table that were concatenated with "/" as delimiter.
if poFetchDataRequest:Queries > "":u and poFetchDataRequest:Queries matches "*CityCountry*":u then do:
poFetchDataRequest:Queries = CalculatedFieldHelper:FilterByConcatenatedValue (poFetchDataRequest:Queries,
"eCustomer":U,
"CityCountry":U,
"City,Country":U,
"/":U) .
end.Filter/Sort on full name field
This example corresponds to Using the CalculatedFieldHelper | Full Employee name and modifies the query string to filter and sort by the FirstName and LastName fields of the eEmployee temp-table that were concatenated with “ " as delimiter.
if poFetchDataRequest:Queries > "":u and poFetchDataRequest:Queries matches "*FullName*":u then do:
poFetchDataRequest:Queries = CalculatedFieldHelper:FilterByConcatenatedValue (poFetchDataRequest:Queries,
"eEmployee":U,
"FullName":U,
"FirstName,LastName":U,
" ":U) .
end.