add calculation (total) on Accounts module

Good morning,
i want to add total field on the bottom of page in Account menu
it is possible to add it?

just like this

Thankyou

Is this on a subpanel? If so no its not possible without custom coding it yourself.

Hello andy, thanks for your response,
yeah sure if its on subpanel, how i can make that one?
i just want to make the total/sum of that data and show on listview.

its possible?

nevermind if i must make it with custom coding.

Thankyou

Hello agasmaha,

It seems there are many ways to do this, some quite a bit simple, and some more complex. I referred mostly to the following two solutions when seeking for this functionlaity:

  1. http://developer.sugarcrm.com/2012/11/06/showing-the-total-amount-for-a-column-in-a-listview-using-just-logic-hooks-and-jquery/
  2. http://www.synaxisworks.com/blog/technology/sugarcrm-add-total-for-opportunity-amount/?utm_source=sugarcrm&utm_medium=forum

The first one is very simple, using Logic Hooks. The second approach is more elaborated and professional, as you’ll probably be able to add a “totals” line in the table to display your results.

For me it was sufficient to go on with the first solution. In my case, I have a custom field on Accounts that represent the distance (Miles, Km) beetween my company and the customer. With Logic Hooks, I could populate automatically a similar custom field in the Meetings Module, and showed that field even on List Views of Meetings. Finally, using solution 1) above, I could see sums of those distances in a list view of the Meetings I was able to filter using Advanced Search.

Attached are the final results for me (unfortunately it’s in Portuguese, I don’t know if you’ll understand it).

Please get back to me if you want code examples on how to do this.

Celso.

2 Likes

Thanks for the advice Webber

but i have a question now, can i use that in the Accounts module?

if i modify the logic_hooks in Accounts module, just override it?

if possible, can i get the example codes for my referrences,

Thankyou

Hi,
Sorry for not answering before.

Yes, you can do that in any module.

All you need to do is:

  1. Edit custom/modules/Accounts/logic_hooks.php and add something like:
<?php
// Do not store anything in this file that is not part of the array or the hook version.  This file will        
// be automatically rebuilt in the future. 
$hook_version = 1;
$hook_array = Array();
// position, file, function 
$hook_array['before_save'] = Array();
$hook_array['after_retrieve'] = Array();
$hook_array['process_record'] = Array();
$hook_array['after_ui_frame'] = Array();

// This will make your function be called for every record in a ListView:
$hook_array['process_record'][] = Array(1, 'Some description of what you are doing',     'custom/modules/Accounts/SomeFileName_LogicHook.php','YourPHPClassName', 'functionToSumYourField');

// This will add the sum to the HTML page just before it is displayed
$hook_array['after_ui_frame'][] = Array(1, 'Some description of what you are doing',     'custom/modules/Accounts/SomeFileName_LogicHook.php','YourPHPClassName', 'functionToDisplayTheSum');

?>
  1. Edit custom/modules/Accounts/SomeFileName_LogicHook.php and create your functions to sum up a specific field and show that amount at the header and footer of your ListView:
<?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

class YourPHPClassName {
        // Local static variable to store your sum
        protected static $yourSum = 0;

        function functionToSumYourField(&$focus, $event, $arguments) {
                // do any processing and calculations you want before summing your values here

                // this will sum up the value inside your desired field into your local static variable yourSum
                self::$yourSum += $focus->the_name_of_any_field_inside_your_module_c;

                // do further processing if you want here
        } // end of functionToSumYourField

        function functionToDisplayTheSum(&$focus, $event, $arguments) {
                if ($GLOBALS['action'] == 'index' || $GLOBALS['action'] == 'ListView') {
                        $yourSum = self::$yourSum; // TODO: maybe you want to add some formatting to numbers, dates, etc.
                        echo <<<EOHTML
<script type="text/javascript">
<!--
$('<p align="center" vertical-align="middle"><b>Your sum is: {$yourSum}</b></p>').insertBefore('.paginationChangeButtons');
-->
</script>
EOHTML;
                }
        } // end of functionToDisplayTheSum

} // end of YourPHPClassName

?>

It’s pretty simple to work with Logic Hooks. Please double check PHP syntax above since I did not test this code in my environment.

