How to make a dependent dropdown based on a multiple value column?

[Jadarius]

Hello,

I have dependent drop down with the first drop down displaying options from a multiple choice column, but users can only select one choice. The first drop down will limit what users can select. Where I'm having the trouble is that each cell in that column contains multiple values. How do I get power apps to notice the different values in this column, then return the "NSN" column results that contain that value? At the moment when I click on the "NSN" Drop down it only shows one blank selection.

My List:

My App:

My "Division" DropDown Settings:

My "NSN" DropDown Settings:

This seems like a very easy solution, but when i research it, i only get topics about multi-select drop downs or multiple values in a column. My apologies if this is the case and thanks in advance!

[Amik]

@Jadarius - so I am clear, you want to display each delimiter separated Division number as a standalone value in a Dropdown control, and that this Dropdown control should remain single-select.

You can then want to Filter the options in a separate Dropdown control (NSN) to display only NSN numbers based on the selected Division?

[Jadarius]

Hi @Amik,

I appreciate the response! Yes, exactly what I wanted to do. Not only to restrict selections, but also to filter excess options since my list is about 3,000 items.

I came across this post that could make it simpler using JSON Column Formatting: Concatenate SharePoint Library Columns with Multi Select Options into a Single Column - SharePoint S...

What do you think? Thanks again, I really appreciate the help!

[Amik]

@Jadarius - assuming the Division field is not a multi-choice field, to display each distinct Division in your Dropdown/ComboBox control, enter the following in the Items property:

Distinct(
    ForAll(
        Ungroup(
            ForAll(
                'MALS-24 HazMat AUL''s',
                Split(
                    Division
                    ";"
                )
            ),
            "Value"
        ),
        Trim(Value)
    ),
    Value
)

To display an associated NSN number depending on the selected Division above, enter:

With(
    {
        _filtered_data: Filter(
            'MALS-24 HazMat AUL''s',
            Len(ComboBox1.Selected.Value) = 0 || ComboBox1.Selected.Value in Division
        )
    },
    Sort(
        Distinct(
            _filtered_data,
            NSN
        ),
        Value,
        SortOrder.Ascending
    )
)

Note:

If the Division field is a Multi-Choice field, use:

Distinct(
    ForAll(
        Ungroup(
            ForAll(
                'MALS-24 HazMat AUL''s',
                Split(
                    Concat(
                        Division
                        Value,
                        ";"
                    ),
                    ";"
                )
            ),
            "Value"
        ),
        Trim(Value)
    ),
    Value
)

And for the NMS dropdown:

With(
    {
        _filtered_data: Filter(
            'MALS-24 HazMat AUL''s',
            Len(ComboBox11.Selected.Value) = 0 || ComboBox1.Selected.Value in Concat(
                Division
                Value,
                ";"
            )
        )
    },
    Sort(
        Distinct(
            _filtered_data,
            Title
        ),
        Value,
        SortOrder.Ascending
    )
)

Note that this solution will not be delegable beyond 2,000 records. If you have a field or a parameter which we can Filter on which we can guarantee will return less than 2,000 rows, you can then run the above on that smaller subset of data without running into delegation concerns.

[Jadarius]

Thanks for the solution @Amik . Though it didn't work, it only displays one blank value for both.  

I also had to change my column separator to "," instead of ";" because my SP column wasn't working how I wanted.

Lastly, both drop downs returned about 14 different errors until i changed them to a single line and added "." between "Division" and "Value". Like so:

Distinct(ForAll(Ungroup(ForAll('MALS-24 HazMat AUL''s',Split(Concat(Division.Value, ","),",")),"Value"),Trim(Value)),Value)

With({_filtered_data: Filter('MALS-24 HazMat AUL''s',Len(DataCardValue11.Selected.Value) = 0 || DataCardValue11.Selected.Value in Concat(Division.Value,","))},Sort(Distinct(_filtered_data,Title),Value,SortOrder.Ascending))

The most results returned for any of the divisions would only be a few hundred. I also have a text search box for the "NSN" column, that I hope to allow for more filtering of the NSN Drop Down.

Any suggestions? Thanks again🙏

[Amik]

"didn't work" is not very specific @Jadarius.

1. Is there an error message? If yes, what is the error message?
2. Could you confirm which method you used and what field type is for the Division field.

[Jadarius]

@Amik It just doesn't display any Division or NSN. When i click the drop downs they both show a single blank value; no divisions or nsn's. No errors either, just a delegation warning for the nsn dropdown.

Division dd:

NSN dd:

For the method, i used the multi-choice field. The division column is a choice column:

[Amik]

@Jadarius - to start, that is not the code I gave you. Please use the code as provided without performing your own modifications (check the Split/Concat section, you have done something different).

[Jadarius]

Hey @Amik,

I fixed my column formatting with this JSON code: 

{
   "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
   "elmType": "div",
   "txtContent": "=join(@currentField, '; '"
}

When I used your code without the modifications it returned the below errors. I added the "." thinking it needed to look into the column for the values and the error for "Value," suggested an operator beforehand. My apologies.

Distinct(  Invalid number of arguments: received 1, expected 2.

ForAll( The function 'ForAll' has some invalid arguments.

Ungroup( The function 'ForAll' has some invalid arguments.

ForAll( Invalid number of arguments: received 3, expected 2.

Split( Invalid number of arguments: received 1, expected 2.

Concat( The function 'Concat' has some invalid arguments.

Value Expected operator. We expect an operator such as +, *, or & at this point in the formula.

Value, Unexpected character. Characters are used in the formula in an unexpected way.

";"      Expected operator. We expect an operator such as +, *, or & at this point in the formula.

),        Unexpected character. Characters are used in the formula in an unexpected way.

Trim(   The function 'Trim' has some invalid arguments.

(Value) Name isn't valid. "Value" isn't recognized.

Value   Name isn't valid. "Value" isn't recognized.

)           Unexpected characters. The formula contains 'Eof' where 'ParenClose' is expected.

I went ahead and did the same thing with the NSN drop down control, but here's what it looks like with no modifications:

Division Invalid number of arguments: received 1, expected 2-3.

Value Expected operator. We expect an operator such as +, *, or & at this point in the formula.

Value, Unexpected character. Characters are used in the formula in an unexpected way.

";"       Expected operator. We expect an operator such as +, *, or & at this point in the formula.

)          Unexpected character. Characters are used in the formula in an unexpected way.

}          Unexpected characters. The formula contains 'CurlyClose' where 'ParenClose' is expected.

||          Delegation warning. The "Filter" part of this formula might not work correctly on large data sets.

in         Delegation warning. The highlighted part of this formula might not work correctly on large data sets.

Below is if I added the "."...I can't recall what else I did, but I didn't get any errors. Regardless it wasn't right.

Sorry for the lengthy post, just wanted to provide enough info since you can't see inside my app.