cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

PowerApps Form: Removing Duplicate Values from a SharePoint List Lookup Column

We have a parent company that owns several smaller companies. I have a master SharePoint list of all the leaders of the smaller companies under the parent umbrella. I have a list in SharePoint with a PowerApps form that has a lookup column to this list of leaders and companies. I need to remove the duplicate values from the CompanyLookup column (Company A has an entry for the President, Vice President, etc.). I found multiple articles and posts that explain the Distinct feature, but all the formulas are different and I can't seem to find the right way to use it. What does not work:

Distinct(Choices([@'Status Change'].Company))

Distinct('Status Change','CompanyLookup')

Distinct(Choices([@'Status Change'].Company),Result)

Distinct (List,ColumnName.Value) (this does not produce an error with a red X, but it renders the dropdown inoperable)

And various other permutations.

How do I craft the expression to make the values of my SharePoint lookup field in my form distinct so that I only see one entry per company?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: PowerApps Form: Removing Duplicate Values from a SharePoint List Lookup Column

Hi @SmileAndWave ,

Do you mean that:
1)in parent list, you have a Company field and one company may have multiple records?

2)in child list, you have a lookup field (looks up to Company field in parent list)?

3)you want to display no-repeated companies for this lookup field?

Actually, lookup field is a nested table with two columns : Value and Id. Value column is with the value of related field(Company) value, Id column is with the value of related item's ID value.

So could you tell me:
if you have Duplicate Values for CompanyA, which ID do you want to update for this company? I assume that the first ID.

 

If so, you could set for the lookup field like this:
combo box's Items:

ShowColumns(
      AddColumns(
           GroupBy(
                   Choices(childlist.lookupfield),
                    "Value","companies"
                    ),  //group based on Value column
            "Id",First(companies).Id   //add a column with the first id 
                 ),
       "Value","Id"       //only need Value and Id column
           )

In your issue, is your child list named [@'Status Change'], lookup field named Company?

If so, you could try this formula:

ShowColumns(
      AddColumns(
           GroupBy(
                   Choices([@'Status Change'].Company),
                    "Value","companies"
                    ),  
            "Id",First(companies).Id   
                 ),
       "Value","Id"       
           )

 This field's data card's Update should still be:

combo boxname.Selected

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
Highlighted
Memorable Member
Memorable Member

Re: PowerApps Form: Removing Duplicate Values from a SharePoint List Lookup Column

Hi @SmileAndWave ,

 

You can Try with the below options but on top of that, you can also try the below options.
GroupBy(DataSource,"FiledName","GroupName").
For reference find ss

VijayTailor_0-1601056939115.png

 

Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Please Vote for my idea if you feel, that will help you in the future or good to have in power app.
https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Ability-s-to-Select-Icon-from-UIFabric/idi-p/69...

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Preview-Mode-for-All-the-Device-Like-Phone-Tabl...



Highlighted
Frequent Visitor

Re: PowerApps Form: Removing Duplicate Values from a SharePoint List Lookup Column

Spoiler
Hi, @VijayTailor . I'm not sure how to do this. Do I have to create a collection or a group?
Highlighted
Memorable Member
Memorable Member

Re: PowerApps Form: Removing Duplicate Values from a SharePoint List Lookup Column

Hi @SmileAndWave 

VijayTailor_0-1601058687180.png

Please check the screenshot, you can figure out how to that.

Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Please Vote for my idea if you feel, that will help you in the future or good to have in power app.
https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Ability-s-to-Select-Icon-from-UIFabric/idi-p/69...

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Preview-Mode-for-All-the-Device-Like-Phone-Tabl...



Highlighted
Frequent Visitor

Re: PowerApps Form: Removing Duplicate Values from a SharePoint List Lookup Column

Ah. I think I understand. The expression you gave is to filter the list to a single entity. So if I have the following:

Company A

Company B

Company C

Company D

And I only want to pull back Company C, this expression will let me do that:

GroupBy('Status Change',CompanyLookup','Company C')

What I want to do is remove all duplicate values. My list actually looks like this:

Company A

Company A

Company B

Company B

Company B

Company B

Company C

Company C

Company C

Company C

Company C

Company D

Company D

I want to use the Distinct feature to filter any duplicate values from my SharePoint lookup column that is connected to my form field. Even though I have multiple values of a specific entity, the result will be filtered to show me only individual values:

Company A

Company B

Company C

Company D

I know that the Distinct feature will do it, but I don't know how to create the expression. Do you know how to use the Distinct feature with a lookup column in SharePoint?

Highlighted
Memorable Member
Memorable Member

Re: PowerApps Form: Removing Duplicate Values from a SharePoint List Lookup Column

if you use group by then no need to use Distinct.
GroupBy also do the Distinct for you.

So you can use the Groupby and Create the Crepression mentioned in the Screenshot, Just replace the ShaprPoint List name and ColumnName that it and User where you want
Highlighted
Frequent Visitor

Re: PowerApps Form: Removing Duplicate Values from a SharePoint List Lookup Column

I'm not quite following.

1st Value: List Name (Status Change)

2nd Value: Lookup Column Name: CompanyLookup

3rd Value: __________

Expression: GroupBy('Status Change','CompanyLookup',__________)

What would the 3rd value be?

Highlighted
Community Support
Community Support

Re: PowerApps Form: Removing Duplicate Values from a SharePoint List Lookup Column

Hi @SmileAndWave ,

Do you mean that:
1)in parent list, you have a Company field and one company may have multiple records?

2)in child list, you have a lookup field (looks up to Company field in parent list)?

3)you want to display no-repeated companies for this lookup field?

Actually, lookup field is a nested table with two columns : Value and Id. Value column is with the value of related field(Company) value, Id column is with the value of related item's ID value.

So could you tell me:
if you have Duplicate Values for CompanyA, which ID do you want to update for this company? I assume that the first ID.

 

If so, you could set for the lookup field like this:
combo box's Items:

ShowColumns(
      AddColumns(
           GroupBy(
                   Choices(childlist.lookupfield),
                    "Value","companies"
                    ),  //group based on Value column
            "Id",First(companies).Id   //add a column with the first id 
                 ),
       "Value","Id"       //only need Value and Id column
           )

In your issue, is your child list named [@'Status Change'], lookup field named Company?

If so, you could try this formula:

ShowColumns(
      AddColumns(
           GroupBy(
                   Choices([@'Status Change'].Company),
                    "Value","companies"
                    ),  
            "Id",First(companies).Id   
                 ),
       "Value","Id"       
           )

 This field's data card's Update should still be:

combo boxname.Selected

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Frequent Visitor

Re: PowerApps Form: Removing Duplicate Values from a SharePoint List Lookup Column

THANK YOU!!!

The second expression worked! Thank you so much! This was making me crazy!

I had typed a response saying that I would try your solutions in a couple of days, but apparently I didn't successfully post it. I see that someone already selected your answer as the solution. Thank you so much for your help. I'm sorry if it seemed like I wasn't responding. I'll pay more attention to posting my replies.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,827)