Pierre Ghezzi
- Oct 19, 2023
- 10 min read

The formula Field

Updated: Mar 8

A formula is a field that displays a value defined by a custom JavaScript function. The following points will be discussed in this article:

Field options
Writing a formula - accessing submission data
Writing a formula - returning typed values

Field options

Here are the different options you can configure on a formula field.

Type

The formula field is polymorphic, which means that it has a result type, defined as its first option. This type is one of the other data Daxium-Air types supported by the formula field. At the time of writing, the possible returning types are: Text, Number, Boolean, Date, Duration and List. See the Fields section for a definition of these types.

For some of the supported types, you may have options for customising the displaying of the data. Those are the same as the selected Daxium-Air type.

Formula

This editor allows the edition of the JavaScript function, as it will be executed by the different engines in our platforms. The editor has several features to ease the writing of such code:

undo/redo
autocompletion (limited to native JavaScript methods) ;
edition in fullscreen mode or floating window ;
errors detection shown in gutter (only in English).

Calculation rules

The following calculation rules are possible:

One time calculation: as soon as the formula is successfully calculated once, it no longer is re-calculated, by any other event (even if a linked field is modified) ;
Auto refresh: the formula is automatically calculated each time it is relevant (when a linked field is modified, when the form is opened or saved, etc.) ;
Manual: users must click a button for the formula to be calculated.

> Return to top

Writing a formula - accessing submission data

Several rules of writing a formula are to be followed for a successful formula. This first part is focused on the way to read the values of the different fields and meta data of the submission containing the formula.

The JavaScript context of a formula

The code of the formula is wrapped in a JavaScript function which is executed with a list of parameters that are injected by Daxium-Air. One of these parameters is items, which is a JavaScript Object that contains the values of the submission used in the formula. Another is metadata, which contains the submission data not being an inner field value.

One important thing to note here is that to be able to populate properly the items object and limit it to the strict necessary, the formula is parsed to detect the values used by the Object. For example, when you write items['myBoolean'] in a formula, it is interpreted as "inject the myBoolean field value in the items Object" by our engines. This means that you always should use the items['systemName'] syntax to declare a variable in your formula.

The implications of this are that:

