How to use expression evaluation custom fields?
How to use expression evaluation custom fields?
Custom fields of the type "Text" and "Number" can have a subtype that indicates what kind of text the field is expected to contain. One of the subtypes is called "Expression" and makes the custom field behave quite differently.
A custom field of the subtype "Expression" allows you to enter a formula or expression when the field is set up. When the custom field is displayed on an object (i.e., on a task/company/equipment/etc.), the result of the formula/expression is calculated. The field's content cannot be edited on the object (the task/company/equipment/etc.); it always shows the calculated result.
A functionality must be enabled (by Ventu) before this is available.
Note that there is both a new and an old syntax for the expressions. This page refers to the old syntax. Custom fields using the new syntax can be more demanding on the system but also offer more possibilities.
Possibilities
The formula/expression can depend on fields from the object. For example, if the custom field is created on a task, the formula can perform calculations based on the task's deadline or time estimate or other fields. An example could be the formula Date.dateadjust(this.tdeadline, "+1mo") which shows the date 1 month after the task's deadline. this refers to the object (a task in this case), tdeadline is a field on the task, and Date.dateadjust() is a function that modifies a date.
For certain objects, it is possible to access linked objects. A custom field on a task can, for example, read fields from the task's company: the formula this.customer.customernumber+" !" will show the company's customer number followed by an exclamation mark. this is again the object/task, customer is a field on the task containing the company, and customernumber is a field on the company.
In the example above, you are reading the "raw" value, meaning if you read this.waref (workspace), you get the workspace's ID, which may not be very useful. You can instead use the function gvd() which reads a formatted value, i.e., this.gvd("waref").
If an object has, for example, 5 custom fields with numbers that are entered when filling out a form, you can create a formula that shows the average (or maximum or minimum, etc.) of these 5 fields.
Supported object types
All object types that support custom fields also support expression custom fields.
Access to other objects
Only certain objects allow you to access linked objects.
Enlarges the table by opening it in a full screen dialogOpen
|
Task |
User, Company, Project |
|
Company |
User, Person |
|
Equipment |
User, Place of home |
|
TIcket |
Task |
Naming of objects
It is built up of names, so if you, for example, want to find a company's customer number and display this in a task custom field, you can specify this.customer.customernumber in a task custom field.
Tasks (Todo)
The following extra fields are available in the this object when this is a task. A list of fields for tasks is provided at the bottom of the page.
|
customer |
The Company object |
|
user |
The responsible user |
|
project |
The associated project, regardless of whether it is the new or old project module |
I.e., this.customer.latlon.lat gives you the company's latitude, and this.project.title gives you the project's name (new project module; for the old project module, you must use this.project.tname).
Company (Customer)
The following extra fields are available in the this object when this is a company. A list of fields for companies is provided at the bottom of the page.
|
person |
The contact person |
|
user |
Our reference |
Equipment (Tool)
The following extra fields are available in the this object when this is a piece of equipment. A list of fields for equipment is provided at the bottom of the page.
|
placeofhome |
The home location for the equipment, this is a companyd |
|
user |
The responsible user |
Operators and functions
This is only an overview of the available operators and functions. A more complete description can be found in the technical documentation for scripts.
| Type | Operators |
| Math | + - * / % |
| Comparison | <=, >=, ==, !=, >, < |
| Type | Functions |
| Date/time | Date.time(), Date.date(), Date.strtotime(), Date.dateadjust() |
| Text | length(), lower(), upper(), substr(), sprintf(), replace(), split() |
| Lists | length(), explode(), implode() |
| Math | Math.min(), Math.max(), Math.sum(), Math.random(), Math.floor(), Math.ceil(), Math.round(), common trigonometry, etc. |
| Microbizz | gvd() |
Examples
These are meant as examples of the syntax; the examples may not be particularly useful in real life...
| Object | Formula | Description | Example Result |
| Task | this.customer.customernumber.length() | Show the length of the company's number | 23 |
| Person | this.firstname.upper() | Show the person's first name in uppercase | JESPER |
| Task | Date.dateadjust(this.tdeadline, "+1mo") | One month after the deadline | 2023-07-12 |
| Task | Math.max(this.custom12, this.custom13)+2 | 2 plus the largest of the values in the two number custom fields with IDs 12 and 13 | 123 |
| Task | this.custom12 > this.custom13 | 1 if the value in number custom field ID 12 is greater than the value in custom field ID 13, otherwise 0 | 1 or 0 |
| Task | bar(this.custom14.split(",")) | Show a chart; requires that custom field ID 14 contains numbers separated by commas, e.g., 12,33,25 | (Image of a bar chart) |
Fields
In the system module, there is a page that shows most of the available fields. The page is found at PLUGINS > EXPRESSION FIELDS.
You can also look in the Field guide in HTML document construction, where you can see many of the available fields, e.g., this.customer.customeradress2.