Unanswered

Build collection from Choice Sharepoint List columns

(0) Share

Report

Posted on by Jon Studsrud Microsoft Employee

Hello,

I'm trying to think of a way in which I can build a collection from values in a SharePoint Choice column. Not sure its possible but thought I'd ask.

I have a specialization list (skill set) that lists an area of expertise and then a choice column on that record that contains the employees that are an expert in that skill. What I want to do is display all of the skills in a gallery, and then I can select each of the skills via a check box. From there I want to go to a new form and display a unique (distinct) list of the employees that have one or all of the skills selected.

I know Choice columns are horrible to work with, if there is a better way to tackle this I'm all ears. But if not I just need to know how I can enumerate all of the choice column values from the selected skills and dump into new collection as individual records.

sp list would have columns: Specialization(text), Employee (Choice)

Here is example of selecting the skill sets displayed in Gallery and then check box selected.

Thanks

Jon

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

Community Power Pla... on at

Like (3)

Report
@Studz02
This code structure should work for Choice columns
ClearCollect( colChoices, Choices( yourListName.yourChoiceColumn))

Was this reply helpful? Yes No
Jon Studsrud Microsoft Employee on at

Like (0)

Report

Thanks. So that works to an extent.

My issue now is building that collection based on a filter of the skills selected. I think I need to rethink the way I built my SP list. I don't think this was the most efficient way of doing this.

Was this reply helpful? Yes No
v-xida-msft on at

Like (2)

Report
Hi @Studz02 ,

Could you please share a bit more about your scenario?

Do you want to display all Skills in a Gallery? Do you enable "Allow Multiple Selections" option for the Choice type column?

If you want to display all Skills in a Gallery, please consider add a Gallery in your app, set the Items property to following:

Distinct('Skill set', Specialization)

Then within the Gallery, add a Checkbox and a Label, set the Text property of the Label to following:

ThisItem.Result

Set the OnStart property of App to following:

Collect( EmployeeCollection, {Employee: ""} ); Clear(EmployeeCollection)

Set the OnCheck property of the Checkbox inside the Gallery to following:

ForAll( LookUp('Skill set', Specialization = ThisItem.Result).Employee, If( Not(Value in EmployeeCollection.Employee), Collect(EmployeeCollection, {Employee: Value}) ) )

set the OnUncheck property of the Checkbox inside the Gallery to following:

ForAll( LookUp('Skill set', Specialization = ThisItem.Result).Employee, If( Value in EmployeeCollection.Employee, RemoveIf(EmployeeCollection, Employee = Value) ) )

then within your New Form screen, add a Dropdown control or ComboBox control to display unique (distinct) list of the employees, set the Items property to following:

Distinct(EmployeeCollection, Employee)

please consider try above solution, then re-load your canvas app (fire the OnStart property of App), check if the issue is solved.

Best regards,

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

Like (1)

Report
@Studz02
Hey mate, sorry for the delay but I think I have built something that works for what you need?

Firstly, I created a simple 2 column SP List with the first column called "Skill" and the second called "Specialist". Skill is a 'Single line of text' and Specialist is 'Person column'. I called this list SkillSet.

Then inside an app I added a blank horizontal gallery and set it up to look the same as yours. The Items property of this gallery is simply SkillSet.

My checkbox has the following code:
// OnCheck property Collect(colSkills, ThisItem) // OnUncheck property RemoveIf(colSkills, Skill = ThisItem.Skill)

Then added a button - but you can use this code on the screen transition to your Form, or wherever you like really - with this code.
Clear(colAllPeople); ForAll(colSkills, ForAll(Specialist, Collect(colAllPeople, DisplayName))); ClearCollect(colPeople, Distinct(colAllPeople, ThisRecord.Value))

The collection colPeople will then be the Distinct list of people with the selected specialisations. You can use this in a drop down or however you like.

Let me know how you get on and if you need anymore assistance.

Was this reply helpful? Yes No
Jon Studsrud Microsoft Employee on at

Like (0)

Report

Thanks everyone. I appreciate the help here. I'll look through all of these suggestions later today. My day is booked so probably won't get to them until this evening to test out. Hopefully have an update by tomorrow morning.

Was this reply helpful? Yes No
Jon Studsrud Microsoft Employee on at

Like (0)

Report

So this is what I have ('Employee Specializations' = SP List). This OData__x0054_SP2 column is what shows up for the Person column in my list.

Clear(colAllPeople);
ForAll(colSkills, ForAll('Employee Specializations', Collect(colAllPeople, colSkills.OData__x0054_SP2)));
ClearCollect(colPeople, Distinct(colAllPeople, ThisRecord.OData__x0054_SP2))

So now when I go into my new form and try to get setup a drop down or use the gallery I run into this issue since my data is in a table. It doesn't look likes its pulling the distinct values. I always run into issue with tables. I'm sure I'm doing something dumb here. I need to get that Result to show the distinct users either in the gallery or dropdown list.

Was this reply helpful? Yes No
Verified answer

Community Power Pla... on at

Like (1)

Report

@Studz02
You just need some slight changes to your code, try this

ForAll(colSkills,
ForAll(OData__x0054_SP2, Collect(colAllPeople, DisplayName)));
ClearCollect(colPeople, Distinct(colAllPeople, ThisRecord.Value))

See how you go with that.

Was this reply helpful? Yes No
v-xida-msft on at

Like (2)

Report
Hi @Studz02 ,

Do you enable "Allow Multiple Selections" for the OData__x0054_SP2 column in your SP List?

Based on the formula that you mentioned, I think there is something wrong with it. On your side, you should modify your formula as below:

1. If you enabled "Allow Multiple Selections" for the OData__x0054_SP2 column in your SP List:

Modify your formula as below:

Clear(colAllPeople); ForAll( colSkills, ForAll( OData__x0054_SP2, Collect( colAllPeople, DisplayName ) ) ); ClearCollect(colPeople, Distinct(colAllPeople, Value))

Then set the Items property of the Dropdown box to following:

colPeople.Result

2. If you do not enable "Allow Multiple Selections" for the OData__x0054_SP2 column in your SP List:

Modify your formula as below:

Clear(colAllPeople); ForAll( colSkills, Collect( colAllPeople, OData__x0054_SP2.DisplayName ) ); ClearCollect(colPeople, Distinct(colAllPeople, Value))

Then set the Items property of the Dropdown box to following:

colPeople.Result

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

Was this reply helpful? Yes No
Jon Studsrud Microsoft Employee on at

Like (0)

Report

Thanks @v-xida-msft and @Anonymous . I went through both and of these an they work great. Thank you. Much appreciated.

Was this reply helpful? Yes No
Tapesh 825 on at

Like (0)

Report

Hi @Studz02 @v-xida-msft @Anonymous

I have some what similar issue trying to add items on check and remove on unched from my collection, could you please let me help with the missing puzzle, please find the link below for the issue details.

How remove Items from collection on Uncheck and ad... - Power Platform Community (microsoft.com)

Thanks for your help and efforts

Was this reply helpful? Yes No