Build collection from Choice Sharepoint List columns

[Studz02]

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

[Anonymous]

@Studz02 

This code structure should work for Choice columns

ClearCollect( colChoices, Choices( yourListName.yourChoiceColumn))

[Studz02]

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.

[v-xida-msft]

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, 

[Anonymous]

@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.

[Studz02]

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. 

[Studz02]

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.

[Anonymous · Accepted solution]

@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.

[v-xida-msft]

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,

[Studz02]

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