Intersect or Lookup based on criteria?

[Keith_Orr]

So far liking airtable a lot. Having a hard time figuring out something which should be fairly simple…and perhaps, I just can’t find it.

I’ve been using relational databases for a while. Not an expert, but definitely comfortable with them.

One of the things I do is set up my weekly beer, wine and liquor order. A third party does the actual inventory, and I get a spreadsheet of item, inv, used, and value of inv.

In order to place the order, I need to have the vendor for each, and in many cases an order number or product code for each.

In the past I have used an intersect, with the item as the common field. That way I have his inventory data, and my product information data in one table, and can print out my order sheets.

Lookup is great and powerful, but I have to renew the linking field every week. I would think that airtable could look up (find?) the criteria to link.

Any ideas.

[Jeremy_Oglesby]

If I am understanding your question correctly, it sounds to me like what you’d want to do is create a new table for line items.

It sounds like you have:

• A table for Products
• A table for Vendors
• A table for Orders

And it sounds like when you place an Order, that order may be for multiple Products and from multiple Vendors. The setup I’m suggesting can be tweaked to accommodate orders from a single vendor only as well, if that is how your orders work.

So it sounds like what you are wanting is the concept of a Line Item as the intersection between a number of Products and an Order, to give an Order the ability to recognize the Vendor and Product Code for each Product in the Order. (I apologize if I am not understanding your question correctly and this is all elementary). So in Airtable, you just need to make this intersection explicit in a new table - a Line Items table. You will be able to create Line Items directly from within the Orders table by using linked records, and then use Lookups/Rollups to pull information from one place to another to avoid duplication.

Here’s the base I used in the example above - if you’d like to take a closer look at formulas and Lookups, you can copy the base using the link in the top-right corner after opening it:

Airtable

Orders Tracking - Airtable

Explore the "Orders Tracking" base on Airtable.

If you have questions about it, feel free to ask.