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

[lui]

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

[Anonymous]

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.

[v-xida-msft]

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,

[lui]

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

[v-xida-msft]

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,

[lui]

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

[v-xida-msft]

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,

[lui]

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

[v-xida-msft · Accepted solution]

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,

[lui]

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