Don’t forget to do a “Quick Repair” after you create the Logic Hooks files. After you create them and do a Quick Repair once, you can edit them without “Quick Repairing” again.

I hope this helps.

Cheers, Celso.

1 Like

Hi and thanks for your help Celso, sorry for the late respond.

i had test the code that you give to me, and i has do a Quick repair. but it not worked.

what about the view.list.php in directory views? should i custom that code too?

Regards
Agas

Hi Agas,
Using Logic Hooks does NOT require you to customize view.list.php.
The proposed solution is pretty simple because it does “inject” the desired text using Javascript into the existing HTML generated previously by standard code.
The code under Logic Hooks is very interesting because it’s called automatically whenever you enter Detail, Edit or List view of the desired module.
To help you debug your own code, please send me all of your PHP files so that I can analyze for any issues.
Regards,

Hi Webber, Thanks for your reply

im sorry i just had a mistake, now its showing in my module.

but i have a new problem, the result of the custom field didnt show.

self::$yourSum += $focus->biaya_bulanan_c;

but i tried to use another module for example i used the Opportunities.

self::$yourSum += $focus->amount_usdollar;

and its works.

i had change the $focus to $bean but it doesnt change anything.

Hi Agas,
Please, double check your code just to make sure everything is ok.
You can try to debug you code by adding the following line after the pertinent lines of code you want to debug, like this:

self::$yourSum += $focus->biaya_bulanan_c;
$GLOBALS['log']->fatal("Your sum = self::$yourSum and your variable is $focus->biaya_bulanan_c");

After adding this debugging, you can check your “sugarcrm.log” file that is located inside your SuiteCRM installation parent folder. Under Linux you can open a terminal and issue something like:

tail -f /path-to-your-suitecrm-installation/sugarcrm.log

If you don’t see your custom fields populated with the corresponding values, maybe you need to load your custom field values. For me it worked without “loading” the custom fields, but maybe our versions work different.
Please try to add this at the very beginning of your Logic Hooks function and see what happens:

$focus->custom_fields->retrieve(); 

I had one situation where I needed to use this to get the custom fields in place.
Hope this helps.
Regards, Celso.

Hi, Celso
I have tried your advice and its works, thanks.
but now, it just sum the one page of list view (20 data on one list).
How i can sum it to all of data? for example i have 1000++ data

Regards,
Agas

This is great news, Agas!
I’m really happy that I could share my very little experience with you.
Unfortunately I’m facing the same problem, and I’m afraid there’s no way of doing that on List Views.
It happens that our method of undergoing each record in a ListView is only possible for the “visible” records.
I’m not quite sure if there is a way to sum up the values in all ListView pages, because our custom code is called only for the displayed records (once for each record).
I’ll try to solve this problem someday, if I have success I’ll let you know.
In the meanwhile, it came across to me this commercial add-on: https://www.sugaroutfitters.com/addons/sugarprint
I didn’t try it yet, but since it’s a perpetual licensed software, I believe it might be a good investment.
See you soon!
Celso.

Hi Celso, thankyou very much for your help.
maybe i will buy it, but i should try any way to calculate all of the module’s data.
how about use SQL query to SomeFileName_LogicHook.php
such as:

 SELECT SUM(anyfield) FROM anytable 

but i didnt understand how to execute the query.
is that the safe way?

Thankyou
Cheers,
Agas

Agas,
It’s possible, for sure. I’ve just didn’t tried anything similar.
Since this functionality would be very useful for me too, I’ll try to investigate something and let you know.
Regards,

Agas,
Another important point here: if you want more advanced reporting, maybe a better solution would be working with the Reports module of SuiteCRM.
The sums in a list view are useful, but I don’t think they would perform well on large amounts of data as you might have (1000+ records).
For me it’s ok, because my needs are very specific. I perform a search in my module, which returns 20-30 records maximum, and then I have the totals in one or two pages of the ListView.
For more advanced needs I would certainly recommend using the Reports module.
Best regards, Celso.

so to recap no elegant solution for combining all of the rows in listview, just the ones that are visible.

we’re working on this and will update the thread when it’s done, if anyone else has a suggestion pls let me know, hope it’s okey i’m reviving this 6 yr old thread

I have this one bookmarked, never tried it though

then final post has something about (really total) totals

1 Like