4D View Pro: Use 4D formulas in your spreadsheet

4D v18 R5 introduces a new command that simplifies writing your code: VP SET CUSTOM FUNCTIONS. It creates custom functions in 4D View Pro which execute 4D formulas.

Thanks to this command you can now allow 4D commands, fields, variables, methods, or any arbitrary expression to be used in your cell formulas. For example, there’s no need to create a method to use a simple variable in 4D View Pro, just pass it as a parameter to your Formula.

HDI: Allow formulas

VP SET CUSTOM FUNCTIONS allows 4D View Pro access to:

• 4D variables
• fields
• methods
• 4D commands

 

During the on load event, you need to declare all of the 4D expressions you want to access in 4D View Pro.

First, create an object and use the object’s attribute to define your method’s call name:

$o:=New object

//Name of the function in 4D View Pro: "DRIVERS_LICENCE"
$o.DRIVERS_LICENCE:=New object

Then, add a formula attribute that contains the formula based upon your expression. This expression will be calculated when used in your cells.

If your DRIVERS_LICENCE function refers to:

• a global variable:

$o.DRIVERS_LICENCE.formula:=Formula(DriverLicence)


• a field table:

$o.DRIVERS_LICENCE.formula:=Formula([Users]DriverLicence)

• a method:

$o.DRIVERS_LICENCE.formula:=Formula(DriverLicenceState)

• a 4D command:

$o.DRIVERS_LICENCE:=Formula(Choose(DriverLicence; "Obtained"; "Failed"))

• a 4D expression:

$o.DRIVERS_LICENCE.formula:=Formula(ds.Users.get($1).DriverLicence)
$o.DRIVERS_LICENCE.parameters:=New collection
$o.DRIVERS_LICENCE.parameters.push(New object("name"; "ID"; "type"; Is longint))


You can add a description of what your function does:

$o.DRIVERS_LICENCE.summary:="Returns the result of the driver's license"

And if you refer to a method, you can add some parameters like the name and the type of the expected parameters by your function:


$o.BIRTH_INFORMATION:=New object
$o.BIRTH_INFORMATION.formula:=Formula(BirthInformation)
$o.BIRTH_INFORMATION.parameters:=New collection
$o.BIRTH_INFORMATION.parameters.push(New object("name";"First name";"type";Is text))
$o.BIRTH_INFORMATION.parameters.push(New object("name";"Birthday";"type";Is date))
$o.BIRTH_INFORMATION.parameters.push(New object("name";"Time of birth";"type";Is time))
$o.BIRTH_INFORMATION.summary:="Returns a formatted string from given information"

When you finish filling your object with all of the information, you need to make it available in 4D View Pro. Simply pass it to the VP SET CUSTOM FUNCTIONS command:

VP SET CUSTOM FUNCTIONS("ViewProArea"; $o)

And voilà! The new function is available in 4D View Pro: