Drop down multi selection

[Anonymous]

Hello Everyone, 

I am trying to build an application to collect the company projects' locations, so I have four combo box with multi selection, I would like to make it dependent but I faced two issues:

1. The first list dose not show all the items in a SharePoint list, the list contain more than 7500 rows, with about 14 unique values but its returning only two values. Here is the code: 

                     Distinct(listLocations,admin1Name_en) 

2. The second combo depend on the first combo, and enabled multi selection for the first list. 

                   Distinct(Filter(listLocations,GovComboBox.Selected.Result in admin1Name_en),admin2Name_en)

The third and fourth lists also depend on each others. 

I tried this https://powerapps.microsoft.com/en-us/blog/configure-easy-dependent-drop-downs-in-powerapps/  solution but it is useful for select one not for multi selection. 

Please let me know if you have any other solution. 

Best Regards

Mahmoud

#DependentDropdowns

[VijayTailor]

Hi @Anonymous ,

Hope you resolve the First Issue then automatically resolve other issues also.

Basically, issue is, SharePoint has a limit for retrieve data. is Bydefault is 500 but you can extend this up to 2000.

but if you have more data then you can use the paging functionality or you can use the concurrent function for a large datasets.

for extend the Power app delegation limit - you can follow the below step - there you can extend upto 2000  

and if still need to retrieve more data then you can use below url.
https://www.youtube.com/watch?v=0PLznP64O9o

Thanks,

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."-Vijay

[v-yutliu-msft]

Hi @Anonymous ,

Do you want to filter based on multiple selections of admin1Name_en?

Could you tell me the data type of admin1Name_en and admin2Name_en?

I assume that they are both text type.

The key points are:
1)you should use "comboboxname.SelectedItems" to represents all the selections that you select in the combo box

2)to compare a filed with a table, you should use formula like this to compare "fieldname in table".

So you should set like this:
1)combo box1's Items:

Distinct(listLocations,admin1Name_en) 

2)combo box2's Items:

Distinct(Filter(listLocations,admin1Name_en in GovComboBox.SelectedItems.Result),admin2Name_en)

The second of your key point is about delegation. Since your list has more than 2000 records and distinct is not delegate, so you could not get all data in combo box1.

Here are delegate functions for sharepoint:

Item	Number	Text	Boolean	DateTime	Complex [1]
Filter	Yes	Yes	Yes	Yes	Yes
Sort	Yes	Yes	Yes	Yes	No
SortByColumns	Yes	Yes	Yes	Yes	No
Lookup	Yes	Yes	Yes	Yes	Yes
=	Yes	Yes	Yes	Yes	Yes
<, <=,<>, >, >=	Yes [2]	No	No	Yes	Yes
StartsWith	-	Yes	-	-	Yes
IsBlank	-	No [3]	-	-	No

I suggest you consider replace distinct function with the functions mentioned above.

Changing delegation limit to 2000 will just make you view 2000 records, not all records.

I suggest you know more about delegation:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

Best regards,

[Anonymous]

Hi @v-yutliu-msft , thanks for your support! it helped me to fix this issue. 

With this, I am facing a new issue, which is related to save the selected items. 

I have a SP listProjects consisted of 12 fields, four of them are Choices, and listLocationsAdmin3. I created a form for these 12 columns. Then, the first Combo Box take the Items set from listPorjects column (proCompatibleWith), which is not an issue here.

Then, the three other Combo Box code as following: 

Governorates: 

Distinct(
    Choices([@listProjects].proGovChoices),
    Value
)

Districts:

Distinct(
    Filter(
        Choices([@listProjects].proDistrictChoices),
        Value in Distinct(
            Filter(
                listLocationsAdmin3,
                admin1Name_en in GovernorateComboBox.SelectedItems.Result
            ),
            admin2Name_en
        )
    ),
    Value
)

Sub Districts: 

Distinct(
    Filter(
        Choices([@listProjects].proSubDistrictChoices),
        Value in Distinct(
            Filter(
                listLocationsAdmin3,
                admin2Name_en in DistrictCombobox.SelectedItems.Result
            ),
            admin3Name_en
        )
    ),
    Value
)

I tried to submit the selected items to SP listProjects, but I faced another issue,

1. If I take off the three Combo Boxes from the form, SubmitForm() is working well, but when I have these three Combo Boxes, I got an error, which is: List Data Validation Error, as you see in the attached photo.

2. If I use Patch(), it give me an error, Expected table found text.

3. If I use ForAll() with Patch(), it is inserting each value in a row. 

Could you please help me to fix it. 

Best Regards

Mahmoud