Dependent dropdown 4 levels - auto select 4th level

AppSheet AppSheet Q&A
1

I have a working dependant drop down using Data Validity rules that is 4 levels deep after the 3rd the last one is only 1 selection and you have to click it to select it.

The value is filled in but not selected I am trying to automatically select it. So I think I need to set the initial value based on the 3rd selection. Or is there a setting to automatically select if there is only one value in a dropdown.

2

I’m curious how you are implementing the “dependent drop down”?

3

the 4 columns are Region, Division, Location Name, Branch
so for each column in the Data Validity> Valid If you put Table[Column] with its own column
so In the Region column I have LocationMaster[Region],
in the Division column I have LocationMaster[Division],
in the Location Name column I have LocationMaster[Location Name],
in the Branch column I have LocationMaster[Branch]
When you go to that form only the Region dropdown shows until you choose a value then the Division shows, then the Location Name shows, then the Branch is a button I have to click to select, this is the thing I want to change. I would like it to be autoselected since it has been filtered down to 1 value.

4

Ok, I get it!

In Initial Value of your Branch column, set the value based on the COUNT of the list of branches equal to 1. So,

IF(COUNT(branch selection) = 1, <ANY(branch selection)>, "")

Use your SELECT statement in place of “branch selection”. The ANY() statement returns the value instead of the row. Normally, it returns the top value of the list but the list could be in any order. since you KNOW there is only 1 row then it will return the value you want.

I hope this helps and I hope have it right!

5

This might give you an idea:

[Filtering available options in selection column](https://community.appsheet.com/t/filtering-available-options-in-selection-column/14793/3) Questions

To the best of my recollection, the pattern for dependent dropdown Valid If expressions is: SELECT( MyTable[ColumnA], TRUE, TRUE ) SELECT( MyTable[ColumnB], ([_THISROW].[ColumnA] = [ColumnA]), TRUE ) SELECT( MyTable[ColumnC], AND( ([_THISROW].[ColumnB] = [ColumnB]), ([_THISROW].[ColumnA] = [ColumnA]) ), TRUE ) SELECT( MyTable[ColumnD], AND( ([_THISROW].[ColumnC] = [ColumnC]), ([_THISROW].[ColumnB] = [ColumnB]), ([_THISROW].[ColumnA] = [ColumnA]) …

1 Like
6

I can make the dependent drop downs work with that - but it still has the same problem he value is not selected unless I click it.
I just need a switch that says show and select the value
so maybe you could do MyTable[ColumnD].autoselect and it would not only show, but show and select the first value.

7

Knowing the expression pattern used for dependent drop-downs, you can produce the results a dependent drop-down would give you for the desired column:

SELECT(
  MyTable[ColumnD],
  AND(
    ([_THISROW].[ColumnC] = [ColumnC]),
    ([_THISROW].[ColumnB] = [ColumnB]),
    ([_THISROW].[ColumnA] = [ColumnA])
  ),
  TRUE
)

Given your expectation there will only ever be one result from that expression, you can extract the first and only item from that List result by wrapping it in ANY():

ANY(
  SELECT(
    MyTable[ColumnD],
    AND(
      ([_THISROW].[ColumnC] = [ColumnC]),
      ([_THISROW].[ColumnB] = [ColumnB]),
      ([_THISROW].[ColumnA] = [ColumnA])
    ),
    TRUE
  )
)

You can then make this the Initial value expression for the column. No Valid If expression needed. You may want to set the column’s Editable? property to the expression FALSE to prevent the user from changing the auto-selected value.

3 Likes
8

Steve:

ANY( SELECT( MyTable[ColumnD], AND( ([_THISROW].[ColumnC] = [ColumnC]), ([_THISROW].[ColumnB] = [ColumnB]), ([_THISROW].[ColumnA] = [ColumnA]) ), TRUE ) )

The expression is valid but its result type ‘Ref’ is not one of the expected types: Number