Relations again

Eduardo_Dalcin · June 30, 2022, 4:16pm

Hi folks! I’m still struggling with simple relations… Sorry about this.

I have a TABLE A with the following columns:

thing

I have a TABLE B with the following columns

thing (referenced to TABLE A-thing)
partOfThing (agregating from TABLE B)

Then, I want to show in TABLE A a new column with a list of parts of each thing

Example:
TABLE A

thing | partOfThing (from TABLE B)
car | wheel, engine

TABLE B
thing | partOfThing
car | wheel
car | engine

Is it clear?

Is there a general rule/template for this?

dmitry-grist · June 30, 2022, 5:15pm

Take a look at References and Lookups - Grist Help Center. It has a very similar example.

In your case, I would do two things:

Ensure that column thing in TABLE B is of type Reference to TABLE A.
Set the formula for column partsOfThing in TABLE A to:
```
TABLE_B.lookupRecords(thing=$id)
```

It would collect all records in TABLE B whose thing column points to the current record of TABLE A. It returns an actual list of records. To see the name of the part from those records, you could set the column type to a ReferenceList, or you could keep it as text, and operate on it further with a formula, e.g. join with a newline to have a multi-line cell with one part per line:

"\n".join(r.partOfThing for r in TABLE_B.lookupRecords(thing=$id))

Eduardo_Dalcin · June 30, 2022, 5:49pm

Thanks Dimitry!

I got the first part, and works fine. I’ll be back to second part asap.