Slice and Sort lookup fields

1

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() 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.
2

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.

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.

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.

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.

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.

3

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

1 Like