cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AakashJaiswal
Helper V
Helper V

How to filter a collection by a column from another table?

Hi,

 

There are 2 tables. Table 1 has 10 entries and Table 2 has 100 entries. Both tables have a common column. 'A'

 

How can I filter Table 2 with the 10 entries I have in Table 1? I need a delegation warning free formula because my records can cross the 2000 mark. Following is my current formula which is showing delegation warning due to the 'in' inside it.

 

ClearCollect(Table3,
      Filter(Table2, ColumnA in Table1.ColumnA)
);

 

Aakash

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-siky-msft
Community Support
Community Support

Hi @AakashJaiswal

 

Try to use "ForAll()" function to iterate over the Table1, then collecting each output table by using the "Filter()" function with a "=" operator.

Clear(Table3);
ForAll(Table1, Collect(Table3, Filter(Table2, ColumnA=Table1[@ColumnA])))

Hope this helps.

Sik

View solution in original post

Thanks @AakashJaiswal ,

I will tell you how I would manage this - it may not suit your purposes, but our complete operation is with lists much bigger than this is on SharePoint, so I try to manage my data accordingly (rather than let it manage me).

The process I do that will probably help you the most is the "shadow" ID number I keep. I have a numeric field in the list and this on the OnSuccess of my new record Forms. 

UpdateIf(
    MyListName,
    ID = MyNewForm.LastSubmit.ID,
    {MyShadowID: MyNewForm.LastSubmit.ID}
)

I can then Collect as many records as I want with 

ClearCollect(
   colTemp,
   MySPList
);
If(
   CountRows(colTemp) = 2000,
   Set(
      vID,
      Max(colTemp,IDRef)  //My Shadow field
   );
   Collect(
      colTemp,
      Filter(
         MySPList,
         IDRef>vID
      )
   )
);
If(
   CountRows(colTemp) = 4000,
   Set(
      vID,
      Max(colTemp,IDRef)
   );
   Collect(
      colTemp,
      Filter(
         MySPList,
         IDRef>vID
      )
   )
);
Then keep going in batches of 2000

If you do this to both lists, you will have fully delegable In filters.

As an aside, I also have shadow dates in numeric fields yyyymmdd format and do not use Lookup fields and very few Choice fields. Everything that ever will have to be filtered/searched is plain text or numeric, so Delegation is just a word.

 

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.

View solution in original post

13 REPLIES 13
eka24
Super User
Super User

Try:

Your Formulas was fine. I believe Table3 is not an Existing Table in your App.

 

ClearCollect(MyTable2Col,Table2);

ClearCollect(MyTable1Col,Table1);

ClearCollect(MyTable3Col,
Filter(MyTable2Col, ColumnA in MyTable1Col.ColumnA))

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

 

Hi @eka24 ,

 

Table 3 is the final output table. I see you have used 'in' in your formula which is non-delegable. Can you suggest an alternate formula that can work even when the records are more than 2000? 

 

Aakash

I Don't know your Datasource but I suggest you use SQL Or CDS which has a wide range of delegable functionalities. 

 

Again once you use collection to move your data inside your App, you can use in without any issue on the collection.

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Unfortunately, I don't have the flexibility to choose any data source other than Sharepoint. Can you help with Sharepoint?

 

Aakash

Marking @v-xida-msft @v-siky-msft @v-bofeng-msft @v-monli-msft for your help.

v-siky-msft
Community Support
Community Support

Hi @AakashJaiswal

 

Try to use "ForAll()" function to iterate over the Table1, then collecting each output table by using the "Filter()" function with a "=" operator.

Clear(Table3);
ForAll(Table1, Collect(Table3, Filter(Table2, ColumnA=Table1[@ColumnA])))

Hope this helps.

Sik

View solution in original post

Hi @v-siky-msft,

 

I replicated your formula and it is still showing the delegation warning. Following is the screenshot for your reference. Please note this is actual formula and Table1 is MyAssignmentsTemp; Table 2 is TaskPlannerDatabase; Table 3 is MyAssignments;

Screenshot (400).png

 

I tried to tweak your formula and created the following.

 

Original (Showing delegation warning due to "in")
ClearCollect(MyAssignments,Filter(TaskPlannerDatabase,TaskID in MyAssignmentsTemp.TaskID ));

 

Option 1 by your formula (Still showing delegation warning- screenshot above)
ForAll(MyAssignmentsTemp, Collect(MyAssignments, Filter(TaskPlannerDatabase, TaskID=MyAssignmentsTemp[@TaskID])));

 

Option 2 (I populated the MyAssignmentsTemp collection by using the distinct formula for "TaskID" column. The output was "Result" column which I later used in the following formula. Fortunately, this formula didn't show delegation warning but the performance was very poor. It was loading rows one by one per second)
ForAll(MyAssignmentsTemp, Collect(MyAssignments, Filter(TaskPlannerDatabase, TaskID = Result)));

 

Can you help?

Aakash

 




 

 

Hi @AakashJaiswal ,

 

The delegation issue is due to disambiguation operator, and your option 2 is exactly what I want to do, rename the TaskID column to avoid ambiguity.

 

However, the performance issue of ForAll function does exist, since it needs to loop over each record one by one, it's going to take time. But so far, I have no better way to solve 'In' operator delegation error for SharePoint.

 

Sik

Thank you for your support @v-siky-msft . I can use it for the time being but since the dataset is large and will keep on increasing, I will need a more efficient formula. 

 

@WarrenBelz Can you help?


Regards,

Aakash

Hi @AakashJaiswal ,

Welcome to SharePoint and Delegation.

I saw your post initially, but @eka24 had already answered it and my thoughts were a bit along the line of @v-siky-msft 's ForAll loop. Also, what is your code for collecting MyAssignmentsTemp? If it is simply a copy of MyAssignments, then Delegation will stop this at 2000 records as well. How many records do you think your List will grow to?

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,751)