It is often helpful or even essential that databases be adapted in a flexible and evolving way to users and their businesses in the computer world. The control of accessible data is also a recurrent and sensitive subject. From this point of view, developers use methods and formulas that are sometimes complex to give or restrict access to information, depending on the context or the access rights of the users.
Let’s take a simple example. In your application, you sometimes need to display a list of people. One of the columns displays their full names, but in your database, you have a first name field and a last name field. Currently, you write a formula in the list box column, and you have to manage the sorting on the column yourself. Wouldn’t it be great to have a computed field where you can define its calculation formula and sorting method, and have all the business logic inside the class and not in each interface?
Well, starting with 4D v19 R3, 4D provides a solution to this, with computed attributes.
IMAGINE…
You have a list of people whose last names, first names, dates of birth, etc.
Imagine that you want to display, in a form, a list of people with their full name (based on first name + last name), their age (based on the date of birth), their current photo (based on their age)…
Then, as soon as a person is selected, you can display information on their parents, grandparents, children, brothers and sisters, uncles and aunts, and let’s not forget the cousins!
Is this possible? Yes, of course.
Without complicated lines of code in the form? Yes!
All this magic is possible thanks to computed attributes, on which it is also possible to search and sort, just like with the other attributes! Let’s take a closer look!
Definition and calculations
To explain the power of this concept, we will start with a concrete example. A “people” dataclass classically contains attributes such as “lastname”, “firstname”, “address”, “zipcode”, “city”, “country”, “birthdate”, etc.
From this class, we may need the full name (lastname + firstname), age (based on the date of birth), or the full address (as an object).
And we can even go beyond that. If the relations allow it, the computed attributes can even be of the entity type (e.g., father or mother) or entity selection (children, parents, grandparents, siblings, etc.).
Access to these attributes will be made possible through the “peopleEntity” class in which new functions must be defined for this purpose.
They will be called when 4D needs access to these attributes, either to read them (e.g., simple display), during a search or a sort, and finally during a possible save after modification.
The result of computed attributes, such as the full address, may need another computed attribute, such as the full name. This type of recursion is also possible; we will come back to this.
These functions (get, set as well as query and orderBy that will be discussed later) shall be defined inside the entity class itself (ex: peopleEntity)
Access to computed attributes
Get
The first function, “get,” allows to define the way the attribute is computed. For this purpose, it must return the result of the calculation.
This function is the only one that is mandatory to use the computed attributes. In fact, it is only its presence that determines the very existence of this attribute.
Function get fullName($event : Object) -> $result : Text
If (This.firstname=Null)
$result:=This.lastname
Else
If (This.lastname=Null)
$result:=This.firstname
Else
$result:=This.firstname+" "+This.lastname
End if
End if
Exposed, Local … or not?
How will this attribute be accessible, and under what conditions will it be calculated?
The “exposed” keyword that can be used to define the function is the equivalent of the “Exposed” checkbox in the inspector of the structure editor.
The “local” keyword used in Client-Server mode determines whether the calculation should be performed systematically on the server or whether it should be conducted locally to limit network access.
SET
The second function, “set,” allows the opposite operation to modify real attributes from a computed attribute.
If we take the example of fullname, the simple rule will be to look for a space in the attribute to redistribute the value in firstname and lastname. Of course, this is only a simple example. If the full name contains several spaces, you will have to apply a more complex business rule, such as using a slash (/), and treat it as a priority.
e.g.: “Pablo Miguel/de la Casa del Mar”.
The advantage is that once defined, this rule will be applied systematically without you having to redefine it according to the input context.
Function set fullName($value : Text)
var $p : Integer
$p:=Position("/"; $value)
If ($p>0)
$p:=Position(" "; $value)
End if
If ($p>0)
This.firstname:=Substring($value; 1; $p-1)
This.lastname:=Substring($value; $p+1)
Else
This.firstname:=""
This.lastname:=$value
End if
A get without a set? And the opposite…
A Get function is mandatory to have access to a computed attribute. A Set function is compulsory to make this attribute modifiable, but what if only one or the other of these functions exists?
When only Get is available, the attribute can be considered “read-only” and cannot be modified.
When only the Set is present, the attribute can be written but cannot be reread. This is the principle of a mailbox or password.
What’s next?
The third and fourth functions query and orderBy are pretty similar and are treated in the same way.
These functions are not mandatory, but using them will increase the performances drastically, as we shall see. In fact, if these functions are not defined, 4D will have to execute the “get” function for each entity and then perform a sequential sort. This is likely to belong and will be far from optimized.
It’s essential to understand and remember that computed attributes do not have indexes. Still, if their calculation is based on attributes, then those attributes may be (or should be) indexed!
For this reason, the possibility is offered to define how the search should actually be done when a query or a sort is launched on a computed attribute.
If we take the example of fullname, based on the attributes firstname and lastname (both indexed), a search of the type <<fullname = “Paul Smith” >> will likely mean that <<firstname = “Paul”>> and <<lastname = “Smith”>>. You are free to extend the search and decide that a search <<fullname = “Pa Sm” >> means that the <<firstname begins with “Pa”>> and the <<lastname begins with “Sm”>>. Or that << = “Martin” >> means that either the last name or the first name begins with “Martin”…
Query
When executing the “query” function defined for any computed attribute, the received parameter will contain two properties necessary to understand the desired search. Knowing this, the query can be rewritten to use one (or more) indexed attribute(s) and thus avoid a sequential search.
$event.operator contains the operator in string form (“==”, “>=”, “<“, etc.)
$event. Value contains the value to be searched or compared, also as a string.
The result of a query function can be either a string or an object.
If the function returns a string, the string must be a valid query.
$result:="lastname = A@ and firstname = B@"
If the function returns an object, it must contain two properties:
- .query: a valid query string that may contain placeholders (:1,:2, etc.)
- .parameters: a collection of values to be used inside placeholders
$result:=New object("query"; $query; "parameters"; $parameters)
Let’s see now how to manage the following dataclass query within the query function!
$es:=ds.people.query("fullname = :1";"Paul Smith")
Here is the complete query function code:
Function query fullname ($event : Object) -> $result: Object
$fullname:=$event.value
$operator:=$event.operator
$p:=Position(" "; $fullname)
If ($p>0)
$firstname:=Substring($fullname; 1; $p-1)+"@"
$lastname:=Substring($fullname; $p+1)+"@"
$parameters:=New collection($firstname; $lastname)
Else
$fullname:=$fullname+"@"
$parameters:=New collection($fullname)
End if
Case of
: ($operator="==") | ($operator="===")
If ($p>0)
$query:="firstname = :1 and lastname = :2"
Else
$query:="firstname = :1 or lastname = :1"
End if
: ($operator="!=")
If ($p>0)
$query:="firstname != :1 and lastname != :2"
Else
$query:="firstname != :1 and lastname != :1"
End if
End case
$result:=New object("query"; $query; "parameters"; $parameters)
Order By
As far as sorting is concerned, it is pretty similar. In this case, sorting people by their age is precisely the same as sorting them by their reverted date of birth. So when a sort by age is requested, it will be clever to use birthdate, which is indexed as the criteria.
- Order by samples:
$es:=ds.people.all().orderBy("age desc")
$es:=ds.people.all().orderBy("age asc")
- OrderBy function:
Function orderBy age($event : Object) -> $result: String
If($event.operator = "desc")
$result:="birthday asc"
Else
$result:="birthday desc"
End if
Use of Composite indexes
Composite indexes can be used in a highly positive way in some instances. Reminder: A sort by “last name” and “first name” does not use the last name or the first name index. For this kind of sort to be optimized, there must be a previous name+first name index, or first name+last name… or both.
In the case of a computed attribute like fullname, 4D will use this composite index if it is aware that it is actually a firstname + lastname sort! It is even possible to force a sort by last name + first name, even if the fullname displays the firstname before the lastname!
- Order by sample:
Form.people:=ds.people.all().orderBy("fullname asc")
- OrderBy function:
Function orderBy fullname($event : Object)->$result : Text
If ($event.descending)
$result:="lastname desc, firstname desc"
Else
$result:="lastname asc, firstname asc"
End if
Other possible types of COMPUTED attributes
In the sections above, we have covered computed attributes of scalar type (text, numeric…), but the computed attributes can also be objects, entities, or entity selections!
Some possible examples:
- fullAddress: object with as many attributes needed (fullname, street, zipcode, city, country, etc.)
Function get fullAddress($event : Object) -> $result: Object
$result:=New object
$result.fullName:=This.fullName // Another computed attribute can be used!
$result.address:=This.address
$result.zipCode:=This.zipCode
$result.city:=This.city
$result.state:=This.state
$result.country:=This.country
- bigBoss: entity
Function get bigBoss($event : Object) -> $result: cs.peopleEntity
$result:=this.manager.manager
- coworkers : entitySelection
Function get coworkers($event : Object) -> $result: cs.peopleEntitySelection
$result:=this.manager.directReports.minus(this)
Conclusion
Computed attributes are born and well-born! They bring both flexibility and power as well as increased control over what is accessible or not. The access to these attributes is optimized so as not to penalize either memory or network access. They are the simple solution to business demands and meet the increased requirements of modern programming.
For more details, read this documentation.