Issue in Fetching data after Adding Record

[Valentino_Escal]

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!

[Mohamed_Swella1]

Hi @Valentino_Escalona ,

I would be able to help if I can see screenshots of the lookup fields.

From what you are describing you can do that if you have formulas in Table 2 and Table 3 displaying the info you need from each record then looking up this formula field in Table 1

[Valentino_Escal]

Hi @Mohamed_Swellam!

Thanks for the response. I’ve attached screenshots bellow ↓

For this example → Table 1 (a grid view) would be shared with Project 1 (a client).

For the 15/8/2022 I want to share just Invoice Amount, Raw Materials & Quantity Used with the client.

I do not want the client to see Project 2 or Project 3.

Would love to hear your suggestions!

Cheers,

Valentino

[Valentino_Escal]

[Mohamed_Swella1]

Hi @Valentino_Escalona ,

Now I understand it much better :slightly_smiling_face:

Actually this is not good practice, you might wanna rethink the tables. you should send the client views from the Projects table not the other tables

[Valentino_Escal]

Hi @Mohamed_Swellam,

Thank you so much for taking the time to help out and respond!

I do understand there are better practices for linking, lookup fields and table structure, and use them when I can.

Given the circumstances though, there is not much room to budge in having multiple primary field (date) duplicates. I will continue on my quest to find a workaround for this issue.

I’m positive there is a solution and if/when it appears, I’m sure it will be much appreciated for other people having the same dilemma.

Cheers,

Valentino