Finding Rows in SPL that are not in another SPL

[BenWishh]

Hi everybody,

I'm currently developing an app and having trouble with checking two SPL.

I have one SP-list "Managers" that contains users that have to complete a certain task every year.

In another list I keep track on who completed the task in the current year.

The lists look like this

"Managers"

Id	Name
1	John
2	Jane
3	Steve

"Task_completed"

Manager_ID	Year
1	2020
2	2020
3	2020
1	2021

Now I want to find out who did not complete the task in a certain year. For example, for the year 2020, the result should be empty, because all three managers completed their task for 2020. For 2021, the result should contain Jane and Steve, because only John did his work.

Is there any way to realize this using Powerapps?

[RandyHayes]

@BenWishh 

Please consider the following formula:

With({_years: [2020, 2021, 2022]},
    ForAll(Managers As _manager,
        Patch(_manager, 
            {_yearsNotCompleted:
                With({_tasks: Filter(Task_completed, Manager_ID = _manager.Id)},
                    Filter(_years, !(Value in _tasks.Year))
                )
            }
        )
    )
)

This will provide a list of managers and all the years they have not completed.

If you are looking from it from the other perspective (a list of managers that have not completed a specified year), then consider the following formula instead:

With({_year: 2021},
    With({_items: Filter(Task_completed, Year=_year)},
        Filter(Managers, !(Id in _items.Manager_ID))
    )
)

NOTE: The above formulas are not using delegable criteria.  If your list is, or is expected to be, over 2000 records, then the results will not be complete.

I hope this is helpful for you.