[Feature Request] Custom Sort and Filter

[Muneeb_Shere]

While custom sorting and filtering is already possible with Grist (by creating a formula column), I’m finding that that leads to needing to create a lot of duplicate columns with no purpose except for sorting/filtering, so the following is more of a request for a convenience feature.

I believe it would be rather straightforward to add a “Formula” (or “Custom”) option to the “Add Column” drop-down of the current sort-and-filter menu/dialog. The user could then type in a formula that returns a list of ids (for sort) or a dictionary of id → boolean (for filter), and an error could be shown if the formula doesn’t return the expected value.

(Alternatively, it might be even eaiser if the user could simply provide a “compare” function that returns -1, 0, 1 for a pair of records (for sort) or a predicate function that returns True or False for a record (for filter), but that would be a trivial difference for someone who’s already willing to write a Python formula)

It goes without saying that for this feature to be truly useful, it should be possible to pin the custom sort/filter as a button just the way that the columns can be pinned currently. The user could also provide a title for the sort/filter, and the pinned sort/filter, when clicked, could provide options to enable or disable the filter, or edit the formula and/or title.

I’d be interested in working on this myself on the repository, if I could have a little guidance.

[dmitry-grist]

A big difficulty with using formulas for filters is that formulas in Grist require edit access to create or modify, are shared across all users, and have access to all data (because they are evaluated in the data engine, not in the browser). The access point means that documents that limit read access using access rules should normally deny permission to edit formulas. But for filters, it’s desirable to expose full filter functionality even to read-only or limited users.

One way we’ve considered addressing it is by supporting a subset of formula functionality – similar to what access rules do – to features like filters that should work without relying on the data engine. This subset of Python includes basic arithmetic operators, comparisons (like <, !=), parentheses, and logical operators (like and/or/not). (In fact, you can see what’s implemented for access rules here.)

Do you feel this would be sufficient for the use cases you have in mind?

[Muneeb_Shere]

Yeah, that would be useful enough for filter formulas. However, what I was feeling the need for based on my usage was more of the sort formulas, and I feel like this workaround wouldn’t work for those, because they (mostly) would run into the need for full-fledged Python, especially if you wanted them to return a list of ids.

I do see your point though; in order for you to be able to reuse the fomula field that’s present everywhere else in the interface, you need to ensure that it complies with the access rules. As a follow up suggestion, would it be difficult, implementation-wise, to perhaps expose a formula that runs in the data engine as usual, but has no access to the data? Or is the need for complete data access tightly baked into the data engine?

To elaborate a bit more, this is the scenario in my mind:

Suppose a table has columns A, B, C, and D. A particular user (say Viewer 1) has read-only access to particular records, and to fields A, B, and C only, and no edit access. Viewer 1 wants to use a filter/sort formula. The formula they enter is sent to the data engine, together with the data that is locally available (i.e, to Viewer 1). The data engine then evaluates that formula by injecting the data that arrived together with the formula, but without any access to data from the broader document. So, the formula would be able to be evaluated over columns A, B, and C, but not D, and only over the subset of records to which Viewer 1 has access to.

Does this sound like something that can be implemented easily? Or would it be too impractical/too much of a hassle?

[dmitry-grist]

It sounds doable in principle, but definitely not easy because it can’t reuse normal ways Grist evaluates formulas. E.g. it wouldn’t be OK to evaluate arbitrary Python code create by non-trusted users in the normal data engine because it could then get access to all data, and because it could interfere with the data engine in ways that can affect other users. And if evaluated in a separate empty data engine (with only the supplied data), I have doubts it would enable all the functionality you actually need.

Can you give some examples of sort formulas that you have in mind? I am still interested in figuring out how much can be done without involving the data engine.

[Muneeb_Shere]

So actually you asking me about my use-case made me think more about it, and for at least half of them, I managed to come to a solution that doesn’t need the custom sort . Also, it wasn’t a big problem, more of a minor quibble. I was running into problems of this kind:

I have a column that’s supposed to contain a date. However, I wanted the date displayed in a particular format, which I couldn’t find in the list of formats for dates (I wanted the day of week). So I was using strftime() to display it in the required format. The problem was that the column then is of Text type, and if I attempted to sort using that column, it would sort alphabetically rather by date, and I didn’t want to have two separate columns for sorting and display. So I thought if there was a formula sort, I could store the date as text of my required format, but use a formula to parse and sort it as a date.

Anyway, your asking me about it make me think that perhaps the custom format for date could do what I needed, and looking at the docs, I see that the Moment.js syntax for dates is supported, so I guess I can keep that column as Date (for sorting) and still have it displayed the way I want.

There’re still some other cases which are not covered this way, so for example in another column I want to display a date as ‘<date> (<days ago>)’, for which there’s still no way except to use Python string formatting, and then I would have the same problem of the Text column being sorted alphanumerically rather than as a date.

And if evaluated in a separate empty data engine (with only the supplied data), I have doubts it would enable all the functionality you actually need.

Well, I think that would enable enough functionality that can be enabled without extensive workarounds, and certainly it would be enough for my use-case. It makes sense to restrict users to only be able to sort/filter according to data that they can already see. Now, perhaps you could use conditional logic where if a user does have complete access to the date engine as an owner/editor, then the sort/filter formulas work exactly as formulas everywhere else, but if the user isn’t supposed to have access to the data engine, then their formula could be processed, as you say, by a separate empty engine with only the locally supplied data. I guess this way no user would actually be able to tell any difference, which means that this would enable all the functionality that anyone could reasonably need.

Another possibility that occurred to me was using some kind of local Python interpreter (say Pyodide) to evaluate sort/filter formulas rather than the data engine (the way the custom JupyterLite widget works, for example) but that’s probably too big a change to make for too small a feature. Still, I don’t know, maybe it could be worth it by enabling future features that are right now similarly restricted because of how the date engine works, but you would know best regarding that.

[dmitry-grist]

Oh, one thing people may not realize is that it’s entirely possible to sort and filter on hidden columns!

You’d add such sorting & filtering from the “Sort and filter” button above any page widget:

So it’s perfectly reasonable to have a Date column in the underlying data, which isn’t shown in the current view, but still sort the view on that. The view is then free to turn the date into any convenient string for display without affecting the sorting.