How to create a column where its List Values update, based on another column

[Bluegrass_Digital]

I have data defined in a LOOKUP table for both Section and Area - see here: Screenshot - f4d52aec7a542243828461bf34a6f2e9 - Gyazo

I am trying to make the AREA column in my ALL DATA table be an available list of options returned from my SECTION lookup table, that only returns the SECTION.AREA column for the user to select from - see here: Screenshot - 8267b1ca2bb69a139f843bc2f802d9f5 - Gyazo

I tried this formula and many others but just can’t seem to get it working:

If(Lookup(SECTION,SECTION.SECTION,thisRow.SECTION)=SECTION.SECTION,"TRUE","FALSE")

Any advice on how to do this?

[Piet_Strydom]

HI

Not sure that I understand your question.

When you select a section, do you want related areas related to that section to be pulled in?

In other words, if you select “performance”, do you want to see Backend and Frontend?

Lookup() is an outdated formula, I would recommend using either filter() if you want to auto-populae, or a lookup column if you want to select from a list or subset of a list.

[Pch]

Hi @Bluegrass_Digital and Welcome to the Community !

Is this what you’re trying to accomplish ?
(Try to select a value in the lookup field Area in the table All )

If so, you should add the “reverse” part of the lookup field Area in your Section table by adding a lookup field from your Section table to your Area table and using a formula such as :

Section.Filter(Area.Contains(thisRow)).First()

(Area in the formula is in fact CurrentValue.Area)

This will retrieve the sections you linked to the areas in your Section table
(Sorry, I’m part of beta concerning the lookup fields and I don’t have access to the “old ones”, so I’m replying from memory )

Then, in your table [All Data], in the Lookup options of your Area lookup field, you should find a section Option settings somewhere in the menu and once you click on it you should find the part Filter.
In Filter Coda should suggest you something looking like Section = Section

This could be what you’re looking for

[Bluegrass_Digital]

Hi @Pch thank you for your reply, and I managed to get part of your solution working, BUT it’s not quite what I need. here’s a better and hopefully simpler explanation what I need:

1. In the LOOKUPS table, I have specific AREAS defined for each SECTION in the Sections table - see here: Screenshot - bf5979eb43144c6c142f0d246d3a3e5f - Gyazo
2. And I only want those Areas to be available in the ALL DATA table when a user selects a Section value, otherwise All Areas should be available - see here: Screenshot - 626c440c0e2827ac80555d8616b71bbb - Gyazo

Appreciate your help!

[Pch]

Hi @Bluegrass_Digital

I think I made a mistake in my previous reply, sorry .

Could you try a slightly different Filter() formula in the Section lookup in your table Area and see if this help with the no matches found (for the performance section) in your [All Data] table

SECTION.Filter(AREA.Contains(thisRow))

As for this:

@Bluegrass_Digital

otherwise All Areas should be available

It seems like the only way to get all areas showing as selectable options (so, when there’s no match found), would be when a section doesn’t have any areas linked to it (in the Section table).

As, you’re asking to have as selectable options in your Area lookup field in the table [All Data], the areas filtered by the sections linked to them (and compared to what’s in thisRow.SECTION in the table [All Data])

I managed to get this by using this formula :

If(
  thisRow.Section.Area.IsNotBlank(),
  Section.Contains(thisRow.Section),
  Section
)

(Note that Section in the 2nd and 3rd lines is in fact CurrentValue.Section, as it correspond to the values in the lookup field Section in your Area table)

I hope this helps

[Bluegrass_Digital]

Thank you @Pch - you did it! Works perfectly, really appreciate the assist.

[Pch]

No problem @Bluegrass_Digital !
I’m glad to know this helped you moving forward !