Using Slice to limit the rows in a view (return Top n rows)

[Dan_Rose]

Want the first N rows that match a query? Use Slice in your filter:

First 5 tasks with highest priority: thisRow.in([Todo List].Sort(false,Priority).Slice(0,5))

[jakobheuser]

Great tip! This is especially awesome when using BulletedList() on the canvas so that you can show a summary in list format.

[Lane_Greer]

I’m not having the same luck. Based on the above I would expect that just adding Table.Slice(0,3) to the table filter would just give me the top 3 rows of the table. When I do that, that’s not what I’m seeing. I’m actually seeing that it doesn’t do anything.

[Maria_Korolov]

I’m having problems getting this to work as well. Do you put this in the main filter for the table? It doesn’t seem to have any effect at all.

Thanks!
– Maria

[shishir]

Here’s an example with a walkthrough of the key steps:

[Maria_Korolov]

Thanks so much! That totally helped.
Makes it easy to sort and filter a row, then just pull out the first X rows of it. Nice!

Thanks,
Maria

[Johg_Ananda]

@shishir Hey this works great but the performance tool indicates its expensive. Any other way to do it without slowing down?

[Jason_Tamulonis]

Hi @Johg_Ananda, Mind sharing what you are seeing in the performance tool and the exact formula? Thanks!

[Johg_Ananda]

I don’t know an easy way to demonstrate this, to get the performance load issue you need a big doc and there isn’t an easy way to randomize or share mine. I am getting this impression because when I used this approach I saw in the performance tool the table filter show up as a resource drain. It was remarkable because I’ve never seen a view filter show up, let alone significantly.

It’s gone but next time I see it I’ll take a gif or something @Jason_Tamulonis.

[Tiago_Melo]

Hello. It works for me but there’s something extra I wanted to do.
I have a task table with some columns, but the ones that matter for this are Urgency(returns a float falue) and Status(Returns a pre-defined string such as ‘to-do, waiting, done, cancelled…’).
Very well, I want to apply the slice in Urgency and sort it out by the highest value. Here’s the fórmula:

thisRow.In(thisTable.Sort(false(),thisTable.Urgency).Slice(1,3))

It works fine, but it brings all the tasks with all Statuses and I want to exclude some status from the result. However, if I try a different filter, it’ll apply over the previous one that is sliced already. For instance, let’s say that I have 5 tasks:

Task - Status - Urgency
Task 5 (Next)(12)
Task 7 (Cancelled)(13)
Task 13 (To do)(15)
Task 2 (Waiting)(20)
Task 3 (To do) 17

The formula would return the tasks in this order: Task 2, Task 3 and Task 13, and that’s fine. But if I add another filter (let’s say that I want to filter out the status Waiting), then the two combined would return only Task 3 and Task 13. That’s kind of obvious, but what I want to do is filter statuses out and still receiving 3 values in a way that it would return the tasks 3, 13 and 7.

One way to do it (but that I cannot figure out how to apply a formula like this) would be having a formula that:

1. Sorts Urgency from highest number to low;
2. Filters out some statuses that I do not with to be shown;
3. Slice the result of the sort and the filter

Looks easy, but how to implement this in Coda?

Thanks in advance

[Mario]

@Dan_Rose @shishir thank you very much for those ideas, i’ve had the problem of limiting results from filter for at least the past year, game changer!

@Tiago_Melo i’ve figured out that the best way (in terms of practicality and not speed or performance) is to filter using an intermediate table, what i mean is that you filter and sort the original table, then you just use the Danyliuk Method (thanks to the legendary @Paul_Danyliuk) and in a new column in a new table you write [yourtable], this will create a link to your existing, sorted & filtered table, then you simply add “.slice(x,y)” to that formula and you’re done!

MEGA TRICK: Dashboard design — Two columns, smaller charts

Here’s a mega trick how you can implement a gorgeous dashboard in Coda: [ezgif.com-optimize (57)] Create a table with a single row. Pull in all the data you need into columns. Each dashboard element (a chart, a table etc) — a separate column. By pull I mean either import the whole table or some filtered subset: [image] Make a Details view out of that table. Go to Customize Layout, and select Quick Style 3: [image] Quick style 3 has one big advantage: you can have two equally wide c…

P.s. that’s actually what i’ve just done, it works flawlessy
Let me know if that help!

[Mary-Ann_Horley]

I have been trying to achieve something similar (I want my next 5 tasks) but I want to filter out those that are complete and if I adapt @Dan_Rose’s formula

thisRow.in([Todo List].Sort(false,Priority).Slice(0,5))

to sort by the Complete column it just won’t do it. It won’t just do the sort if I remove the Slice() either. Any ideas?

[Johg_Ananda]

@Mary-Ann_Horley this would be easier to help if you had a working doc, but right now it looks like the Sort() is working on the In() whereas you may want the sort to run after the filtering / In().

[Mary-Ann_Horley]

I have mocked up a dummy table and view which is embedded below:

Thanks for your help!

[Mckenna_Coffey]

Apologies, as I know this is quite an old thread (I’m new here, wasn’t entirely sure where to post.) I used this exact formula, but can not get it to work on any table in any of my docs? This is what I put in the filter formula section:

Slice(thisTable,1,10)

and the output is still 80 of 80 rows? What I’m actually trying to do is pull the last ten rows, but as I can’t seem to get this working, I’m a bit confused. If anyone can point out my error, that would be great!

[Christiaan_Huizer]

hi @Mckenna_Coffey

what about thisTable.ColumnName.Slice(1,10)

should give a different result, cheers, Christiaan

[Mckenna_Coffey]

Thanks for the prompt suggestion. Helpful, but still nothing. Says 80 of 80 rows match the filter.

[Christiaan_Huizer]

maybe you can consider to share a doc with the issue, so we can have a look

in this post you see how slice can work : Referencing the cell above - #28 by Christiaan_Huizer

[Alexandru_Dan]

Hi, guys.

I’ve used the formula [thisRow.In(thisTable.Sort(false, thisTable.crITic).Slice(1, 5))] to view the last X rows, but it seems that it doesn’t “collaborate” with another added filter, despite their order.

This is how it looks just with the split filter:

and this is how it looks when I filter by daily/zilnic column.

I’ve also tested it with a checkbox column and the result is the same as in my case (lookup column).

Do you have any idea? Thanks.

[joost_mineur]

Hello @Alexandru_Dan ,
I don’t mind helping you out and I do have a gut feeling about the cause of your problem, but if you don’t show us the daily/Zilnic column, it is kind of hard to give you any advise.
If you share a dummy doc with your setup, but without any private data, you will get the best help, and usually pretty quick too.
Greetings, Joost