In a formula, how can I reference a specific entity (by index) in a collection, so I can use its fields in the formula?
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.
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
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.
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.
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.
It sounds like the short answer is âno, there is no way to retrieve a single entity from a collection in a formulaâ.
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.
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
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.
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
So if I didnât care which entity I got, this monstrosity would work:
ReplaceRegex( Children.Join( [Text field], "&đ©&" ), "&đ©&.*", "" )
Haha, awesome lateral thinking 
See here:
A general formula for getting the index of an entity within a collection:
(Find(Collection.Entities.Sort([Parameter to sort on]).Join("#" + Right("000" + [Public Id], 4), ""),"#" + Right("000" + [Public Id], 4)) + 4) / 5
I expect it will slow down the formula service if you have a large collection size though 