Filter DropDown list with specific record item using Collection

(0) Share

Report

Posted on by LeonardLeo

184

Hi There,

I have a "category" Dropdown that referenced to a Collection at Screen OnVisible event.

ClearCollect(CollectEmerg,{Result1:"All Emergencies"},SortByColumns(ForAll(Distinct(PhoneBook,Subcategory.Value), {Result1: ThisRecord.Value}),"Result1"))

And the Dropdown Item is the collection "CollectEmerg". But its bringing all Subcategory items. The red underlies are the cascading items from Emergency main category which I want to show in this dropdown list.

I am trying the following code instead of above but it is showing error ..

ClearCollect(
 CollectEmerg,
 {Result1: "All Emergencies"},
 SortByColumns(
 Filter(PhoneBook, Category.Value = "Emergencies"),
 "Result1"
 )
)

Can anyone tell me where is the issue?

Thanks

Categories:

Building Power Apps

All responses (6)

Answers (1)

Sort by

NPPlatform 606 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
It's hard to say without seeing the data from your datasource "PhoneBook". But am I correct in assuming you want all the distinct names from your datasource PhoneBook where the category is "Emergencies"? If so, your code would be something like

ClearCollect(CollectEmerg, Distinct(Filter(Phonebook, Category.Value = "Emergencies")))
This will first filter your datasource Phonebook on Category.Value = "Emergencies", then it will only keep distinct rows and these will be collected in your collection "CollectEmerg".

If this doesn't help it would be nice if you could share your datamodel.
LeonardLeo 184 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi NNP,
The above code is giving error which should not. Here is my data model.
My SP list name is PhoneBook
It has Category, Subcategory, Location, Name and Phone number fields. I want to dropdown of the Subcategory filed which will only show the items , Fire Service, Ambulance, Police Station under main Category "Emergency".
I have a collection at Screen OnVisible event that helps me show "All Emergencies" in that drop down to show all data in my gallery. I dont bother on Delegation warning as my data set is not too large.

Data structure... Category Subcategory Location Name Phone Emergency Fire Service CXB ABC2 XXXXX Emergency Police Station CXB ABC1 XXXXX Emergency Ambulance CXB ABC4 XXXXX Emergency Fire Service CXB ABC6 XXXXX Service Fire Service BCO ABC8 XXXXX

My Gallery Item properties is:
These codes filter the records as Category: "Emergencies" and after that there is another Dropdown to filter by location. I Have added another IF for Emergency Category to show only the Emergency Subcategory item in the other dropdown named "Drop_emergency".

Filter( PhoneBook, Category.Value = "Emergencies", If( Drop_location_2.Selected.Result = "All Locations", true, Location.Value = Drop_location_2.Selected.Result ), If( Drop_emergency.Selected.Result2 = "All Emergencies", true, Subcategory.Value = Drop_emergency.Selected.Result2 ) )

Following is the "Drop_Emergency" dropdown which as other subcategory items (red underlined) from main category which I dont want.
Hope this will clarify you to provide ideas to solve this.
Thanks.
NPPlatform 606 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Thank you for the additional information, @LeonardLeo . I have recreated your list, as shown in the first image below. The second image shows a dropdown with only the subcategories you want, correct? This was done by setting "Items" of the dropdown to the following code:

Distinct(Filter(PhoneBook,'Name ({Name})' = "Emergency"),Subcategory)

Does this solve your problem? If so, please consider marking this as the solution. If not, let me know what problem arises.

Edit:
If you want this dropdown to be sorted, you can wrap the code in a Sort()-function. This results in :

Sort(Distinct(Filter(PhoneBook,'Name ({Name})' = "Emergency"),Subcategory),Value,SortOrder.Ascending)
LeonardLeo 184 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi NPP,
Sorry, the Gallery Item pointed to a Collection called "CollectEmerg", which collects the data and show it in the dropdown including "All Emergencies" choice.
So Im trying the following code to be applied in the Screen OnVisible event/properties to filter the Collection data with only "Emergency" Category. Otherwise, it display all Subcategory item in dropdown that are not part of Emergency category.

ClearCollect(CollectEmerg, {Result2: "All Emergencies"}, SortByColumns(ForAll(Filter(PhoneBook, Category.Value = "Emergency"), {Result2: ThisRecord.Value}), "Result2"))

This code giving me the following error.

Not sure why this error is showing.
Verified answer

NPPlatform 606 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @LeonardLeo ,

Could you try the following code in your on visible:

ClearCollect(CollectEmerg,{Result2: "All Emergencies"},Sort(ForAll(Distinct(ForAll(Filter(PhoneBook,Category = "Emergency"),{Result2: Subcategory}),Result2),{Result2: Value}),Result2,SortOrder.Ascending))
And set the drop down Items to CollectEmerg.

This works for me, see the image below. I've used a button with OnSelect instead of OnVisible. But the principle is the same.
LeonardLeo 184 on at

Like
a
(1)

Report
Copy link

Link copied!

Yeesssssss !!! Finally, it works. Thanks for the workaround
Much appreciated!!!