Announcement

Collapse
No announcement yet.

Calculate SUM value in Opportunities

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Calculate SUM value in Opportunities

    Hello there,

    New to EspoCRM and i have a quick question:

    I want to add a new field inside the Contacts page where it will calculate the SUM of all the Won Opportunities a client have. Any idea how to proceed?

    PS: Also any idea how to delete completed tasks automatically after X days?

  • #2
    Hi khairfactory,

    I want to add a new field inside the Contacts page where it will calculate the SUM of all the Won Opportunities a client have.
    1. Make a Currency type field via Entity Manager (e.g., "sum"), add it to desired layouts.
    2. Next, create a simple List type Report for the Contact entity with the Name column.
    3. Create a Workflow rule with the Contact target entity and Scheduled trigger.
    4. Select report created before, specify scheduling frequency (e.g., */5 * * * * (every 5 minutes)).
    5. Add Execute Formula Script action with the following formula:
    Code:
    sum = entity\sumRelated('opportunities', 'amount', 'won');
    Click image for larger version  Name:	2021-12-22_16-29.png Views:	0 Size:	35.7 KB ID:	77703
    Last edited by Vadym; 12-22-2021, 02:38 PM.

    Comment


    • #3
      khairfactory,

      PS: Also any idea how to delete completed tasks automatically after X days?
      The first way:

      1. Make a simple List type Report for the Task entity with the Name column.
      2. Create a Workflow rule with the Task target entity and Scheduled trigger.
      3. Select report created before, specify scheduling frequency (e.g., 30 1 * * * (at 01:30 once a day)).
      4. Add Execute Formula Script action with the following formula:
      Code:
      $createdAt = datetime\format(createdAt, 'Europe/Amsterdam' ,'YYYY-MM-DD');
      
      ifThen (
      datetime\today() == datetime\addDays($createdAt, 7) && status == 'Completed',
      deleted = true
      );
      Click image for larger version  Name:	2021-12-22_16-42.png Views:	0 Size:	48.9 KB ID:	77713


      The second way:

      1. Make a List type Report for the Task entity with the Name column and the following Filters:
      • Field -> Status -> Any Of -> Completed
      • Field -> Created At -> Older Than X Days -> 6
      2. Create a Workflow rule with the Task target entity and Scheduled trigger.
      3. Select report created before, specify scheduling frequency (e.g., 30 1 * * * (at 01:30 once a day)).
      4. Add Execute Formula Script action with the simple formula:
      Code:
      deleted = true;
      Click image for larger version  Name:	2021-12-22_17-08.png Views:	0 Size:	39.2 KB ID:	77709Click image for larger version  Name:	2021-12-22_16-58.png Views:	0 Size:	10.6 KB ID:	77711

      More information about Workflows, Reports, and Formula:
      https://docs.espocrm.com/administrat...ows/#workflows
      https://docs.espocrm.com/user-guide/reports/#reports

      https://docs.espocrm.com/administrat...culated-fields
      Last edited by Vadym; 12-22-2021, 03:19 PM.

      Comment


      • #4
        Is there no way of doing basic "Master-Detail" summation without the reports extension?
        Given "sumRelated" and other formulas available in Espo, it's unfortunate and quite lacking that additional pieces are required to make something this basic, actually work.
        Last edited by Mark G.; 12-22-2021, 10:47 PM.

        Comment


        • #5
          Hi Mark G.

          In Entity Manager, it's possible to define script (formula) for a specific entity type. This script will be executed every time before the record is saved.
          To edit formula for a specific entity type, follow Administration > Entity Manager > dropdown menu on the right on the row of the entity type > Formula.
          The admin can run Recalculate Formula action for specific records from the list view: select records (or all search results) > click Actions dropdown > click Recalculate Formula.


          Code:
          sum = entity\sumRelated('opportunities', 'amount', 'won');

          Comment


          • #6
            Vadym ,

            Any workarond if i don't have workflow, to, calculate" sum"
            I tried the formula without help

            Comment


            • #7
              stayfi

              The method described above is for users who do not have an Advanced Pack extension. (post #5)
              Just create a Currency type field (e.g., "sum") via Entity Manager, add it to the Detail view and paste the Formula (Administration -> Entity Manager -> Contact -> Formula):
              Code:
              sum = entity\sumRelated('opportunities', 'amount', 'won');
              Last edited by Vadym; 12-23-2021, 02:37 PM.

              Comment


              • #8
                Thanks, Vadym!

                It is unfortunate that there is not some kind of "update" command in the Espo scripting language, that would update the calculation and re-display it in real time ... aside from when saving the record.

                e.g.

                sum = entity\sumRelated('opportunities', 'amount', 'won');
                update;
                Last edited by Mark G.; 12-24-2021, 11:17 AM.

                Comment


                • telecastg
                  telecastg commented
                  Editing a comment
                  It is unfortunate that there is not some kind of "update" command in the Espo scripting language, that would update the calculation and re-display it in real time ... aside from when saving the record.
                  This updated commercial extension will allow you to add a "Recalculate Formula" button to any entity in Detail view without having to write any code in Espo 7. https://payhip.com/b/28Dhw
                  Last edited by telecastg; 12-31-2021, 08:29 PM.

              • #9
                Vadym -
                Mark G.

                Hey Vadym tried this: sum = entity\sumRelated('opportunities', 'amount', 'won'); without success,

                i want to achieve this, please test it on your end, and tell me if the Sum works,

                you have a one Contact Has Many Cleaning relationship set, every time a contact visit a new cleaning entity should be created with the two fields (amount and payedAmount). i think best way is to add 3 fields in contact like below:

                - totalCleaningAmount
                - totalPaidAmount
                - totalBalance

                When a new cleaning entity is created for the given contact, you can add formula on contact to calculated total amounts of related cleaning, see formula below:

                totalCleaningAmount = entity\sumRelated('cleanings', 'amount');
                totalPaidAmount = entity\sumRelated('cleanings', 'paidAmount');
                totalBalance = totalPaidAmount - totalCleaningAmount;

                Comment


                • #10
                  Hi stayfi

                  Tell me, please, how did you use this Formula? Provide a screenshot.
                  Did you create a Currency type "sum" field and add it to the layout?

                  Comment


                  • #11
                    Vadym

                    this is the formula: totalCleaningAmount = entity\sumRelated('cleanings', 'amount');

                    also totalCleaningAmount is set to read only
                    Last edited by stayfi; 12-28-2021, 01:34 PM.

                    Comment


                    • #12
                      I'm having the same issue ... I can make calculated fields work within a single/child entity record ... e.g. item quantity x item price = line item total ... but not summations (integers or currency) updating to the parent entity record.

                      Comment


                      • #13
                        Vadym Maybe vadym will try it on espo 7 and report

                        Comment


                        • #14
                          Hi!

                          entity\sumRelated(LINK, FIELD, [FILTER]) function is supported from 5.3.0 version to the last EspoCRM release.

                          For it to work, there must be a One-to-Many or Many-to-Many relationship between entities.
                          I hope the screenshots bring a little clarity.
                          Attached Files
                          Last edited by Vadym; 12-30-2021, 11:06 AM.

                          Comment


                          • #15
                            Vadym

                            vadym. i want the results to be shown in the opportunities, in my case in contacts, can you try with this example?

                            You have a one Contact Has Many Cleanings relationship set, every time a contact visit, a new cleaning entity should be created with field (amount)

                            I want - total amount to be shown in the contact page




                            Last edited by stayfi; 12-31-2021, 10:41 AM.

                            Comment

                            Working...
                            X