Possibility to recalculate the formulas by formula?

[shalmaxb]

Hi,
I have an entity, wher some related fields do not recalculate, when I make chnages to them, e.g. deleting the related link. There are some fields, that would not empty in that case. When I recalculate the formula in the list view drop-down, it recalculates correctly.
Is there a way to achieve this by formula or in another way?

[Maximus]

Hi there,
It sounds weird. Could you describe it more detailed so I could reproduce it?
1. What EspoCRM version?
2. What entity?
3. Your formula for this entity.
4. What related link (in a Detailed View or in a Relationship Subpanel)?

If you tried to trigger the formula by unlinking some record via the Relationship Sub-panel than it won't work. Formula triggers only if you updating the fields that included into the "fields":{} scope in entityDefs.

[shalmaxb]

Hello,
I have one entity with a value for a price (among others), that I relate by a one-to-one to another entity, where this price field is visible in a foreign field.
The second entity is kind of an invoice entity, that lists by this way from one to five related records from the first entity.
Each linked record from the first entity has the price field in the second entity, where I want to sum these values up to a total price.

For the foreign filed I succeded in updating the related foreign field by formula, when the price is updated in the first entity.

As the sum field is in the second entity I do not succeed.

I hope, this is understandable. I am not very experienced in relationships and do a lot of stuff, by creative trying, what often works, but also maybe a totally wrong approach by means of correct use.

[shalmaxb]

Hi,
thought more about solving my problem. Would it be possible to create a custom button for the detail view, where I could trigger recalculating the formulas? Which would be the command for that button?
Thank you for any hint.

[Maximus]

> For the foreign filed I succeded in updating the related foreign field by formula when the price is updated in the first entity.

The foreign field just allows you to see the value of the related entity field. You don't need to try to recalculate it with formula or try to affect it with some another way.
If you want to Sum all the price values from the all related records, then you need to create an appropriate field (currency or float type), and utilize this formula for SUM action https://docs.espocrm.com/administrat...titysumrelated.

But you need to know that changing the price in the first entity won't trigger the formula for the second entity. For such purpose you need to utilize Workflow for the first entity (e.g. if the entity is changed -> update related record (the second entity) -> run entity\sumRelated formula).
If you don't have Advanced Pack then you can try to write a hook https://docs.espocrm.com/development/hooks/.

[shalmaxb]

Maximus , thank you very much for your hints. I understand, that the related field in the second entity is a mere representation of the related field in the first entity and I know, that I will not be able to change the field in the second entity itself. I only wanted a procedurce, that in case of a changed value in the first entity, it would update the representation in the second entity. This only is executed, when I recalculate the formulas in the second entity (at least in my case here).
As it is a bit complicated and inconvenient for a normal user, to go to the list view, mark the records and click recalculate in the dropdown, I thought it could be possible by a formula to execute this. This apparently is not possible.

As I already asked here in this thread: Is there a possibility to create a custom button to execute the procedure of recalculating? What would be the command?

[Maximus]

This is a short manual of how to add a Formula recalculate button to a detailed view. In my case, I did it for the 'Base' type entity called 'Basic'. So let's start by utilizing suggestion gaves us here https://docs.espocrm.com/development/custom-buttons/:
1. Create a file /client/custom/src/basic-recalculate-formula-handler.js:

define('custom:basic-recalculate-formula-handler', ['action-handler'], function (Dep) {

    return Dep.extend({


        actionRecalculate: function (data, e) {

            var data = this.getActionSelectionPostData();

            Espo.Ajax.postRequest('Basic/' + 'action/massRecalculateFormula', data).then(function (result) {
            }.bind(this));
        },

        initRecalculate: function () {
        },

        controlButtonVisibility: function () {
        },

        getActionSelectionPostData: function () {
            var data = {};
            var ids = false;
            if (this.view.model.id) {
                data.ids = [];
                data.ids.push(this.view.model.id);
            }
            return data;
        },
    });
});

2. Create a file / add to a file /custom/Espo/Custom/Resources/metadata/clientDefs/Basic.json the code:

{
    ...
    "menu": {
        "detail": {
            "buttons": [
                "__APPEND__",
                {
                    "label": "Recalculate Formula",
                    "name": "recalculateFormula",
                    "action": "recalculate",
                    "style": "default",
                    "acl": "read",
                    "aclScope": "Basic",
                    "data": {
                        "handler": "custom:basic-recalculate-formula-handler"
                    },
                    "initFunction": "initRecalculate"
                }
            ]
        }
    },
    ...
}

3. Administration -> Clear Cache
4. Refresh a web page.

Now, when you click the Recalculate Formula it will trigger formula for the target record. The only thing I didn't achieve is how to show changes live. So to see the changes you need to refresh a web page.
Probably telecastg might help you to improve this code in order to be able to view changes 'live'.

[shalmaxb]

Maximus , thank you very much, your help is really appreciated. I will test this for my case and give feedback.

[shalmaxb]

Maximus , the above code works. I looked around a bit about a javascript function to reload the browser page and perhaps this could help: https://www.bitdegree.org/learn/javascript-refresh-page. Only that I do not know much about javascript coding. Would it be possible to integrate the reload function into this script?

[Maximus]

Unfortunately 'location.reload()' won't help here. I tried it. I guess the render() function should do the trick, but it is not available to call in this file. I am not a developer so Java Script code is not friendly to me.
telecastg please take a look at this task. Do you have any tips. Thanks in advance.

[item]

Hello,
look here : https://forum.espocrm.com/forum/deve...3749#post63749

you must try :
self.reRender()
self.model.fetch()

Here in the code of Maximus :

actionRecalculate: function (data, e) { var data = this.getActionSelectionPostData(); Espo.Ajax.postRequest('Basic/' + 'action/massRecalculateFormula', data).then(function (result) {       self.model.fetch();                        }.bind(this)); }, 

I'm not good in backbone :s

[telecastg]

Hello, I would suggest that you try complementing Maximus and item code with the statement this.view.reRender() after the Ajax response to achieve a "reload":

actionRecalculate: function (data, e) {        
    var data = this.getActionSelectionPostData();
    Espo.Ajax.postRequest('Basic/' + 'action/massRecalculateFormula', data).then(function (result) {      
        this.view.reRender();                        
    }.bind(this));
},

Just for information, the reason why we would need to use this.view.reRender() instead of this.reRender() is that the reRender() method is available to the underlying view class but not to the action-handler class.

I am busy and did not have time to test it but it should work. If not try using this.view.model.fetch()

Cheers

[shalmaxb]

Hi,
thank you for all the help, but unfortunately, so far none of them (tried also various combinations) will work.

[Maximus]

item, telecastg,
Thanks for your input. this.view.model.fetch(); works perfectly. Thanks.

[shalmaxb]

Maximus , item, telecastg,
I can confirm, that it works now, amend only this.view.model.fetch(); to the code of Maximus, Post #7.

I am really happy to receive that lot of help in this forum, and it is not the first time.