List functions

The Collection Utilities category works with lists: the products of an opportunity, the contacts of an account, the selected options of a multi-select choice, or the entries of a text you split. You can total and average them, keep only the items you need, transform each item, sort them and pick single items.

Related list functions in other categories:

• Join and Split turn lists into text and back. See Text.
• Contains Item and Any check what a list holds. See Comparisons and Checks.

Working with each item

Projection, Filter and Sort By calculate something for every item of a list. You give the current item a name in the Element Name slot, such as line or c, and then use that name in the expression for each item:

• In Projection and Sort By, the current item is name.Element and its position is name.Index, starting at 0. For example line.Element.quantity.
• In Filter, the current item is the name itself, for example c.emailaddress1.

Totals

Collection Count

Count(list)Returns Whole Number

Returns the number of items in a list. Works with any list, not only numbers. Shown as Count Of in the outline.

Parameter	Type	Required	Description
Collection	List of any type	Yes	The list to count. A missing list gives 0.

Example: Count(Account.Contacts) = 0 finds accounts without contacts.

Collection Sum

Sum(list)Returns Number type of the list

Adds up a list of numbers. Shown as Sum Of in the outline. A list of Currency values gives Currency.

Parameter	Type	Required	Description
Collection	List of numbers	Yes	The numbers to add up.

Example: Sum(Projection(Opportunity.Products, p, p.Element.extendedamount)) totals the products of an opportunity.

Collection Average

Average(list)Returns Floating Point Number, or the list's number type

Returns the average of a list of numbers. Shown as Average Of in the outline.

Parameter	Type	Required	Description
Collection	List of numbers	Yes	The numbers to average.

• A list of Whole Numbers gives a Floating Point Number, so the average of 4 and 5 is 4.5. Other lists keep their type.

Example: Average(Survey.Scores) < 3 flags customers with a low satisfaction average.

Collection Minimum

Min(list)Returns Number type of the list

Returns the smallest number in a list. Shown as Smallest Of in the outline.

Parameter	Type	Required	Description
Collection	List of numbers	Yes	The numbers to compare.

Example: Min(Projection(Quote.Lines, l, l.Element.priceperunit)) gives the cheapest unit price on a quote.

Collection Maximum

Max(list)Returns Number type of the list

Returns the largest number in a list. Shown as Largest Of in the outline.

Parameter	Type	Required	Description
Collection	List of numbers	Yes	The numbers to compare.

Example: Max(Projection(Order.Lines, l, l.Element.manualdiscountamount)) finds the largest line discount for an approval check.

Empty lists and empty items count as 0

For Collection Sum, Collection Average, Collection Minimum and Collection Maximum, an empty or missing list gives 0, and empty items count as 0. An unanswered survey score lowers the average and becomes the minimum. Remove empty items first with Filter and Not Null.

Transform and filter

Projection

Projection(list, selector, elementName)Returns List of the selector's type

Builds a new list by calculating a value for each item.

Parameter	Type	Required	Description
Collection	List of any type	Yes	The list to go through.
Selector	Any	Yes	The value to calculate for each item, using name.Element and name.Index.
Element Name	Text	Yes	The name for the current item.

Example: Projection(Order.Lines, line, line.Element.quantity * line.Element.priceperunit) gives the amount of each order line, ready for Collection Sum.

Filter

Filter(list, condition, elementName)Returns List of the same type

Keeps only the items for which a condition is Yes.

Parameter	Type	Required	Description
Collection	List of any type	Yes	The list to filter.
Filter	Yes/No	Yes	The condition each item must meet. An empty result counts as No.
Element Name	Text	Yes	The name for the current item.

Example: Filter(Account.Contacts, c, Not Null Or Empty String(c.emailaddress1)) keeps only contacts with an email address, for a mailing.

Pick items

First Item

First(list)Returns One item of the list's type

Returns the first item of a list.

Parameter	Type	Required	Description
List	List of any type	Yes	The list.

• An empty or missing list gives empty, also for lists of numbers (not 0).

Example: First(SortBy(Case.Activities, a, a.Element.createdon, true)) gives the most recent activity on a case.

Last Item

Last(list)Returns One item of the list's type

Returns the last item of a list. Same parameter and rules as First Item.

Example: Last(Split(fullname, " ")) gives the last word of a name.

Item at Position

