👨‍🔬How formulas work

A formula is a field that allows you to display a value thanks to a custom javascript formula.

This allows you to format data to present it differently, or to make calculations based on values of the submission's fields. 

 

General case

Data type

To configure a formula, you must first specify the type of data it will return. Here are the options available: 

• Text
• Number
• Boolean
• Duration
• Date
• List

For each type, you have several options to display the data.

Writing the formula

The formula must be written in javascript. It must include a return statement, to return the desired information. 

A few specificities:

• Date type :
  • You must return a UNIX Timestamp (number of seconds elapsed between the desired date and the 1st of Jan 1970 at midnight UTC time)
  • Example for the March 26th 2020 10am UTC

return 1585216800;

By default, dates are displayed with the timezone of your device. It is possible to work around this by imposing UTC time

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

• Duration type :
  • You must return an integer, in seconds
  • Example for 58mn 26s pour:

return (58 * 60) + 26; 

• List type :
  • You must return an array of complete list objects. For formatting, it is highly recommended to use the below searchList function.
  • Exemple : 

return services.searchList({
 type: "id",
 value : 427459
});

 

Services

searchList

By calling services.searchList(); you can directly have access to the elements from the list of your formula. It's taking an object as parameter that can have 3 values: 

• type (mandatory) : you can choose the search mode amongst "name","external_id" or "id".
• value (mandatory) : the value to look for, according to the type defined above.
• parent (optional) : You can enter the id of the parent node of your tree list. 

searchList returns an array of complete list objects:

[
  {
    "id": 123,
    "external_id": null,
    "caption": "Nom de l'élément de liste",
    "color":null,
    "imageId": "504d4a60-0db2-4bed-b234-a6d75e9950f4"
  },{
    "id": 124,
    "external_id": "456,
    "caption": "Autre nom de l'élément de liste",
    "color": null,
    "imageId": null
  }
]

 

Retrieving data

Get the data from a simple field

We use the same syntax to get elements from a specific field for most fields types:

• Text
• Number 
• Boolean 
• Date
• Duration
• Email
• Phone
• Formula returning one of the above types.

We use the system name of the field.

items['system_name']

For instance, if we want to get the VAT included price from the not included one, where we apply a 20% markup : 

return items['price_noVAT'] * ( 1 + 20/100);

NB: If the field is not yet filled, then it is not necessarily present in the items object. It can be interesting to include a check if it is present in order to display the correct information:

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

  

Get the metadata of the submission

It is possible to access the system-generated data from the submission by using the javascript object metadata:

• number allows you to get a unique number to identify the submission 
• uuid is also an identifier to find the submission
• number_in_structure allow you to know the number of the submission for a specific form (incremental)
• created_at date of creation of the submission (Unix timestamp)
• user information about the user that created the submission. It is an object with the following information : email, first name, last name.

Below is the structure of the object

{
   "number": 63930,
   "number_in_structure" : 12,
   "uuid": "38c6d76e-5442-4cbd-baef-cb9e5d6c4daa",
   "created_at": 1550242752,
   "user": {
      "email": "toto@gmail.com",
      "last_name": "Toto",
      "first_name": "Toto"
   }
}

Below an example :

return metadata['number'];

NB : number, number_in_structure et created_at are available only after the first time the submission got saved.

Getting data from a location field

A location field is composed of a text address (ex : 21A boulevard Guist'hau, 44 000 Nantes, France) and of geographic coordinates (ex : lat 47.2165879, long -1.566951)

To get this information, you need to specify the data that you want to get. Below is an example for a location field in which the system name is "addresse" (text type formula)

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

 Response :

Adresse : 21A boulevard Guist'hau, 44 000 Nantes, France

lat: 47.2165879, long: -1.566951 

⚠️Warning, formulas of type location are not compatible with our V1 Android app.

Getting data from a list field

A list can have multiple items selected. We have then set up a more comprehensive way to access this data. Let's consider the example below with animals:

• Animal
  • Feline
    • Cat (external_id : cat)
    • Tiger
  • Bird
    • Seagull
    • Pidgeon

Amongst those choices, we will imagine that "Cat" & "Seagull" are selected in the list field that has a system name "list_animal"

items[‘list_animal’] will then be an array of objects :

• To get the name of the first selected item: 
  • items['list_animal'][0].caption
  • Response: Cat
• To get the external id of the first selected item:
  • items['list_animal'][0].external_id
  • Response: cat
• To get the parent the closest to the root of the list: 
  • items['list_animal'][0].parents[0].caption
  • Response: Animal
• To get the direct parent: 
  • items['list_animal'][0].parents[ items['list_animal'][0].parents.length – 1 ].caption
  • Response: Feline

A comprehensive example to get all the selected items: 

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

Response: " - Cat. - Seagull."

 

Getting data from a relation field.

It is also possible to get information from submissions in relation to the current submission.

Child submission

If the relation is defined in the current submission with the system name "system_name", you can access the submissions with:

relations.children.system_name.submissions

Then, to get the field "title" from the first related submission:

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

 

Parent submission

If the relation is defined in another submission, the syntax is similar, but you need to use "parent" instead of "children":

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

So to list all submissions that have established a relation towards the current submission with the relation "relation_name" (those submissions need to have a system name "title" somewhere):

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

 

Relation with quantity (count)

Some relations, like relation with quantities, have attributes that we can use in formulas.

To get the attributes of a relation, you need to use the attributerelationProperties instead of itemsin the related submissions. If a relation has aproduct system name, and that we want to access the quantity (property : count)

relations.children.product.submissions.relationProperties.count

Example: My parent submission is an order with products entered in a relation with quantity (system name: "product"). The price of each product appears in the field "price" of the products. If I want to get the total value of the order, I can write:

var price = 0;
if(relations && relations.children && relations.children.product && relations.children.products.submissions && relations.children.product.submissions.length > 0){
  relations.children.product.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;

 

Getting data from a user field

Like for lists, a user field can have multiple elements selected. We have set up a similar method to access this information.

items['user_field_system_name']

returns a user array.

 

Below are the properties of a user:

• email
• first_name
• last_name

In order to display the first & last name of a user in a user field:

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

 

Important notice

When a formula is set up in Daxium-Air, Daxium-Air is reading the formula to deduct the fields involved in the calculation. To do so, we parse the javascript looking for items['system_name']. It is then important to keep this syntax in those formulas.

2 examples that don't work: 

Example 1

return items.number * 2 ;

Example 2

var total = 0 ;
for(var i=1 ; i<10; i++){
  if(items['number'+i]){
    total += items['number'+i];
  }
}
return total;