# Formula help: How to index one entity in a collection? **URL:** \ **Category:** Get Help\ **Created:** [July 19, 2021, 3:53pm UTC](https://community.fibery.io/t/formula-help-how-to-index-one-entity-in-a-collection/1817 "2021-07-19T15:53:45Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\ **Post date:** [July 19, 2021, 3:53pm UTC](https://community.fibery.io/t/formula-help-how-to-index-one-entity-in-a-collection/1817/1 "2021-07-19T15:53:46Z")
In a formula, how can I reference a specific entity (by index) in a collection, so I can use its fields in the formula? ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [July 19, 2021, 4:26pm UTC](https://community.fibery.io/t/formula-help-how-to-index-one-entity-in-a-collection/1817/2 "2021-07-19T16:26:08Z")
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. ---
**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\ **Post date:** [July 19, 2021, 4:39pm UTC](https://community.fibery.io/t/formula-help-how-to-index-one-entity-in-a-collection/1817/3 "2021-07-19T16:39:29Z")
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”). ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [July 19, 2021, 5:03pm UTC](https://community.fibery.io/t/formula-help-how-to-index-one-entity-in-a-collection/1817/4 "2021-07-19T17:03:20Z")
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. ---
**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\ **Post date:** [July 19, 2021, 7:11pm UTC](https://community.fibery.io/t/formula-help-how-to-index-one-entity-in-a-collection/1817/5 "2021-07-19T19:11:47Z")
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. ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [July 19, 2021, 7:30pm UTC](https://community.fibery.io/t/formula-help-how-to-index-one-entity-in-a-collection/1817/6 "2021-07-19T19:30:51Z")
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. ---
**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\ **Post date:** [July 19, 2021, 7:43pm UTC](https://community.fibery.io/t/formula-help-how-to-index-one-entity-in-a-collection/1817/7 "2021-07-19T19:43:42Z")
It sounds like the short answer is “no, there is no way to retrieve a single entity from a collection in a formula”. ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [July 19, 2021, 7:47pm UTC](https://community.fibery.io/t/formula-help-how-to-index-one-entity-in-a-collection/1817/8 "2021-07-19T19:47:47Z")
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. ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [July 19, 2021, 7:54pm UTC](https://community.fibery.io/t/formula-help-how-to-index-one-entity-in-a-collection/1817/9 "2021-07-19T19:54:30Z")
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). ---
**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\ **Post date:** [July 19, 2021, 8:39pm UTC](https://community.fibery.io/t/formula-help-how-to-index-one-entity-in-a-collection/1817/10 "2021-07-19T20:39:26Z")
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. ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [July 19, 2021, 8:54pm UTC](https://community.fibery.io/t/formula-help-how-to-index-one-entity-in-a-collection/1817/11 "2021-07-19T20:54:07Z")
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. ---
**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\ **Post date:** [July 19, 2021, 10:01pm UTC](https://community.fibery.io/t/formula-help-how-to-index-one-entity-in-a-collection/1817/12 "2021-07-19T22:01:48Z")
So if I didn’t care _which_ entity I got, this monstrosity would work: `ReplaceRegex( Children.Join( [Text field], "&💩&" ), "&💩&.*", "" )` ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [July 19, 2021, 10:23pm UTC](https://community.fibery.io/t/formula-help-how-to-index-one-entity-in-a-collection/1817/13 "2021-07-19T22:23:41Z")
Haha, awesome lateral thinking 👏 ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [September 6, 2024, 12:51pm UTC](https://community.fibery.io/t/formula-help-how-to-index-one-entity-in-a-collection/1817/14 "2024-09-06T12:51:31Z")
See here: > [@Formula to assign number based on sorting](https://community.fibery.io/t/formula-to-assign-number-based-on-sorting/3652/5): > > I don’t know why it took me so long to figure out a more elegant solution to this, but here is an example of a formula that will tell you the relative position of a Task entity amongst all the Tasks related to a common parent Project: (Find(Project.Tasks.Sort([Start Date]).Join("#" + Right("000" + [Public Id], 4), ""),"#" + Right("000" + [Public Id], 4)) + 4) / 5 In this example, the position is given for items based on their start date, but it can equally be used to get the position of items… 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 ☹