Formula help: How to index one entity in a collection?

Get Help
1

In a formula, how can I reference a specific entity (by index) in a collection, so I can use its fields in the formula?

2

Which index do you mean?
The Public Id?

Try this:
[Collection name].filter([Public Id]="1")

To get basic field values, you can either append .Join([Field name],"") for strings or .Avg([Field name]) for numbers.
For relationship fields, you can create a further Lookup field on the formula.

3

I have a collection that normally contains only a single entity, but could contain more than one. I’d like to create a Formula that takes values from the first entity in the collection.

I’m not trying to select an entity based on its field values – just to select the first one in the collection (so “index = 0”).

1 Like
4

Well, I guess the problem is that there are multiple possible definitions of the ‘first’ entity in the collection.
Does it mean first created, first alphabetically, most recently modified or some other sort order?
Once you define that, then the formula should be fairly easy.

5

If there was such an “indexing” operator, I would hope you could apply it to any collection expression, including following a sort or filter operation.

6

Well, formulas don’t support a sort operation.
And after applying a filter to a collection, you are still left with the question of what defines the ‘first’ entity amongst the filtered entities.

It’s true that there isn’t anything equivalent to an array index, e.g. Collection[0] but I would argue that such a feature wouldn’t be much use unless the array is known to have been populated in a particular order (or re-sorted). Otherwise, you might as well just return any random entity from the collection.

7

It sounds like the short answer is “no, there is no way to retrieve a single entity from a collection in a formula”.

8

Maybe I’m sounding difficult, I don’t mean to, but it totally is possible, you just need to define what criterion you want to order the entities by and then you can define formula(s) to extract the highest/lowest as necessary.
Alternatively, if you have no preferred criterion then you could e.g. order by creation date.

9

Here’s an example of how you could do it with two types (Parent and Child) which are related together.

Create a formula in the Parent type called MaxDate
Children.Max([Creation Date])

Then create a lookup in the Child type called ParentMaxDate which gets this field from the parent.

Then create a second formula in the Parent type called OnlyOneChild
Children.Filter([Creation Date] = [ParentMaxDate])

In this way, you will return a single child (which will always be the most recently created).
If you wanted the oldest child, you can just switch .Max for .Min

Alternatively, maybe sometimes it would be preferable to choose Modification Date instead, or some other property (as long as it can never return two entities with the same value).

1 Like
10

Won’t that filter still return a collection (with one result)?

If so, it doesn’t solve the problem, which is how to get the field values of just one entity in a collection.

11

Well, depending on which field value you want, you can just add on an extra operator, e.g.

Children.Filter([Creation Date] = [ParentMaxDate]).Join([Text field],"")
or
Children.Filter([Creation Date] = [ParentMaxDate]).Avg([Numeric field])

These operators are designed to work on collections, but work just fine on a collection of only one entity.

1 Like
12

So if I didn’t care which entity I got, this monstrosity would work:

ReplaceRegex( Children.Join( [Text field], "&đŸ’©&" ), "&đŸ’©&.*", "" )

13

Haha, awesome lateral thinking :clap: