This week, a quick tip on how to identify the first record of each group in a portal with only one relationship.
Let’s see how simple you can realize something like that.
Our example shows how to display a list of names more clearly with a column of the first letter of the last name.
For the sake of simplicity we use only one table ‘Persons’ with the fields
– Firstname (text)
– Lastname (text)
– Grouping (Text, indexed, auto-enter Calculation replaces existing value, Calculation: Left ( LastName ; 1 ))
– ID (number, indexed, auto-enter serial)
– Name (text, auto-enter Calculation replaces existing value which provides the first and last name for display)
We now create a layout in which Portal displays the data of its own table.
In the right column we show the field ‘Name’ and on the left the field ‘Grouping’.
Up to now, the first letter of the last name is displayed on the left for each name.
To identify the first record of a group we build a self relation ‘Persons~Persons#Grouping’
on the table and connect the field ‘Grouping’ between the tables:
If we now put the calculation ‘Persons::ID ≠ Persons~Persons#Grouping::ID’ for the condition ‘Hide object when’
on the ‘Grouping’ field, the field will only appear on the first occurrence of a ‘Grouping’.
The explanation is very simple:
A calculation always ‘sees’ only the first data set via a relationship.
If we now connect the ‘Grouping’ fields, the calculation always sees only the first ID of this letter group.
So we look if our current row sees its own ID, then it is the first name with this initial letter.
We hide all other fields in the rows.
Pretty simple trick, isn’t it ?
So instead of writing script loops or sprawling calculations, we just use a relationship.
Our result can then look like this:
If you like to have an open version of the file, just send me an email.
You have any questions? Feel free to send me an email, I’ll try to answer as soon as possible.