Help

Re: Lookup fonction

Solved
Jump to Solution
754 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Denis_Filion
6 - Interface Innovator
6 - Interface Innovator

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?"

1 Solution

Accepted Solutions

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 

Alexey_Gusev_0-1724937810360.png

 

 

See Solution in Thread

8 Replies 8
Alexey_Gusev
13 - Mars
13 - Mars

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.

Incompréhensible cette explication

Denis_Filion
6 - Interface Innovator
6 - Interface Innovator

I do not think it works. I tried and the two tables do not communicate.

The Excel "equivalent" is the LOOKUP fonction.

Hmm, I tried out @Alexey_Gusev 's instructions and they worked fine to display the "Number" field from Table 2:

Screen Recording 2024-08-29 at 2.02.36 PM.gif

Link to base

Could you provide some screenshots of your tables and the relevant fields?

Denis_Filion
6 - Interface Innovator
6 - Interface Innovator

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 1.jpg

TABLE 2

Table 2.jpg

  

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 

Alexey_Gusev_0-1724937810360.png

 

 

Denis_Filion
6 - Interface Innovator
6 - Interface Innovator

True. It is working. I have to create the linked record in Table 2 from Table 1, but it is quite easy.

Thanks!

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)