Advanced Issue - Displaying Data in Lookup / Linked Field

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!

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

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



Hi @Valentino_Escalona ,

Now I understand it much better :slight_smile:

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

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

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.