Remove Item from Choices in ComboBox List Once Chosen

(2) Share

Report

Posted on by Phineas

4,800 Super User 2025 Season 1

I have Gallery1, in which is ComboBox1.

ComboBox1 is populated from an SP List using the following formula.

Filter(Activities_Collection,Title = 

JS_Location_Dropdown.Selected.Project_Name,

Billing_Unit_and_Description)

I won't to restrict the ability of the user to select an Activity more than once - essentially,
remove the item from the ComboBox list once it has been selected.

I am doing a similar thing with a ComboBox regarding some images using the following -

  Filter(col_Drop_Job_Picture_Title_Collection,
        Not(
            Image_Name in Drop_Job_Image_Collection.DisplayName
  )
  )

How can I update my Activities ComboBox1 formula to achieve the
same function?

Categories:

Using Apps Using Formulas

All responses (13)

Answers (3)

Verified answer

Phineas 4,800 Super User 2025 Season 1 on at

Like (0)

Report
Remove Item from Choices in ComboBox List Once Chosen

@stampcoin

Edit of Original

I think I got it; at least some of it.

I still couldn't figure out how to combine the two.

However, I commented out my version of your formula and created a new one.

The new one seems to be working.

/*ClearCollect(

Activities_Collection,

RenameColumns(

ShowColumns(

FirstN(

Sort(

Activities_List,

ID,

SortOrder.Ascending

),

1100

),

Billing_Unit_and_Description

),

Billing_Unit_and_Description,

ActivityRemaining

)

);*/

'OnStart' now has -
ClearCollect(

colActivityChoiceCollection,

RenameColumns(Activities_Collection,Title,Location,Billing_Unit_and_Description,Activity));

Clear(colPicked);

ComboBox Items now has -

Filter(colActivityChoiceCollection,Location = JS_Location_Dropdown.Selected.Project_Name
&& !(Activity in colPicked.Activity))
Verified answer

stampcoin 1,302 on at

Like (0)

Report

Remove Item from Choices in ComboBox List Once Chosen

@Phineas Hi,

That should be the same , just use your own column.

My example uses Purchase order number, I could use PartNumber or Supplier code instead.

Does it make sense to you ?
Phineas 4,800 Super User 2025 Season 1 on at

Like (0)

Report

Remove Item from Choices in ComboBox List Once Chosen

@stampcoin

Location is “customized”.

Locations are chosen by the user from a list in a Dropdown control on screen one.

Location (Title) is a single line of text column in the

‘Project_List (Collection).

Though many of the Activity choice are the same across ALL Locations, some have a few differences. Hence the need to filter the Activity ComboBox by Location.
stampcoin 1,302 on at

Like (0)

Report

Remove Item from Choices in ComboBox List Once Chosen

@Phineas Hi,

When you say 'Location', is it the default type one, or your customized one (single line text) ?

default location type is kind of a table....
Phineas 4,800 Super User 2025 Season 1 on at

Like (0)

Report

Remove Item from Choices in ComboBox List Once Chosen

@stampcoin

Actually, I think I wrote something wrong in the 'OnStart'.

I have this at the top of 'OnStart', which use to populate ALL columns of the Activities_Collection
with data.

This is what the Activities_Collection looks like now, with the original formula at the top of 'OnStart'
and the new (my version of your suggestion) formula at the bottom.

I think I should probably rename the collection in my version of your formula so that it contains only
one column and doesn't impact my original Activities_Collection, maybe?

ClearCollect(

Activities_Collection,

RenameColumns(

ShowColumns(

FirstN(

Sort(

Activities_List,

ID,

SortOrder.Ascending

),

1100

),

Billing_Unit_and_Description

),

Billing_Unit_and_Description,

ActivityRemaining

)

);

Clear(colPicked);

Also, did you have any recommendation on how to filter your version by Location, with something like the following?
Filter(Activities_Collection,Title =
JS_Location_Dropdown.Selected.Project_Name,
Billing_Unit_and_Description)
Phineas 4,800 Super User 2025 Season 1 on at

Like (0)

Report

Remove Item from Choices in ComboBox List Once Chosen

Got it working, except it is showing ALL remaining items from the SP List/Collection.

The original formula filtered by Location (see below).

How do I incorporate the following into the ComboBox items so that the return is ONLY specific to the Location (chosen elsewhere)?

