discarding existing elements in a dropwdown

[Expolio]

Hi i made an app and i have a sharepoint list where there is a column which is a look up column of another list , I want to make a dropdown that only list the items that arent existing in that column, can anyone help me?

[AngeloG]

Probably you should create a collection and bind it to dropdown.

Get data from list (of your lookup column), punt into collection, then removed data with filter, and bind to your control

[v-albai-msft · Accepted solution]

Hi @Expolio ,

I did a test on my side for your reference.

Here my first list called list8 with a Lookup column called lookup1, which is generated data from Title column in list10. 

Here is the code using in my dropdown's Items property:

(pay attention to the code with colours, you need to replace with your own column/list name )

ShowColumns(Filter(list10,Not(Title in AddColumns(list8,"lookupvalue",lookup1.Value).lookupvalue)),"ID","Title")

Above code will exclude the data that already selected in the LookUp column, and will show the rest data.

I used to answered a similar question, you can refer to that thread for more further information.

Solved: Re: Remove already added values and disable drop-d... - Power Platform Community (microsoft....

Best regards,

Allen

[Expolio]

thanks for your quickly answer ,i tried what you said but it doesnt retrieve any options

[v-albai-msft]

Hi @Expolio,

Is there any result when you click on the end of your code in formula bar? Like this:

If yes, remove the code from your formula bar, then re-add the code, check the result each time.(try some times of this action because sometimes there is delays to get the results into dropdown control).

Best regards,

Allen

[Expolio]

edit

[Expolio]

Okey now I figure it out ,the problem is that my column a is number ,do you know how to do it if the lookup1 column is a number?

[v-albai-msft]

Hi @Expolio ,

I cannot understand your issue. 

What do you mean "the problem is that your column is number"? Do you mean your Lookup column get Number from another list like below? (Number1 is a Number column from list10)

Please show me a screenshot of the settings of your Lookup column. 

Best regards,

Allen 

[Expolio]

Exactly , the column in list10 is a number like your example  ,besides i have tried your example several times and only work when the column in list 10 is title ,if the column is a number or a single line text it do de functions correctly but in the combo box doesnt return any value .

[v-albai-msft]

Hi @Expolio ,

If this is a Number column, you need to convert it to narmal format, since the defualt format of Number column is like "14.0000000".

So try this formula(below has same results if still use ShowColumns function mentioned in my prrvious reply):

Filter(list10,Not(Number1 in AddColumns(list8,"lookupvalue",Value(lookup1.Value)).lookupvalue)).Number1

Then set Update property of DataCard to:

{
    '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
    Id: LookUp(list10,Number1=Dropdown5.Selected.Number1).ID,
    Value: Dropdown5.Selected.Number1
}

Best regards,

Allen