Comparing SharePoint Lists

[STin13]

Hi All,

I'm trying to solve some coding. I have 2 SharePoint lists. List one is called "Course List" and the other is called "Completed Courses". I ran into delegation issues (Completed Courses will have a high number of records), so I used FirstN as a workaround (not sure if this is correct use). I'm trying to have a text box that will output if the current user has completed the mandatory courses listed on the course list. Completed courses list will contain many other courses I just need to narrow it down to just the courses on the course list. I have put a matching record in both SharePoint lists, but my output is saying 0/10 when it should pick up that one matching record. I can't figure out where I'm going wrong. Any suggestions?

Course List
Title	Course code
Learn my ABC	ABC123

Completed Courses
Title	Course code
user email	ABC123

desired outcome to look like below text box.

"You have completed "&
If(
CountRows(
Filter(
FirstN('Completed Courses', 2000),
Title = currentUser.Email,
'Course code' = 'Course code'
)
) > 0,
CountRows(
Filter(
FirstN('Completed Courses', 2000),
Title = currentUser.Email,
'Course code' = 'Course code'
)
),
0
) & " of " & CountRows(FirstN(Course List, 2000))&" mandatory courses."

[WarrenBelz]

Hi @STin13 ,

Assumiong the user does not have more than 2,000 courses, this should work

With(
   {
      wData:
      Filter(
         'Completed Courses',
         Title = currentUser.Email &&
      )
   },
   CountRows(
      Filter(
         wData,
         'Course code' = 'Course code'
      )
   ) & 
   " of " & CountRows(wData)& " mandatory courses."
)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[STin13]

I'm getting there. I still have a delegation warning for the Completed courses list as it has a lot of data in there already. The below has got me pretty close to the output I need.
"You have completed " &
With(
{
learningData: Filter(
'Completed courses',
Title = currentUser.Email
),
debtData: Filter(
'Course List',
'Course code' in 'Completed courses'.'Course code'
)
},
CountRows(
Filter(
learningData,
'Course code' in debtData.'Course code'
)
) &
" of " &
CountRows(learningData) &
" mandatory courses. " &
CountRows(debtData) &
" matches found in Course List."
)

[WarrenBelz]

@STin13 ,

It might be better to explain exactly what is in those two lists, their size and how the three values are actually derived.

[STin13]

Hello, Warren.

The Course List will only feature up to 40 courses and will be manually updated with the course code and course name. When a course is completed, information such as the course code, email, date completed, time it took to complete, and so on are patched into the Completed Courses List.

I'm developing an app that compares the two lists and notifies the current user if they have completed all or some of the courses on the Course List. If the current user has completed any of the courses listed on the Course List, it will appear on the Completed Courses List.

[WarrenBelz · Accepted solution]

Hi @STin13 ,

I may be over-simplifying this

With(
   {
      wComplete:
      Filter(
         'Completed Courses',
         Title = currentUser.Email
      ),
      wCourses: 'Course List'
   },
   CountRows(wData) & 
   " of " & CountRows(wCourses)& " mandatory courses."
)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[STin13]

Thank you @WarrenBelz that worked. No delegation error and correct output!!!