calculated field: count of relationship occurrences

[davideps]

Hello,

I'm using SugarCRM 6.1.4 on Ubuntu 10.04.

I've created a one to many relationship between Documents and Notes. When I click "view documents", I'd like the list to contain the number of notes related to each document. Some will have no notes. Some may have 30 or more. I'm guessing I need to create a new variable that displays a count of rows matching an SQL query against an association table. I've found various tips on "calculated fields" from different versions of SugarCRM, but not one that counts a type of relation. I'm hoping a guru may be able to point me in the right direction.

thank you,
-david

[Angel]

Take a look at the "Custom Aging Field" example on the developers site (http://developer.sugarcrm.com). You should be able to adapt it to use a COUNT query that retrieves the number of notes that are linked to the specific Document.

[davideps]

Angel, thank you.

[dmontminy]

I need the very same functionality implemented, and I'm at a complete lost when it comes to modifying the actual "CustomCode.php" from the AgingField example.

The SQL itself is easy enough:
SELECT COUNT(*) as cnt FROM relationship_table WHERE idA = id AND deleted = '0';

The only thing I need now is the correct names of "relationship_table" and "idA" in a manner that is upgrade_safe.

I can't find any sort of API documentation that would list objects and functions I can rely on...

[Angel]

The general idea is that the calculated field calls a custom function within a custom PHP file. The function in question in turn returns a value/data that matches the data type of the field.

So, for a situation like this, we would typically want to return an integer value, to be displayed in a field that is also of type integer.

Your function would look something like this:

 function calculatedValue($focus, $field, $value, $view)
{
   $temp = 10;

   $temp2 = 2;
 
   $result = $temp / $temp2;

   return $result;

} 
  

This is a very simple example, but the important part is the return portion as it gives us the result of 10 / 2, which in turn is passed on to the calculated field. Now to extend that to use SQL queries, we simply add our logic for that before the return and leverage the already existing Sugar connection, as such:

 function caculatedValue($focus, $field, $value, $view)
{
           //Calculate record count
        $query = "SELECT COUNT(*) as the_count FROM notes ";
            
        $results = $focus->db->query($query, true);
        $row = $focus->db->fetchByAssoc($results);
        
        $total = $row['the_count'];

                return $total;

} 
  

[dmontminy]

Thanks for the reply.

Everything you mentioned is very helpful (and will be in the future for anyone searching for this topic), but not exactly what I am missing in term of information.

What type of objet is $focus? Where is it defined? Can I get a list of possible function/Objets call I can make?
I'm sure I can manage to do everything myself, but I can't find the right documentation.

[Angel]

$focus is a representation of the SugarBean, which in turn represents the current record, connection and other valuable parameters.

Check out data/SugarBean.php if you want details on all the functions, etc. I am not sure if they are documented in the Developer's manual, but you should give that a look as well. It is available at http://developers.sugarcrm.com

[dmontminy]

I have hard-coded the SQL query and it seems to work fine. I can add the Calculated field to the Detail view but not to the listview. If I do Save&Deploy in the studio, it removes the field. I can add any other field, just not that one. Any idea?

(side-note: this thread should be moved to the 'help' section of the forums)

[andopes]

Probably you created a after_retrieve logic hook.
For List View you need to configure a process_record logic hook.

Cheers

[tkendo]

I am trying to do something very similar that you guys are doing, but have one question. I have two modules "Districts" and "Schools" and the one-to-many is set up so there are multiple schools for each district, and I am looking to pull the number of schools per district into an easy to read field. I am using the same setup as above with a "CustomCode.php" file.

I have everything working except for the proper SQL query I think. Right now the function is returning '306' which is the total number of records in the school module. I was thinking I could add a WHERE argument to the query to reference the name of the district which I could store as a separate field in each schools record, but I am not sure how to get that kind of data from the sugarbean. I don't understand exactly what $focus and such do and what they return....

Here is my function:

function getBldgCount($focus, $field, $value, $view)
{
        $query = "SELECT COUNT(*) as the_count FROM dists_schools ";

        $results = $focus->db->query($query, true);
        $row = $focus->db->fetchByAssoc($results);

        $total = $row['the_count'];

                return $total;

}