0

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?

6 replies

null
    • Fred
    • 2 yrs ago
    • Reported - view

    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.

    • Fred
    • 2 yrs ago
    • Reported - view

    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.

    • svs
    • 2 yrs ago
    • Reported - view

    Thank you, most helpful. Didn't know about the square brackets. I'm assuming this is SQL?

    • svs
    • 2 yrs ago
    • Reported - view

    Where can I find out more about the use of brackets?

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi svs

     

    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.

     

    For example

    customers.orders[status="unpaid'].lines[value>100]

    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

     

    Regards John

    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    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.

Content aside

  • 2 yrs agoLast active
  • 6Replies
  • 318Views