Custom Expressions can be applied to columns of data in reports to change the appearance of the data. A common use for custom expressions is to change text values into numeric values so they can be used in calculations. This allows you to change the field options that were captured on a form into report data that reflects changes or requirements based on funding. This is a premium, add-on feature and the Ongoing Support Team does not troubleshoot any custom expressions, related errors, or consultations with these; however, you may purchase consultation time with our Professional Services team who would be able to assist with Custom Expressions and related issues.
Is "Custom Expressions" Turned On?
- To find out if you have access to Custom Expressions, select the Administrator tab.
- Select Apricot Settings from the left hand menu.
- Under "A La Carte Features," check to see that Custom Report Expressions has been enabled. If you need access to this feature and it has not been turned on, please contact the Customer Care team.
Accessing Custom Expressions
- To add the Custom Expressions feature to a report, open an existing report for editing and open the column gear box where you would like to change the appearance of your data with a custom expression.
- Select either "Custom Numeric" or "Custom Text."
Custom Numeric Expressions
Custom Numeric Expressions can be applied to an entire column of data that contains either numeric or text values. When a custom expression has been correctly applied, the resulting "custom" data will be changed into numeric values. A common use for this kind of custom expression is to change text values into numeric values.
One example is changing a "no" into a 0 and a "yes" into a 1. To achieve this, click into the text box under "Custom Numeric," delete what is in the box and type in: IF([column] = 'no', 0, 1)
When these columns have been successfully transformed with a custom expression, they can be used like any other numeric field - in their display options, their summary options, and in calculations. In the example above, we can use the summary option "total" to find the number of clients who have filled out an intake survey.
Custom Text Expressions
Custom Text Expressions can be applied to an entire column of data that contains either numeric or text values. When a custom expression has been correctly applied, the resulting "custom" data will be changed into values that will be treated like text. A common use for this kind of custom expression is to change text values as they have been recorded in the database because a specific funder or a specific reporting requirement asks for slightly different wording, like changing race or ethnicity categories to satisfy state or federal reporting standards.
To change text values from one thing to another, click into the text box under "Custom Numeric," delete what is in the box and type in: IF([column] = 'Latino', 'Hispanic')
When these columns have been successfully transformed with a custom expression, they can be used like any other text field - in their display options and their summary options.
Custom Expressions follow a strict syntax so even a small error - like a missing closed parenthesis or an extra comma - can return an error message, as in the example above.
When this happens, open the column gear box, select the custom expression, cut and paste it into a blank text document where you can review the string for errors. Once the custom expression has been removed, the error message will disappear and your report will work as it did before you added the custom expression. Then you can correct your custom expression and paste it back into the text box for the Custom Numeric expression (in the column gear box) when it is ready.