cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cweaver
Advocate II
Advocate II

Dynamic Dependent Dropdown Default Value

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.

3 ACCEPTED SOLUTIONS

Accepted Solutions
DylanSimons
Resolver III
Resolver III

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.

View solution in original post

@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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

@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!!! 

View solution in original post

8 REPLIES 8
DylanSimons
Resolver III
Resolver III

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.

View solution in original post

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.

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)

 

@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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

@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.

@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.

@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.

@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!!! 

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,030)