Formula - Getting the first item in 'Rank' order

So, I have an entity “Task”, and it has sub Tasks. Sub Tasks can be marked completed or not, of course.

The Sub Tasks are drag-n-dropped into the order that makes sense for completion, and some may be completed along the way… so for example, a Task that is in progress could look like:

Task: Get Groceries
SubTask: Drive to the store [DONE]
SubTask: Put groceries in cart [not done]
SubTask: Pay for groceries [not done]

I’m trying to create a formula on the parent Task that returns the “first” non-completed task. This is what I have now:

  .Filter(State.Name != "Done")
  .Sort([Creation Date])

The problem is the .Sort([Creation Date]), because that’s not really what I want (but of course you HAVE to Sort before a First). Sorting on Creation Date works if the tasks were always entered chronologically in the right order, but as soon as I remember a step I forgot, add that new step, and drag it into its correct spot, Creation Date is now suddenly not the correct Sort order.

I know there’s not a way to Filter / Sort based on the Entity’s position in a view, but what about a collection. Since I’m accessing the specific Parent Task, is there no way to grab the first Sub Task Entity in the order shown in that Parent’s Entity view?

If you leave the sort parameter empty, it will sort on rank, which is the positional order.
e.g. Sort().First()

Thanks @Chr1sG ! I’d considered this (and even tried it), but what I was not realizing was that if my Entity view of the SubTasks had a sort applied, Sort().First() ignores that. Which makes sense, now that I think about it, but it took me a bit of time to realize what was happening.

Thanks for the fast responses as always, Chris!

It shouldn’t work as you have described (assuming I have understood).
If your formula uses Sort().First() then the application of a sort on the relation field view should have no impact on the formula result.

That’s correct, that was the behavior I was seeing as well (I just did a poor job of explaining it).

1 Like

OK, my bad. I misunderstood what you meant.