cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jharville1
Helper IV
Helper IV

Using 'Distinct' Function To Remove Duplicates Pulled From Lookup Column In Combo-Box

I have a combo-box in a form called "Review Material"

 

jharville1_0-1609308177311.png

 

The items inside the "Review Material" combo-box are items pulled from a lookup column in a SharePoint List.

However, there are 3 duplicates of each Review Material (as each Review Material relates to a different country).

 

jharville1_1-1609308890267.png

 

When I click on the "Review Material" combo-box to view all items, it shows 3 of each item since there are 3 of each in the lookup column pulled from a SharePoint List.

 

jharville1_2-1609309257493.png

 

(When you click on one of the 3 duplicate items, it selects all of the duplicate items, but only shows one instance of the item in the actual combo-box selection ~ see below)

 

jharville1_3-1609309683527.png

 

How do I remove duplicates of items in my combo-box pulled from a Look-Up Column in a SharePoint List? I only want to show 1 instance of each "Review Material" item ... not 3 duplicates. We only want to show the name of unique values, no duplicates. Would I be using the 'Distinct' function? Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @jharville1 ,

 

I think you have some misunderstandings about the Distinct and Choices functions, that default Choices function in the Card means select choices from the LookUp column in cfa test list, while the Distinct I post means insert distinct values that come from testList 'review Material' column into the combo box as items. So the data sources in that two functions are different.

Please try modify the Items of the combo box to: 

Distinct(testList, 'Review Material')

or

Distinct(Choices('cfa test list'.reviewItem), Value)​

 

Hoping I have clearly explained and it would help.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

10 REPLIES 10
v-jefferni
Community Support
Community Support

Hi @jharville1 ,

 

Yes, you're right about the Distinct function.

 

So, in your case, the Items of the Combo box need set to:

Distinct(testList,'Review Material')

This would make the combo box display distinct 'Review Material' values of "testList". But as a result, you will need to modify the Update of the ReviewMaterialDataCard to a LookUp function since it is a LookUp column:

LookUp(Choices(MainList.LookUpColumn),Value=ReviewMaterialDataCard.Selected.Result)

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Thank you for the response Jeffer, the solution however is error-ing out.

 

My current code in the 'Item' property of my combo-box is:

jharville1_0-1609353751341.png

 

Would I need to remove the "Choices" and replace it with "Distinct" ?

Hi @jharville1 ,

 

What error is prompting when Items with 

Distinct('cfa test list',reviewItem)

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

There aren't any errors with that line of code itself, but once I type it in, it errors out 6 other areas of my app, all involving the use of my variable, "Value" ... there were no errors at all when I used my previous code for the Items property of the Review Materials combo-box.

 

jharville1_0-1609394507820.png

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

 

jharville1_1-1609394619698.png

 

Here's another error for the "Value" variable that comes up when I entire your code in the Items property:

 

jharville1_2-1609394676904.png

 

Would you have any idea why it's erroring out my "Value" variable, when it didn't have any errors with my previous code? Here is what the error message is showing:

 

jharville1_3-1609395013783.png

 

 

I'll be working on troubleshooting over the next 24hrs and provide any updates once I have any. Thank you for your help! I appreciate it!

Hi @jharville1 ,

 

That's because the column name of Distinct result table is "Result", but "Value" column of the Choice result table.

 

So, you could try to replace those formulas' "Value" with "Result".

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

jharville1
Helper IV
Helper IV

I have another question actually, when I typed in your code to the 'Items' property of the combo-box, it showed no items in the combo-box...

 

jharville1_0-1609437042108.png

 

And when we use "Result" as an alternative to "Value" to try and fix the errors we are getting as a result of putting:

Distinct('cfa test list',reviewItem)

 ... in the Items property of the combo-box, we're still getting the following errors:

 

12.31 ex.png

 

12.31 ex.png

Hi @jharville1 ,

 

For the first question, please check if the display field has been set to "Result"

84.png

If this doesn't work, I think you may share us the details about the "cfa test list" since it is not the one in your original post which was testList. How many items are there in this "cfa test list"? Or if you have changed the list name, you will also need to refresh the data source.

 

This formula is supposed to be correct12.31 ex.png

and I made a test to verify it:

85.png

 

So, the point is to make the combo box display items accurately.

 

Please have a try and if have any information you could update here.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Hi Jeffer,

 

I really appreciate your response. I checked to see if the "Primary Text" was Result ( as you stated ) , but it is "Value" and it won't let me change the option. Do you know why that capability is locked?

 

jharville1_0-1609730176357.png

 

Also, I forgot to mention that 'cfa test list' is the exact same list as 'test list' ... I just forgot to add in the first word of the list. I meant to say 'cfa test list' is 'test list'

Hi @jharville1 ,

 

I think you have some misunderstandings about the Distinct and Choices functions, that default Choices function in the Card means select choices from the LookUp column in cfa test list, while the Distinct I post means insert distinct values that come from testList 'review Material' column into the combo box as items. So the data sources in that two functions are different.

Please try modify the Items of the combo box to: 

Distinct(testList, 'Review Material')

or

Distinct(Choices('cfa test list'.reviewItem), Value)​

 

Hoping I have clearly explained and it would help.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

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