Field Formula | How to Count how many related records

Dear friends...

Think i am just stuck on the sytax to use for this?

We have some related modules where the hierarchy is like this:

  1. Product Class (like the family of that product)
  2. Product (a specific part number of above, ie a double sided printer model)
  3. Serial Number (the individual item/record)

The Goal:  

  1. In Product I want to count how many serial numbers there are.
    Note that Sugar already does this anyway, so maybe there is something else i can do that I have not thought of?
    When you look at any related module, it tells you how many there are:  (this is all I want to do, but in a field on the layout) the 12286..


  2. Then in the Product Class I would then sum all the Product amounts.  Hopefully that makes sense.

It will pave the way to add more data into product Class and even Product for things like % of returns for each product Class.
  

But, I am stuck on the field formula to count the Serial Numbers related to the Product
The first step, which i thought would be the easy bit! Disappointed


Tried Decimal & Integer field types, with claculated formulas such as:

count($p_product_s_serialnumber_1)

count(related($p_product_s_serialnumber_1,"name"))

Recalculated Values, and QRR'd - but neither int nor Dec with any formula (that does save OK) ever works.

 < testing in sanbox

I bet this is dead easy and i am just missing the obvious...
Thank you in advance for any guidance!
Luke.

Parents
  • Hi  ,

    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:

    p_productclass_p_product_1_name
    p_productclass_p_product_1p_productclass_ida

    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  ,

    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  ,

    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  ,

    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

Reply Children
No Data