Oct 16, 2017 05:38 PM
Greetings,
I have an Airtable field titled: Course Status and to the right of that field is Total Enrollment. In my Excel spreadsheet version, I used a vlookup to return the value of ‘course status’
The formula: =VLOOKUP([@Enrolled],Table30,2,FALSE)
Table30
0-2 enrolled = On Standby
3-9 enrolled = Low Enrollment
10-28 = Will Make!
Is there a way to accomplish this in Airtable?
Thanks in advance for your support!
Lisa
Sep 17, 2020 02:14 AM
Hi,
You could use the new Automation feature to run a script when a record is added.
Note 1 - The scripts in Automation are limited by run time (30 sec) and CPU use (1 sec? I believe) so more complex scripts will not work.
Note 2 - Since you are talking about adding a new record, this could be actually a much simpler script, just searching for a value from the new record in the target table to match. This could work depending on size of the database. It might be also possible to use Automation action Update instead of script, but I would have to check this one.
Nov 30, 2020 02:21 PM
Thanks, this is great! I think you need to add await to the
await mainTable.updateRecordAsync(record, {
line, I was having trouble in a slightly larger table of it just updating some records.
Nov 30, 2020 07:37 PM
Yes! Thank you for noticing that! I have corrected the gist.
:pray:
Jan 28, 2021 11:42 AM
@Greg_F This code is tremendously helpful. In your example if the value of item.barcode is removed the link in the Product field remains? In my instance I want that link to be severed. Do you know how I could do that?
Jan 28, 2021 07:36 PM
@Laura_Greer you right, the link would remain if the value in the lookup table gets deleted (unless the record in the lookup table gets deleted as well). In this case, you would have to run the script again, with an explicit declaration that if there is no match - the link field should be updated to empty.
Jan 29, 2021 06:39 AM
Yeah, I can’t figure out how to do that. I’m not. I’m a complete novice. Can you give me a push in the right direction?
Aug 11, 2021 03:03 PM
@Greg_F Thank you so much for sharing! One question: How can I make the script to return multiple values if the Barcode is used more than once. I set the Products field up to accept multiple linked records but the script just returns one. Thanks in advance. Christoph
Aug 11, 2021 08:38 PM
For a No-Code approach to Vlookups in Airtable, we have a function for that in our On2Air: Actions product that can be set up in minutes:
Aug 12, 2021 04:28 AM
@Greg_F Solved it myself. I was starting from the wrong end, made the products my maintable and order the lookup…
Aug 12, 2021 05:32 AM
It would be actually also possible to return array as result of search and link array as multiple links in linked field.
Either way - congrats on successfully hacking to a solution!