Calculated fields and live formula and showing one to many related entities

[devespo]

Is there a way to achieve following:

1. Have formula work not on save, but on retrieval of the entity. For example, If I have date of birth for a lead, and I want to show age based on that, the formula would calculate the age and store it on save, but if I do not re-save the entity lets say in a year, the age won't change. Is there a way to have a column be calculated "live" or as it is being retrieved, not on just save?

2. Let's say I have one to many relationship of contact and opportunities and opportunities have a monetary value. How would I be able to sum all the opportunities monetary value and display with the contact?

3. I created a custom entity "Test". There is a Contact -oneToMany- Test relationship. I am however not able to display linked Test entities in the contact details page, it does not appear in the fields when I try to modify the contact detail layout. How can I show related/linked entities in contact?

Thanks!

[Maximus]

Hello,
1. Formula afects record beforeSave only. Make calculation dinamicaly you can by utilizing scheduled workflow wiht a report.
2. I think you should use this https://github.com/espocrm/documenta...titysumrelated.
3. Administration -> Entity Manager -> Contact/Test relation edit -> Check the 'Link Multiple Field' box. Now this field is available for the detail view.

[devespo]

thank you for the reply Maximus. Can reports/report widgets be added to detail view of the entity?

[Maximus]

> Can reports/report widgets be added to detail view of the entity?
It is not supported.

[devespo]

Make calculation dinamicaly you can by utilizing scheduled workflow wiht a report

I have purchased the advanced pack. How can I do what you suggested above?

[Maximus]

1. Create a list report for the Lead entity, and make filter to check if 'dateOfBirth' field is not empty.
2. Create a scheduled report for the Lead entity. Link created report (see attachment).
Code to calculate the 'age' field:

age = (datetime\year(datetime\today()) - datetime\year(dateOfBirth)) - 1;

ifThen(
    datetime\month(datetime\today()) > datetime\month(dateOfBirth),
    age = (datetime\year(datetime\today()) - datetime\year(dateOfBirth))
);

ifThen(
    datetime\month(datetime\today()) == datetime\month(dateOfBirth) &&
    datetime\date(datetime\today()) >= datetime\date(dateOfBirth),
    age = (datetime\year(datetime\today()) - datetime\year(dateOfBirth))
);