# Slice and Sort lookup fields

**URL:** <https://community.fibery.io/t/slice-and-sort-lookup-fields/9564>\
**Category:** Misc\
**Created:** [September 10, 2025, 3:43pm UTC](https://community.fibery.io/t/slice-and-sort-lookup-fields/9564 "2025-09-10T15:43:38Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![helloitse](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/helloitse/32/498_2.png) [@helloitse](https://community.fibery.io/u/helloitse)\
**Post date:** [September 10, 2025, 3:43pm UTC](https://community.fibery.io/t/slice-and-sort-lookup-fields/9564/1 "2025-09-10T15:43:38Z")

</div>

When a formula returns an array of entities, it would be great to be able to sort them and “slice” the data to only show a portion of the data set.

Coda calls this the slice formula and defines it: The **[Slice()](https://coda.io/formulas#Slice)** formula returns part of the provided text or list based on the numerical position. There is another optional input: the position to stop counting

Example Uses:

- Compute the previous 4 meetings from the same meeting type (enum)
- Reveal the last 5 payments per client on each client entity
- Compute the 3 ticket types with the most tickets in the service desk in the monthly report entity.

---

<div class="post-metadata">

**Author:** ![Sev](https://avatars.discourse-cdn.com/v4/letter/s/90db22/32.png) [@Sev](https://community.fibery.io/u/Sev)\
**Post date:** [September 11, 2025, 12:03am UTC](https://community.fibery.io/t/slice-and-sort-lookup-fields/9564/2 "2025-09-11T00:03:37Z")

</div>

Hi. Since **First()** and **Last()** exist, for a very contrived, bad, hacky attempt at this, I tried creating nested formulas that exclude the last/first from inner to outer layers, but unfortunately, I was unable to come up with a single-formula hack, since I got the **error** : `Ouch, we can't calculate Count, Sum, Join, Avg, Min, Max, First or Last inside a Filter. Please create two Formulas instead.`

So, only in very specific and urgent cases (more of a proof of concept that anything else), you can create _n+1_ columns to get the top/bottom-_n_ by any sort, as shown in my example of getting the **top 3 Tasks by Priority in a Projects table**.

#### Formula Column: Task #1

Sorts all Tasks by Priority and takes the last one, which is the **top-priority Task**.

```auto
Tasks.Sort(Priority).Last()

```

#### Formula Column: Task #2

Sorts by Priority but excludes Task #1 by Public Id, then takes the last one, which is the **2nd-priority Task**.

```auto
Tasks.Filter(
	[Public Id] != [This Project].[Task #1].[Public Id]
).Sort(Priority).Last()

```

#### Formula Column: Task #3

Sorts by Priority but excludes Task #1 and Task #2 by Public Id, then takes the last one, which is the **3rd-priority Task**.

```auto
Tasks.Filter(
	[Public Id] != [This Project].[Task #1].[Public Id] and
	[Public Id] != [This Project].[Task #2].[Public Id]
).Sort(Priority)
.Last()

```

#### Formula Column: Top-3 Tasks A-Z

Filters for the **top 3 Tasks** , using Task #1, #2 and #3 by Public Id and sorts by Priority. **See IMPORTANT NOTE below.**

```auto
Tasks.Filter(
	[Public Id] = [This Project].[Task #1].[Public Id] or
	[Public Id] = [This Project].[Task #2].[Public Id] or
	[Public Id] = [This Project].[Task #3].[Public Id]
).Sort(Priority)

```

The sorting is not respected by the collection output, but only for aggregations such as Join(), as discussed in my message quoted below.

> [@Sort and filter on the Lookup field](https://community.fibery.io/t/sort-and-filter-on-the-lookup-field/3788/5):
>
> ❌ The problem is that **the sort is not respected in the field’s UI**. But of course the formula itself does return it sorted, as we can confirm if we return the value as a joined text field by adding `.Join(Contact.Name, ",")`.

---

<div class="post-metadata">

**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 11, 2025, 8:29am UTC](https://community.fibery.io/t/slice-and-sort-lookup-fields/9564/3 "2025-09-11T08:29:11Z")

</div>

You can find the Nth item with the use of a couple of formulas:

ID to find:  
`Middle(Bugs.Sort().Join(Right("000" + [Public Id], 4), "/"), N * 5 - 4, 4)`

Nth bug:  
`Bugs.Filter(Right("000" + [Public Id], 4) = [This Project].[ID to find]).Sort().First()`

You can of course adapt these if you want to find the Nth item based on a different sort order than rank.

I think you can extend this to return a batch of items, by adjusting the ID to find formula so that it returns a string of Ids (with delimiters) and then use a `Find()>0` function in the Nth bug formula