Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Re: Autofill Lookup Fields

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

Hi, all. I'm transitioning from Google Sheets and I understand things are different here, so please have patience with me :-). I am basically trying to implement a VLOOKUP functionality.

I have the following use case: I have two tables, customers and orders. Their only commonality is the User ID.

I am now trying to look up a field in the customer table (name) and display it in the orders table by using Lookup. Each customer has a large number of orders.

When linking the two records and creating a lookup field, I can get the name to display on the orders table. But only for that one particular order and only by manually linking a record. 

My expectation was that Airtable recognizes that each "name" field in the orders table should now be filled the relevant value from the customers table. Like I would do with an arrayformular in Google Sheets. But that's not happening. 

What am I doing wrong?

1 Solution

Accepted Solutions
pressGO_design
10 - Mercury
10 - Mercury

You've copied and pasted in a lot of data, I'm guessing? And all of it is single line text. So you need to go in and link the existing single line text fields - not the individual records! - by editing the existing single line text field. See attached video. Sorry for the cough at the beginning - I'm getting over a cold.

You can create multiple views of your tables that show/hide/filter info based on whatever you want to see. I normally set up bases for my clients that have one main table with all eleventy-zillion lookup/rollup/linked/whatever fields and then create different views to allow them to see what they want. So you might set up a view called Country where you hide all the tables except the customers' names and the country they're in and a view called Product A where you filter for customers who bought product A and hide all the fields except the names and product field and a view called Country and Product where you show all the fields but group them by country and then product.

See Solution in Thread

21 Replies 21
Ron_Daniel
8 - Airtable Astronomer
8 - Airtable Astronomer

Hi Malakai,

I might be misunderstanding your question. Check out these screencaps and tell me if they are doing what you need:
Screen Shot 2023-01-05 at 9.36.29 AM.pngScreen Shot 2023-01-05 at 9.36.17 AM.png

Malakai
6 - Interface Innovator
6 - Interface Innovator

Hi, Ron. Thanks for the help. Ideally, the end result looks like on your screens. My issue is that I apparently have to link each order to a customer manually (by opening the record, clicking on "add record" or so and selecting said record). I need a way to do this automatically.

Ron_Daniel
8 - Airtable Astronomer
8 - Airtable Astronomer

Entry is super-fast in Airtable... Which part of this should/could be automated?

entry.gif

Malakai
6 - Interface Innovator
6 - Interface Innovator

If you have multiple hundred orders that need matching to a customer, automation is the only way to go :-). Basically I want Airtable to permanently monitor & match the User IDs in the orders and customers "sheets"/tables to each other and add customer infos to the orders table (customer ID, name, country, whatever). Just like VLOOKUP would dose automatically in a sheet. Also for new orders.

Ron_Daniel
8 - Airtable Astronomer
8 - Airtable Astronomer

If you're pasting in multiple orders, then existing Customers are automatically linked, along with whatever customer info you've used lookup fields to show in the Order table:

Screen Shot 2023-01-05 at 1.00.05 PM.png

Screen Shot 2023-01-05 at 1.00.24 PM.png

Screen Shot 2023-01-05 at 1.00.58 PM.png

Screen Shot 2023-01-05 at 1.01.10 PM.png

Malakai
6 - Interface Innovator
6 - Interface Innovator

I'm not sure if I understand. Or if that is what I am looking for. 

A quick example: When I add another order (containing a customer ID) to Airtable via a Form, I expect a row to be added to the orders table. And I expect the linked record field in that table to immediately search for the customer ID provided in the customers table and create that linked record by itself.

Same thing if I add the customer ID to an order in my table that, for whatever reason, doesn't have it yet. As soon as it's entered, I expect Airtable to look up the ID in the customer table and create that link.

None of that is happening though. No row in the orders table is linked to a customer unless I do it manually.

Ron_Daniel
8 - Airtable Astronomer
8 - Airtable Astronomer

Linked records will be created if they're pasted in. But lookup fields will not. Here, I pasted in orders with new Customers, and the customer records were created. However, I would not have been able to paste Address and Phone into the Orders table, because they are just lookup fields and can't be written into.

Screen Shot 2023-01-05 at 1.47.01 PM.pngScreen Shot 2023-01-05 at 1.45.41 PM.pngScreen Shot 2023-01-05 at 1.46.12 PM.pngScreen Shot 2023-01-05 at 1.46.21 PM.png

Malakai
6 - Interface Innovator
6 - Interface Innovator

My mistake, I was throwing linked records and lookup fields into the same bucket. Sorry for the confusion. Is there any way at all that I can automatically fill a lookup field by some process or so with the relevant values like in a spreadsheet? 

Ron_Daniel
8 - Airtable Astronomer
8 - Airtable Astronomer

Unfortunately, I don't know of a way to do that.... hopefully someone else will join this thread. 🙂