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!