I am trying to achieve a solution for the following problem –
For each Account, I want to know the assets of an account belongs to how many different productGroups (to see products belonging to how varied of product groups each Account(Client) is buying from us)
ProductGroup is a formula field on Assets which gets the value from Products object ( ProductGroup is a picklist on Products)
For example, if I have:
- Product groups P1, P2, P3, P4, P5
- Assets A1, A2, A3, A4
A1, A2, A4 are related to products of product group P1
A3 is related to a product of product group P4
I want the count to be 2 because all the assets of this account belongs to 2 different product groups. From this, I would know Account X is buying our products from 2 productGroups.
My approach –
To get the no. of assets in each productGroup, my approach would be for each picklist value in Product Group, using SOQL query ,
SELECT COUNT(AssetID) from Assets__c GROUP BY Product_Group__c
For each productGroup, get the number of records where the above mentioned countVariable is > 0
Copy this value on to a new custom field(BreadthOfAssets) on Account object.
Do i need to write a trigger on Asset or Account for this and need help/suggestions/starting point in coding this.
Best Answer
It seems like you have the general idea of what you need to do. Your proposed query isn't quite right though.
What your proposed query would do is count the number of
Asset
records per product group across all accounts (instead of counting per account).Instead, I think the following is what you're looking for:
I can't give you the exact query with the information you've provided so far (looks like you're using a custom
Assets__c
object instead of the standardAsset
object, and I don't know the fields or relationships), but the gist of things are:Account
first and foremostCOUNT_DISTINCT()
to get the number of distinct values ofProduct_Group__c
(instead of getting counts of how many assets fall under each product group)That second bit is kinda hard to put to words. Given your example...
COUNT_DISTINCT(Product_Group__c)
should give you a result of 2.COUNT(Product_Group__c)
should give you 2 results for yourAccount
, telling you that "P1" has 3 records under it for thisAccount
, andP4
has 1 record under it (for the sameAccount
)As for whether this should be a Trigger on
Asset
orAccount
, either would work. My preference would be to make this anAsset
trigger (so that any newAsset
or change to an existingAsset
is guaranteed to cause you to re-evaluate).For getting started with the code, that's getting close to crossing the line between "helping you overcome your problem" and "doing your work for you" (such questions tend to be poorly received here, just so you're aware).
That said, the general trigger pattern looks like
I can add more explanation if you'd like, but I'm hoping you can take things from here.