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

 

13 REPLIES 13

@WarrenBelz 

 

Following is the flow of the process.

  1. Step 1
    I extract jobs assigned to me in a collection, 'MyAssignmentsTemp'. I extract it from SP data source, 'ResourceDatabase'. ResourceDatabase can easily reach and even cross 5000 records.

    //Here ProfileDisplayName is variable I set on app start.
    ClearCollect(
    MyAssignmentsTemp,Filter(Resource_database,
    Resource_TO = ProfileDisplayName || Resource_TO2 = ProfileDisplayName ||
    Resource_TO3 = ProfileDisplayName || Resource_TO4 = ProfileDisplayName ||
    Resource_TO5 = ProfileDisplayName).TaskID
    );
  2. Step 2
    I filter TaskPlannerDatabase using all the records I have in MyAssignmentsTemp. The output is saved in 'MyAssigments'. (This is where I need column from MyAssignmentsTemp to filter TaskPlannerDatabase. TaskPlannerDatabase also has 5000 records or more)  

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

Hi warren, this is great thanks.  Pitty there is no thing like Loop or Do Until, Do while - Ah just a wish I suppose

Thanks @leyburn19 ,

I will give you another small piece of left-field information. I make extensive use of Access, which connects directly and very well to SharePoint Lists. Both VBA and SQL function perfectly in most cases directly into SharePoint - the only thing on loops or Insert statements to watch is throttling.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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