cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Workaround for IN not being supported for Sharepoint Delegation?

Hey, 

 

I have a ComboBox that selects multiple items, puts them comma delimited list, and then along with other fields, saves the data to a Sharepoint List. The ComboBox has it's own Sharepoint List that fills it, a list of about 4000 items. 

 

So in my gallery, when I have someone who wants to edit their a record, I have to retrieve the ComboBox values and set them as defaults. But since it is multiple values, the IN operator isn't delegable, and it won't retrieve everything. 

 

How do I replicate the IN operator, while still being delegable? 

 

I know I can do it, individually, like in 4 different variables, but then it's a set limit. I might be able to write a while loop, but I don't like that solution,  since it feels like there is a more simple way.

 

Thanks,
Derek

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

Re: Workaround for IN not being supported for Sharepoint Delegation?

Hi All, 

Thanks for the suggestions, 

I got a workaround done, in the meantime. 

Set(
    First_Result,
    Last(
        FirstN(
            Split(
                LookUp(
                    TABLE,
                    ID = lookup_id,
                    COLUMN TO SPLIT
                ),
                ","
            ),
            1 (ITERATE FOR HOW MANY YOU WANT)
        )
    )
);
Collect(COLLECTION TO SET TO COMBOBOX DEFAULT (MUST MATCH EXACTLY WHATS IN THE ITEMS,
LookUp(
    'LARGE SHAREPOINT LIST',
    Title = First_Result.Result
));
RESET(COMBOBOX);

 

I believe this is probably better than loading 2000+ objects into a Collection, Runs pretty fast for me, at 10 iterations. 

Put this code in the PAGE ON VISIBLE, It'll grab the what the multi select combo box previously saved into sharepoint item, for 1 value and grab it from your large sharepoint list, and stick it in a collection. Just copy pasta code for how many iterations you want. I'm sure somebody could loop it, but eh, not necessary IMO.  Reset combobox once at the end, just to get it to show the new values. 

 

Thanks,
Derek

View solution in original post

4 REPLIES 4
Highlighted
Super User
Super User

Re: Workaround for IN not being supported for Sharepoint Delegation?

@whymakeme 
SharePoint does not have the ability to filter by IN, Contains, or do a Search: all of which are fundamentally the same thing.  The only workaround we have is the StartsWith operator and its only a partial workaround.

 

If you need the ability to Search then you'll have to switch to another datasource: Common Data Service or SQL.  It will cost $10 per user per app.  Don't shoot the messenger, I don't work for MS.  I am simply a member of this community helping others for free. 

 

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

Highlighted
Community Support
Community Support

Re: Workaround for IN not being supported for Sharepoint Delegation?

Hi @whymakeme ,

Do you want to avoid delegation limit about "in"?
I'm afraid write a while loop will not work.

Delegate functions supported for sharepoint is very limitted:

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

Except the operators, the supported functions are: filter,sort, sortby columns, lookup, startswith, is blank.

None of them is related to  while loop.

(In powerapps, we usually use if,forall  to write while loop, but they are both non-delegate functions in sharepoint.)

 

Actually, I more suggest you try to use collection to avoid delegation problem.

In collection, there's no delegate limit.

You do not need to worry about using delegate functions any more.

You just need to save all your data in collections at the app's OnStart.

Then in your app, use these collections as data source.

For example: Filter(collection1,fieldname in combo box.SelectedItems.Value).

This will not have delegation warning.

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Community Champion
Community Champion

Re: Workaround for IN not being supported for Sharepoint Delegation?

@whymakeme ,

I will add small contribution here.

If you can collect a subset of data with a delegable filter (text or numeric) then In/Search will work fine on the Collection. I do this extensively on large data sets - you just have to make "the shoe fit the foot" and have your data able to be segregated when required.

As an example, I have a large list with a text field Status with values Pending/Planned/In Progress/Completed/Audited/Finalised which is updated automatically with logic when other fields are completed. Any one of these can be put in a Collection (up to 2000 records) and then all filters work. A gallery can then be presented to the user and the ID of the record Set(vID,ThisItem.ID) is then a Variable and the resulting screen has the Item Lookup(YourListName,ID=vID).

Pretty much all SharePoint delegation issues can be managed with a bit of thought on your data structure.

Frequent Visitor

Re: Workaround for IN not being supported for Sharepoint Delegation?

Hi All, 

Thanks for the suggestions, 

I got a workaround done, in the meantime. 

Set(
    First_Result,
    Last(
        FirstN(
            Split(
                LookUp(
                    TABLE,
                    ID = lookup_id,
                    COLUMN TO SPLIT
                ),
                ","
            ),
            1 (ITERATE FOR HOW MANY YOU WANT)
        )
    )
);
Collect(COLLECTION TO SET TO COMBOBOX DEFAULT (MUST MATCH EXACTLY WHATS IN THE ITEMS,
LookUp(
    'LARGE SHAREPOINT LIST',
    Title = First_Result.Result
));
RESET(COMBOBOX);

 

I believe this is probably better than loading 2000+ objects into a Collection, Runs pretty fast for me, at 10 iterations. 

Put this code in the PAGE ON VISIBLE, It'll grab the what the multi select combo box previously saved into sharepoint item, for 1 value and grab it from your large sharepoint list, and stick it in a collection. Just copy pasta code for how many iterations you want. I'm sure somebody could loop it, but eh, not necessary IMO.  Reset combobox once at the end, just to get it to show the new values. 

 

Thanks,
Derek

View solution in original post

Helpful resources

Announcements
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,436)