Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 18, 2022 12:30 PM
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!
Aug 22, 2022 07:11 AM
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
Aug 22, 2022 12:29 PM
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
Aug 22, 2022 12:30 PM
Aug 23, 2022 06:11 AM
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
Aug 23, 2022 09:04 AM
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