Display a Single Result after Making Selections from Cascading Dropdowns

[suzinger]

Hello Everyone!

So I am creating an app that will automate equipment sizing for our Salesmen.  The app creates a collection based on the input from the Salesmen.  There are three cascading/dependent dropdowns in the app that all work perfectly.  However, how do I go about displaying a single result based on the Salesmen's selections?  

My data is a Sharepoint list named Units that has columns named Style, Type, Height, Size.

How do I display only the Size of the Unit for a specific Style, Type, and Height (chosen from dropdown lists)?

I tried this: LookUp (Units, Height=UnitHeight.Selected.Result, Size).   But this only gives me the first Size for the chosen Height not the Size of the specific unit I am looking for.  Do I need to somehow "nest" the LookUps for each choice from the dropdown lists?  I essentially want to drill down to the specific unit I need based on choices from the dropdown lists and then display that specific size.

Any assistance would be sincerely appreciated!

Thank you!

[mdevaney · Accepted solution]

@suzinger 

You are definitely on the right track by using LOOKUP since it will return only a single result based on some criteria.  The problem is LOOKUP will return only the first result it finds and then stop looking.  If this criteria outputs the wrong unit's size then we know the Height of the unit is not unique and we should be doing something different.

Height=UnitHeight.Selected.Result

I am not aware of how your cascading dropdown controls work but I suspect any combination of the three (Style, Type and Size) should find a unique result in the Units list.  You can use multiple criteria within a LOOKUP like this.

LookUp(
    Units,
    (
        Style=UnitStyle.Selected.Result 
        And Type=UnitType.Selected.Result
        And Height=UnitHeight.Selected.Result
    ),
    Size
)

Please keep in mind that I am only guessing at your column names here and you will have replace them with the proper names.  If you are not able to find the solution by building from my example I would suggest the next step is to post a screenshot and some sample data.  Anything that can help me to replicate your situation helps 🙂

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[v-yutliu-msft]

Hi @suzinger ,

The reason why you met this problem is that Lookup function will not return the first record that meet the requirements.

Could you tell me where do you want to use the filtered size value?

If you want to use it as a table (for example, set a drop down's Items), please try filter function. This function will return all the records that meet the requirements.

Try this:

Filter (Units, Height=UnitHeight.Selected.Result,Type=....,Stype=...).Size

//this will return a table with all the size that meet the requirements

If you want to get the first value, you could use:

First(Filter (Units, Height=UnitHeight.Selected.Result,Type=....,Stype=...)).Size

If you want to get the second value, you could use:

Last(FirstN(Filter (Units, Height=UnitHeight.Selected.Result,Type=....,Stype=...),2)).Size

....

If you want to get the first value that meet all the requirements, you could also use:

 LookUp (Units, Height=UnitHeight.Selected.Result&&
                Size=....&&
                Style=..., 
          Size)

Please check this doc, pay attention to the differences between filter function and lookup function:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

Best regards,

[mdevaney]

@suzinger 

Thanks for coming back to mark the solution.  Happy New Year!

[suzinger]

Thank you so much for your assistance @mdevaney!  I sincerely appreciate your time in answering my question!  Happy New Year to you!

[suzinger]

Thank you for taking the time to explain the differences between the LookUp and Filter functions @v-yutliu-msft.  I sincerely appreciate your helpfulness and the information you provided!  Happy New Year!