Answered

Filter or collection based on two sharepoint lists

(0) Share

Report

Posted on by crawlejg

Hello!

I have been trying unsuccessfully to either filter a gallery or build a collection that can be used as the source for a gallery.

I have two SharePoint lists:

ActCat (contains activities - Columns: ID, Desc, etc.)
ActTrack (tracks activities assigned to people - Columns: ActivityID, Person, Date, etc.)

ActivityID is a lookup column that uses ID (ActCat) to pull in other information about the activity.

I would like to display in a Gallery only those courses for which varUser does not already have assigned. In other words, I want to filter ActCat by any ID that is in ActTrack (as ActivityID) and assigned to varUser.

I have tried:

Filter(ActCat,ID in Filter(ActTrack,Person.Email=varUser.mail).ActivityID) - error can't convert this data type. Power Apps can't covert this Number to Record. ID is the issue.
Filter(ActCat,ID in LookUp(ActTrack,Person.Email=varUser.mail,ActivityID.Value)) - this returns only the first value that matches my criteria
ForAll(ActCat,Collect(colTemp,Filter(ActCat,ID in LookUp(ActTrack,Person.Email = varUser.mail).ActivityID.Value))) - returns the same record from ActTrack 137 times (which is the number of records in ActCat)

I created a collection (colTemp) that has just the activities for which varUser has had assigned (13 records), but I'm not sure how to use it to filter ActCat.

Any help would be appreciated. Thank you!

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

WarrenBelz 156,584 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @crawlejg ,

You have a many-to-many relationship there, so this is not straight-forward. It is also further complicated by wanting the non-matching records (activities not present), therefore will get duplicates as each iteration will show everything not matching the item being compared. I cannot test this as I do not have your data, but try

With( { wUserData: Filter( ActTrack, Person.Email = varUser.mail ) }, Distinct( Ungroup( ForAll( ActCat As aCat, Filter( wUserData, !(aCat.ID in wUserData.ActivityID) ) ), "Value" ), ActivityID ) )

This will also return one column Result

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
crawlejg 56 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you very much for the quick reply.

I am still having an issue with the ID column - I tried replacing the search column (ID) with a different one (ActivityDesc but got the same error (except can't convert Text to a Record).

I also get the same error if I enter a number or "text" in place of aCat.ID - the error in that case is "Incompatible types for comparison. These types can't be compared: Text, Table).

aCat.ID is a number and wUser.ActivityID is a table (with Id and Value columns).
So, I also tried using wUserData.ActivityID.Value and wUserData.ActivityID.Id, which both gave errors "Name isn't valid. 'Value' isn't recognized" - or - 'Id' isn't recognized).

Below are the relevent field:
ActCat (aCat)

ActTrack.ActivityID (wUserData)

Any thoughts about how to get around this issue? Thanks again!
Verified answer

crawlejg 56 on at

Like
a
(0)

Report
Copy link

Link copied!

I think I got it. This is the solution that worked for me:

With(
{
wUserData: Distinct(
Filter(
'ActTrack',
Person.Email = varUser.mail
),
ActivityID
)
},
Filter(
'ActCat',
!(ID in Distinct(wUserData,Result.Value)
)
)
)

That is basically the very first thing that I tried, but I couldn't figure out the error - (Can't convert this data type. Power Apps can't covert this Number to Record.) The trick here is that I needed to use Distinct to get to the Value of ActivityID in the Filtered 'ActTrack' list.

Thanks for your help!