Aug 27, 2024 11:26 AM
I feel like what I want to do is very simple, but honestly, I'm wasting a lot of time without succeeding. My level of incompetence is quickly reached.
So, I have in my Table 1 a Date field and a field that I'll call field N. In my Table 2, there is also a Date field and a Number field.
I want AirTable to find the corresponding Date in Table 2 for each record in Table 1, then return the Number field and enter it in the N field of Table 1.
I've been searching for the function, but I can't find it. Does anyone know how to do this?"
Solved! Go to Solution.
Aug 29, 2024 06:23 AM
No, it doesn't matter. When you copy value to the field linked to Table 2, it searches for this value in the primary field of table 2. Try to copy-paste a single value
Aug 27, 2024 12:47 PM - edited Aug 29, 2024 01:09 AM
Hi,
Make Date primary field in Table 2.
In the Table 1, create linked field to Table 2.
(i'm not sure it's mandatory, but I would do that - adjust Date format in both fields to be the same. I prefer ISO format. You can change it later)
Copy-paste whole Date field of Table 1 into new created Link field. It will link to matching dates. Now in Table 1 add lookup field 'Number' from table 2. Copy-paste it into N field.
Note: if you have Dates in Table 1 that has no match in Table 2, it will auto-create new empty records with Primary value filled. If your Table 2 unsorted, you could easily see them and delete.
But to prevent auto-creation, you have 2 options (in Table 2):
1) Make primary field a formula, taking value from Date. Formula field values cannot be directly edited, so it won't allow to auto-create new records.
2) Edit Table 2 permissions. Set 'Who can create new records' to Nobody. After copy-pasting and linking, you can revert all previously made changes. Links will remain. The link is tied to a whole record and it displays record name (value of a primary field). So, if you change primary field of a Table 2 afterwards, all links will display other values, but they will be tied to the same records with respective dates.
Aug 27, 2024 08:04 PM
Incompréhensible cette explication
Aug 28, 2024 05:12 AM
I do not think it works. I tried and the two tables do not communicate.
The Excel "equivalent" is the LOOKUP fonction.
Aug 28, 2024 11:04 PM - edited Aug 28, 2024 11:05 PM
Hmm, I tried out @Alexey_Gusev 's instructions and they worked fine to display the "Number" field from Table 2:
Link to base
Could you provide some screenshots of your tables and the relevant fields?
Aug 29, 2024 05:45 AM - edited Aug 29, 2024 05:54 AM
Of course.
I think the problem is my DATE field in Table 1 is not my primary field. And it cannot be.
TABLE 1
TABLE 2
Aug 29, 2024 06:23 AM
No, it doesn't matter. When you copy value to the field linked to Table 2, it searches for this value in the primary field of table 2. Try to copy-paste a single value
Aug 29, 2024 11:06 AM
True. It is working. I have to create the linked record in Table 2 from Table 1, but it is quite easy.
Thanks!
Aug 29, 2024 04:48 PM
Great to hear! Note that such linking work as expected only when your primary field in Table 2 is unique.
As soon as you have duplicates, one will be linked and one - not.
Also, you can create Pivot table for any field by creating new linked field to a new table and then copying whole field of values there, in link field. (values must not contain comma or ampersand, because it counts comma as separator for 2 values)