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

(1) Share

Report

Posted on by jharville1

183

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

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

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.

(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)

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!

Categories:

Building Power Apps

I have the same question (0)

All responses (14)

Answers (1)

Sort by

v-jefferni Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
jharville1 183 on at

Like
a
(0)

Report
Copy link

Link copied!

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:

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

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
jharville1 183 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

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

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

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:

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!

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
jharville1 183 on at

Like
a
(0)

Report
Copy link

Link copied!
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...

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:

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @jharville1 ，

For the first question, please check if the display field has been set to "Result"
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 correct
and I made a test to verify it:

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.

Was this reply helpful? Yes No
jharville1 183 on at

Like
a
(0)

Report
Copy link

Link copied!

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?

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'

Was this reply helpful? Yes No
Verified answer

v-jefferni Microsoft Employee on at

Like
a
(3)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
jharville1 183 on at

Like
a
(0)

Report
Copy link

Link copied!

I used your second code and made a few adjustments elsewhere, and it worked! Thank you so much for your help, I really do appreciate it

Was this reply helpful? Yes No