Summary


Computed fields can be used to calculate any number of time measurements. There is always a target field. That is the field that we create our computed field conditional action on. The computed field conditional action will always contain a formula. It can optionally contain a condition. That condition will execute the formula if it is true. If no condition is supplied, then the formula will always be executed upon save, regardless of any previously calculated or entered value. 

Computed fields can be tested in the Preview window of the Form Builder. This allows the designer to test all computations right from the form builder (assuming no external values are included in the formula).


Computed fields display a calculator icon next to or below the field. That icon, when clicked/tapped, will evaluate the condition and, if true, will execute the formula. 

All computed fields will also be executed each time a form is saved.

Table of Contents


The following lists all the supported functions within Datatrak Direct Computed Fields and the required format to call the function:


Single Parameter Functions:

  • SQR: Square function which can be used as SQR(X)
  • SIN: Sinus function which can be used as SIN(X), X is a real-type expression. Sin returns the sine of the angle X in radians.
  • COS: Cosinus function which can be used as COS(X), X is a real-type expression. COS returns the cosine of the angle X in radians.
  • ATAN: ArcTangent function which can be used as ATAN(X)
  • SINH: Sinus Hyperbolic function which can be used as SINH(X)
  • COSH: Cosinus Hyperbolic function which can be used as COSH(X)
  • COTAN: which can be used as COTAN(X)
  • TAN: which can be used as TAN(X)
  • EXP: The exponential functionexp(x), calculates the value of e to the power of x, where e is the base of the natural logarithm, 2.718281828.   Can be used as EXP(X)
  • LN: natural log, which can be used as LN(X)
  • LOG: 10 based log, which can be used as LOG(X)
  • SQRT: which can be used as SQRT(X)
  • ABS: absolute value, which can be used as ABS(X)
  • SIGN: SIGN(X) returns -1 if X<0; +1 if X>0, 0 if X=0; it can be used as SQR(X)
  • TRUNC: Discards the fractional part of a number. e.g. TRUNC(-3.2) is -3, TRUNC(3.2) is 3.
  • CEIL: CEIL(-3.2) = 3, CEIL(3.2) = 4
  • FLOOR: FLOOR(-3.2) = -4, FLOOR(3.2) = 3

 

Functions That Take Two Parameters:

  • INTPOW: The INTPOW function raises Base to an integral power. INTPOW(2, 3) = 8. Note that result of INTPOW(2, 3.4) = 8 as well.
  • POW: The Power function raises Base to any power. For fractional exponents or exponents greater than MaxInt, the Base must be greater than 0.
  • LOGN: The LogN function returns the log base N of X. Example: LOGN(10, 100) = 2
  • MIN: MIN(2, 3) is 2.
  • MAX: MAX(2, 3) is 3


Functions That Take No Parameters:

  • RND: RND() function generates a random number (double value) between 0 and 1. 


Functions That Take Unlimited Parameters:

  • SUM: SUM(2,3,5,...) functions return the sum of its arguments. There is no preset limit on the number of parameters. 

    

Branching Functions:

  • IF: The IF(b, case1, case2) function provides branching capability. If b is not 0, then it returns case1, else it returns case2. Behavior is similar to C#'s: return b? case1 : case2;
  • If b==0 then case1 will not be Evaluated, and vice versa. Example: IF(HEIGHT, 3/HEIGHT, 3) will make sure 3/HEIGHT does not cause division by zero.

Date Granularity

Date Granularity determines the level of detail you want on your date/time computations. The more detail (granularity), the more converting you will have to do on your formulas. The rules of date/time formulas, however, are pretty straight forward. Datatrak Direct will convert all date values to the granularity unit. It assumes that all numbers entered in are in the proper unit or are converting to another unit for display. Regardless of which granularity level you define, you can still calculate to a lower unit. 

For example, if you define seconds as your granularity unit, you can calculate and display days and years. However, if you use days as your granularity unit, you cannot calculate to seconds, minutes or hours.


As mentioned earlier there are four levels of granularity that can be used when computing dates. These levels are set at Form/Page Properties in the Data section of the Form Builder application.


The 4 levels of granularity include:

1. Second

2. Minute

3. Hour

4. Day (No time calculations)


When Datatrak Direct evaluates date/time formulas, it takes defined fields, retrieves their values and converts them to time since 01-01-0001. Depending on the selected granularity, that value is expressed differently. 

For example, if you use the granularity Hour, the date is expressed numerically as hours since 01-01-0001. Therefore, if you are finding the difference between two dates and you want to express that in hours, you can simply subtract the two dates, it will return hours. However, if you wanted to express that difference in days, you would want to divide that number by 24.


One other note about form-based Date Granularity. If you are using external fields in your calculation, and that external field is of type Date/Time, it will use the granularity of the form where the computation is defined and not the form where the field was originally created.


Computation Examples:


Example 1: Calculate Age using a granularity of hour

To calculate a subject’s age the basic formula is (Assuming day granularity):


(CurrentDate – DateOfBirth) / 365.25


 However, we are using hour granularity, therefore we must add the following to the formula:


(CurrentDate – DateOfBirth) / 24 / 365.25


The difference between the Current Date and Date of Birth will be calculated and expressed in hours. However, we do not want age in hours, we want age in years. Therefore we must convert age in hours to age in years. We do this by dividing the age in hours by 24 and then dividing that by 365.25. That gives us the age in years. The following shows the formula in Datatrak Direct. DM_DATE2 is the CurrentDate and DM_DATE1 is the DateofBirth.

Note: with the example of age, it is typically desired as a rounded value to the lowest whole value. The system will not automatically round down on computations, so for age specifically, it is suggested to use a function such as FLOOR within the formula.


( FLOOR (( CurrentDate-Date ofBirth) / 365.25 ))


Example 2: Calculate Elapsed Time in Minutes

To achieve this we will need to define our form to use Minute granularity. Remember you cannot convert up the granularity scale (hours to minutes).

First, you will need two-time fields.


In this example, the two fields are Date/Time fields defined using Time Only with Seconds. Next you will need to create a number field to display and store the elapsed time. If using this method, be sure to set the appropriate maximum length and desired decimal places on the number field.


The last step is to create the formula. It should look like this:


Example 3: Computing an Anniversary Date

You can also compute date fields. Date fields are computed by taking an existing date field and adding a number to that date field. As with all date computations, the number is assumed to be in the form’s Date Granularity time unit. So calculating a 30-day anniversary date from the Procedure Date, would mean just adding 30 to the Date Of Procedure field. That, of course, assumes that the date granularity for the form is days. If not, you must convert whatever the time unit is to days. So if it is hours, you would simply add 30 days’ worth of hours (24 * 30).


We want to calculate a 30-day anniversary date. However, our form time unit is minutes. First, we must have a date field to calculate the anniversary date from.


Next, we need to create our computed field formula. Notice we are converting 30 days to minutes.


Next, we need to have our anniversary date field. By clicking the compute icon our anniversary date is calculated.


Example 4: Compute an Estimated Time

Computing times is the same as computing dates. You have to understand your date granularity unit. Then simply add a time number to a time field. Let’s calculate a time, first we need two-time fields; one to calculate from the other to hold the computed time:


Next, let’s add 2 hours to Time 1. Remember, we are using seconds as our date granularity so we must convert hours to seconds:


Because we are using seconds for our date granularity, we can now calculate a time accurate to the second.


Need more help?

Please visit the Datatrak Contact Information page.