cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Expolio
Helper II
Helper II

discarding existing elements in a dropwdown

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-albai-msft
Community Support
Community Support

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

valbaimsft_0-1625737877139.png

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

View solution in original post

Hi @Expolio ,

Is these numbers correct?

valbaimsft_0-1626332889642.png

And all numbers display in the dropdown?

If yes, re-try below method which I mentioned before(also refresh your datasource):

valbaimsft_1-1626332993076.png

Best regards,

Allen

View solution in original post

13 REPLIES 13
AngeloG
Regular Visitor

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
Community Support
Community Support

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

valbaimsft_0-1625737877139.png

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

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

PRUEBA2.jpg

 

 

Hi @Expolio,

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

valbaimsft_0-1625798230974.png

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

edit

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?

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)

valbaimsft_0-1626082824222.png

 

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

Best regards,

Allen 

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 .

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (3,167)