Autofill Lookup Fields

[Malakai]

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?

[pressGO_design]

---

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

[Malakai]

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.

[pressGO_design]

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?

[pressGO_design]

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?

[Malakai]

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. 

[pressGO_design]

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. 

[pressGO_design]

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

[Malakai]

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 

[pressGO_design]

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]

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