Interacting with Multi-Value Columns (People, Dropdown, Tags and Connect Board)

The People, Dropdown, Tags and Connect Board columns accept multiple values in monday.com. Because of this, they require a set of functions to interact with them.

What the values of these columns have in common

  • Whether they are people, dropdown labels, tags or connected items, the elements that you can add to a multi-value column need to be defined in monday.com prior to being used in a formula. At this stage, the Advanced Formula Booster does not offer a way to create them through a formula.
  • These elements have a name and an identifier. Typically, the identifier is hidden from users in the monday.com interface, and what is displayed is the name. However, in the background, monday.com stores the identifier in the column, not the name. This way, if you decide to change the name of an item, it is a lot easier for monday to handle it.
    The Advanced Formula Booster takes the same approach. When you save a formula containing one of the specific functions detailed below, it fetches the identifier of the element from monday.com and stores it in the formula without the user noticing it. In other words, if you create a formula to add a tag named "Marketing" to a Tags column, the Advanced Formula Booster will store the tag's identifier in the formula, not the name. As a consequence, if you later rename the tag "Marketing Opportunity", the formula will keep working.

How to read the values of a People, Dropdown, Tags or Connect Board column

When you read the value of one of these columns, you will get the identifier of the item formatted like this Number:Name.

1:[C]={Dropdown}-> 3:Free

2:[B]={Tags}-> 17810765:Construction,17811165:Marketing

3:[C]={Connect Board}-> 6846226461:Item 1,6846223456:Item 2

In the case of a People item, you will also get its type, ie. Person or Team, like this Type:Number:Name.

4:[A]={People}-> Person:38798354:Bob Smith

In the Test pane of the Formula Editor, the Advanced Formula Booster displays the elements of the identifier as shown below, so that they are more readable. If if the Test pane seems to show the elements of the identifier in a different order, remember that if you need to use these values in a calculation, the name comes last as shown in the piece of code above, ie. id:name or type:id:name for People.

To only get the name of the person/team, label, tag or connect item

Use the PEOPLENAME, LABELNAME, TAGNAME, CONNECTEDITEMNAME functions to get the name(s) of the element, instead of the identifier. If there are more than one item in the column, the names are separated by commas.

1:[A]=PEOPLENAME({People})-> Bob Smith

2:[C]=LABELNAME({Dropdown})-> Free

3:[B]=TAGNAME({Tags})-> Construction|Marketing

4:[B]=CONNECTEDITEMNAME({Connect Board})-> Item 1|Item 2

The functions specific to the People, Dropdown and Tags columns

To avoid having to know the identifier of a People, Dropdown item or Tag to interact with it, we created special functions that only need the name of the element.

Use the | character ("pipe" character) as the separator of multiple items

In the examples below, note how we use a pipe (vertical bar) to separate people, labels or dropdown when specifying multiple items.

The CONTAINS functions

Should you want to check if an item is among the values of your column, use the CONTAINSPEOPLE, CONTAINSLABEL, CONTAINSTAG, CONTAINSCONNECTEDITEM functions.

These functions require 2 parameters: the value(s) that you are checking for and the column you are searching. It returns true only if all values are found, false if one or more values are missing.

1:[A]=CONTAINSPEOPLE("Bob Smith",{People})-> True or False

2:[A]=CONTAINSLABEL("Marketing|HR",{Dropdown})-> True only if both

3:[A]=CONTAINSTAG("Free",{Tags})-> True if it contains the Free tag

