Answered

Delegation to SQL, filter table by column

(0) Share

Report

Posted on by Arrow3009

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,

Categories:

Building Power Apps

All responses (6)

Answers (2)

Sort by

WarrenBelz 156,748 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
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.
Verified answer

CU-18081211-6 9,272 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@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 !
Arrow3009 30 on at

Like
a
(0)

Report
Copy link

Link copied!

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.
Arrow3009 30 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks @gabibalaban,

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

CU-18081211-6 9,272 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

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.
Arrow3009 30 on at

Like
a
(0)

Report
Copy link

Link copied!

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.