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.

Reply
  • 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.

Children
  • Did we try all the fields from looking nat the Product record JSON, thinking it could be p_productclass_p_product_1

        "p_productclass_p_product_1_name": "Enduro+",
        "p_productclass_p_product_1": {
            "name": "Enduro+",
            "id": "a0ud0000001GbvVAAS",
            "_acl": {
                "fields": [],
                "_hash": "654d337e0e912edaa00dbb0fb3dc3c17"
            }
        },
        "p_productclass_p_product_1p_productclass_ida": "a0ud0000001GbvVAAS",

  • 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