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.

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