Calculated True/False Flag in a Reporting Function Field
Ever had a situation where thousands of records had many string variables mixed across numerous fields? Some of these are meaningful, and others less so. The thought was to use the report’s function field to set up a True/False flag for the variants needed. (Note: this requires a specific role, and will add a custom field to your table.)
All of these scenarios work on the understanding that 0 is False and 1 is True. (In tests, it is found that you can end up with more than one False or True in the chart or pivot table. The understanding is that it will resolve the value based on proximity to 0 or 1, eg 0.1 is False, but not as False as 0. In a list view, this is not a problem, but two False groups on a chart is annoying!)
The approaches below rely on the operators we have to convert strings to integers, and basic arithmetic to convert integers to 0 or 1.
Use Case 1: String contains anything or nothing
My string field was essentially optionally and manually entered data, and I wanted to flag the ones with data and those without.
glidefunction:divide(coalesce(u_state,0),coalesce(u_state,0))
How this works:
- coalesce returns a string if u_state has anything in it. By providing 0 as the second variable, I ensure I’ll have 0 if u_state is empty.
- By dividing the value, whatever it is, by itself, I will only ever get 1 for every non-zero value, and 0 (smoothing over a div by 0) if the result was 0.
Note: Although this works, it’s not great math. It would be logically cleaner to use position to convert the strings to integers if you care about the purity of the calculation.
Use Case 2: Contains a specific string versus anything else
This time we only needed one value among the dozens entered (eg, “published”).
glidefunction:subtract(1,position(coalesce(u_state,'BLANK'),'published'))
How this works:
- coalesce deals with the optionality of the data. As it returns the first string value, if u_state is empty the result with be ‘BLANK’.
- position confirms the string I need is in the field – any value other than ‘published’ returns 0
- Actually, we wanted the inverse value – imagine the column name was Not Published – so using subtract on the position result (0 for false and 1 for true) from 1 will flip the value
Although unique Use Cases, we hope this helps if you have found yourself in this situation. Additionally, we have a Platform Analytics Academy session that talks in depth about Reporting Function Fields "Tips & Tricks"
