Ad hoc formulas in views

When building views I find myself wanting to see derived information - most commonly another field from a relation and an aggregation of fields. For example when looking at Feature requests associated with User I might want to see User.company or User.status (Trial, Paying, etc.) instead. And honestly sometimes I just want to play around to see if connecting some of the information together will deliver an insight.

At the moment I need to add an entity level formula field to derive this information which then “pollutes” the entity. Also - as I understand but haven’t tested it - users without permissions to modify entity schema won’t be allowed to do this at all.

It would be great to be able to define view-level (aka “throw-away”) formula fields when specific information is only required for bespoke view or it’s needed one time only when playing around with your knowledge base.

Similar to: Formulas in Views Filters

I mentioned what I think is the same idea a while back, it’s similar to something Coda can do, and Michael said it should be “easy” to implement, so hopefully it comes around!

Also possibly related (read through the thread, hopefully you’ll see the connection):

3 Likes

It does look related, although the conversation was focused on Blocks, which was a bit confusing to me. Are Blocks intended to replace current Views (Table, List, etc.)? Will Table and List views stay as the basic out of the box components and Blocks are intended to be the more advanced flexible alternative?

I’m not sure I fully understand the use case you’re describing. Are you thinking in reference to a particular type of view (Report, Board, Timeline, Table, Document…)?

Where do you imagine the user information (Company or Status) would be displayed/presented when viewing a particular set of Feature requests?

Or are you talking about being able to filter which Feature requests are shown according to a characteristic that belongs to the User?

I’m thinking about Table or List view (but I think it applies to any view that can display Fields).

For example in Table view it would be displayed as a Table column - in the same way Field is normally displayed. The only difference is that the column would not display Field value directly but instead would be computed on the fly from a Formula.

OK, understood.
So would it be effectively solved if fields could be hidden in entity view?
i.e. you create the formula field you need (in Table view or wherever) and it would be created as a new database field (available in any view) but wouldn’t show up on the entity view UI by default? (or any other view)

For the use case presented here it would be a step forward although I think it’s a bit of a workaround because:

  • over time these ad hoc fields will accumulate and might be tricky to trim them down: “have I created that hidden field or someone else did”, “are those fields used anywhere, which views are using them”
  • users without permissions to edit schema would be still restricted in their ability to experiment
2 Likes

Indeed.
Quick q: Is your wish that the ad hoc fields should be per-user as well as per-view? Or per-view and available for all users to see?

1 Like

In my mind it was per-view and available for all users to see (ie fields are subordinate to view in terms of permission, if a user can see the view they can see the fields).

2 Likes

I am 100% with Pawel here. Ad-hoc fields in Rich Text/Docs (or even Whiteboard? :exploding_head:) would be a huge value-add to me. There are zillions of uses I can think of for them. Coda has this, and there are multiple other systems that implement varying degrees of sophistication in similar tools, e.g. Patera:

Now just imagine the examples on that Patera page, but with also the ability to do formulae that connect to Fields/Entities, etc. :rocket:

2 Likes

That’s not quite what he’s asking for, i believe. He has asked for ad-hoc fields in views (table view, list view etc) which is a subtle difference.

1 Like

Oh! Oh, that’s interesting. In that case I am indeed not sure how it would work. I would think the most likely best way to handle such a need would be via Blocks with embedded Views and an in-line calc function for Rich Text which could reference one of the in-line Views.

FYI below a mockup of roughly what I was imagining. “Insert formula” would display a formula window and then a “local field” to the list of fields. This “local field” would only be available on current view.

To be fair - once I did this mockup I realised the subtle complexities of the user interface :smiley: For instance - how do you differentiate between “entity fields” and “view fields” (which in programming sense are more like “view fields” or perhaps “variables”).

3 Likes

Helpful clarification with the mockup! Indeed many complexities with this, I think. Some obvious, like what you have pointed out, and others that I think would come up only during implementation or even eventual deployment. That said, the core idea is an interesting one and I can see the potential benefit.

So I’ll refer back to the future-viable approach of using the new Blocks and embedding a Table, then putting the calculated fields elsewhere on the page. Assuming there can be e.g. a page-level “Calculations” Block that can reference any Databases/Entities/Fields in the system (or at the very least any that are embedded/linked to from the current page), would this approach work for you?

Perhaps :smiley: Although calculations would need to be able to somehow work with a table format (a’la SQL select) to match this use case. Rough mock up:

1 Like

Some really nice inspirational stuff here. This should definitely feed into our work on blocks (and formulas, transclusions, etc.)

3 Likes

Related: "Joins" - include related entity's fields in Views

Finally stumbled across this. Definitely would love to have this :slightly_smiling_face:

Had posted this duplicate.

I guess I’ve previously indicated that I’m not a big fan of unnecessary (static) Lookup fields because I feel that they clutter my entities unnecessarily.

Hence, I love Formulae and the flexibility and dynamic manipulation possibilities they give. The main place I find them missing at this point are in View Filters.

I’d love to be able to:

  • Display only features with “must have” user stories in a given sprint
  • Tasks that were completed between three weeks and two weeks ago (a little construed but right now I’m relying on the predefined options)
  • Tasks that are in a “final” (or even custom) state

All these things would be possible the moment I can use formulae in Filters. You have most of the pieces already in place and I hope this will be added soon. Just not sure if that’ll impact performance but I would think with GraphQL underneath there should be no impact filtering on the fly?

Thanks!

PS: Not sure, if this is actually a duplicate of this FRQ.

2 Likes

It’s defo a duplicate of Ad hoc formulas in views