Alternatives to "in" operator and CountRows to filter for all rows of one table based on the value from another table in a many-to-many relationship schema

[KevinAlc0r]

Hi,

I am using SQL tables as the data source for my Power Apps. I am having trouble in finding a delegable function to filter for rows from one table based on the value from another table which has a many-to-many relationship with the current table I want to filter.

Suppose I have two tables: STUDENT and COURSE that have many-to-many relationship between them (one student can enroll to multiple courses and one course can have multiple students). I also have a table to handle the many-to-many relationship between the two tables called STUDENT_COURSE:

Let's say I want to filter for all students who attend a Course with a CourseId = 37, what I would normally do is:

// Find all studentId of all students who enroll to the course by filtering the STUDENT_COURSE table
Set(currentStudentIdsInCourse, 
    Filter(STUDENT_COURSE, CourseId = 37).StudentId
);

// Step 2: Filter for all Students from the STUDENT table using the studentIds obtained from the previous step and get their names

Set(studentNamesInCourse,
    Filter(STUDENT, studentId in currentStudentIdsInCourse).StudentName
);

Or alternatively, I can also use CountRows:

// OR I can also use CountRows as the filtering condition to find students who enroll to the course

Set(studentNamesInCourse,
    Filter(STUDENT, CountRows(
         Filter(STUDENT_COURSE, courseId = 37 && STUDENT[@studentId] = STUDENT_COURSE[@studentId])
        ) > 0
    ).StudentName
);

// Alternatively, I can also use the "in" operator in a single step like shown below instead of what I did previously:
Set(studentNamesInCourse,
    Filter(STUDENT, STUDENT[@studentId] in
        Filter(STUDENT_COURSE, courseId = 37 && STUDENT[@studentId] = STUDENT_COURSE[@studentId]).studentId
    ).StudentName
);

Using either "in" or CountRows works fine. However, both of them throw out a Delegation warning and I found out that both "in" operator in this case and the CountRows function are non-delegable. Reading into this documentation from Microsoft, the "in" operator is supposed to be delegable as long as the operator is used for columns in the base data source which I believe I have done correctly (both "in" operator usage in my example uses the studentId column from the STUDENT table, studentId is a column inside the STUDENT table which acts as the base data source).

I am worried that in the future my database would grow to accommodate even more data which is why I am concerned with these Delegation problems.

Are there any other alternative functions or methods for this filtering process?

Thanks a lot!

[DBO_DV · Accepted solution]

Hey @KevinAlc0r,

// Find all studentId of all students who enroll to the course by filtering the STUDENT_COURSE table
Set(currentStudentIdsInCourse, 
    Filter(STUDENT_COURSE, CourseId = 37).StudentId
);

This part is fine I think we can agree on this. 

Could you please share with us, how many students are in one course. And also the use case: do you need this app to be fast or can there be a loading time? Is There a possibility to load the student names in the Background, so that the user would do something else in the meantime?

Because if you have time for the function to run you could use ForAll:

ForAll(currentStudentIdsInCourse,
    Collect(studentNamesInCourse, 
        LookUp(Student, studentid=ThisRecord.Value).StudentName)
    )
)

It's not the fastest way but delegable.

[VishalJhaveri · Accepted solution]

No, there are no alternative functions or methods for filtering process.

However you can try the method to retrieve more than 2000 records at a time:
Overcome 2000 items limit using Power Apps Collect function • Tomasz Poszytek, Business Applications... Re: 500 item limit in CDM entity search filter(nee... - Page 4 - Power Platform Community (microsoft... -> Scroll down in this link and find Mr. Dang(user) Solution How to overcome 500 items limit in PowerApps - CEO-Refleksje (michalguzowski.pl)


If my answer helps you please mark it as solution.

[iAm_ManCat · Accepted solution]

Hiya,

So since you already have the student IDs from the intermediate data source, you should be able to loop over that and do individual lookups that you add to a collections - that way there's no delegation issue as the lookups wouldn't be using 'in'. It won't be as fast but it will be scalable.

Set the course and list of StudentId for that course:

Set(currentStudentIdsInCourse,
    Filter(
        STUDENT_COURSE, CourseId = 37).StudentID
);

Then create a collection (rather than setting a variable) by iterating through all of those records and doing a lookup for each student detail based on that. This should get you a table (collection) of students with their details that you can then use as you need:

Clear(StudentNamesInCourse);
ForAll(currentStudentIdsInCourse As CSIIC,
    Collect(LookUp(STUDENT, StudentId = CSIIC.StudentId).StudentName)
)

You might need to tweak your code slightly (might need to use .Value instead of .StudentID) to reference the column names directly.

Not a perfect solution, but as it's doing individual lookups with equal operator it will not face delegation issues 👍

[KevinAlc0r]

My original intent is to do this process inside a detail view. The user/student clicks on their profile/account information and they can see the courses that they have enrolled in. So I would say the app needs to be fast, some loading time is fine but I don't think the user will do anything in the background while waiting for the loading time.

Anyway, thanks a lot! The ForAll example is still another alternative and it is at least delegable.