Calculated Field

In ADITO, an EntityField that is not directly related to a specific database column is called "calculated field". For instance, "age" could be a calculated field, as you can calculate it from the EntityField "DATEOFBIRTH".

There are 2 different ways to calculate an EntityField:

1. In the RecordContainer.
2. In the EntityField.

RecordContainer

In the RecordContainer, there are 2 RecordFieldMappings per EntityField: value and displayValue. Each of them has got a property named expression. The result of this process will be added to the SELECT clause of the loading SQL, surrounded by parentheses. Usally, it will be a subselect:

let subSelect = newSelect("ANOTHERTABLE.MYCOLUMN")
    .from("ANOTHERTABLE")
    .where("ANOTHERTABLE.MYTABLE_ID = MYTABLE.MYTABLEID");
result.string(subSelect);

Example of expression process.

This will result in a loading SQL that will look like this:

select COLUMN1, COLUMN2, COLUMN3, (<expression>), COLUMN4...
from MYTABLE

-> for the above example:

select MYTABLEID, COLUMN2, COLUMN3, COLUMN4,
 (
 select ANOTHERTABLE.MYCOLUMN
 from ANOTHERTABLE
 where ANOTHERTABLE.MYTABLE_ID = MYTABLE.MYTABLEID
 ),
 COLUMN5...
from MYTABLE

important

To avoid attacks via "SQL injection", always use the SqlBuilder to create your subselect.

EntityField

An EntityField has 2 properties for calculating values: valueProcess and displayValueProcess. The result of these JDito processes will then be directly used as value/displayValue of the EntityField.

warning

The valueProcess/displayValueProcess is executed after the loading SQL has been executed, separately for every single dataset (row). Therefore, these processes need to be handled with great care, as they can lead to severe performance issues - especially, if their code is complex and a lot of datasets are involved. If possible, always try to calculate the value/displayValue via the RecordContainer (see above).

When to use which?

Generally, the calculation via the RecordContainer will show the better performance (see above). If code is present both in the expression and in the valueProcess/displayValueProcess, ADITO automatically prefers the calculation via expression - except for one single case: If the user is about to create a new dataset (via an EditView), the value/displayValue cannot be retrieved via expression, as the dataset is not yet stored in the database. Therefore, in this case, a valueProcess/displayValueProcess is required - e.g., to preset a value, or to calculate the value of a (readonly) field that depends on the selection of another field (e.g., the age could be calculated, after the date of birth has been entered). As then only one single dataset is involved, performance issues are not to be expected.

Usually, a valueProcess/displayValueProcess should include a suitable condition that ensures that the calculation is only executed when a new dataset is to be created and the field has not yet a value:

import { neon, result, vars } from "@aditosoftware/jdito-types";

if(vars.get("$this.value") == null && neon.OPERATINGSTATE_NEW == vars.get("$sys.recordstate"))
{
    <calculation>
    result.string(<calculated value>);
}

Pattern for valueProcess/displayValueProcess

To sum up, in practice, you should proceed like this:

1. Always try to calculate the value via SQL code in the expression.
2. Only use the valueProcess/displayValueProcess, if 1. is not possible or if you need the calculation also in OPERATINGSTATE_NEW. In the latter case, insert a suitable condition that prevents the execution of the process in any other operating state.

Preselection

As an example for the above, the valueProcess can be used for preselecting (initializing) the value of an EntityField in an EditView - e.g., with the current date and time for a specific EntityField of contentType DATE:

import { datetime, neon, result, vars } from "@aditosoftware/jdito-types";

if(vars.get("$this.value") == null && neon.OPERATINGSTATE_NEW == vars.get("$sys.recordstate"))
{
    result.string(datetime.date());
}

XXX_entity.MYDATE.valueProcess