Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Autofill Lookup Fields

Solved
Jump to Solution
11295 21
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.

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.

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. 🙂