Answered

Narrow gallery options via multi selection data in SharePoint list?

(0) Share

Report

Posted on by DGWolfe

Hi all, I have an app whereby I need to group specific data rows to user roles (one to many type relationship)

I am using SharePoint list.

I have my data set with each row showing the relevant info. The last row is my "RoleConnector" (one to many relationship) "Choice" column. This column, has "multi selection" turn on for the choices.

In a very similar app, I used the below code to locate the data type based on the role selected. This works perfectly.

Filter('Sharepoint list','column name' = TextInput.Text && RoleConnector = roleconnectortInput.Text)

However, this was based on a single choice sharepoint list. I have attempted the same, but after the '&&' I run into a delegation warning with the RoleConnector. There is only 133 lines of data, so I assume the issue is reading the "multi choice" fields of the RoleConnector column.

Any suggestions on how to refine and fix this?

Cheers,

Categories:

Building Power Apps

All responses (10)

Answers (1)

Sort by

Verified answer

WarrenBelz 156,730 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @DGWolfe ,

Correct - the Delegation issue is because it is a Multi-Choice column though actually your code should be

Filter( 'Sharepoint list', 'column name' = TextInput.Text && roleconnectortInput.Text in RoleConnector.Value )

If you want to get rid of the warning

With( { wList: Filter( 'Sharepoint list', 'column name' = TextInput.Text ) }, Filter( wList, roleconnectortInput.Text in RoleConnector.Value ) )

although you need the top filter to return less than your Delegation limit for this to work properly.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps
DGWolfe on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz you are a genius! Thank you sooooo much! it worked perfectly! 🙂
DGWolfe on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz sorry, one last question. I have about 6 rows of data that is assigned to all roles. This is approx 45 roles in total.
I've noticed these 6 rows don't show up in the formula and can only surmise it is because there are so many roles. Is this correct? Is there a limit on what the formula can read and match?
WarrenBelz 156,730 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @DGWolfe ,

How many items are in your list and what is your Delegation Limit ?
DGWolfe on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz

There are 133 rows of data in the list.

In the multi select column, there are 45 choices (6 rows of the data set have all 45 roles applied to them).

I have set the delegation limit to the max of 2000.
WarrenBelz 156,730 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @DGWolfe ,

The number of choices is probably not an issue, but how many of them are likely to be in any record ?
DGWolfe on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz just checked. One of the roles (which is in the middle of the muli select choice column) appears in 30 rows of data. But in the app it only shows up 4 times.

Another role (the first in the choice column) shows up correctly and appears in 15 rows of data
WarrenBelz 156,730 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @DGWolfe ,

The only thing I can think of (and I have never had close to that number of values in a Multi-Choice field) is that you are taxing the limits a bit too much. Have you considered having "All" as an option ?
DGWolfe on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz i did have all originally, but I had to group as the filter on my text input needs the role. Otherwise I will bring up the role, but not the all.
DGWolfe on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz worked it out. You have to cap multi choice at 12. So split a few up and the above works perfectly. Thanks again for the guidance.