Advice needed - Handling blank form fields in a world without NULLs

Advice needed - Handling blank form fields in a world without NULLs

One of our common needs is the creation of "Case Report Forms" (CRFs) for clinical research. A CRF is basically a form used to collect data about patients (demographics, clinical measurements, etc.)

Many of our CRFs include numeric/decimal and date fields that are optional. What we're finding is that because OutSystems does not directly support the concept of NULL data (see "About Null Values" in Help) whenever a user leaves a field blank, OutSystems is instead storing actual data in these fields (e.g., 0 for numerics and 01/01/1901 for dates). I have serious concerns with this approach of [not] handling of NULL values and I'm hoping someone here has some helpful advice based on their experiences.

In our business (and others I imagine), when a user leaves a field blank this is important information in itself as it indicates that there was no measurement taken/captured/known. For a research study, for example, capturing height of "0" for blank values massively confuses any attempts to statistically analyze the data. For comparison, in the underlying relational database, NULL values would be conveniently and properly excluded from statistical aggregates like AVERAGE(...).

Workarounds seem to be of one of the following two classes:

1. "Magic Value Workaround" - Define a "magic value" for each field that represents the NULL/blank (e.g., for "height" this could be -1) and configure that as the field's Null Value in OutSystems. Then additional programming would be done at display-time to translate -1 to "".

My concerns here are:
1.1 - This is a significant amount of effort given that any field could be displayed in a variety of pages/reports/etc.
1.2 - The "magic value" needs to be chosen carefully since, for example, -1 isn't a good "magic value" for a height_change field since the range of valid values there includes negatives
1.3 - The user will still see the "magic value" when they edit the field, causing confusion and fears that the data is corrupt (height of -1???)
1.4 - Attempts to export the data to Excel (etc.) using OutSystems will also show the "magic values" instead of blanks

2. "Not Supplied Field Workaround" - For each field, create an additional field that tracks whether that field is truly empty/blank or not. Additional programming would then be needed to set/reset these fields appropriately based on examining the raw character-level input provided by the user into the screen widgets. In addition, all of the display-time and edit-time issues from the "Magic Values Workaround" are in play.

I'm really hoping that I've missed something and that OutSystems can indeed support the concept of blank data out-of-box. This has proven to be a significant challenge for our efforts so I'm hoping someone has a good magic bullet here. :-)

Thanks in advance for your thoughts/suggestions!

Eric Kramer, Nationwide Children's Hopsital
Columbus, Ohio
Hi Eric!

OutSystems made a thoroughly thought-out design decision to exclude null values from its world, as you put it. The rationale behind it was to soften the learning curve of the platform to a wider demographics of users (i.e. not only veteran programmers) but also to simplify its application design logic for a great majority of use cases.

To implement something like what you are suggesting, however, I'd say that the easiest way to do it would be indeed one of the two that you suggested, but encapsulating the read/write code into specific actions that read and write the data in the format you want.

There's also the null value that you can set for inputs, in order to facilitate the rendering of the data that you read. You can read about the "Null Value" property in the "Web Input Widget Properties" page ( in the Service Studio online help. Furthermore, if you use either method you mention, I'd say that the statistical treatment of the data would be easier - using a query that only considers values greater or equal to zero, or one that only considers values that have indeed been filled.

Please let us know if you can think of anything else to handle this - or if this still makes it very unpractical for you to handle the data.

I'm really sorry for not having a magic bullet here - but with Service Studio, we already tried to provide you with a magic gun ;)

Best regards,

Paulo Tavares
Hi Eric

From the two approaches you are analyzing, I have typically recommended approach 2 - not supplied field workaround, together with encapsulation API. Meaning that:
  • Typically you will want to create structures with text values to be used in all input pages;
  • You will have actions to convert structure -> entity and entity->structure and possibly direct actions GetToStructure e Create/UpdatefromStructure;
In 5.0, you can also take advantage of one feature - read-only public entities - to ensure that, in case you want, no write is made without using your API. To do this, you would simply create an eSpace to contain entities, make them public-read-only and use the API in all other eSpaces.


Hi Eric,

I think I may have a third option for you.

For your CRF you have for sure a set of question to which you want to get an answer. So instead of having an entity with all the questions representing a CRF you could have an entity representing a quesiton posed in the CRF. In your interface you would have a list of questions instead of an edit record representing the form.

Then you model the answers to those questions as an entity. This way if the users don't answer the question no record is created in the answer entity and you're aggregates provide you with the correct values.

This is actually the base architecture of Inquiries, Surveys and Appraisals that you can find on the Components section.