You’re offline. This is a read only version of the page.
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.
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:
Share
Report
All responses (
Answers (
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.
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
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.
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.
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.
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.
#1
WarrenBelz
146,522
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,890
Most Valuable Professional
