I have a formula in a search box that filters a table. The table has 435 rows, and sometimes the filter returns too many results. for example a short string may give 200 results.
how can i add a second filter/modify a filter to just show me the first 10 results? i cant get slice() to work here, its always gives me all 435 results.
i basically want it to count the results and then only show me the first 10 results and ignore the rest
Basically this:
Add a checkbox somewhere to toggle the “Show only 10 first results” on or off.
Add a column Filtered Position to your filtered table with a following formula:
If(
[Show only 10 first results],
YourViewName.Find(thisRow),
""
)
This formula will ensure that when the checkbox is on, the table will calculate positions of each row within the view. For items not in the view it will calculate -1 but we don’t care about those. And when the checkbox is off, the column doesn’t run expensive calculations.
Add the following to your filter:
[Show only 10 first results] != true
OR thisRow.[Filtered Position] > 0 AND thisRow.[Filtered Position] <= 10
This filter tells the system that we need to either: let all rows through if the checkbox is unchecked (first part of the expression that will return true for all rows), OTHERWISE return only those rows that have Position in that column calculated as 1 to 10.
Hey there!
I haven’t fully read through @Paul_Danyliuk 's post, but you could also try this method
Essentially the formula is:
thisRow.in(thisTable.Filter(Name.Lower().StartsWith([Search Source Table Name])).Slice(1,3))
Heres the doc where its working, doesn’t require any extra columns. You would just have to modify the “startswith()” formula that I used to something more fitting.
Then if you want to conditionally change the amount of numbers show, you could use a slider and reference the slider in your formula
Reading through, Pauls solution is cooler than mine. Go with his
@Paul_Danyliuk
thanks, this does do the trick but it seems like i have to toggle the limit10 to show results. after one toggle it works to limit/notlimit… but then if i search for a new item it shows no results until i give it a toggle again.’
edit: it looks like its because the filtercolumn doesnt refresh automatically on search change, but a toggle refreshes it and makes it work. unfortunately this means we can leave that toggles as default because someone needs to activate it. I guess an automation to toggle might be needed? seems like a whole lot of work to limit results tho
Usually the problem is dependency on a filtered view that’s already filtered. The chicken-egg problem basically. The only fix there would be to essentially duplicate what you have in your view filter into that Find formula.
I.e. MyView.Find(thisRow)
becomes MyBaseTable.Filter(all the same conditions).Find(thisRow)
I remember Hannah (one of our Codans) using this hack as well: Using Slice to limit the rows in a view (return Top n rows). This always trips me up, so you’re not alone here haha.
The trick is in how you use thisRow for your comparison and the In() formula.
I tried but couldn’t get it to work. Will try again when I get home !
Here’s the demo. I ended up moving the filter expression into a checkbox column Matches search to reuse it in index calculation
I copied and pasted and edited for my table @Scott_Collier-Weir but the search wont yield any results with Contains()… it works with StartsWith()… but it seems that contains() breaks it 
thank you for the efforts
Hey there! Any chance you can send over your doc? Or at least a screenshot of the formula?
Happy to trouble shoot! But always easier to do with context
this does work for me thank you
sure let me re-add it back in there
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.