Get and Set cell content in 4D View Pro

The ability to interact with a document via programming is an important part of using 4D View Pro. Now you can fill your documents by programming. You can set data as labels, database fields, or formulas in your document. Of course, if you can set, you can get too! You can also retrieve cell formulas or values entered by your users.

To set and get values in 4D View Pro, 4D provides you a new set of commands:

• VP SET VALUE
• VP SET BOOLEAN VALUE
• VP SET DATE TIME VALUE
• VP SET DATE VALUE
• VP SET TIME VALUE
• VP SET NUM VALUE
• VP SET TEXT VALUE
• VP SET FORMULA
• VP SET FIELD
• VP Get value
• VP Get formula

 

Here’s an HDI to see these commands in action:

HDI Get / Set value

SET and GET value in cells

SET value in cell(s)

To set a value in one or several cells, you first need to create a range of all the cells you want to fill.

Then depending on the type of value (text, date, time, etc.), you have two ways to set it:

Per type: VP SET BOOLEAN VALUE, VP SET DATE TIME VALUE, VP SET DATE VALUE, VP SET TIME VALUE, VP SET NUM VALUE, VP SET TEXT VALUE // Setting "Hello World" in cell G11 
VP SET TEXT VALUE (VP Cell ("ViewProArea";6;10);"Hello world")


Generic: VP SET VALUE

// Setting "Hello World" text in the cells G11 
VP SET VALUE (VP Cell ("ViewProArea";6;10);New object("value";"Hello world"))


GET value in a cell(s)

To get a value from a cell, use the VP Get value command. It returns an object containing the value of the cell:

$cell:=VP Cell ("ViewProArea";5;2)
// retrieve value of F3 cell
$value:=VP Get value ($cell)
// Verify if value is a Text
If (Value type($value.value)=Is text)
  // set the value in uppercase in the cell
  VP SET TEXT VALUE ($cell;Uppercase($value.value))
end if

Set and Get formula

set a formula

To assign a formula to a cell or a group of cells, use the VP SET FORMULA command. For example:

// Automatically calculate a total in F26
VP SET FORMULA (VP Cell ("ViewProArea";5;25);"SUM($F$21:$F$25)")


You can also assign a declared 4D method, as explained in Use methods and database fields in 4D View Pro with the VP SET FORMULA command:

// Set the licence information in A1
VP SET FORMULA (VP Cell ("ViewProArea";0;0);"get_LicenceInfo()")


get a formula

To get a formula assigned to a cell using the VP Get formula command:

// Create a cell range: D17
$cell:=VP Cell ("ViewProArea";3;16)
// Get the formula of the cell D17
$formula:=VP Get formula($cell)
// Returns $formula="SUM($F$21:$F$25)"


Set database fields

set a field

You can assign a field to a cell with the VP SET FIELD command. Of course, you need to declare your fields as virtual structures as explained in the Use methods and database fields in 4D View Pro blog post.

// assign the Price1 field to the F21 cell
VP SET FIELD (VP Cell ("ViewProArea1";5;20);->[Invoices]Price1)


Keep in mind that this field appears in the formula bound to the cell, and the table and field names are replaced by the names declared in the virtual structure.