Item(list, position)Returns One item of the list's type

Returns the item at a position.

Parameter	Type	Required	Description
List	List of any type	Yes	The list.
Position (1 = first, -1 = last)	Whole Number	Yes	1 is the first item, 2 the second. Negative numbers count from the end: -1 is the last item, -2 the second-to-last.

• Positions start at 1 here, unlike text positions, which start at 0.
• Position 0, or a position outside the list, gives empty.

Example: Item(Split(address1_line1, ","), 2) gives the second part of a comma-separated address line.

First Items

Take(list, count)Returns List of the same type

Returns the first items of a list.

Parameter	Type	Required	Description
List	List of any type	Yes	The list.
Number of items	Whole Number	Yes	How many items to keep. A negative number counts as 0. More than the list holds returns the whole list.

Example: Take(Sort(Scores, true), 3) gives the top three scores.

Skip Items

Skip(list, count)Returns List of the same type

Returns the list without its first items.

Parameter	Type	Required	Description
List	List of any type	Yes	The list.
Number of items	Whole Number	Yes	How many items to skip. A negative number counts as 0. More than the list holds returns an empty list.

Example: Join(Skip(Split(fullname, " "), 1), " ") drops the first name and keeps the rest.

Order

Reverse List

Reverse(list)Returns List of the same type

Returns the list in reverse order.

Parameter	Type	Required	Description
List	List of any type	Yes	The list.

Example: Reverse(Split(Payload.approvers, ";")) processes a list of approvers received from another system in the opposite order.

Sort

Sort(list, descending)Returns List of the same type

Sorts a list of numbers, dates or text.

Parameter	Type	Required	Description
List	List of numbers, dates or text	Yes	The list to sort.
Descending	Yes/No	No	Yes sorts from largest to smallest. Defaults to No.

• Text is sorted ignoring case.
• Empty items come first when sorting ascending and last when descending. Items that compare equal keep their order.
• Lists of records cannot be sorted with Sort; the editor tells you to use Sort By.

Example: Sort(Split(new_tags, ",")) puts a comma-separated list of tags in alphabetical order.

Sort By

SortBy(list, elementName, key, descending)Returns List of the same type

Sorts a list, usually of records, by a value of each item.

Parameter	Type	Required	Description
List	List of any type	Yes	The list to sort.
Sort by (a value of each item)	Number, date or text	Yes	The value to sort on, such as o.Element.estimatedvalue.
Element Name	Text	Yes	The name for the current item.
Descending	Yes/No	No	Yes sorts from largest to smallest.

• Items with equal values keep their original order.
• Text values are sorted ignoring case.

Example: First(SortBy(Account.Opportunities, o, o.Element.estimatedvalue, true)) gives an account’s largest opportunity.

Change the items

Remove Duplicates

Distinct(list)Returns List of the same type

Removes repeated items and keeps the first occurrence of each.

Parameter	Type	Required	Description
List	List of any type	Yes	The list.

• Text is compared ignoring case, so “Billing” and “billing” count as the same.
• Records count as the same when they are the same row of the same table. New records that have not been saved yet are never duplicates.

Example: Distinct(Projection(Case.Contacts, c, c.Element.parentcustomerid)) lists each customer involved in a case once.

Add Items

Union(list, itemsToAdd)Returns List of the same type

Adds items to a list, without creating duplicates.

Parameter	Type	Required	Description
List	List of any type	Yes	The starting list.
Items to add (a list or one item)	A list or one item of the same type	Yes	What to add. An empty item adds nothing.

• The result has no duplicates and keeps the order of first appearance.
• Its main use is adding options to a multi-select choice column without losing the options already selected.

Example: add the Golf option to a contact’s Interests (a multi-select choice) by picking Add Items with new_interests as the list and a Choice constant Golf as the item to add. If Golf is already selected, nothing changes.

Remove Items

Except(list, itemsToRemove)Returns List of the same type

Removes items from a list.

Parameter	Type	Required	Description
List	List of any type	Yes	The starting list.
Items to remove (a list or one item)	A list or one item of the same type	Yes	What to remove.

• The result also has no duplicates.
• Its main use is removing options from a multi-select choice column.

Example: when a contact unsubscribes from newsletters, use Remove Items to take the Newsletter option out of new_interests and keep every other interest.

Related

• Data types
• Empty values
• Logic flow variables