4D View Pro: Enhancement of Custom Functions

HDI 4DVP Collection and Object in Custom Functions

Custom functions are a powerful feature of 4D View Pro that allows you to extend its functionality and perform custom calculations or operations tailored to your specific needs. You can now accept collections as parameters to custom functions containing values from user-specified cell ranges.

Collection management

Custom function creation

First, we’ll create the function called by 4D View Pro. This function, called _averageNonZeroValues(), takes one collection parameter and is defined like this:

Function _averageNonZeroValues($values : Collection) : Real
    // Average of the non zero and non null values in the range
	
    // Sum of all the collection values
    var $total:=$values.flat().sum()
    // Calculates the number of cells containing a value other than 0 or NUll
    var $NonZeroValueNumber : Integer:=$values.flat().count()-$values.flat().countValues(0)
	
    // Returns the calculation result to be displayed in the cell
    return $NonZeroValueNumber>0 ? $total/$NonZeroValueNumber : 0

Enable the Custom function in 4D View Pro

Function averageNonZeroValues()->$customFunction : Object
    var $this : Object
	
    $customFunction:={}
    $this:=This // Capture This for the formula
	
    // formula that will be called when the custom function is used in the spreadsheet
    $customFunction.formula:=Formula($this._averageNonZeroValues($1))
    // If a parameter is of collection type, the declaration of the custom function parameters is mandatory
    $customFunction.parameters:=[{name: "Values"; type: Is collection}]
    // Summary of the custom function using in the autocomplete popup 
    $customFunction.summary:="Returns the average of non zero values"
    // Expected number of parameters
    $customFunction.minParams:=1
    $customFunction.maxParams:=1

Object management

• Establish a function intended for execution by 4D View Pro to conduct the calculation.
• Formulate an object to specify the custom function options.

For this example, we’ll complete the CustomFunctionCreator class.

Custom function creation

First, we’ll create the function called by 4D View Pro. This function, called _modifiedProperties(), takes one object parameter and is defined like this:

Function _modifiedProperties($object: Object) : Text
    // Comparison between the properties in the Form.people object and the properties in the spreadsheet
    var $myObject : Object:=$object.value

     // Search the object attributes modified between the people data and the data in the spreadsheet
     For each ($property; $myObject)
          // comparison between the object returned by the spreadsheet and the Form.people object
          If ($myObject[$property]#Form.people[$property])
               return "Data modified."
          End if 
     End for each 
     return ""

Enable the Custom function in 4D View Pro

Function modifiedProperties()->$customFunction : Object
    var $this : Object
	
    $customFunction:={}
    $this:=This // Capture This for the formula
	
    // formula that will be called when the custom function is used in the spreadsheet
    $customFunction.formula:=Formula($this._modifiedProperties($1))
    // If a parameter is of collection type, the declaration of the custom function parameters is mandatory
    $customFunction.parameters:=[{name: "Values"; type: Is object}]
    // Summary of the custom function using in the autocomplete popup 
    $customFunction.summary:="Returns a message when the object is modified by the user"
    // Expected number of parameters
    $customFunction.minParams:=1
    $customFunction.maxParams:=1

Custom function declaration

The last step is to call VP SET CUSTOM FUNCTIONS on the “on Load” event of the form using the averageNonZeroValues() and modifiedProperties() functions:

Case of 
		
  : (FORM Event.code=On Load)
    var $customFunctions:={}

    // Declaration of authorized custom functions in the 4D View Pro area
    var $creator:=cs.CustomFunctionsCreator.new()
    $customFunctions.MY_AVERAGENONZEROVALUES:=$creator.averageNonZeroValues()
    $customFunctions.MY_MODIFICATIONS:=$creator.modifiedProperties()

    VP SET CUSTOM FUNCTIONS("ViewProArea"; $customFunctions)
End case 

Now, you have 2 new functions usable in your 4D View Pro area:

• “MY_AVERAGENONZEROVALUES:

• “MY_MODIFICATIONS”:

In conclusion, using custom functions in 4D View Pro elevates spreadsheet functionality to new heights, enabling tailored calculations and operations. Please see our documentation for further information.