Multiple Depeding dropdown list

AppSheet AppSheet Q&A
,
1

Hello,

I’m working on a tourism app and I have the following tables:

  • Destination (Parent Table)
  • Restaurant (Child Table of Destination)
  • Restaurant Services (Child Table of Restaurant)

Logic behind it: A Travel Agent will book a service (buffet, for example) at a restaurant in a specific destination.

The issue:
I’m trying to create several depending dropdown list

Restaurants, when a destination is selected (This one works).
Services, when a restaurant is selected (This one is the problem since it depends on the previous one).

Progress

This is the data structure:

3X_8_7_8776556c04c950749528f6fe0edb42123cf3bd4e.png
3X_8_7_8776556c04c950749528f6fe0edb42123cf3bd4e.png1022×623 79.5 KB

Based on this article (https://help.appsheet.com/en/articles/961554-dependent-dropdown) my formulas at Data Validity are as follow:

For restaurant dropdown (this works fine)
IN( [_THIS], SELECT(Restaurant[restName], [_THISROW].[destinoID] = [restDestino]))

For restaurant services dropdown
IN( [_THIS], SELECT(RestaServ[restServServicio], [_THIS].[restID] = [restID]))

However, when I open the form the list of restaurant services is already populated with all the options even though the restaurant hasn’t been selected yet.

I’m guessing that this might be because of [_THIS].[restID] is blank when the form opens?

Thanks for any assistance!

1 Like
2

Automatic dependent dropdowns only work for columns with Valid If expressions that use only a table-column reference. You appear to be using more sophisticated expressions, which will override the automatic dependent dropdown feature.

This your non-working expression:

IN( [_THIS], SELECT(RestaServ[restServServicio], [_THIS].[restID] = [restID]))

Try this instead:

IN( [_THIS], SELECT(RestaServ[restServServicio], [_THISROW].[restID] = [restID]))
1 Like
3

Hi Steve!

I have applied this change and the result is the opposite that it was happening hehe.

Now when the form loads the restaurant services appear in blank (which is great!) but even if a Restaurant is selected the previous dropdown the list of services won’t upload.

1 Like
4

Try this for restID:

FILTER("Restaurant", [_THISROW].[destinoID] = [restDestino])

And this for restServID:

FILTER("RestaServ", [_THISROW].[restID] = [restID]))

In a Valid If expression, the IN() wrapper of IN([_THIS], ...) is unneeded (but also okay to use) if ... is a list. Both SELECT() (which you used) and FILTER() (which I use here) produce lists.

I’ve suggested FILTER() because it always produces a list of Ref values. Many people get confused with how to gather Ref values using SELECT(), so FILTER() removes the chance to make a mistake.

1 Like
5

Steve:

FILTER("RestaServ", [_THISROW].[restID] = [restID]))

It works great:) Thanks!

It is great to have this work around.

1 Like