[SalesForce] Code/Trigger to get count of number of different Product groups, the Assets of each Account belong to at the Account object level

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:

[SELECT COUNT_DISTINCT(Product_Group__c) FROM Assets__c GROUP BY AccountId]

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 standard Asset object, and I don't know the fields or relationships), but the gist of things are:

  • group by the related Account first and foremost
  • use COUNT_DISTINCT() to get the number of distinct values of Product_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...

  • Using COUNT_DISTINCT(Product_Group__c) should give you a result of 2.
  • Using COUNT(Product_Group__c) should give you 2 results for your Account, telling you that "P1" has 3 records under it for this Account, and P4 has 1 record under it (for the same Account)

As for whether this should be a Trigger on Asset or Account, either would work. My preference would be to make this an Asset trigger (so that any new Asset or change to an existing Asset 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

trigger someTrigger on MyObject__c (before|after insert|update|delete){
    // Declare a collection to hold values we want to query against
    Set<Id> myIdsSet = new Set<Id>();

    // Iterate over a collection of objects, and gather the values we want to query
    //  against
    for(MyObject__c myObj :trigger.new){
        myIdsSet.add(myObj.someLookup__c);
    }

    // Declare a collection to hold records that we want to update
    List<OtherObject__c> otherObjsToUpdate = new List<OtherObject__c>();

    // Perform the main query you want to run, using the data gathered earlier
    //   to filter the query
    for(OtherObject__c otherRec :[SELECT <fields> FROM OtherObject__c WHERE Id IN :myIdsSet]){
        // The main parts of your trigger logic generally go in here

        // After you're done with the main logic, add the record to be updated
        otherObjsToUpdate.add(otherRec);
    }

    // Perform the final DML to update the records we worked on so the results are
    //   persisted
    update otherObjsToUpdate;
}

I can add more explanation if you'd like, but I'm hoping you can take things from here.

Related Topic