Hello
My forumla should be a simple one but can’t seem to get it to return any results!
My setup:
A Movie Database
First table (Movies) contain a list movies, with cloumns inclusing “Title”, “Year”, “Director”, and “Actors”
Second Table (Actors) has columns: “Name”, “Movies”
Third Table (Directors) is the same as above
The first table columns “Director” and “Actors” and reference list columns from the third and second tables respectively
So…
In “Actors” Table, I want the “Movies” column to return a list of “Title (Year)” from the “Movies” table for every “Title” where the “Actors” contains “Name” from the “Actors” table
So what I will have is a page of movies where I manunally enter the directors and actors.
But then the Actors and Directors Tables will self populate with the moves they are involved.
I hope that makes sense
Thanks in Advance
Movies.lookupRecords(Actor=CONTAINS($id))
$id is a hidden field that references a unique I’d for the current record. This is what is actually stored in a reference column.
I believe “rec” should also work as it is a reference to the current record, but I’m on mobile and can’t test it.
Hi and thanks
That formula will return the $id of the movie, which I have working. I have cells with a list of numbers
What I want to do is a step further by retreiving the film title and year from that row
Which I think is a horizontal lookup, which is not supported by grist?
That formula returns the entire records from the Movies table. If it is only displaying the ID, then that is probably just a formatting issue.
Make sure the column type is “Reference List”, the Data From Table is set to “Movies”, and the Show Column is set to the field you want displayed.
Thanks again, it was a formatting issue.
Once you pointed that out, I was able to get the results a wanted