I would like to ask the community about advanced linked records & extracting information for each specific record.
Data Overview
Table 1 ↓
I have a Zapier set up that creates a record each day into a table using a European date field type. Then I have created a linked field to Table 2 & 3.
i.e.
16/8/2022
17/8/2022
18/8/2022
Table 2
This is a shared table synced to another base. This table includes projects, staff members & invoice amount. It’s based on the same European date field. This table has a linked field with Table 1.
Table 3
This is a shared table synced to another base separate from Table 1 or 2. This table includes projects, raw materials & quantity used. It’s based on the same European date field. This table has a linked field with Table 1.
I have set up an automation in Table 2 & 3 that when a record enters a view, it automatically adds the date to the linked field (Table 1).
Issue
As Table 1 is the main source of information, I want to be able to access all aspects of the information.
There seems to be no way to separate the pieces of information in the linked/lookup field in Table 1. When there are 3 projects on the same day, it displays the whole invoice amount or quantity used ect.
So a lookup field was created to extract information from Table 2 & Table 3.
Table 2 → One lookup field for projects, one for workers and one for invoice amount (3 in total).
Table 3 → One lookup field for projects, one for raw materials and one for quantity used (3 in total).
The main goal would be to find out when there is a Project 1, who was working, invoice amount, raw materials & quantity used. Then compare it with Project 2 or Project 3 ect…
Any Suggestions?
Thanks a million!
