Interacting with Multi-Value Columns (People, Dropdown and Tags)
The People, Dropdown and Tags 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 or tags, the items 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 items 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 item 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 or Tags 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}
2:[B]={Tags}
In the case of a People item, you will also get its type, ie. Person or Team, like this Type:Number:Name.
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 or tag
Use the PEOPLENAME, LABELNAME and TAGNAME functions to get the name(s) of the item, instead of the identifier. If there are more than one item in the column, the names are separated by commas.
1:[A]=PEOPLENAME({People})
2:[C]=LABELNAME({Dropdown})
3:[B]=TAGNAME({Tags})
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.
When returning multiple items, these items are separated by a pipe as well.
The CONTAINS functions
Should you want to check if an item is among the values of your column, use the CONTAINSPEOPLE, CONTAINSLABEL, CONTAINSTAG 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})
2:[A]=CONTAINSLABEL("Marketing|Construction",{Dropdown})
3:[A]=CONTAINSTAG("Free",{Tags})
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})
2:[A]=ISPEOPLE("Marketing|Construction",{Dropdown})
3:[A]=ISPEOPLE("Free",{Tags})
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.
1:[A]=GETPEOPLE("Bob Smith")
2:[B]=GETLABELS("Option 1|Option 2",{Dropdown})
3:[C]=GETTAGS("Free|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 functions allow you to add an item 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})
The REMOVE functions
The REMOVEPEOPLE, REMOVELABEL, REMOVETAG 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})