Power Platform Community Forum Thread Details

Trying to create a collection of SharePoint list items who's ID's are not in another SharePoint list

We have kPIs and KPI responses
When a user responds to a KPI its logged into the KPI responses table
I want to filter the gallery so the user only sees the KPI's they have not responded to yet

I created a numeric column in the responses table to capture the ID of the KPI they responed to .

I keep getting the cannot convert table to number error in my filter collection. Using Value does not work

What am I missing?

thank you!

ClearCollect(colhs,KPI_HSList);
ClearCollect(colkpiinprogress,Filter(KPI_HSList_responses,Office=useroffice,RatingPeriod=curq));
ClearCollect(kpiids,colkpiinprogress.HS_ID);
ClearCollect(colhas,Filter(KPI_HSList,Not(KPI_HSList.ID in kpiids.HS_ID)));

Categories:

Building Power Apps

Hi @ctedesco3307,

Assuming that the HS_ID field in KPI_HSList_responses is a number and represents the matching ID in KPI_HSList, then this should show the missing records from KPI_HSList where they do not have a match in KPI_HSList_responses. NOTE: both datasets (the full KPI_HSList and the filtered KPI_HSList_responses need to be under your Data Row Limit as the Not filter is not Delegable.

With(
   {
      _InProgress:
      Filter(
         KPI_HSList_responses,
         Office = useroffice,
         RatingPeriod = curq
      ),
      _HSList: KPI_HSList
   },
   ClearCollect(
      colhas,
      Filter(
         _HSList,
         !(ID in _InProgress.ID)
      )
   )
)

Please ✅ Does this answer your question 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 answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn