July 27, 2023 - u/OnionLoose8717
Hello everyone,
I'm trying to find a formula in Notion that seems a bit complicated so I'm not sure if there's really any option to achieve what I want, but anyway, I wanted to post it here so maybe some of you can help me, which I would really appreciate.
So I have a database in Notion for all the movies that I watch and the ones that I want to watch, with a lot of information such as year, country, directors, etc.
What I want to achieve is that somehow I can know which is the next film from the filmography of a particular director I have to watch next, sorted by the oldest year.
This means that I need a formula that takes all the films from the same director, then from this group takes the ones that are set "To watch" and then from these ones finds the one with the oldest release date.
I've created an example that I link below, with a couple of directors and different release dates and status. Following this example, this Formula should say that "Jurassic Park" is the next Steven Spielberg film that I have to watch and "Goodfellas" is the next one for Martin Scorsese. The formula can just end with a checkbox, that would work for me.
This seems very complicated to achieve to me but I'm sure some of you might have some ideas! :)
Thank you in advance!
Quick answer
<aside> <img src="https://prod-files-secure.s3.us-west-2.amazonaws.com/9210293a-6b0f-4638-8e8d-3c40b40ed42b/b8366055-a446-4861-aa67-1e926814ca74/Invisible.png" alt="https://prod-files-secure.s3.us-west-2.amazonaws.com/9210293a-6b0f-4638-8e8d-3c40b40ed42b/b8366055-a446-4861-aa67-1e926814ca74/Invisible.png" width="40px" /> Hi, there,
it took me 3 minutes (explanations take me longer). I didn't try to optimize, nor to see if it's viable. It remains a solution that will eventually weigh down the database.
Create a second database [Next movie to watch (DB)] with a single entry. This is used to select the desired Director.
You'll need to list the directors again.
Create a relation to the Films database
All entries in the Films database must be linked to the single entry in Next movie to watch (DB).
To ensure that future entries are automatically linked, use the forced filters (in Films, create a filter to select the single entry in the relation).
In Films, create a rollup to retrieve the name of the selected director.
Using a formula, display only the dates of films whose director's name matches and which have not yet been watched)
if(and(prop("Director") == prop("Director (selected)"), prop("Status") != "Watched"), prop("Date"), fromTimestamp(toNumber("")))
Using a rollup in the Next movie to watch database (DB), we retrieve the date created in 6. by choosing the "Earliest date" option.
Transform this date into a timestamp using a formula (so that it can be transformed back into a date and not into more difficult-to-use text).
Recover the timestamp from Films by rollup
Use the timestamp to compare it with the dates of the films selected in 6.
This last point is used as a filter.
It is possible to have the same system without having to create a second database (but this is a little more complex). The advantage would be that you wouldn't have to recreate the list of directors.
</aside>
By @Ouinx2 - **Buy me a Beer**