Thread Details
  • State Not Answered
  • Replies 7 replies
  • Subscribers 336 subscribers
  • Views 1262 views
  • Users 0 members are here
Forum Actions
Related

Field Formula | How to Count how many related records

Luke Ridgway

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
  • 0

    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

    Co-Founder | SugarCRM's 2024 Global Outfitters Partner of the Year

Reply
  • 0

    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

    Co-Founder | SugarCRM's 2024 Global Outfitters Partner of the Year

Children
  • 0 in reply to Chris Raffle

    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.