How to sum up attached quotes

đź’¬ General Discussion
1

Hi,
I have an oportunity that has many quotes (to be honest I will be probably suming up some custom fields from custom module, but lets use quote as an example).
I want to add all the quotes (total field) and put summary (a+b+c…=Sum) in custom field in Opportunity.
I have tried Workflow with Calculate Fields but it will work on one-to-one relationships and one-to-many. I can do some math with this Worflow but I cannot sum all referenced. The number of quote will be variable.

Any ideas?

2

create a non-db field and write a function to loop through all the related quotes and add them up and populate the non-db field.

3

Thx
Where can I hook this function? Why to use of non db field? can it be custom field?

4

It doesn’t have to be a non-db field, if it’s not then you’ll have to do an before save hook that populates the field on save of the record. The issue with this is that when the user goes to look at the record it’s only “accurate” if the user re-saves the record.

I would use a non-db field and the process record hook. That way the data is always current when the user views the screen.

If you really need it written to the database, a scheduled job might be better, to run once an hour or something to go and recalculate all the opportunities. Of course you’d probably have to limit it to opportunities with quotes created “today” let’s say so you’re not taxing your system depending on how many records you’ll have.

5

Thnx a lot, now I fully uderstand the idea. Non DB would be great as you mentioned, but I need to have this in db for further analytics.
Can you point me to a doc or example how to start doing the counting loop every x time?

6

You probably want to do this as a scheduled task then, that way you won’t have to rely on the user to re-save the record in order for it to be accurate. Here’s the documentation with an example.

I don’t know of any specific to your needs but basically you’ll want to find AOS_Quotes related to the bean and then loop through them while adding up the total. When there are no more to add up, you’ll save your opportunity bean and move on to the next one.

You might want to consider 2 jobs. One that you run like once a month that updates all opportunities this would probably be pretty intensive. Then another that runs less often maybe every hour on the quotes side something like if new quote then get the related opportunity, then get all quotes related to that opportunity, add them up and update the opportunity. That way you have a comprehensive job that is kind of a one off and a regular job that deals with changes.

7

Another approach for the incremental might be to run as a before save hook on AOS_Quotes, and if there is a related opportunity, then get all quotes related to the opportunity and then update the opportunity bean with the sum of existing quotes plus the new one. This might get messy when you are editing one though as opposed to creating a new one.

8

Would this do ir? Opportunity field total value = Sum of all related Quotes beans’ total fields.

9

Ufortunatelly no for 2 reasons:
-calculated field need parametres to be explicit - you cannot loop through unknown number of fileds
-for above reason you cannot calculate one-to-many relationship. only the one “one” on the other side…

I will try @pstevens idea and share the results

10

My add-ons can loop through related records easily, so PowerFields could do that and store it in a field, and PowerReplacer could compute it directly for an email or a PDF.

11

That is nice. How can I test PowerFields?

12

I’m sorry but I have no technical way of providing a test, if I give the code, it’s gone, so I really only give it to the people in the necessary tier of my sponsorship program.

What is your SuiteCRM version? I’m only starting to work on SuiteCRM v8 compatibility, so I can’t guarantee it will work in your version.

13

It is 7.14.2, and regarding the sponorship - of course, I think I already done that 2 hours back

14

That is exatctly what I did. Thanks for the hint.
Below is sample code that works as a proof of concept. I am hooking this to “on save” in opportuniy.
It took me some so maybe anyone can benefit from this

<?php
class Calc_Qoute
     {
         function Summarize($opp, $event, $arguments)
         {
           $opportunityId=$opp->id;
          $quoteBeans = BeanFactory::getBean('AOS_Quotes');
          $search = $quoteBeans->get_full_list('name','aos_quotes.opportunity_id="'.$opportunityId.'"');

          $opp->amount=0;
          foreach($search as $quoteBean) {
                $opp->amount+=$quoteBean->total_amt;
           }
         $opp->save();

         }
     }
?>
2 Likes
15

You’ll have to do something on the Quotes side too. If someone adds a new quote related to an Opportunity that will not get captured unless some one re-saves the opportunity.

It might be as simple as re-saving the related Opp. Then your other hook would kick in.

16

Of course you are right! I will dig into that and update the solution…

1 Like