I'm trying to use 4 dependent dropdowns but it's not working.

[Maki]

Hello,

I'm trying to use 4 dependent dropdowns from a group column but it's not working.

The app has two tables, "master" and "form", each with four columns.

The "Master" table are:

The 4columns in the "Form" table are:
Column1 name is "group1", type is Enum, base type is Ref and Referenced table is "Master".
Column2 name is "group2", type is EnumList, base type is Ref, and Referenced table is "Master".
Column3 name is "group3", type is EnumList, base type is Ref, and Referenced table is "Master".
Column4 name is "group4", type is EnumList, base type is Ref, and Referenced table is "Master".

The desired behavior is
Dropdown1, if you select "A" in column[group1], you can select 'A1', 'A2' and 'A3' in column[group2].
Dropdown2, if you select "A1" in column[group2], you can select "Auser1" in column[group3].
And, Dropdown2, if you select "A1", "A2", "A3" in column[group2], you can select "Auser1", "Auser4", "Auser6", "Auser7" in column[group3].

Dropdown 1 and 2 worked.
The following expression entered in dropdown 3 "Valid if" gave the desired result when only one value was selected from the EnumList.

SELECT(
Master[group3],
AND(([group1] = [_THISROW].[group1] ),
IFS(CONTAINS([_THISROW].[group2],"A1"),[group2] = "A1" )
))

But I don't know the formula to select multiple comma separated values ??from an EnumList.
By the way, the following formula did not give the desired result.

SELECT(
Master[group3],
AND(([group1] = [_THISROW].[group1] ),
IFS(CONTAINS([_THISROW].[group2],"A1,A2,A3"),CONTAINS([group2] ,LIST("A1","A2","A3" ))
))

How can I get multiple values ??like "in" in SQL?
How can dropdown 3 work?

[WillowMobileSys · Accepted solution]

Please check this article first for more details:

Dependent drop-downs 

And You might want to look at the IN()  function for finding items in a list.

[Maki]

Hello WillowMobileSys,

Thank you for your reply.

I put the following formula in the "Valid If" of dropdown3 and it did what I wanted.

---- Valid If
IN(
[_THIS] ,
SELECT
(
Master[group3],
AND(
([group1] = [_THISROW].[group1]),
CONTAINS([_THISROW].[group2], [group2])
)
)
)

In addition, put the following expression in "Initial value" so that when "B" is selected in "group1", "group3" associated with the value selected in "group2" is automatically selected.

---- Initial value
SELECT
(
Master[group3],
AND(
([_THISROW].[group1] = "B"),
IN([group2], [_THISROW].[group2])
),
TRUE
)

Also put the following expression in "Editable_If" so that the auto-selected value cannot be edited:

----- Editable_If
IF([group1]="B",FALSE,TRUE)

Now it works as expected.

I find it a little inconvenient that "automatically advance form" does not work in the next required column when you enter a value in "initial value".
Or is there another way?

Thank you.

[WillowMobileSys]

---

@Maki wrote:

I find it a little inconvenient that "automatically advance form" does not work in the next required column when you enter a value in "initial value".
Or is there another way?

---

Not that I'm aware of

[Maki]

Thank you.