Filter sql table on basis of ID stored in collection

(0) Share

Report

Posted on by ridhan

158

Hi All,

I have a requirement in which i need to filter Student data on basis of student ID present in one collection. But i am getting delegation warning on filter condition.

Code using:

ForAll(
colListofNewAddedStudent As passThrough551,Collect( colExportData,
Filter(Student_Details,Student_ID = Value(passThrough551.student_id))))

Thanks in advance

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

LaurensM 12,516 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @ridhan,

Could you try the code below:

ForAll( colListofNewAddedStudent As passThrough551, Collect( colExportData, With( {wStudentId: Value(passThrough551.student_id)}, //Changed to LookUp since I expect there only to be one match when using ID LookUp( Student_Details, Student_ID = wStudentId ) ) ) )

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

Was this reply helpful? Yes No
ridhan 158 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @LaurensM - Thanks you for providing the aboce solution. It worked half way. ACtually i am facing below twqo issues while using above code:

1. There are 50 columns in my table but using above code it is only extracting student ID in the new colllection and not extracting data of rest of the columns which is really strange for me.

2. I can not use the Lookup as i have multiple records of 1 student ID in that table so it needs to pull records for 1 student Id. So i have changed it to Filter again.

ForAll(
colListofNewAddedStudent As passThrough551,
Collect(
colExportData,
With(
{wStudentId: Value(passThrough551.student_id)},
//Changed to LookUp since I expect there only to be one match when using ID
Filter(
Student_Details,
Student_ID = wStudentId
)
)
)
)

Was this reply helpful? Yes No
AndrewR1 1,572 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi can you try this for me:

ClearCollect( colExportData, Filter( Student_Details, Student_ID in colListofNewAddedStudent.student_id ) )

Was this reply helpful? Yes No
AndrewR1 1,572 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Sorry can you try and adjust your flomul to be like this:

ClearCollect( colExportData, AddColumns( Filter( Student_Details, Student_ID in colListofNewAddedStudent.student_id ), "Selected", true ) ); Collect( colExportData, ForAll( colExportData, { wStudentId: Student_ID, wStudentData: Filter(colExportData, Student_ID = wStudentId) } ) )

Was this reply helpful? Yes No
Verified answer

timl 37,212 Super User 2026 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!
Hi @ridhan

The reason why all the columns don't show is due to the 'explicit column selection' of SQL Server. You can modify @LaurensM 's formula to include a call to ShowColumns. Here, you would specify the columns that you want to retrieve.

ForAll( colListofNewAddedStudent As passThrough551, Collect( colExportData, With( {wStudentId: Value(passThrough551.student_id)}, ShowColumns( Filter( Student_Details, Student_ID = wStudentId ), "ColumnYouWantToShow1", "ColumnYouWantToShow2", "ColumnYouWantToShow3" ) ) ) )

The alternative is to disable explicit column selection in you app settings. This isn't recommended because it'll have a negative impact on overall performance.

Was this reply helpful? Yes No
ridhan 158 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @timl @LaurensM - It worked like a charm. Thanks for all your help!

Was this reply helpful? Yes No