Filter(Activities_Collection,Title =
JS_Location_Dropdown.Selected.Project_Name,
Billing_Unit_and_Description)
stampcoin 1,302 on at

Like (0)

Report

Remove Item from Choices in ComboBox List Once Chosen

@Phineas Hi,

It might come from your cache or something, since Activities_Collection only has one column, colPicked should have one column as well...
Phineas 4,800 Super User 2025 Season 1 on at

Like (0)

Report

Remove Item from Choices in ComboBox List Once Chosen

I'm working through it.

This is close.

I have more than one column in my colPicked collection (even though only one has content).
My version shows the renamed column with appropriate content. However, it is also bringing
in ALL columns from the list.

Shouldn't I only have one column in the colPicked collection?

'OnStart' has the following. What am I missing?

ClearCollect(

Activities_Collection,

RenameColumns(

ShowColumns(

FirstN(

Sort(

Activities_List,

ID,

SortOrder.Ascending

),

1100

),

Billing_Unit_and_Description

),

Billing_Unit_and_Description,

ActivityRemaining

)

);

Clear(colPicked);
Verified answer

stampcoin 1,302 on at

Like (0)

Report
Remove Item from Choices in ComboBox List Once Chosen

@ Phineas Hi,

I will explain another example ( similar to my first one).

1. I have a purchase order List in SharePoint, I will use Purchase Order Number to feed the combox1.

2. OnStart:
ClearCollect( MyPO, RenameColumns( ShowColumns( FirstN( Sort( PurchaseList, //My List in SPO ID, SortOrder.Ascending ), 1100 //Fetch out 1100 records. ), Title //I only need this column ), Title, // rename Title to POnumber. PONumber ) ); Clear(colPicked); //clear ComboBox1.SelectedItems) in colPicked.

3. Combox1 .

OnChange = ClearCollect(colPicked,ComboBox1.SelectedItems)

Items = Filter(MyPO,!(PONumber in colPicked.PONumber))

4. Optional, if you don't want show a long list, use below for Items.

With( { // if the user has typed, do a Search() // otherwise, show the first 5 POs baseSet: If( Len( ComboBox1.SearchText ) > 0, Search( MyPO, ComboBox1.SearchText, PONumber ), FirstN( Sort( MyPO, PONumber, SortOrder.Ascending ), 5 ) ) }, // now hide any they’ve already picked Filter( baseSet, Not( PONumber in colPicked.PONumber ) ) )

hope this give you a lead.
Phineas 4,800 Super User 2025 Season 1 on at

Like (0)

Report

Remove Item from Choices in ComboBox List Once Chosen

You can disregard this...

  Filter(col_Drop_Job_Picture_Title_Collection,

  Not(

Image_Name in Drop_Job_Image_Collection.DisplayName

  )

  )

I was using it as an example of where it is working elsewhere in
my app. But I don't think it will work here.

I am fairly sure I have over-complicated things, and this rambling probably
won't make it any easier, but here it goes...

My goal is to prevent the user from being able select an item in a
ComboBox more than once.

Once an item is selected it should be removed from the ComboBox list.

The source data is hundreds of items, so using something like -
ClearCollect(
TestValues,
{ Value: "1" },
{ Value: "2" }...

...at 'OnStart' on screen 'OnVisible' is problematic.

Is there no way for the formula above to refer to an entire column
in a collection, rather than itemizing each value?

If no, I came up with a second option; a warning notification pop-up.
However, I can't get the language right.

The ComboBox 'OnChange' has -

If(!IsBlank(Activity_Description_Fld.Text),

Collect(colActivityComboBoxList, {ActivityName: Activity_Description_Fld.Text,

Activity_intID: Activity_Gallery_intActivityID_Fld.Text}))

The colActivityComboBoxList collection shows the data from the ComboBox 'OnChange'.

However, the warning notification is nested inside the Gallery and the Gallery has no reference to the
ActivityName (which is the key), as I'm not trying to limit the number of Activities that can be created,
only that an Activity type cannot be used twice.

Collect(

colActivities,

{

intActivitiesID: GUID(),

rem: false

}

);

The best I can get so far is the count of item in the colActivityComboBoxList

If(CountRows(colActivityComboBoxList) >1, true)