Skip to main content
Solved

Lookup fonction

  • August 27, 2024
  • 8 replies
  • 52 views
D
Forum|alt.badge.img+5

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

Best answer by Alexey_Gusev

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

  


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 

 

 

8 replies

Alexey_Gusev
Forum|alt.badge.img+25

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.

TheTimeSavingCo
F
Forum|alt.badge.img+2
  • FredRochet
  • New Participant
  • August 28, 2024

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

D
Forum|alt.badge.img+5
  • Denis_Filion
  • Author
  • Known Participant
  • August 28, 2024

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

The Excel "equivalent" is the LOOKUP fonction.

TheTimeSavingCo
Forum|alt.badge.img+31

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:

Link to base

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

Adam, Airtable Consultant - thetimesaving.company
D
Forum|alt.badge.img+5
  • Denis_Filion
  • Author
  • Known Participant
  • August 29, 2024

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

  

Alexey_Gusev
Forum|alt.badge.img+25
  • Alexey_Gusev
  • Brainy
  • Answer
  • August 29, 2024

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

  


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 

 

 

D
Forum|alt.badge.img+5
  • Denis_Filion
  • Author
  • Known Participant
  • August 29, 2024

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

Thanks!

Alexey_Gusev
Forum|alt.badge.img+25

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)

Terms & ConditionsAccessibility statement