Lookup reference to a different table with multiple active conditions

[Elfur_Logadotti]

So, I’m building a planning base where I have a pricing table with 4-5 different columns setting criteria and the final column displaying the price for the product that fits that criteria. Demo data:

Item  - Critera1 - Criteria 2 - Criteria 3 - Price
Mug   - Blue     - Tall       - Decorated  - $4
Mug   - Red      - Tall       - Decorated  - $4.5
Glass - Blue     - Tall       - Decorated  - $3
Glass - Blue     - Small      - Decorated  - $4

Now I want to use a lookup from the planning table to the pricing table where I’ve narrowed down the items (Tall, blue, decorated Mug) and have the calculated field return the value $4.

I accept any suggestions that would assist me with solving this problem :slightly_smiling_face:

thanks
/elfur

[Elias_Gomez_Sai]

I don’t know what do you call Planning. Would please explain a little deeper?

[Elfur_Logadotti]

Hi Elias,

I’ll sure try to explain it better. By Planning I mean scheduling, this was a translation carelessness late last night as English is my second language :).

So this is a base where we create a schedule for a specific scenario that lasts for a specific period of time. Each “plan” contains numerous lines (between 5 and 50).
We have a few tables with various different datapoints that I use to pull together in the schedule table (planning table) and one of the tables has information on price for each item (pricing table), where each item is a part of a few different groups, that could be used to categorize them, each and every one (in the example above I used the categories size, color and decoration, for ease of explanation).

I’m trying to keep the example simple while what I’m trying to solve is fairly complex, hence the example of a tall, blue, decorated mug. The selections in this scheduling entry of tall, blue and decorated all come from different tables with further information each (they really need to be in different tables).

The pricing table has all the combinations available, as outlined in a simplified form above, but in the scheduling/planning table the selectors are decided upon at each time, yielding numerous different combinations for each “plan”.

I’m looking for a massive time-saver and error-relief for a project that yields at least 20,000 records/yr, where my staff of two have been entering the selectors and the prices manually in an excelsheet, pulling in information and data from different systems and information points each time.
It saves time if they don’t have to look up the price each time they make a selection and it really is an error relief in so many different ways, to have this db driven rather than manual excel driven.

I hope this better explains what I’m aiming for, as I really, really hope Airtable will help me solve this.

thanks again
/elfur

[Julian_Kirkness]

Hi Elfur

In some other database applications, the feature you’re looking for would normally be called something like ‘Cascading Combo Boxes’ - i.e. the first drop down provides a filter for the second and so on.

This isn’t currently possible in Airtable but what you can do is to Concatenate the values in the pricing table into it’s Name field with a Formula - so your Name field would be something like

Mug - Blue/Tall/Decorated - from a formula - Item & " - " & Colour & “/” & Size & “/” & Decorated

Your users could then select the Mug by typing mug - bl and the system would filter the list of available items they can select:

This is what your price table might look like:

Not quite what you’re looking for but quite workable.

I hope this helps!

Julian

[Elfur_Logadotti]

Thank you Julian for this suggestion.

This is one of the things that I had considered as a solution, should there be no other, but as I mentioned, the problem that I’m trying to fix has complexities that this doesn’t solve.

I was hoping for the function where I could have the four fields as independent in the “planning” table because I forsee the ability to use the batch update block for one specific criteria in a schedule, while still in planning mode. And with any such batch update, the price and possibly other fields would be updated as a result, if these fields would be correctly computed as desired.

Perhaps when I’m further along with the project I will better realise the details and will be able to better describe them. I’m still in consept creation mode :slightly_smiling_face:

thanks
/elfur

[Julian_Kirkness]

Hi Elfur

Here, I think you’re saying that you don’t wan the values from the price table to be Looked Up in the other table - so that they DON’T change when the values in the Price table changes - is that correct?

If so, Airtable’s not so good at this on it’s own - but you could always look at using Zapier to get the values from the price table when you enter a record - the only problem is that it wouldn’t happen straight away.

[Elfur_Logadotti]

yeah, I’m kind of saying both :slightly_smiling_face:

I want the lookup feature to function where I can display price based on 4 different interchangeable criteria.

But I also want to have the option to alter one or more of the 4 different criteria after the initial lookup has taken place and have the calculated price field change its value based on the alterations made without any manual interaction on that field.
Perhaps, at the end of planning I opt for half of the mugs scheduled, should not be decorated and half of the half should be green rather than blue (or whatever) and the price table has a different price for these datapoints.

The solution you’re suggesting (and the one I will use until another will be available) won’t give me the second option, only the first option.
I will have to go and manually change the color field and the deco field and also change the lookup field to fetch the pricing, whereas the solution I’m dreaming of will create the lookup and the calculation based on the changed criteria on the fly.
For now, with the suggested solution, I will have to create an evaluation field, to ensure that the staff has remembered to alter the price lookup when they alter any of the criteria fields.

Here’s hoping, one day, I won’t have to.

thanks again.
/elfur

[W_Vann_Hall]

I’m not going to have a chance for a few days to sit down and go through your post, but you may want to check out this reply and see if there is anything you can take from the demo base it references. Admittedly, the vague similarities I thought I saw during a quick scan could easily not exist, in which case feel not just free but encouraged to forget I ever mentioned this. :winking_face:

[Luke_Maxcy]

Did you ever make any further headway on this?
I’m in a similar situation where I want to reference an item from an order and so I need to look up Item 1 from Order 1 and Item 2 from order 1 and then have the info from those individual items pull into my main table.

[Allen_Moldovan]

Airtable rolled out “Dynamic Variables” to their automation “Find Records”. It enables you to set a condition as dynamic.

IF this were to be rolled out to the the lookup field type, you could accomplish this.
This functionality would look through values in the destination table and output the values for only those records which match the conditions.

please be sure to email this to support@airtable.com to push them on this linked record feature