Unanswered

Filter items in a gallery on a single column based on multiple values from a collection

(0) Share

Report

Posted on by myka

I have 2 SharePoint lists spLOCNames and spVACBookings. (Note: not the real list or column names, I changed them for brevity)

spLOCNames contains columns LOC_NAME a text field and LOC_OWNER a person. LOC_NAMES are "MEXICO", "CANADA", "USA", "VIETNAM", "THAILAND", etc...

spVACBookings contains columns LOC_NAME a text field, GUEST_NAME a text field, SPECIAL_TITLE a text field. The records in the list will have multiple records with the same LOC_NAME. So, if I'm the LOC_OWNER for 2 different locations, "MEXICO" and "VIETNAM" and I want to see all records in the spVACBookings list where LOC_NAME contains either "MEXICO" or "VIETNAM"

I created a collection:

Collect(colLOCNAMES, Filter(spLocationNames, User().Email='LOC_OWNER'.Email));

In my case the collection, colLOCNAMES has 2 records in it, "MEXICO" and "VIETNAM"

When I filter on items for my gallery:

Filter('spVACBookings', LOC_NAME in colLOCNAMES.'LOC_NAME', LOC_NAME)

After I click the "Run on start" and open my app, I only get records where LOC_NAME is "MEXICO" - it does return 158 results for "Mexico" which is the correct number

It seems that it is only assessing the first item it sees, which is "MEXICO"

Any recommendations?

Categories:

Building Power Apps

All responses (2)

Answers (0)

Sort by

NandiniBhagya20 4,667 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

You can use ForAll for filtering the second collection. Here is the formula for your reference:
Onvisible property of the screen :
Clear(colFinalResult);ClearCollect(colLOCNames, RenameColumns(ShowColumns(Filter(spListLOCName, Lower(LOCSPOC.Email)=Lower(User().Email)),"LOCName"),"LOCName","LOCNameCustom"));ForAll(colLOCNames, Collect(colFinalResult,Filter(MainList, LOCName=LOCNameCustom)))
WarrenBelz 156,754 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @myka ,

Firstly @NandiniBhagya20 's structure here is sound. If you want to do it all in the Items of your gallery (without collections) - note StartWith used as it is not case sensitive

With( { wLOC: Filter( spLOCNames, StartsWith( LOCSPOC.Email, User().Email ) ) }, Ungroup( ForAll( wLOC As aLoc, Filter( spVACBookings, LOC_Name = aLoc.LOC_NAME ) ), "Value" ) )

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.

Visit my blog Practical Power Apps