cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jerry1567
Level: Powered On

Delegation with Filter()

Hi,

 

Is there a way to create a delegatable filter in some way that does the following?

Filter(task_list, 'phase_list:ID'= _task_id )
Filter(task_list, 'phase_list: phase_number'= _phase_number )

 

So, there's a sharepoint list called task list with a relationship to a phase list and has the phase_list as a foreign key to task_list.

 

The phase_list becomes a lookup column with additional fields that are populated by sharepoint to have the ID and phase_number.

 

So in addition to the regular fields in task_list: there is a "phase_list", "phase_list:ID" and "phase_list: phase_number".
All of them being lookups from the phase_list and phase_list respective fields

 

Is there a way to make the filter function above, delegatable as:

Filter(task_list, phase_list.Id = _phase_number) 

does not appear to be.

 

Thanks!

3 REPLIES 3
Super User
Super User

Re: Delegation with Filter()

Hi @Jerry1567 

 

Microsoft Powerapps applies delegation to avoid delay in fetching the records, if you want to overcome getting limited number of records, you can create a collection and add all the records into it, and once all the data is loaded you can use this collection everywhere and filter the data out of it. For doing so:
 
1) Please check if your Data Row limit for Non-Delegable queries is set to 2000, this is the max value that can be entered by the user and works upto 2000 records out of all using the Filter query. The soft limit is 500 which can be updated to 2000 records.
 
2) If you have more than 2000 records and wants to get all the data into a collection, then you can iterate over loops to get the data:
a) There is an attribute ID in sharepoint which keeps the row index. We can create loops based on row count.
b) create a collection to store the number of loop count:
ClearCollect(LoopCount,FirstN([0, 2000, 4000, 6000, 8000, 10000],RoundUp(First(Sort(SharepointListName,ID,Descending)).ID/2000,0)));
c) Now work on the main collection to get all the data
Clear(MainCollection);
ForAll(LoopCount,Collect(MainCollection,Filter(SharepointListName,ID> Value && SNo <=ID+ 2000 )));
 
Hope this Helps!
 
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
Community Support Team
Community Support Team

Re: Delegation with Filter()

Hi @Jerry1567  @yashag2255 

 

Thanks for your posts.

 

More information:

delegation-overview

 

Hope this could be helpful.

 

Best Regards.

Yumia

Highlighted
Jerry1567
Level: Powered On

Re: Delegation with Filter()

@yashag2255  @v-yuxima-msft 

 

Thank you for your posts, so there is no way to delegate records based on foreign key ids as of right now?

One workaround I'm considering is adding a Microsoft flow to each table that triggers whenever a row is added or modified such that the foreign key references, the foreign key id is written to a regular number field.

 

For instance if Tasks, has a phases lookup column, there will be a regular number field that gets Phases.ID and writes it to this number field. Not sure about performance considerations/consistency as powerapps may not be aware of the changes.

 

Does filter always call Sharepoint or does it use a cache? So if I have a delegateable query, then will it get fresh information on what I need? 

 

Thanks!