the syntax items.systemName is not accepted (i.e. the systemName variable won't be injected in the formula) ;
you cannot write your system name as a dynamic variable.

For example, let's say you have 3 fields systemName1, systemName2, systemName3.

var systemName;
var fieldValue;
for (var i=0; i=2; i++) {
	systemName = 'systemName' + i.toString();
	fieldValue = items[systemName];
	// perform instructions using fieldValue variable
}
return ...

This way of accessing the system names will not work as when the JavaScript interpreter reads the formula, it doesn't understand that the fields systemName1, systemName2, systemName3 are involved in this formula. The system names must be fully written at least once somewhere in the code, even if as comments. The below code would work for instance:

/*
items['systemName1']
items['systemName2']
items['systemName3']
*/
var systemName;
var fieldValue;
for (var i=0; i=2; i++){
	systemName = 'systemName'+i.toString();
	fieldValue = items[systemName];
	// perform instructions using fieldValue variable
}
return ...

> Return to top

Syntactic and configuration considerations

As a field may easily not be filled, it cannot be injected in the items Object. It is considered good practice to check its value with:

if (items['price_tax']) {
	return items['price_tax'] * ( 1 + 20/100);
} else {
	return 0;
}

For the same architectural reasons of how the formulas are built in Daxium-Air, it is not possible to use a line comment operator (//) at the end of the formula (after the closing return keyword). Only use block comments (/* ... */) at this place.

Finally, be careful not to create dependency cycles in your formulas. As a formula may be considered as a genuine Daxium-Air type, it can be injected and used in another formula. But, it is not possible to create a cycle through these formulas, otherwise, it would never end the calculation.

> Return to top

Accessing fields values

Depending on the Daxium-Air type of the items accessed, the format of the values in the items Object is different.

Simple types

The Text, Email, Phone (RFC 3966 Standard), Number and Duration (in milliseconds) fields (and their typed formula variant, if any) are retrieved through the corresponding primitive JavaScript types (String or Number). The items Object with these types would look like:

var items = {
    "textSystemName": "Hello",
    "emailSystemName": "john.doe@company.com",
    "phoneSystemName": "+33123456789",
    "numberSystemName": 68,
    "durationSystemName": 42000,
}

Date

Dates are stored and returned in the Unix timestamp (the number of seconds between the desired date and January 1, 1970 midnight UTC) format:

var items = {
	"dateSystemName": 1466437174
}

By default, dates are processed with your device's system timezone. It is possible to work around this and impose UTC by doing:

var date = new Date();
return (date.getTime() + date.getTimezoneOffset() * 60 * 1000) / 1000;

Boolean

A boolean variable can only have two values : true or false.

var items = {
	"booleanSystemName": true
}

To check the value of a boolean, we ask you to refrain from using the operator:

===

and to use instead:

==

as the first one produces different behaviours according to our platforms (web, Android, iOS).

Moreover, in Daxium-Air, a boolean field (given that it is not required) can also be null. That translates in the back office as "Not defined", or with neither "Yes" nor "No" selected on the mobile.

In a formula, it is possible to check if a boolean field is null by using:

items['booleanSystemName'] == null

Objects and Arrays fields

When writing items['systemName'] of those fields, you will retrieve either an Object or an Array of Objects. You can access any property by using the syntax:

items['systemName'].key

or if it's an Array, where "i" is the position of the element in the Array:

items['systemName'][i].key

For all the fields that are JavaScript objects, it is necessary to check if those fields are not null before assigning them to a variable. When the field is an Array of Objects, you also need to check if the number of elements in the array is not 0.

What NOT to write:

var listField = items['listSystemName'];
if (listField[0].caption == "A text value") {
	...
}

But instead:

if (items['listSystemName'] && items['listSystemName'].length != 0){
	var listField = items['listSystemName'];
}
if (listField[0].caption == "A text value") {
	...
}

Location

A Location field is made of a textual address (e.g.: Cluster R - Jumeirah Lake Towers - Dubai, UAE) and two WGS84 GPS coordinates (e.g.: lat 25.0768995, long 55.1472642). Here is an example of how to return a textual representation of a Location field with system name address:

var result = "";
if (items['address']) {
	if (items['address'].address) {
		result = "Address : " + items['address'].address;
	}
	if (items['address'].lat && items['address'].lng) {
		result = result + "\nlat: " + items['address'].lat; 
		result = result + ", long: " + items['address'].lng;
	}
}
return result;

This outputs:

Address: Cluster R - Jumeirah Lake Towers - Dubai, UAE
lat: 25.0768995, long: 55.1472642

Please note that the Location-typed formulas are not usable on the Android V1 application.

User

A User field can have multiple selected items. The corresponding item in a formula is therefore a JavaScript Array of objects. Such an Object has three self-explanatory String properties:

email ;
first_name ;
last_name.

For example, to retrieve the user's email address, use:

return items['userSystemName'][0]['email'];

To return the concatenated first and last names of a user in a non-multiple user field, use:

if (items['userSystemName'] && items['userSystemName'].length > 0) {
	var user = items['userSystemName'][0];
	return user.first_name + ' ' + user.last_name;
} else {
	return "No user selected";
}

Note that the access to these properties in a Formula field is independent of the User field display option, they are always available.

List

As for the User field, a List field in a Daxium-Air formula is an JavaScript Array of objects. It is defined like (in a case where two list elements are selected in the submission field):

var items = {
	"listSystemName": [ 
		{
			"caption": "My list label 1",
			"external_id": "My external id 1",
			"id": 104839483, // Daxium System ID
			"parent_id": 104839483 // For list with multiple levels
		},
		{
			"caption": "My list label 2",
			"external_id": "My external id 2",
			"id": 104839484, 
			"parent_id": 104839485 
		}
	]
}

You can only retrieve the properties:

caption ;
external_id ;
parents, to access the items hierarchy.

The syntax to retrieve "My list label 1" will then be:

items['listSystemName'][0].caption

To learn how to access a List field hierarchy, let's use an example with this List:

Animal
- Feline
  - Cat (external_id: cat)
  - Tiger
Bird
- Seagull
- Pigeon

To write our formulas, let's say we have selected the Cat and Seagull items in the corresponding field called animal_list.

To get the name of the first selected element:

items['animal_list'][0].caption

returns

Cat

To get the external identifier of the first selected element:

items['animal_list'][0].external_id

returns

cat

To get the root of the List:

items['animal_list'][0].parents[0].caption

returns

Animal

To get the direct parent of the first selected element:

items['animal_list'][0].parents[ items['animal_list'][0].parents.length – 1 ].caption

returns

Feline

To get a list of all the selected choices:

if (items['list_animal']) {
	return items['list_animal'].reduce(function(result, element) {
    	return result + " - " + element.caption + ".";
  }, "");
} else {
	return "No items selected in list.";
}

returns

 - Cat. - Seagull.

When using Daxium Air's multilingual feature, we highly recommend using external IDs in all your lists. Indeed, the caption value changes for each list item you translated, according to the language of the user. So checking if a caption is equal to a particular value can yield 2 different results according to the language of the user.

Relation

A Relation field is an Array of Submission objects. If the option "Multiple" is not selected on the field, the Array will always contain only one element. If the option "Countable" is selected, an additional count Number property is present.

To access the data contained on such a Relation field, you should use the relations Object that is injected in the items Object. It contains two properties:

children, which contains all the submissions related to the current submission through the Relation fields it contains ;
parents, which contains all the submissions related through a Relation field contained by another form.

It is important to note that the position of a submission in the Array is not defined by anything, and is not relative to the creation date of the child submission. Therefore, the assumption that relations.children.system_name.submissions[0] is the first child created submission is wrong.

If the relation field is in the current form with the system name ‘system_name’, you can access the related submissions with:

relations.children.system_name.submissions

So to retrieve a field named title:

relations.children.system_name.submissions[0].items['title'];

If the relation field is in a parent form, the syntax is similar, but using parents instead of children:

relations.parents.system_name.submissions[0].items['title'];

To list all the titles of the records that have established a relationship to the current submission through the relation Field relationSystemName (each must therefore have a title Text field):

if (relations.parents.relationSystemName && relations.parents.relationSystemName.submissions) {
	return relations.parents.relationSystemName.submissions.reduce(
		function(result,element) {
		    return result + " - " + element.items['title'] + ".";
  		}, ""
	);
} else {
	return "No parent record found";}

Countable Relation fields

A Relation field is said to be countable when the option of the same name is activated on it. This adds some attributes to each related submission that can be exploited in formulas.

To access such attributes, you must use the relationshipProperties Object of the related relation. Therefore, if the relation has the system name products and we look for the associated quantity (parameter count), this gives:

relations.children.product.submissions.relationProperties.count

Practical case: the parent form is an order with the products related through a countable Relation field (system name: product). The price of each product appears in the price Number field some products. To get the total price of my order, you can do:

var price = 0;
if (relations && 
	relations.children && relations.children.products && 
	relations.children.products.submissions && 
	relations.children.products.submissions.length > 0) {

	relations.children.products.submissions.forEach(
		function(prod) {
			var quant = 1;
			if(prod.relationProperties.count && 
				prod.relationProperties.count) {
      			quant = prod.relationProperties.count
			}
			if (prod.items && prod.items['price']) {
				price += prod.items['price'] * quant;
			}
		}
	);
}
return price;

As a rule of thumb, be extra careful when manipulating money with floating point arithmetic (in JavaScript here, but also in other languages). It is good practice to use only integers for manipulation and conversion to floats when displaying like we did in the previous example.

> Return to top

Accessing the submission data

It is possible to access some system data of the submission using the metadata JavaScript Object, that contains the following properties:

uuid, a 128-bit UUID that uniquely identifies the submission, represented as a String ;
created_at, the date of creation of the submission as a Unix timestamp ;
updated_at, the date of last update of the submission as a Unix timestamp ;
number, a unique number to identify the submission (Number) ;
number_in_structure, a unique number to identify the submission, limited to its structure (Number) ;
user, an Object containing the email, first and last name of the user who created the submission.

Here is an example of such an Object:

var metadata = {
	"number": 63930,
	"number_in_structure" : 12,
	"uuid": "38c6d76e-5442-4cbd-baef-cb9e5d6c4daa",
	"created_at": 1550242752,
	"updated_at": 1550242752,
	"user": {
		"email": "jd@company.com",
		"last_name": "Doe",
		"first_name": "John"
	}
}

It can be used like this:

return metadata['number'];

Note that number, number_in_structure and created_at are only available after the first saving of the submission.

> Return to top

Writing a formula - returning typed values

Depending on the return type defined on the formula field, you need to return the corresponding value from the formula function. For that, always use the return JavaScript keyword.

Simple fields

Simple fields like Text, Number, Boolean, Date (as a UTC Unix timestamp) and Duration (in milliseconds) can be directly returned by the corresponding primitive JavaScript value (as a String or Number type).

List field

A JavaScript Array of complete List objects must be returned. For formatting, it is strongly recommended to use the searchList function detailed below.

You must not use special characters in the external id of a list element (example: the underscore "_")

Returning list fields with the searchList function

By using the function services.searchList(), you can directly find elements of a Daxium-Air List linked to the formula. This is the only option of the List-typed formula field. It can be used with a List that is not linked to any List field of the submission. It takes a JavaScript Object as only parameter, with three values:

type (required): search using either the name, the external_id or the id ; this is a String ;
value (required): the value by which performing the search ; the type of the value depends on the type:
- name and external_id types should have a String value ;
- id type should have a Number value ;
parent (optional): you can enter the id of the node in your hierarchical List to restrict the search to the sub-elements below this node.

Here is an example of how it can be used:

return services.searchList({
	"type": "name",
	"value": "My list element"
});

Here's another:

return services.searchList({
	"type": "id",
	"value": 12345,
	"parent": 345
});

This function returns an Array of complete List objects:

[
	{
		"id": 123,
		"external_id": null,
		"caption": "Name of the element",
		"color": null,
		"imageId": "504d4a60-0db2-4bed-b234-a6d75e9950f4"
	},
	{
		"id": 124,
		"external_id": "456",
		"caption": "Other element",
		"color": null,
		"imageId": null
	}
]

> Return to top

Daxium Center

The formula Field

Field options

Type

Formula

Calculation rules

Writing a formula - accessing submission data

The JavaScript context of a formula

Syntactic and configuration considerations

Accessing fields values

Simple types

Date

Boolean

Objects and Arrays fields

Location

User

List

Relation

Countable Relation fields

Accessing the submission data

Writing a formula - returning typed values

Simple fields

List field

Returning list fields with the searchList function

Related Posts

Comments

Documentation Utilisateurs

Get Informed & Exchange

Technical Support

UX Workshop

More information on our commercial site www.daxium.com

Copyright © 2023 Daxium - All rights reserved