4:[A]=CONTAINSCONNECTEDITEM({Board2Items.Item#2},{Connect Board})-> True if it contains the 2nd item of Board 2

The IS functions

Should you want to perform a strict comparison, use the ISPEOPLE, ISLABEL, ISTAG functions. By "strict" we mean that you want to check if the values in the column exactly match the item(s) specified.

These functions require 2 parameters: the value(s) that you are checking for and the column you are searching.

1:[A]=ISPEOPLE("Bob Smith",{People})-> True or False

2:[A]=ISPEOPLE("Marketing|HR",{Dropdown})-> True if contains both and only both

3:[A]=ISPEOPLE("Free",{Tags})-> if it contains only Free tag

4:[A]=ISCONNECTEDITEM({Board2Items.Item#2},{Connect Board})-> True if it contains only the 2nd item of Board 2

The order of the items in the column does not affect the result. Whether the column displays Bob Smith then Marketing or Marketing then Bob Smith does not matter.

The GET Functions

The GETPEOPLE, GETLABELS, GETTAGS functions return item(s) based on their name. The GETPEOPLE AND GETTAGS only require one parameter, the name. Since a label is specific to a Dropdown column, the GETLABELS requires the column as an extra parameter.

There is no equivalent function for the Connect Board column. You can't get a connected item from a string of characters. You have to use the Item virtual column.

1:[A]=GETPEOPLE("Bob Smith")-> Person:38798354:Bob Smith

2:[B]=GETLABELS("Option 1|Option 2",{Dropdown})-> 1:Construction|2:Marketing

3:[C]=GETTAGS("Free|Perpetual")-> 17810765:Free|17811165:Perpetual

These functions are used to set the content of a multi-item column.

We saw how we can use the ADD and REMOVE functions to add or remove an item. But what if we want to completely change the items in the column?

1:{People}=GETPEOPLE("Bob Smith")

2:{Dropdown}=GETLABELS("Marketing|Construction")

3:{Tags}=GETTAGS("Free|Perpetual")

Remember that the Advanced Formula Booster stores the number identifying the item, not its name. So if you save the above formula, go to monday.com and change the name of user "Bob Smith" to "Bob", then reload the formula in the Formula Editor, it will now display GETPEOPLE("Bob").

The ADD functions

The ADDPEOPLE, ADDLABEL, ADDTAG, ADDCONNECTEDITEM functions allow you to add an element to your column. They take 2 parameters the name(s) of the item(s) you want to add and the column.

1:{People}=ADDPEOPLE("Bob Smith|Marketing",{People})

2:{Dropdown}=ADDLABEL("Option 1",{Dropdown})

3:{Tags}=ADDTAG("Construction|Marketing",{Tags})

4:{Connect Board}=ADDCONNECTEDITEM({Board2Items#2},{Connect Board})

The REMOVE functions

The REMOVEPEOPLE, REMOVELABEL, REMOVETAG, REMOVECONNECTEDITEM functions allow you to remove an item from your column. They take 2 parameters the name(s) of the item(s) you want to remove and the column.

1:{People}=REMOVEPEOPLE("Bob Smith|Marketing",{People})

2:{Dropdown}=REMOVELABEL("Option 1",{Dropdown})

3:{Tags}=REMOVETAG("Construction|Marketing",{Tags})

4:{Connect Board}=REMOVECONNECTEDITEM({Board2Items#2},{Connect Board})

The GETCOMMON functions

The GETCOMMONPEOPLE, GETCOMMONLABELS, GETCOMMONTAGS, GETCOMMONCONNECTEDITEMS functions allow you to retrieve the common elements between 2 or more columns.

1:[PeopleInCommon]=GETCOMMONPEOPLE({Person},{People})

2:{LabelsInCommon}=GETCOMMONLABELS({Dropdown1},{Dropdown2},{Dropdown3})

3:{TagsInCommon}=GETCOMMONTAGS({Tags1},{Tags2})

4:{Connect Board}=GETCOMMONCONNECTEDITEMS({Connect Board1},{Connect Board2})

In the code above, we get the common elements between columns of the same item, but you are not limited to the same item.

1:[PeopleInCommon]=GETCOMMONPEOPLE({Person},{Previous.People},{Next.People})

2:[LabelsInCommon]=GETCOMMONLABELS({Dropdown},{BoardItems.Dropdown#5)

You can even use a list of items (but in this case, you can only use 2 parameters, one for a single item ({Person} in the code below) and one for multiple items ({BoardItems.Person} in the code below) :

1:[PeopleInCommon]=GETCOMMONPEOPLE({Person},{BoardItems.People})