Show all records in another record that are related by an ID
I have a table of persons. Each person has a familyID. Persons in the same family get the same ID. When I open I record I'd like to see all members of their family listed. How can I accomplish that please?
You can create a formula field with the following:
let t := this;
(select persons)[familyID = t.familyID].FirstName
The first line gathers data of the current record.
The second line finds all records from the table persons (you would change this to match your particular table name) where the familyID matches the familyID of the current record (notice the use of t). Then it gathers data from the field called FirstName. Of course you would change this to whatever you want.
This will give you a field that creates an array of the records, i.e. Fred,SungLi,Amari. There are other options as well. This is just a start.
If you are not familiar with using brackets , I would recommend that you start to familiarize yourself with them. They can be used in place of a where statement. So the above select statement can be rewritten:
(select persons where familyID = t.familyID).FirstName
Brackets can also be used to filter linked records. Which is what you will eventually try to do more of instead of doing select statements. But that is another discussion.
It's not SQL although some of the syntax looks similar.
Square Brackets can be used where ever you want to scope a select statement or a relationship. It can be used more than once and I have seen examples where it has been nested too.
from an orders screen would give you a view of all line items for the same customer where the orders are upaid and the line items value is > 100
I would add that, inside the square brackets, one can put a script, with the statements separated by semicolons as usual. When writing such a script, one should remember that:
- the script is executed once for each record of the target table in a "select" statement, for each record of the linked table when filtering a relation, or for each record whose descriptor is contained in the array when filtering an explicit array;
- that the context for each execution of the script is the record currently being tested; this means that the fields of that record are accessible directly, but that one can also follow relations to access the fields of linked records like in any formula; it also means that fields of the table where the formula is defined are not accessible: if one needs the value of a field of that table, for example to use as a comparison base, one must save it in a variable before invoking the "select", the relation or the array;
- that the script must return a boolean value, to indicate if the record being tested must be included in the filtered set.
"value>100" is thus a valid script if "value" is a field in the table whose records are being filtered.