Dynamic Dependent Dropdown Default Value

[cweaver]

Hello,

I have a series of dropdowns for users to locate a specific item in a large SharePoint list. The final dropdown is a material selection dropdown using the following item property:

Distinct(Filter(SourceTable,Dropdown3.Selected.Result=TargetColumn),ResultColumn)

This formula will yield between 1 & 5 - never 0 - results. In an effort to improve UX, if the above formula only yields 1 result, I would like to push that result as the default. Is this possible? I need to avoid using delegation as my SP source list is well over 2,000.

[DylanSimons · Accepted solution]

If you set the Default property to:

If(CountRows(Distinct(Filter(source,logical_test)))=1, Distinct(Filter(source,logical_test)))

That should do what you want, otherwise there is no default.

[cweaver]

Hi @DylanSimons,

Thanks for looking at this. Your solution was my first instinct to try, however I encountered 2 issues:

1. Error message saying that a text value is expected.
2. Expected delegation warning notice due to the CountRows function.

[DylanSimons]

Yes, both of those are true.

1. you would change the second argument in the "If" function to something like to get text(assuming you have a Title field with text in it):

Distinct(Filter(source,logical_test)).Title

2. This is something that cannot be avoided unless you collect the results. Count is not a delegable function, though it is supposedly planned

The only way I assume this would work is if you have an OnChange property of the previous dropboxes which would collect the filtered results and then use the collection in the countrows. On the dropdown before the one we are discussing the OnChange would look like this:

ClearCollect(MyCollection, Distinct(Filter(SourceTable,Self.Selected.Result=TargetColumn),ResultColumn))

Then your Default on the last dropdown would be:

If(CountRows(MyCollection)=1, MyCollection.Title)

[RandyHayes · Accepted solution]

@DylanSimons 

FYI...a couple ways around the delegation warning for CountRows:

1) Inject another column:

CountRows(
    AddColumns(
        Distinct(Filter(SourceTable,Dropdown3.Selected.Result=TargetColumn),ResultColumn)
        "tmp", true
    )
)

2) Count Characters:

Len(
    Concat(
        Distinct(Filter(SourceTable,Dropdown3.Selected.Result=TargetColumn),ResultColumn)
        Left(Result, 1)
    )
)

Just an FYI

[cweaver]

@DylanSimons,

I hadn't thought of adding .Title to my first attempt to remedy the first issue. I like your 2nd approach of adding an OnChange event to the previous dropdown; that, however yields 2 different issues:

1. There are no items in the collection, no matter how many times I change the value of the dropdown or run the app from OnStart.
2. The Default property of the dropdown in question is throwing an error with the MyCollection.Title portion of the code: "Name isn't valid. The identifier isn't recognized." The only identifier available to me Result which throws up an "Expected text value." error.

[DylanSimons]

@cweaver, I would use @RandyHayes 's solution above. It is a very good workaround to the delegation warning!

Using collection would slow your performance compared to his solution. If you need help understanding it, let me/us know.

[cweaver]

@DylanSimons& @RandyHayes,

Thanks for helping with this; I think I'm really close! I inserted a random text label to test out the formulas on, and I did the following formula for the Text property:

If(CountRows(AddColumns(Distinct(Filter(SourceTable,Dropdown3.Selected.Result=TargetColum),ResultColumn),"tmp",true))=1,"true","false")

which does correctly display true or false depending on the Dropdown3 selection, however, I cannot get the true value to be from the SP source list; when I try and set the true value of the If condition to:

Distinct(Filter(SourceTable,Dropdown3.Selected.Result=TargetColumn),ResultColumn).Title

I still get the same identifier error with the .Title portion of that code and the only identifier available to me is Result which throws up the "Expected text value." error. I also get the same errors when trying to apply the If formula in the Default property of the dropdown in question.

[cweaver · Accepted solution]

@DylanSimons & @RandyHayes,

With your help, the following code for the default property works wonderfully:

If(CountRows(AddColumns(Distinct(Filter(SourceTable,Dropdown3.Selected.Result=TargetColumn),ResultColumn),"tmp",true))=1,LookUp(SourceTable,Dropdown3.Selected.Result=TargetColumn,ResultColumn))

Thank you!!!