cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kmw1130
Post Patron
Post Patron

Creating a Collection from a SharePoint List with Add Columns

I've added a Collection using AddColumn using a column in my SharePoint List, but since there are multiple values that are alike, I would like to add a Distinct.  How would I accomplish that?

Here is my Collection formula:

ClearCollect(colOptions, AddColumns(SPList, "OptionName", ColumnName, "IsChosen", true) 

and my Gallery is displaying my colOptions, but with multiples.

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @kmw1130 :

Thanks for @eka24  's answer, I just want to add a few things.

If you want to get the colOptions after deduplication according to "ColumnName", please try this code:

 

ClearCollect(
    colOptions,
    AddColumns(
        ForAll(
            Distinct(
                SPList,
                ColumnName
            ), /*Get the ColumnName list after deduplication*/
            LookUp(
                SPList,
                ColumnName = Result
            )
        ), /*Traverse the ColumnName list after deduplication and get the records in the corresponding SPList*/
        "OptionName",
        ColumnName,
        "IsChosen",
        true
    )
)

 

Best Regards,

Bof

View solution in original post

5 REPLIES 5
eka24
Super User
Super User

You can do that on the items of the Gallery;

Distinct (colOptions,ColumnName)

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

v-bofeng-msft
Community Support
Community Support

Hi @kmw1130 :

Thanks for @eka24  's answer, I just want to add a few things.

If you want to get the colOptions after deduplication according to "ColumnName", please try this code:

 

ClearCollect(
    colOptions,
    AddColumns(
        ForAll(
            Distinct(
                SPList,
                ColumnName
            ), /*Get the ColumnName list after deduplication*/
            LookUp(
                SPList,
                ColumnName = Result
            )
        ), /*Traverse the ColumnName list after deduplication and get the records in the corresponding SPList*/
        "OptionName",
        ColumnName,
        "IsChosen",
        true
    )
)

 

Best Regards,

Bof

View solution in original post

@eka24  and @v-bofeng-msft  for you answers.  I had tried the Distinct(colOptions, ColumnName) and it does work, except it removes the text of the field results.  I will try @v-bofeng-msft suggestion and see if that works.

Here are my functions:

Button (OnSelect) ClearCollect(colOptions, AddColumns(PIT, "OptionName", ContractType, "IsChoosen", true))

ButtonResults.PNG

On the Gallery > Items is the colOptions, when I put Distinct(colOptions, FieldName) the text disappears

GalleryDistinct.PNG

OnCheck and OnUnCheck - Patch(colOptions, ThisItem, {IsChoosen: true})
Default - ThisItem.IsChoosen

Is it Resolved

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

@v-bofeng-msft This worked!!  Thank you both!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,597)