Unanswered

Filter items that do not have a corresponding record in another list

(0) Share

Report

Posted on by Community Power Pla...

I have a Sharepoint list of locations and a list of tracked items for those locations.

I am unsure which locations have a corresponding list of tracked items, so how can I filter a gallery of locations that have a corresponding tracked items record? Basically showing me only locations that need to have a tracked items record created for it?

Sample data

Locations List
ID	Name
0001	Something
0002	Nothing

Tracker List
ID	Lookup to Locations	Tracked task
0001	0001	yes

How can I list all of the locations that do not have a corresponding tracking record in the "Tracker List"? It would, in this case return 0002 (Nothing).

Thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (0)

mdevaney 29,989 Moderator on at

Like (0)

Report

@Anonymous
Please share some sample data from your list. Then show an example of what the filter should look like. By providing this specific information our forum members can give a better response.

You can “fake” the data if it is confidential. The more important thing is to learn the structure of your data

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

I posted the sample data to the original message.

Was this reply helpful? Yes No
WarrenBelz 153,051 Most Valuable Professional on at

Like (0)

Report
Hi @Anonymous ,
I will use names in blue - replace them with your names.
Your Source data name is Tracked for the ones you are looking for.
The column name in this is TrackedLocation
The column in the main list Locations is Location
So try this on the Items property for the gallery
Filter( Locations, isBlank( Lookup( Tracked, TrackedLocation = Locations.Location, TrackedLocation ) ) )
I have tried to keep this delegable. There is a less complex solution using In if you have a data source under 2000 records.

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.

Was this reply helpful? Yes No
mdevaney 29,989 Moderator on at

Like (0)

Report

@Anonymous
You can put it into a collection called itemsNotTracked like this. Collections can be used in the Items property of a gallery, a data table and in several other ways.

ClearCollect(itemsNotTracked,
Filter(
AddColumns(
‘Locations List’,
“AnyRecords”,
!IsBlank(LookUp(‘Tracker List’, ‘Lookup to Locations’.Value = ‘Locations List’[@ID]),
AnyRecords=false
)
)

—-
Please Accept as Solution if this post answered your question so others may find it more quickly. If you found this post helpful consider giving it a Thumbs Up.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
Here's what code I'm trying to use.

Filter( Clinics, IsBlank( LookUp( 'Clinic Task Tracker', Clinic.Id = Clinics.ID, Clinic ) ) )

I have a feeling I'm stuck on the "Clinic.Id = Clinics.ID" line.

How can I fix this and also can you explain the in code?

Was this reply helpful? Yes No
WarrenBelz 153,051 Most Valuable Professional on at

Like (0)

Report
Hi @Anonymous ,
What is the matching field in Clinics and Clinic? It will not be the ID - is it "Lookup to Locations"? So maybe
Filter( Clinics, IsBlank( LookUp( 'Clinic Task Tracker', 'Lookup to Locations' = ID, Clinic ) ) )
will work - I m sure you will see you have to try to match values that should be there.

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.

Was this reply helpful? Yes No