Unanswered

Create a Collection For All Branches

(0) Share

Report

Posted on by Laik

Dear PoweRappers,

I have a bit too complex requirement (for me) as briefed below, looking for an easy solution please:

I have a SharePoint Branch List, containing records of all Branches information for many companies, including a column of ‘codes’ (Text field), and I would want to create a Collection in Power Apps, to extract the codes from all these branches where, the requested Company is equal to the Company in Branch List.

After we get a list of all the ‘codes’ in a collection, which may include duplicate/triplicates ‘codes’, I also want to filter-out unique codes, as a next step to get a list of unique ‘codes’ for each company.

The following function works for a single branch as copied below, I need For All branches:

ClearCollect(colAllCodes,Filter(colISICKSA, 'ACTIVITY CODE' in LookUp('Branches List', 'Branch CR No.'= galRequested.Selected.'CR Number', Activities).Value))

Codes

Where colAllCodes is new collection name

coISICKSA is a collection of all Codes (Master Codes List)

ACTIVITY CODE is the column field in colISICKSA

Activities is a Lookup field of Codes (looking up to Codes (Master Codes List)

Hope I am clear in expressing my request!

Thanks for the prompt response.

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

MarkRahn 1,229 Super User 2025 Season 2 on at

Like (0)

Report
Hi @Laik

The LookUp function only returns the first record found.

https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-filter-lookup
LookUp returns only the first record found, after applying a formula to reduce the record to a single value. If no records are found, Filter and Search return an empty table, and LookUp returns blank.

You need to use Filter or Search and then use Distinct to get the Distinct values.
https://www.matthewdevaney.com/powerapps-collections-cookbook/unique-values-from-a-collection/

I'm going to make some assumptions such as:
1) only one Company is selected
2) a single Company has multiple rows from the Branches List
3) Activities is a single select Lookup field in the Branches List that is linked to the Master Codes List

When a Company is selected from galRequested, in the OnSelect you should put something like:

ClearCollect( colAllCodes, Distinct( Filter( 'Branches List', 'Branch CR No.' = galRequested.Selected.'CR Number' ), Activities.Value ) );

Filter will get all the matches for the Branch CR No. Distinct will get all the distinct Activities from the returned records.

I don't think you need to reference colSICKSA which has a list of all your codes since Activities is a lookup on the Master Codes List which colSICKSA is built from.

-Mark
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

Was this reply helpful? Yes No
Laik on at

Like (0)

Report

Thanks Mark .. it seems to have worked ... can you let me know as to, how do I display these extracted Activities Codes in Gallery, screenshot is here

Was this reply helpful? Yes No
MarkRahn 1,229 Super User 2025 Season 2 on at

Like (0)

Report

Hi @Laik

Is the Gallery you are asking about the galRequested one or a separate Gallery just to display the distinct Codes/Activities?

For a separate Gallery, you would set the Data Source to colAllCodes and link to ThisItem.Value.

-Mark
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

Was this reply helpful? Yes No
Laik on at

Like (0)

Report

Dear Mark,
Thanks for your prompt response.
I have set the datasource/Gallery Items as colAllCodes and I was trying to show the unique codes in colAllCodes, in a Gallery, however I am getting this error.
Any thoughts?

Was this reply helpful? Yes No
MarkRahn 1,229 Super User 2025 Season 2 on at

Like (0)

Report

Hi @Laik

ThisItem.Value should be used in the Gallery controls. I can't see from your screen shot what Property you have selected. I am going to guess it is the Items Property for the Gallery.

Items for the Gallery should be colAllCodes. That is your Data Source. "Items" and "Data Source" are the same for a Gallery.

When you drop controls in the Template of the Gallery such as a Title Label, you should set the "Text" Property to ThisItem.Value. ThisItem references a record from your Collection colAllCodes.

-Mark
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

Was this reply helpful? Yes No
Laik on at

Like (0)

Report

Hey Mark,
Thanks for all your time and instant replies that is helping me a lot. As you can see, I have done exactly what you had asked me to do.
After we create that collection colAllCodes,
I have 3 branches for this company, 1 branch activity is blank, 2nd branch has 10 activities and the 3rd branch has 4 activities, the requirement is to have a total of 14 records minus the duplicates (4 activities in this case) ... the final outcome should list 10 unique activity codes in the column Value in 10 rows in colAllCodes.

Was this reply helpful? Yes No
MarkRahn 1,229 Super User 2025 Season 2 on at

Like (0)

Report

Hi @Laik ,

What does your Branches List look like? What are the fields? The "Activities" field is a Lookup to your Master Codes List. Is the "Activities" field set to allow multi-select? It is unclear how your data is structured.

-Mark

Was this reply helpful? Yes No
Laik on at

Like (0)

Report

Yes Mark, The Activities lookup field is multi-select field in the Branches List..

Was this reply helpful? Yes No
Verified answer

MarkRahn 1,229 Super User 2025 Season 2 on at

Like (1)

Report
Hi @Laik ,

Someone else on here might know a better way but I think you need to use a temporary Collection to get all the values from the Activities multiselect Lookup field. Then get Distinct values from that Collection.

Something like:
Clear(colAllCodesWDuplicates); ForAll( Filter( 'Branches List', 'Branch CR No.' = galRequested.Selected.'CR Number' ), ForAll( ThisRecord.Activities, Collect( colAllCodesWDuplicates, ThisRecord.Value ); ); ); ClearCollect( colAllCodes, Distinct( colAllCodesWDuplicates, Value ) );

The first ForAll will look thru the Items found from the Filter. The second ForAll will loop thru the values for the Activities/Codes. These values are added to a Collection. Then you can use Distinct to get those values.

-Mark
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

Was this reply helpful? Yes No
Laik on at

Like (0)

Report

Thanks Mark ... That was excellent ... it worked exactly the way I wanted. you are a gem.

Was this reply helpful? Yes No