Hi everyone,
ok this is a bit tricky to explain but i’ll try my best.
I have the following tables (in Airtable):
- Project → has contract linked + has reports linked
- Contracts → belongs to a Project
- Contract positions → belong to a Contract
- Reports → belong to a project
- Work done → belong to a Report
Now basically a user can create a new Report that is linked to a Project.
Within the report you can add work done items through a form. Within that form, you can select a contract item from a dropdown.
Now I want to build a filter that only shows contract items that are linked the the contract that the Project of the report is linked to. So basically depending on the Project that is linked to the Report, different contract items are shown.
When I open the form, the Report is already linked to a project, so there is a connection to the project’s contract in theory. However i cannot find the right filter settings.
Technically the Filter should read something like this i think:
Contract is equal to (=) Record > linked report > linked project > linked Contract. (Or linked report > lookup contract from linked Project)
I can choose Record > linked report > Report values but i cannot choose any lookup fields here.
Would there any way to archive this? I could potentially add more direct links if necessary. But so far nothing I tried worked.
Funnily enough if i use static values in the filter, i can choose one Contract. But i really want it to dynamically make this connection. Any help would be greatly appreciated! I added a graphic aswell to visualise the connections between the different tables.
Thank you!