Display strings and null values

Sunday 5 August 2012 @ 4:05 pm

I read a forum discussion where someone was asking how they could have all the null values on the report print ‘n/a’. They were faced with having to write a formula field for every field on the report. I suggested that they could use the Display String property that is available when you format a field. The Display String allows you to override the value that will print on the report. The advantage is that you can use the function CurrentFieldValue within the condition formula and this makes it easy to apply to lots of fields at once.  I suggested that they use a Display String formula that was something like this:

If CurrentFieldValue = ”
then ‘n/a’
else CurrentFieldValue

My plan was to convert the nulls to default values inside the formula so that all nulls would be treated as an empty string [“”]. Then this format could be quickly transferred to all the other fields using the format painter.  I thought it was a great plan.

However, there was a snag. A field with a null value won’t show a Display String, no matter what you do. To me this seems like a bug or at least an inconsistency in the Display String logic. To be sure I put in a hard coded Display String of ‘X’ on a field.  All values in the column printed an “X” except for the null value records.  None of the ‘convert null’ settings had any effect.

On a related note, I also tested the functionality of the CurrentFieldValue function in the Hyperlink property formula. I found that when a field is mentioned by name in that formula it can be converted to null using the “Convert Null to Default” settings.  So a hyperlink can be generated even when nothing is printing in the field.  But if you try to use the CurrentFieldValue function, like my example above, the formula will not be able to convert the null value.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro