Solved: How to filter a collection by a column from anothe...

Anonymous · ‎05-02-2020

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

v-siky-msft · ‎05-03-2020

Hi @Anonymous ，

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

WarrenBelz · ‎05-06-2020

Thanks @Anonymous ,

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

eka24 · ‎05-02-2020

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.

Anonymous · ‎05-02-2020

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

eka24 · ‎05-02-2020

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.

Anonymous · ‎05-03-2020

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 · ‎05-03-2020

Hi @Anonymous ，

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

Anonymous · ‎05-05-2020

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;

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

v-siky-msft · ‎05-05-2020

Hi @Anonymous ,

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

Anonymous · ‎05-05-2020

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

WarrenBelz · ‎05-05-2020

Hi @Anonymous ,

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?