Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 04, 2023 09:50 AM
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?
Solved! Go to Solution.
Jan 06, 2023 06:25 AM
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.
Jan 05, 2023 06:39 AM
Hi Malakai,
I might be misunderstanding your question. Check out these screencaps and tell me if they are doing what you need:
Jan 05, 2023 07:12 AM
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.
Jan 05, 2023 07:54 AM
Entry is super-fast in Airtable... Which part of this should/could be automated?
Jan 05, 2023 08:07 AM
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.
Jan 05, 2023 10:03 AM
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:
Jan 05, 2023 10:29 AM
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.
Jan 05, 2023 10:50 AM
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.
Jan 05, 2023 10:59 AM
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?
Jan 05, 2023 11:00 AM
Unfortunately, I don't know of a way to do that.... hopefully someone else will join this thread. 🙂