Cascading 3 columns issues-Values from dropdown need to be reselected to display correct value in another dropdown

[SD29]

Hi,

I have 3 fields in my app which are Combo Box1, DropDown1 & DropDown2. All 3 are cascaded with each other , which means where based on values selected in combo box, dropdown1 list is displayed and when values selected from dropwdown1, dropdown2 returns a value.

Below SharePoint Lookup List has Assign To as Person/Group column data type-

App has ComboBox1 for Team, Person Detail for DropDown1 & Assign To for DropDown2.

Existing functionality on DropDown1- When I select more than 1 values from Combo Box1  then Drop down1 only displays NA .

Issues1- If I select Team C from Combo Box1, DropDown1 is displayed with Person 3 & Person 4 values but Dropdown 2 is blank. But when I reselect Person 4 and then click again on Person 3 in DropDown1, correct value person3@domain.com is displayed in DropDown2.

Unable to understand as WHY I am required to go back and forth selection in dropdown1 to reflect values in dropdown2? 

Issue2- Due to Issue1,   DropDown2 is displaying old value and not displaying na@domain.com when I do multiselection in combo box1 where NA is displayed in DropDown1.

Kindly help me with this as I am stuck with this since days.

[Gochix]

@SD29 ,

Please provide the formula you currently have for Dropdown2 Items property.

or change your Dropdown2 Items property to either:

If(CountRows(ComboBox1.SelectedItems)>1,["na@domain.com"],Distinct(Filter('Team Lookup','Person Detail'=Dropdown2.Selected.Value),'Assign To'))

or

If(CountRows(ComboBox1.SelectedItems)>1,["na@domain.com"],Table({Value:LookUp('Team Lookup','Person Detail'=Dropdown2.Selected.Value,'Assign To')}))

_____________________________________________________________________________________
Please give a thumbs up if I resolved your issue! Please click Accept as Solution to close the topic!

[SD29]

Hi @Gochix , Thanks for your response. 

Items Property for DropDown2 is below where collection is created -colAssignedTo

For OnChange property for DropDown1 is below

Clear(colAssignedTo);
ForAll(Filter('Team Lookup',Person Detail=DropDown1.SelectedText.Value),Collect(colAssignedTo,ThisRecord.AssignedTo));

Instead of mentioning [''na@domain.com"] in code, can it be replaced with anything else which can make it dynamic instead of hardcoding the email address as it might change in future.    

If I change the email address in SharePoint list in future, Again I have to change in PowerApps code as well. Can you share an code where even if I change the email address under Assign To column in SharePoint list, app will automatically reflect the new email address for NA displayed in DropDown1.

My Issues1 still present- If I select Team C from Combo Box1, DropDown1 is displayed with Person 3 & Person 4 values but Dropdown 2 is blank. But when I reselect Person 4 and then click again on Person 3 in DropDown1, correct value person3@domain.com is displayed in DropDown2.

please help me with both the issues .

[Gochix]

@SD29 ,

You don't have to complicate things by creating a new collection with OnChange from Dropdown1.
All you need is to add the formula on the Dropdown2 Items property.

use:

If(CountRows(ComboBox1.SelectedItems)>1,Table({Value:LookUp('Team Lookup',Team="Unknown",'Assign To')}),Table({Value:LookUp('Team Lookup','Person Detail'=Dropdown2.Selected.Value,'Assign To')}))

Now the formula is looking for the "Unknown" record from Team column in your SharePoint list. As there must be something to look for, I believe the name "Unknown" will stay the same.

P.S: It is of course totally up to you, but what is the reason for having the 2nd dropdown if it will only display a person's email address? You will not be able to select any other email address. Wouldn't it be easier for you to just display it in the text label?

_____________________________________________________________________________________
Please give a thumbs up if I resolved your issue! Please click Accept as Solution to close the topic!

[SD29]

Currently its throwing an error of - Expected a different collection structure.

If I change the Drop Down2 with Text label, what shall be the code then as the email address displayed in Assigned To column, gets the mail notifications too via backend power automate flow on form submit.

Also opting for Text label, will also eradicate my issue 1 of reselection of values from DropDown1 as values were not getting displayed in DropDown2?

[Gochix]

@SD29 ,

Check your collection - there is no issue with the code from my side. 

AssignedTO shouldn't be AssignTo ?

Other than that I can't see anything unusual.

_____________________________________________________________________________________
Please give a thumbs up if I resolved your issue! Please click Accept as Solution to close the topic!

[SD29]

Hi I modified the same and commented out the OnChange property for DropDown1 as well. Still same issue stating - Expected a different collection record structure.

Now can you share code for text label if i do not use DropDown2 for Assign to in app. What shall be the code then?

[Gochix · Accepted solution]

@SD29 ,

Not sure why it doesn't work for you, but can you try it with Filter function and see if that makes a change?

If(CountRows(ComboBox1.SelectedItems)>1,
Distinct(Filter('Team Lookup',Team="Unknown"),'Assign To'),
Distinct(Filter('Team Lookup',PersonDetail=Dropdown2.Selected.Value),AssignTo))

If you wish to use a text label then just use:

LookUp('Team Lookup','Person Detail'=Dropdown2.Selected.Value,AssignTo)

Feel free to read some documentation to learn something as well.

https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-filter-lookup 
_____________________________________________________________________________________
Please give a thumbs up if I resolved your issue! Please click Accept as Solution to close the topic!

[SD29]

It still gave the same error with DropDown2.

Ok Let me try with text label then.

[SD29]

Just want to understand, as DropDown2 was used for Email Address. Now If I am using Text Label in place of that and removed the dropdown2.

why its required to mention Dropdown2 in code.

LookUp('Team Lookup','Person Detail'=Dropdown2.Selected.Value,AssignTo)

Bit confused. Apologies, if I misunderstood something.