-
-
-
- Resource files
- Velocity - How does it work?
- Quick Velocity Tutorial
- Debugging velocity scripts
- Velocity Command Reference
- Script Editor Tutorial
- Using velocity for calculations
- Using predefined forms in a html design
- Velocity forms
- Data field identifiers
- Macros and methods
- Velocity library
- Velocity: using the #macro command
- velocity and xml
- Using AJAX in your velocity scripts
-
Data field identifiers
Data field identifiers are used in scripts. They refer to a field somewhere in the blueprint and can also contain routes and functions.
Data field identifiers are most frequently used:
- as an argument for the method get or put in a record
- in queries to define a result field, filter field, sort field or group field.
We shall start explaining the data field identifiers for the method get in a record. We will use the velocity syntax in the examples, but in other languages the data field identifiers will be identical.
Getting a value from a record
Suppose you have a record of class person:
#set($persons = $session.getTable('person'))#set($person = $persons.getFirstRecord())
To get the first name, you can simply type:
$person.get('first name')
The identifier 'first name' is the name of the field as defined in the blueprint. You can use the exact name, or replace any special character by an underscore. E.g. you can also use:
$person.get('first_name')
Important: when the name of the field contains a dot '.', you must replace the dot by an underscore.
TIP: Most classes have some extra fields (depends on class properties under the history tab). You can use the following identifiers:
- createDate creation date of the record .
- createUser name of user that created the record.
- modificationDate date of last modification.
- modificationUser - name of user that made the last modification.
Getting values from a foreign record
Suppose the class order has a relation to a foreign class person (to identify the customer linked to the order), and person has a field first name. We now create a variable for a record from the order class.
#set($orders = $session.getTable('order'))#set($order = $orders.getFirstRecord())
You can get the first name directly, by typing:
$order.get('person.first name')
In this example person is the name of the foreign class, while first name is the name of a field in the foreign class.
Getting values from a connected table
Suppose the class person has a connected class order, and order has a field date. You can get a list of all order dates of all orders of a person, by typing:
$person.get('order.order date')
Important: using a field in a connected class will always result in a list of values, except when a function is used.
When you only want the first order date, type:
$person.get('first(order.order date)')
In this example first is the name of a function. The result is a single date: the first order date.
Allowed functions are:
- average the average of a list of numbers
- average_rounded the rounded average of a list of numbers, the result is always a whole number
- concat concatenation
- concat_unique concatenation of the unique values, ordered alfabetically
- count the number of records in the connected table not having an empty value on the given field.
- count_distinct the number of records in the connected table with distinct values on the given field, ignoring empty values.
- first the value in the first record of the connected table
- last the value in the last record of the connected table
- maximum the maximum value
- minimum the minimum value
- sum the sum of all values
Getting values from more remote neighbours
You can use field identifiers as shown above, for any field that is related to the main record using a chain containing one or two class relations. Suppose an order line is related to a foreign class order and the order class is related to a foreign class person, which has a field called region . You can get the first name of the person directly from the order_line record:
$order_line.get('person.first name')
Or get the total quantity in a order line:
$person.get('sum(order_line.quantity)')
Please note that the class of the record should be directly related to the class of the field, or have at maximum one intermediary class.
More complex routes to remote neighbours
The simple way to retrieve data as shown above, can not be used when:
- the class of the record and the class of the field are related using more than one intermediary class
- there is more than one single route from the class of the record to the class of the field
In those cases we have to define the route to be used explicitly, using a field route. A field route consists of a description of a field (in a remote class) and a route to reach that remote class from the current record. See the following examples:
Instead of $order.get('person.first name'), use:
$order.get('person.first name {order [Person] >-- person}')
The exact route is added within curly brackets.Use either >-- or --< to specify the direction of the relation.
>-- is used to define a many to one relation from the left-hand side class to the right-hand side class. In this case the left-hand side class is the connected class, while the right-hand side class is the foreign class.
--< is used to define a one to many relation from the left-hand side class to the right-hand side class. In this case the left-hand side class is the foreign class, while the right-hand side class is the connected class.
The connected class should always be extended with the exact foreign key field within square brackets.
In this example, order is the connected class, person is the foreign class, and Person is the foreign key field.
In this example, order line is the connected class, order is the foreign class, and order is the foreign key field.
To string up a number of segments to a more complex route, use the pipe symbol (" | ") as a separator between the segments.
The examples shown above can be re-written with their formal notations as well.
$person.get('order.order date') can be formally written as:
$person.get('order.order date {person --< order[Person]}')
$person.get('first(order.order date)') can be formally written as:
$person.get('first(order.order date {person --< order[Person]})')
$order line.get('person.first name') can be formally written as:
$order_line.get('person.first name {order_line[Order] >-- order | order[Person] >-- person}')
$person.get('sum(order line.quantity)') can be formally written as:
$person.get('sum(order line.quantity {person --< order[Person] | order --< order line[Order]})')
Possible simplifications
If there is only one single relation between the class person and class country, you can leave out the foreign key field. Furthermore, the direction indicators --< and >-- may be written with any number of dashes ("-"), including zero. The class in the remote field can also be skipped since this field always belongs to the last class in the route. So the following is also a correct alternative for
$order.get('person.first name {order [Person] >-- person}')
$order.get('first name {order > person}')
Please also note that whitespaces are irrelevant.
Another situation
Suppose we have the class person, which contains a field First name, and a field Manager, which points to another person and indicates the manager of the person in question. To get the name of a person's manager, you need to do something like:
$person.get('first name {person >-- person}')
Any clue what the following might result in?
$person.get('first name {person --< person}')
As you might see, the direction of the relation is changed. This results in a list of names of all persons managed by the current person.
More complex relations
Given that the class organization is connected to the class person through an intermediate class person-organization. To get a list of the names of all the organizations of all persons that are connected with the current person:
$person.get('name{person --< person | person --< person-organization | person-organization >-- organization}')
As you can see, we eventually get to a list of organization names by first getting all connected persons of the current person, then getting all connected records in the class person-organization, and then finally getting the related organizations.
Of course, functions can be applied on all field routes. Consider the following:
$person.get('first(organization.name{person --< person | person --< person-organization | person-organization >-- organization})')
This wil result in the name of the first organization, related to the first person that is managed by the current person.
Suppose we are only interested in the names of the organizations of the first person that is managed by the current person. In that case we have to apply the function first() to the first segment, like this:
$person.get('organization.name{first(person --< person) | person --< person-organization | person-organization >-- organization}')
As you might notice, there is a difference with:
$person.get('organization.name{person --< person | first(person --< person-organization) | person-organization >-- organization}')
Now the function first() is applied on the second segment which will return the name of the organizations of all persons managed by the current person.
Appendix A: Reference
A data_field_identifier consists of one of:
- function(simple_data_field_identifier)
- simple_data_field_identifier
A simple_data_field_identifier consists of one of:
- field_identifier
- full_field_identifier
- full_field_identifier[route_identifier]
A full_field_identifier consists of:
- field_id
- class_identifier.field_identifier
A class_identifier consists of one of:
- clas_id
- name of the class, converted to a identifier (see below)
A field_identifier consists of one of:
- field id
- name of the field, converted to a identifier (see below)
A field_route_identifier (new method) consists of one of:
- class_identifier . field_identifier
- class_identifier . field_identifier { segment_identifier_list }
Note: The class_identifier may be left out
A segment_identifier_list consists of one or more segment_identifiers seperated by a "|" (pipe).
A segment_identifier consists of one of:
- class_identifier --< class_identifier [ foreign_keyfield_identifier ]
- class_identifier [ foreign_keyfield_identifier ] >-- class_identifier
- function_identifier ( class_identifier --< class_identifier [ field_identifier ] )
- function_identifier ( class_identifier [ field_identifier ] >-- class_identifier )
Note: "[ foreign_keyfield_identifier ]" may be left out
Allowed characters in a class_identifiers and a field_identifier
Please restrict to the following characters in a class_identifier and in a field_identifier:
- characters between a and z (lower and upper case)
- numbers between 0 and 9
- white space
- one of the following special characters: '_', '-', ':', '(', ')', '+', '&' or '%'.
White spaces and the mentioned special characters may be replaced by an underscore: '_'. All other special characters must be replaced by an underscore: '_'.
Appendix B: Field routes - old method
This method of field route notation is not encouraged, as it might introduce ambiguity in your routes, but it is still supported. Below the original description of the use of the former method of field route notation:
When we use a route, the example above getting data from a foreign class, would look like:
$person.get('country.region[person.country]')
Between the square brackets the route is specified by naming one of the foreign key fields in the relation between the two classes.
You can use routes that consist of more than one segment. E.g. to get the region from the country when you have an order record, using the person as intermediairy record.
country.region[order.person,person.country]
When you have a country record, and want to get data from the order record, you can also use routes in combination with a function. The total turnover in a country, with field routes:
$country.get('sum(order.turnover[person.country,order.person])')
The big advantage of using field routes is that the route to the remote field, is cleary defined. Important: use the short notation only when there is no risk of misunderstandings about the route to be used (either by the engine or by human developers).
Appendix C: Adding the start class - old method
In all examples above, the data_field_identifier is used in a method get. The route that is used always starts at the class of the record. When routes are used in cases that the start class is not known, you can add that to the route. Right after the '[' you can add the identifier of the start class, followed by the segments, seperated by a colon ':'.
product.group[country:person.country,order.person,orderline.order,orderline.product]