Filtered Look-Up list influenced from same record field entry

Been tinkering away with systems and methodology - this evening I got thinking (dangerous in itself) of how I could use Airtable to design/showcase product codes, and hopefully even allow users to develop their own products with this base.

However, preventing me from pushing forward is a limitation of Airtable that’s stuck year on year - but interestingly it really hasn’t bothered me until now… where I realise it’s kind of in the way and a big deal… so I figured I would try my luck and ask for some opinions on these lovely forums.

So for an example - Let’s say, we’re Willy Wonker’ing it all up in our bases :tophat::lollipop:;

We can use this “Candy Code” table to name and design our candy products and associated codes - however, as the colour red can apply to both strawberries and apples, we have a slight dilemma with a need for adding multiple reds into the Colours Table.

Sure, we can filter views via themes so that the “Apple Red” only shows up under an Apple view, and a “Strawberry Red” only shows up under a Strawberry view.

But this doesn’t solve a pick-list issue, where by selecting the “Theme” within the Candy Code table doesn’t then limit the selection of the following Colour and Flavour lists.

I’ve been thinking about this workflow for many hours now, concluding that two things;

  • A Feature Request for Airtable Devs is needed to solve this.
  • A Scripting App interface is needed to come to the rescue.

I’m keen to hear everyone’s thoughts on this, and please don’t judge me for my sugar coated example (although how cool would it be to design candy for a living! :rofl:). And I did find similar questions from a few years back, but just thought there’s no harm in checking as Airtable seems to be receiving a lot of development lately.

2 Likes

There has long been a desire for dependencies between pick lists.

I think On2Air has one solution when using a form.

If you have only one user at a time picking for only one record at a time, you can use a system of roll ups and a filtered view to accomplish this. I believe @Justin_Barrett and @W_Vann_Hall and I all have systems like this.

Finally, if picking the records in the second linked record field is done in scripting block, this is quite doable.

2 Likes

@Karlstens Be sure to email support@airtable.com to request this… they don’t seem to understand the importance of this much-needed feature.

There is a workaround to this dilemma here, but it comes with its own set of limitations & challenges:

1 Like

I came here to say how much I appreciate the Willy Wonka candy example base with related emojis!! :lollipop::lollipop::lollipop: :grinning_face_with_smiling_eyes:

I have to create example data on a regular basis. My last one was Marvel/DC Characters characters - Captain America, Iron Man, Wonder Woman, Superman, etc. :woman_superhero:

And yes, dependencies per field would be great!

As @kuovonne stated, it is possible using On2Air Forms. Here’s an example: How to Create Custom Forms Showing Filtered Records in Airtable

2 Likes

Thanks for your kind words everyone, and glad that I hadn’t missed anything obvious and that this issue still stands. @Hannah_Wiginton you’re free to expand on my Willy Wonka demo theme too :rofl:.

For the short-medium term, I guess I’ll see if I can work on a Script applet that allows for data entry via a filtered lists system - Although I suspect I’m not at the stage needed for writing such an app (and in all honesty I still have my Look-Up Fields MarkDown Text Roll-Up project that I need to push forwards with, as that’s something I KNOW I can code, just need time + motivation)

Regardless, I’ve reached out to Airtable Support regarding this thread, honestly I think this limitation needs to be on top of their list - especially now Interfaces are a thing and more and more users are using Interfaces for data entry, filtered lookup fields will be absolute magic to have within the Interface environment - I’d even go as far to say that I would be OK if this feature only existed via Interfaces but not within the Base itself (although realistically it would be best if it was in both the base and interface).

2 Likes

It’s worth noting, that even with this current limitation of unfiltered Look-ups, I pushed on with this database idea (and make no mistake, having to deal with unfiltered look-ups is very much a real problem) to find the potential that lay under-the-hood of such base design. And to my surprise I discovered this.

"\"" & LEFT(ARRAYJOIN({Methodology Index Codes},"  "),(FIND(",",ARRAYJOIN({Methodology Index Codes},",")))-1) & "\"," & ARRAYJOIN({Code Order Rollup}, " & ")

This one line of code is what blew my mind, when I realised that not only can Airtable be used to design product methodology (be it for Candy, or umbrellas, or Volvos, what ever) - but once the structure is in place it gives users the ability to write code-generating formula fields. But then… This lead to another bug-bear that many of us have witnessed and complained about previously - Linked-Field Field Entry Order.

Perhaps a Candy methodology should be, Flavour then Colour… or no, it should be Colour, then flavour! What ever it is, unfortunately, the more complex the methodology, the more Airtable makes this task manual and laborious.

Note the restructure in the Base by the Ompa-Loompas, for instead of a separate Flavour table and a Colour table, they’ve now been both rolled up into a “Global Fruity Index” table.

All Colours and Flavours created in the one index and mapped to “Type”. And it’s worth noting that here is where users will find real power to Airtable Views, as a “Colour” view can be created, and a “Flavour” view created, and then the pick-lists in the Candy Code creator table can be limited to those very lists, but this then swings back to the original problem of this base, the View pick-lists can’t be field-dependant filtered, such as filtering by a theme. But I digress, back to this secondary issue I’m hi-lighting.

Eventually, with a bit of remapping each methodology of every product can be setup dynamically - which is freakin’ amazing!!! … but with one huuuuge draw back - the order of each methodology (assuming they’re unique for each product - because of “reasons”) can only be adjusted manually through a drag and drop process. :sob:

All the fancy Airtable views that can programmatically address order within the Index table are simply ignored when it comes to the structure needed within the Methodology, and the user/developer is left with this…manually, god-forsaken process of dragging and dropping each individual entry, over and under, and under and over, ad nauseam - Too much candy perhaps? ( ͡~ ͜ʖ ͡°).

Now this is not such a big deal for a methodology that consists of 2 index, a flavour and a colour… but… in the real world - right now I’m looking at a list of 200 indexes across 200 unique products… and growing!

Airtable devs, if you’re reading. Please. These two features need love! Absolute and wholehearted love!

I’d love to hear everyone else’s experience and suggestions with this second limiting issue I’ve highlighted here.

2 Likes

Lol. I’ve been writing code-generating formulas for a while. The main issue is that the code cannot be directly used. It must be copied from the formula result to wherever it is going to be used.

I have a scripting automation that I use to automatically sort linked record fields.

1 Like

I’ll note to make time for researching a scripted method for sorting linked record fields. Am I daft in thinking how useful the ability to sort linked record fields via a sorted table view would be?

And I’m OK with copying out output formula - I’d hate to think of what mischief I’d get up to if we could call them somehow. :joy: If anything, they’re saving my eyes when it comes to writing a Switch statement, consisting of many cases and countless field references - what was taking 10 minutes of typo’s per case is now a simple copy and paste. Unfortunately this is where the order of references becomes important, so definitely need to look into your idea of a script to sort orders (and I know straight away I now have two projects that could use this).

1 Like