Field Formula | How to Count how many related records

Hi Luke Ridgway ,

You should be able to achieve this with your first formula example (i.e. count($p_product_s_serialnumber_1) ) in an integer field. I tested in a stock instance of Sugar and I was able to get an accurate count of records when referencing a custom relationship. Since that isn't working for you, do you have multiple relationships between your Product and Serial Numbers modules? If so, perhaps the relationship name you are using isn't referencing the actual relationship for the subpanel. 

You can verify which relationship names are valid by going to Admin > Studio > Product > Relationships. 

Chris

Hello Chris,

Better late than never (this reply - sorry it took so long)

I have just the one relationship form Product to Serial Number:

p_product_s_serialnumber_1

But still can't get this to work in the Integer Field Formula?

count($p_product_s_serialnumber_1)

I can create similar count($something) for example in the account module to count the contacts, and that works fine.

Also, changing the formula (in the same field) to use the registrations relationship worked fine... it is the same type of relationship too:
count($p_product_g_registration_1) < works

So I wonder if we have something related to this custom module preventing the count?

This also doesn't work: 
count(related($p_product_s_serialnumber_1,"name"))

And the formula builder will not let me add some other variations:

So, I wonder even if this is the correct way to acheive what I imagined?  I wonder if it would give 'live' data anyway?  Would each record need recalculate values / edit & save to refresh the number as new serial are added - if so - I am barking up the wrong tree anyway...

 - Maybe a roll up sum on the product class module could count all the child products > child serial numbers?

 - Or just use reports?

Not sure!  But I do have a deadline...

Any other tips would be very welcome - Thank you once again in advance...

Luke.

Hi Luke Ridgway ,

The formula should work as described and you noted that the same format works for other relationships. I think it would be best to raise a case with Sugar Support to evaluate it further.

To the more important point, all Sugar Logic calculations require explicit saves to trigger the calculation so you would need to run a recalculate values on the records once you have a functioning formula. While Sugar Logic is not a "real-time" representation, formulas like this should show a reliable count provided you are only entering and modifying records directly through the app or via the standard REST API. With each parent or child record save, the calculation would be updated. If you are doing anything like direct SQL updates to modify the relationships, then Sugar Logic would not be a reliable means of reporting on these counts.

Chris

Hi Luke Ridgway ,

The formula should work as described and you noted that the same format works for other relationships. I think it would be best to raise a case with Sugar Support to evaluate it further.

To the more important point, all Sugar Logic calculations require explicit saves to trigger the calculation so you would need to run a recalculate values on the records once you have a functioning formula. While Sugar Logic is not a "real-time" representation, formulas like this should show a reliable count provided you are only entering and modifying records directly through the app or via the standard REST API. With each parent or child record save, the calculation would be updated. If you are doing anything like direct SQL updates to modify the relationships, then Sugar Logic would not be a reliable means of reporting on these counts.

Chris

Awesome, thanks again for the info Chris Raffle ,

I am just looking at using reports next, then after that John suggested logic hook (run for example on open, to get the data into the fields).  This would mean we could have some nice data directly in each product class and could also then report on that...  but for the requirement just a report somehow will do the job.

We do import our serial number records from an automated system the John built, via Rest API.  So very good to read that the field would update with every chlild record creation too.  

Will carry on and update in here with progress. when there is some...

Just a long overdue update to this one, hopeully usful if anybody else finds this post:  I raised a case with support and this reply explains what I came up against:

Hi Luke,

When you edit/save a Product, due to the count() sugar logic, you are forcing the recalculation which will "resave" each record.
The more related records, the more time it would take for the save to complete, and it is possible that it would hit the MySQL timeout.

For example, this record (*link removed*) has 3603 serial number records.
The example record (*link removed*) has 12286 related Serial Number records.

The error that you are in the sugarcrm.log

- Exception evaluating expression in SetValueAction, count($p_product_s_serialnumber_1) : An exception occurred in the driver: Commands out of sync; you can't run this command now

...is because on the next line, there is a 'mysqli has gone away, retrying' error which means that the connection was killed by the DB.

There is the Issue#80932 ( https://portal.sugarondemand.com/#supp_Bugs/80932 ) that is reporting a performance degradation on the count() Sugar Logic.

To work around this, you could change the calculated field to a readOnly field that is updated via a custom logic hook (after_relationship_add, after_relationship_delete) for the relationship p_product_s_serialnumber_1.

Regards,
SugarCRM Support

Hi Luke Ridgway ,

We have a product in development that alleviates the need to handle this scenario via custom code as well as the performance bottlenecks with using Sugar Logic in this fashion. If there's any interest in a plug-in to solve your use case, please reach out via our website and we'll schedule a demo.

Chris