cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lui Helper III
Helper III

Don't show selected values from choice column once it has recorded into the list.

Team,

The idea is to create a filter and i don't want to show ALL the choices, only the ones that are NOT present in a column of another list.

 

Card1 includes Dropdown1 (single selection)

ITEMS for Dropdown1 = Sort(Distinct(Filter(Sublist,Field_Name="ABC"),Value_Name),Result,Ascending)

 

When Dropdown1 is saved, it's being patched/saved to Sharepoint list: MasterList on a text column: Text1

 

How can I only display choices (items) that are not in Text1 column from SP MasterList

 

FYI, I've found solutions from other posts with the following solution but i cant seem to make it work when the items are being filtered from another sp list (i'm not using a choices column)

 

Filter(
Choices('YourSPList'.YourChoiceColumn), /* <-- YourChoiceColumn represents the Choice type column in your SP list */
Not(
Value in Distinct('YourSPList', YourChoiceColumn.Value)
)
)

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Don't show selected values from choice column once it has recorded into the list.

Hi @lui ,

Do you also want to combine above filter condition with the filter condition for first source?

 

I have made a test on my side, please consider take a try with the following workaround:

Sort(
     Distinct(
                Filter(
                           Sublist, 
                           Field_Name = "ABC",
                           Not(Value_Name in Filter(MasterList, "ABC" in Fields).Text1)  // Modify formula here
                ),
                Value_Name
     ),
      Result,
      Ascending
)

 

Please take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
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

10 REPLIES 10
EricLott Resident Rockstar
Resident Rockstar

Re: Don't show selected values from choice column once it has recorded into the list.

Get your data into a collection at the beginning using ClearCollect() and then when an option is chosen, use Remove() to take that item out of the collection.

Community Support
Community Support

Re: Don't show selected values from choice column once it has recorded into the list.

HI @lui ,

Do you only want to display available options which are not saved in the Text1 column in your another List within your Dropdown1?

 

Based on the needs that you mentioned, I have made a test on my side, please consider take a try with the following workaround:

Set the Items property of the Dropdown1 to following:

Sort(
     Distinct(
                Filter(
                         Sublist, 
                         Field_Name = "ABC",
                         Not(Value_Name in MasterList.Text1)   // Add filter condition here
                ), 
                Value_Name
     ),
     Result, 
     Ascending
)

 

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lui Helper III
Helper III

Re: Don't show selected values from choice column once it has recorded into the list.

@v-xida-msft  how do i add a second condition to the second source 

Not(Value_Name in MasterList.Text1)

 

For example, Not(Value_Name in MasterList.Text1 && "ABC" in MasterList.Fields) Does not work.

I want to say those items without  "ABC" in Fields and Value_Name in Text1

I hope im making sense. 

Community Support
Community Support

Re: Don't show selected values from choice column once it has recorded into the list.

Hi @lui ,

Please consider modify your Filter formula as below:

Set the Items property of the Dropdown1 to following:

Sort(
     Distinct(
                Filter(
                         Sublist, 
                         Not("ABC" in MasterList.Fields),
                         Not(Value_Name in MasterList.Text1)   // Add filter condition here
                ), 
                Value_Name
     ),
     Result, 
     Ascending
)

or

Sort(
     Distinct(
                Filter(
                         Sublist, 
                         Not("ABC" in MasterList.Fields)&& Not(Value_Name in MasterList.Text1)
                ), 
                Value_Name
     ),
     Result, 
     Ascending
)

 

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lui Helper III
Helper III

Re: Don't show selected values from choice column once it has recorded into the list.

@v-xida-msft  it doesnt work, no items show up. I think i need to explain a bit better.
I want to say only consider those "Value_Name" in MasterList.Text1 with "ABC" in Masterlist.Fields 
I've tried Not(Value_Name in MasterList.Text1), Not("ABC" in MasterList.Fields) but nothing works. I appreciate the help.

Spoiler
Sort(
     Distinct(
                Filter(
                         Sublist, 
                         Field_Name = "ABC",
                         Not(Value_Name in MasterList.Text1)   // I WANT TO SAY ONLY CONSIDER THOSE "Value_Name" in MasterList.Text1 with "ABC" in Masterlist.Fields
                ), 
                Value_Name
     ),
     Result, 
     Ascending
)
Community Support
Community Support

Re: Don't show selected values from choice column once it has recorded into the list.

Hi @lui ,

Do you want to filter your Items whose "Value_Name" in MasterList.Text1 with the "ABC" in Masterlist.Fields already?

 

Based on the needs that you mentioned, I think two Filter function could achieve your needs. Please take a try with the following formula:

Sort(
     Distinct(
                Filter(
                        Filter(Sublist, "ABC" in Masterlist.Fields),
                        Value_Name in MasterList.Text1
                )
                Value_Name
     ),
     Result, 
     Ascending
)

 

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lui Helper III
Helper III

Re: Don't show selected values from choice column once it has recorded into the list.

@v-xida-msft yes but what about the condition for the first source (SubList) ?

 

This is what I have so far and it works fine, however, I only want to disregard the Value_Name in MasterList.Text1 that have "ABC" in MasterList.Fields

Filter(Sublist, Field_Name = "ABC",Not(Value_Name in MasterList.Text1)), Value_Name),Result,Ascending)

Where do I enter if "ABC" = MasterList.Fields

I tried the following and no items show up:

Sort(Distinct(Filter(Filter(Sublist, Field_Name = "ABC","ABC" in MasterList.Fields),Not(Value_Name in MasterList.Text1)),Value_Name),Result,Ascending)

Community Support
Community Support

Re: Don't show selected values from choice column once it has recorded into the list.

Hi @lui ,

Do you also want to combine above filter condition with the filter condition for first source?

 

I have made a test on my side, please consider take a try with the following workaround:

Sort(
     Distinct(
                Filter(
                           Sublist, 
                           Field_Name = "ABC",
                           Not(Value_Name in Filter(MasterList, "ABC" in Fields).Text1)  // Modify formula here
                ),
                Value_Name
     ),
      Result,
      Ascending
)

 

Please take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
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

lui Helper III
Helper III

Re: Don't show selected values from choice column once it has recorded into the list.

Thank you, seems to be working except for the 500 record limit. I changed it to 2000 and it works better, but last question:

If I'm using the filter function, shouldnt that get rid of the non-delegable query maximum? 

In source SubList, i only have 15 items/records with "ABC" in Field_Name. 
What do you suggest I do to prevent any issues if I go over 2000 total records in the source SubList? 
This is a sharepointintegration (customized form). Thank you

Helpful resources

Announcements
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

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