Add a formula field

[dufoli]

It is quite a standard feature on many CRM (saleforce, neolane,...).
It can be very flexible and useful for many things.
A formula field store a formula on metadata but nothing is stored in DB.
The formula is based on record fields and is calculated on the fly when api query this field.
potential use case:
with function:

• dateonly(mydate)
• urlized(myTitle)
  -DaysAgo(myDate)
  -If(IsClosed;'Closed', 'Open')
  or just operations:
• TotalWithTaxes = TotalHt * VAT
• Cost = Price + shippingFee
• Surface = width * Height

[Aurelsicoko]

Yes, yes and yes! I've added it to the public roadmap, it could be awesome to have this new type of field available.

https://portal.productboard.com/strapi/c/58-formula-field

Please upvote the card and give me your insights, I close the issue.

[dufoli]

For that, you will need a little language with formula editor and a list of function. You can take look to salesforce editor here:
https://trailhead.salesforce.com/en/content/learn/modules/point_click_business_logic/formula_fields

[mik3lov3st3ch]

Directus uses some kind of "in-field templating" e.g. for the content of a dropdown like:
{{brand.name}} {{title}}

[TeoGoddet]

Could/Should I write an RFC for this ?

Main Tradeoff seems to be about performance and genericity
The more generic possible solution would run on each record in strapi
But a more efficient solution would be to delegate some work to the ORM and DB
if querying 100 of ObjectA.child1.property1, the difference is clear : 1 db request at ORM level but 101 at strapi level

If going on with row by row evaluation :
This calculator look really great : https://github.com/handsontable/formula-parser
It can handle variable name (unfortunately without . and [] inside but that seem a relatively easy change to do) and call a function to resolve variable names (giving us the opportunity to provide the value from the object or do the required queries to find it)
it has a huge list of implemented functions

The other option is to parse the whole formula and try to translate it as much as possible to orm/strapi query language, at least retrieving all the used values a once.

In any case, this repo can be a great inspiration for the jison lexer/parser

Another trade-off is the level of complexity of the allowed excel like code,
should it understand full json path : https://goessner.net/articles/JsonPath/ ?
should it allow traversing relations ?
should it allow same object fields to be used ?

Some point other point of attention:

• validation of user pseudo code input and the error handling
• circular reference detection

Maybe I have some time to wirte it in february.
Tagging @Aurelsicoko as the issue is closed, but seems the better place to discuss it

[derrickmehaffy]

@dufoli please do feel free to write an RFC :) I'm not sure when we will be able to look at it as our "book" is quite full utnil after Q3