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

Delegation to SQL, filter table by column

Hi,

 

I've got an issue where i'm producing an app which needs to filter a SQL Table (>2000 rows) based on a column in a collection. 

 

The way the data is structured is I have a parent table for the "Reports", then a child table called "Report_Items" , which are items raised within the report. The app first fetches the last 3 report entries from the Report table (GUIDs for ID's) e.g. abc123.., def456... and hij789..., what I then need it to do is check the  Report_Items table and return all of the items related to these three reports.

 

The problem i've got is with the delegation. As far as I understand, you can't filter a table in excess of 2000 rows using a column, like in the below syntax:

 

Filter(Report_Items,Report_ID In Reports.Report_ID)

 

From what I can make out, it has to be a string value, something like the below.

 

Filter(Report_Items,Report_ID in "abc123, def456, hij789")

 

Is there any way around this where I can successfully filter a table via delegation using a column? For context, i'm storing all this data in a collection as the app needs to work offline until submission of the report.

 

 

Kind Regards,

 

 

 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
gabibalaban
Super User
Super User

@Arrow3009 ,

 

ForAll(
       Reports As Report, 
       Collect(localDataSource,
               Filter(Report_Items,
                       Report_ID=Report.Report_ID)
)

 

Please be aware that this approach can be a very large time consumer.

Hope it helps ! 

View solution in original post

This is your formula ..

Filter(Report_Items,Report_ID In Reports.Report_ID)

I suppose that the reports you are needed ( like in your example 101,102,103 ) are gathered in Reports as collection.

As Report is used only for internal purposes of the formula to clarify the values I'm referring to. 

 

View solution in original post

6 REPLIES 6
WarrenBelz
Super User
Super User

Hi @Arrow3009 ,

You actually need

Filter(
   Report_Items,
   Report_ID = 102
)

if the ID is numeric or "102" if it is Text.

 

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.

 

gabibalaban
Super User
Super User

@Arrow3009 ,

 

ForAll(
       Reports As Report, 
       Collect(localDataSource,
               Filter(Report_Items,
                       Report_ID=Report.Report_ID)
)

 

Please be aware that this approach can be a very large time consumer.

Hope it helps ! 

View solution in original post

Hi Warren,

 

Thanks for the reply, apologies it was a poor example. The ID's i'm using are actually GUIDs (Will update in the original post for clarity).

 

The issue i've still got is that i'm not actually looking for records from my Report_Items table based on one ID value, but multiple GUIDS that i've now got in my Report collection. i.e. abc123, def456 , hij789. 

 

 

Thanks @gabibalaban,

 

Just to clarify, what are the "Reports As Report" in this context before the Collect?

 

 

This is your formula ..

Filter(Report_Items,Report_ID In Reports.Report_ID)

I suppose that the reports you are needed ( like in your example 101,102,103 ) are gathered in Reports as collection.

As Report is used only for internal purposes of the formula to clarify the values I'm referring to. 

 

View solution in original post

Thanks for your help @gabibalaban , I've just tested this and it works well. It does take a bit of extra time to run, but as this only needs to happen max once per day, I don't believe it's a barrier to usage.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,822)