Formula to assign number based on sorting

Ideas & Features
1

Hi everyone :slight_smile: Not sure if this is a help request or a formula function request:

Is there an elegant way to number up entities in a formula field based on a sorting?

If this is not possible, I’d like to request a position() function, which returns the position of an entity within a collection based on a sorting.

E.g.:
Projects can have tasks assigned, each of which have a start date.
If a project has 4 tasks assigned, I would want an auxiliary (formula) field showing 1-4, depending on whether it’s the first task (based on start date) and so on.

For example, with the requested position() function, this simple formula would return what I want:

[assigned to project].[assigned tasks].sort([start date]).position(task)

It would check for each task to which project it is assigned to, then sort all assigned tasks of said project and return the position of the task.

Use case:
Contracts can have amendments, which are also contracts. The field “amendment of” indicates whether a contract is an amendment, as it will not be empty in that case.

Now my question is, if a contract IS an amendment, I would like to have an auxiliary integer formula field showing the “rank”, or the “how manyth amendment is this” (based on start date).
I use this to create unique IDs (also auxiliary field) which then goes into the name of every contract.

My workaround is nesting ifs comparing the name the contract with the name of the first/last contract within sorted collection (with first() or last()), and return 1 if it matches first, 3 if it matches last, 2 if neither. This works as long as no contract has more than 3 amendments.

This leads me to another missing function: the return of an entity of a collection which is not first or last.

Maybe the first() and last() functions could have a parameter, such that if you put in e.g. first(3), it returns the third entity and if you put in last(3) the third-last entity? Basically meaning first() = first(1) and last() = last(1)

2

It is possible with some clever relations and automations to ‘sort’ items in a collection, in such a way as to enable formulas like you are describing to work.

For example, you can relate tasks to each other (one-to-one) as ‘previous’ and ‘next’.
An automation can run each time a task is created (or its start date is updated) to find the tasks that come immediately before and after.
In such a way, tasks can get linked in a daisy-chain. Then it is fairly trivial to write a formula field to get the task’s position in the chain.

There are a couple of potential gotchas to be aware of though - you may need to also write automations to cover the case where a task is deleted from the middle of the chain, to ensure that the loose ends of the broken chain are connected together.

If you’re interested, I could rustle up a template to show you how it’s done.

3

Have a look at this template:
'Sequencing — Fibery

4

Thank you Chris, indeed something like that would work.

Albeit not liking the idea of having to “clutter” the workspace, I might go for that route.

Moving this threat to feature requests.

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 based on manual sorting order, e.g.

(Find(Project.Tasks.Sort().Join("#" + Right("000" + [Public Id], 4), ""),"#" + Right("000" + [Public Id], 4)) + 4) / 5

It will work provided there are no entities with a public Id value greater than 9999, but could be adjusted to increase that limit.

1 Like