Implemented Formula Fields

[eugenio]

I am working on Formula fields. It's basically a method to define calculated fields without writing a PHP calculated field function.

In the form configurator, you will be able to define, for any field, its formula, using placeholders to refer to current table fields or linked table fields.



For example, in a products table, you could define the final_price field as:

( {price} - {discount} ) * 1.22

(where sales taxes are 22%)

In a customers table having id_city as lookup field, you could refer to the current customer's city values in the cities table just by calling their names e.g.

{cities.name_city}

In an invoices_table having as items table an invoice_items table, you can define the total_amount field as:

SUM( {invoice_items.price} * {invoice_items.quantity} )

At the moment, it is possible

• to refer to the current form values
• to refer to a linked record values
• to aggregate records that are in master/detail relationship with the current record, using SUM(), COUNT(), MIN(), MAX(), AVG()
• to use parenthesis
• to use basic math

I am thinking about implementing some additional common operators, such as:

• days_diff(): difference between two dates, in days
• month(), year(): extract month or year from a date
• round()

Before I finalize the release of this feature, I would love to hear from you. Your feedback is important in ensuring that I am including all the necessary operators and functionality that our community needs.

Please share your thoughts on:

• Common operators you frequently use that at the moment are not supported
• Specific use cases where formula fields could replace calculated fields
• Any other features or enhancements you would like to see related to formula fields.

Best,

 

[dolhaps2000]

Wow. Now thats interesting.
This really fit to my project.

 

[Dominik Gamperl]

YES, PLS
THX, BEST PHD EUGENIO EVER

 

[Matthijs]

For me, the functions described are already complete!

 

[Syntax]

This looks great.
Will we also be able to conditional items (with comparison operators or logical operators) in the functions ?

Anyway, your software is already awesome, Eugenio. Thanks for all.

 

[ozcpa]

Hi Eugenio,

An excellent and very useful idea!

It would be nice to be able to perform calculations such as for a {discount} field as follows (as an example):

• IF {quantity} <= 2 THEN {discount} = 0
• IF {quantity} > 2 AND {quantity} <= 10 THEN {discount} = {price} * 0.05
• IF {quantity} > 10 THEN {discount} = {price} * 0.10

Best Regards, Paul

 

[eugenio]

@Syntax and @ozcpa

Thanks!
I think you are referring to the same feature: IF statements at the moment are not available, but yes they would be a good improvement.
I will see how difficult the implementation will be; not easy to implement I think, considering I am trying to map (also for security reasons) all the formula operators to SQL operators.

 

[ozcpa]

Good luck and I am hopeful as it would make an already great system even more fabulous

 

[juergen_mueller]

Hi Eugenio

That's exactly what I missed for one of my projects. Looking forward to using it soon ;-)

 

[luigidusmet]

thanks for the new feature

I suggest to extend "to aggregate records that are in master/detail relationship with the current record, using SUM(), COUNT(), MIN(), MAX(), AVG()" with the possibility of calculating the expected values also on unrelated tables, specifying the talble and search filters of the records to be processed for aggregation

the feature to which I refer is implemented in navision business central, see :

Creating FlowFields and FlowFilters - Business Central

Examples of FlowFields and FlowFilters that are used to display the result of the calculation described in the CalcFormula property.

learn.microsoft.com

CalcFormula property - Business Central

Sets the Calculation formula for a FlowField.

learn.microsoft.com

best regards, luigi

 

[eugenio]

thanks for the new feature

I suggest to extend "to aggregate records that are in master/detail relationship with the current record, using SUM(), COUNT(), MIN(), MAX(), AVG()" with the possibility of calculating the expected values also on unrelated tables, specifying the talble and search filters of the records to be processed for aggregation

the feature to which I refer is implemented in navision business central, see :

Creating FlowFields and FlowFilters - Business Central

Examples of FlowFields and FlowFilters that are used to display the result of the calculation described in the CalcFormula property.

learn.microsoft.com

CalcFormula property - Business Central

Sets the Calculation formula for a FlowField.

learn.microsoft.com

best regards, luigi

Thanks, I will look into that, but if the table is unrelated to the current one, could you provide a user case/example where we need to aggregate records and use the result in a field of the current table?

 

[luigidusmet]

the tipical scenario where it could be usefull a calcolated field on unrelated table :
- table customer (Number, Name, ecc..)
- table "sales invoice" (number, date, customer number, amount of invoice, ecc...)
- calculated field "sales amount" on table customer, calculated as SUM ("Sales Invoice".Amount where "sales amount"."customer no." = customer."No.")

is an alternative to a static accumulate field, that has to be manage on insert, change, delete of invoices

luigi

 

[eugenio]

the tipical scenario where it could be usefull a calcolated field on unrelated table :
- table customer (Number, Name, ecc..)
- table "sales invoice" (number, date, customer number, amount of invoice, ecc...)
- calculated field "sales amount" on table customer, calculated as SUM ("Sales Invoice".Amount where "sales amount"."customer no." = customer."No.")

is an alternative to a static accumulate field, that has to be manage on insert, change, delete of invoices

luigi

Hello Luigi,
this doesn't seem an unrelated table to me. sales_invoice is linked to the table customers through customer_number.
In the example you made, if you attach sales_invoice to customers in master/detail relationship, you can just set as sales_amount formula the following

SUM( {sales_invoice.amount} )

Best,

 

[eugenio]

Implemented in V 12.4, released yesterday.

 

[luigidusmet]

ok, thank you, luigi