Help

Re: Autofill Lookup Fields

Solved
Jump to Solution
5094 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

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
7 - App Architect
7 - App Architect

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
7 - App Architect
7 - App Architect

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
7 - App Architect
7 - App Architect

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


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.

Are you using Airtable forms? If yes, is your customer ID field a linked field in the orders table (and therefore a linked field on the form)?

 

Not yet, but that is the plan. The customer will send his ID to the orders table via a hidden field on a form. And the customer ID is a linked field on the orders table.

So, right now when you say that you're submitting an order form, what form is that? And what format is the customer ID in?

Apologies for not waiting for your reply. I mocked up a quick base to try and troubleshoot your issue. Here is a view that creates a pre-filled form with a hidden customer ID for an order. When you submit the order, you can see a new line in the Orders table, and also see that the Number of Orders rollup field has been incremented by 1. A read-only link to the base is here.

Is this the functionality you're trying to achieve?

Thank you for the awesome effort. I had a look and it looks very similar to what I am planning. What I am trying to achieve, is that with each new order being placed, Airtable also provides me with e.g. the address of the customer in the order table.

So I would have to take the customer ID from the order table, look it up in the customer table and then write the address to the order table automatically.

And should that address be changed by me in the customer table next month, the order table should reflect the change in real time. 

Click on the field name of the linked record.

Click “Add Lookup Fields” - I think it’s the second option from the top? The first one is Edit Field. Choose the fields you want to have show in the orders table. That should give you what you want. 

I miss the edit button in this forum. If you change the address, it changes it backwards and forwards, not just forwards.

Malakai
6 - Interface Innovator
6 - Interface Innovator

Hi @pressGO_design,

I think I haven't explained what I'm trying to do particularly well - apologies for that :-(. I made a commented screenrecording of my use case and hope this makes it easier to understand. And here's the link to the base I used in the example: https://airtable.com/shr2yPaKn1GCTFRZA 

Ah ha! Got it. The field that needs to link to your customer ID table is the existing customer ID field in your orders table. You don’t need a separate field for that. It’s not like the VLOOKUP where you have an additional column with that formula. The linked column is the formula (if that makes sense).

Malakai
6 - Interface Innovator
6 - Interface Innovator

What do I do, if I actually NEED the value "Country" in my order table? Without manually linking the records (see screen recording)?