Skip to main content

Forcing Castor to calculate with empty fields in EDC/CDMS

This tutorial covers the functions you can use to calculate with multiple fields, even if one or more of the values are not set. 

The tag '##allowempty##' tells Castor to allow for empty fields to be used in a calculation.

'##allowempty##';

 

Empty fields are normally stored as 'NA' (which means Not Available).

The tag '##setemptytozero##' sets these values to zero, which enables further numerical calculations. The '##setemptytozero##' tag can only be used in combination with the '##allowempty##' tag and will enable the empty values to automatically be set to zero to clean up your calculation.

 

'##setemptytozero##';

 

Note: it is not possible to use the '##allowempty##' and '##setemptytozero##' tags with grid fields.

 

Calculating with empty numerical fields

For instance, we have a calculation field 'Addition' with the calculation template:

'##allowempty##';
{var1}+{var2}+{var3};

 

This calculation template calculates the sum of the three numbers and will result in the following:

 

We need to remove the 'NA'. To ensure the calculation works properly, we have two options. 

  1. Use the '##setemptytozero##' tag 
    '##allowempty##';
'##setemptytozero##';
{var1} + {var2} + {var3};
    The '##setemptytozero##' tag will enable the empty values to automatically be set to zero to clean up your calculation.
  2. Use if statements to confirm that a value has been set.
'##allowempty##';
if("{var1}" == "'NA'") {
var1 = 0;
}
if("{var2}" == "'NA'") {
var2 = 0;
}
if("{var3}" == "'NA'") {
var3 = 0;
}
var1 + var2 + var3;

 

Both options will result in the following:

 

Calculating with empty checkbox fields

If you are calculating with one or multiple checkbox fields and would like to allow for them to be empty, you can also use the '##allowempty##' tag before your calculation. For example, you would like to calculate the number of checked boxes across multiple checkbox fields, but you would like to execute the calculation even if one of those checkbox fields has nothing selected. You need to use this tag even if your calculation is simply checking if the field is empty. Otherwise the calculation cannot be performed. 

 

Using single and double quotation marks

Different fields are stored differently in a database, which is why it is important to use single or double quotes in calculation templates. Double quotes around a variable "{date}" are used if there's already a single quote used in a string, as the double quotes denote that it's a string. Common variables where quotes need to be used are date, date and time, year, and text fields.

 

When checking whether a certain field is empty by applying '##allowempty##'; tag generates various 'NA' values depending on the field type - ‘NA’, NA or just an empty field. For example, if a variable has the value 'NA', then in your calculation it should be "{var}" == "'NA'". Since 'NA' already contains single quotation marks, double quotation marks need to be added to denote that the single quotation marks are part of the string.

 

Calculating with User Missing Values

If a calculation contains variables that have been marked as missing within the record, the output will be 'NaN'.

Related articles