A140 – Calculated field

A calculated field is an object field with some properties:

• It must be read-only: Forced value by calculation
• It must have a Calculated expression, otherwise its value is calculated by code
• If it has no physical column, it will be calculated each time it is displayed (expensive), otherwise it will be calculated and store to the database (recommended for costly calculations such as SQL requests).

Exercise:

Prerequisite:

AppClient object must have a customer score historic.

The goal is to create a calculated field that displays the maximum score of the customer, using its historical scores. Add a field cliScoreMax to AppClientobject (see Add field), the field should be:

• Visible: in order to check the value calculated on the screen
• Read only
• Integer 10
• With a physical column cli_score_max

Open the field and enter a Calculated expression:

• JavaScript syntax
• The variable obj is the instance of the current object
• The variable grant is the instance of the rights of the current user
• Clear all caches before testing the calculated field.

Exercise:

Enter the expression:

(function(){  
    var max = 0;  
    if (!obj.isNew()) {  
        var clientId=obj.getRowId();  
        var sql="select max (cli_score) from app_client_hist Where row_ref_id =" + clientId;  
        max=grant.simpleQueryAsLong(sql);  
    }  
    return max;  
})()  

Code Explanation:

• Tests whether it is not a creation (in which case the customer has not yet interests)
• Retrieves the ID (row_id) customer
• Executes the query that seeks the maximum value of the score in the customer historic
• Return the result

Another possibility when the code becomes too long or complex is to use the script of the business object:

• JavaScript syntax using Simplicité APIs
• this: instance of the current object
• this.getGrant(): Instance of user rights
• Here are some common hooks:
• initForm: Before display an object form
• initList: Before displaying the list of an object
• initSearch: before the search display
• Order calls when recording: 1) preValidate: before field validation 2) postValidate: after field validation (only if the standard validation is OK) Then if the validation is OK:

3) preSave: before saving 4) preCreate or preUpdate: Before creating or updating the value of the following row_id 5) postCreate or postUpdate: After creating or updating (called only if the update OK) 6) postSave: after saving whether it is a creation or an update (called only if the update in OK) and at a removal: pre / postDelete

Exercise:

• Edit the script of the AppClient object
• Add the following script:

  AppClient.preUpdate = function () {  
  var clientId = this.getRowId();  
  var sql = "select max(cli_score) from app_client_hist where row_ref_id="+clientId;  
  var max = this.getGrant().simpleQueryAsLong(sql);  
  this.setFieldValue("cliScoreMax",max);  
  };  

  Code Explanation:

• Using preUpdate hook to act just before the update of a customer
• Retrieves the customer id
• Executes the query that select the maximum score in the customer historic
• Set the result to the cliScoreMax field value
• No need to make a this.save () because it is just before the update

Clear all caches before testing the calculated field.