Robin Lamb, Director of Informer Client Services, discusses helpful tips on using Custom Field Expressions to enhance your Informer 5 experience.
On occasion, database-level functions need to process data fields before those fields are ready for use in reporting tools. For example, referencing a TRANS subroutine to pull Fields from one file into another on U2 databases or a SUBSTRING function on a SQL database to extract the ID of a multi-part key field. Many of these subroutine or function fields are ready and available on databases but there are times when they are not.
With Custom Field Expressions, it’s easy to create these subroutine and function fields inside of Informer 5 without having to touch the underlying database.
For instance, below is an example of creating a year field from a stored database date field. To create a Custom Field Expression, follow the steps below:
- Go to Mappings, and then choose the Mapping where the date field lives
- Click on Add Field
- Name the Field (no spaces or special characters)
- Choose the return type
- Add the expression
- When entering the expression, use the Insert Keyword Icon to assist with the expression language
Once you have entered the expression, use the Test Expression (checkmark Icon) to test the results.
Another example using this feature is creating a value based on a parameter of another field. In the example below, the code is creating a value for a product but this could be translating to any if/then type of statement.
CASE WHEN ${MAPPING_ALIAS}.product = 'productA' OR ${MAPPING_ALIAS}.product = 'productB' THEN .25
WHEN ${MAPPING_ALIAS}.product = 'productC' OR ${MAPPING_ALIAS}.product = 'productD' THEN .50
WHEN ${MAPPING_ALIAS}.product = 'productE' THEN .4
WHEN ${MAPPING_ALIAS}.product = 'productF 'THEN 1.5
ELSE 0 ENDThere are many ways to use this feature to enhance the Informer 5 functionality. Other examples include creating aggregate fields, formatting output of data, concatenating fields together, and many more.
