0

We use assets to track products purchased by accounts. We have to report on our "utopia" quarterly - the utopia is all of the products each account does not yet own. I need a way to create a related object on the account that shows all products not in the asset listing on that account.

Ideally, this object would also pull the Price Book we have on the account and show the total potential dollar amount for our sales team on each account.

I have tried using the FindCommonandUncommonRecords apex, but I cannot get it to work. I have tried using a flow with get records and loops, but that also doesn't work as I can't compare the two collections within the flow. I can't find a report which shows products without assets and can't create a custom report type or a cross filter with all 3 objects (accounts, assets and products).

We only have 50 products and we will only had 1 or 2 new ones per year and this seems like it should be an easy ask, but I am completely stumped.

1

1 Answer 1

0

A report is not going to work because you cannot report on records that do not exist only on ones that do. However, I think this can be done.

If this were me I'd start with a record trigger/flow on Opportunity, when it's closed won you should be adding the products bought as an asset then. This would run after that.

Start by getting querying all the assets on the account related to the Opportunity.

Query all your active products.

I think it'll be a shorter loop if you loop through the assets rather than all the products.

For each asset remove the product Id from the collection, so you're left with products that are not in assets. You're not comparing the lists to each other you're just checking if one value is in the list and then removing it.

Make another loop and then create the new object records for all the products left over.

Insert the new object records.

This feels really basic but you can expand from here. Add in the amount from the pricebook would be query the PBE for that Opportunity's pricebook for that Opportunity's currency that are active instead of just the products.

You can add in checks to remove existing new object records when an asset is created for newly closed Opportunities. Or if you don't want to check each time you can wipe the new object records off that account and re-add the delta products. Depending if you have multiple Opportunities for one account this might get messy but the basic idea is something like Opp Closed -> Create Assets -> Delete New Object Record -> Create New Object Record.

This seems to work in my test environment, the benefit with APEX over flow is that you can use maps to store the key as an Id, then you're just matching the id of the product:

public with sharing class stackexchange {

public static void startMatching(Map<Id, Opportunity> opportunities)
{
    Set<Id> pbSet = makePriceBookSet(opportunities);
    Set<Id> accountSet = makeAccountSet(opportunities);
    Set<String> currencySet = makeCurrencySet(opportunities);
    Map<Id, PriceBookEntry> pricebookEntries = getPriceBookEntries(pbSet, currencySet);
    Map<Id, List<Asset>> accountAssets = getAssets(accountSet);
    List<Account_Product__c> accountProductsToInsert = matchAssets(accountset, pricebookEntries, accountAssets);
    insert accountProductsToInsert;
}

public static Set<Id> makePriceBookSet(Map<Id,Opportunity> opportunities)
{
    Set<Id> s = new Set<Id>();

    for(Opportunity oppy : opportunities.values())
    {
        s.add(oppy.Pricebook2Id);
    }
    return s;
}

public static Set<Id> makeAccountSet(Map<Id,Opportunity> opportunities)
{
    Set<Id> s = new Set<Id>();

    for(Opportunity oppy : opportunities.values())
    {
        s.add(oppy.AccountId);
    }
    return s;
}

public static Set<String> makeCurrencySet(Map<Id,Opportunity> opportunities)
{
    Set<String> s = new Set<String>();

    for(Opportunity oppy : opportunities.values())
    {
        s.add(oppy.CurrencyISOCode);
    }
    return s;
}

public static Map<Id, PriceBookEntry> getPriceBookEntries(Set<Id> pbSet, Set<String> currencySet)
{
    Map<Id, PriceBookEntry> pbeMap = new Map<Id, PriceBookEntry>();

    for(PriceBookEntry pbe : [SELECT Id, Product2Id, UnitPrice FROM PriceBookEntry WHERE IsActive = TRUE AND PriceBook2Id IN :pbSet AND CurrencyISOCode IN :currencySet])
    {
        pbeMap.put(pbe.Product2Id, pbe);
    }
    return pbeMap;
}

public static Map<Id, List<Asset>> getAssets(Set<Id> accountSet)
{
    Map<Id, List<Asset>> assetMap = new Map<Id, List<Asset>>();

    for(Asset a : [SELECT Id, AccountId, Product2Id, Price FROM Asset WHERE AccountId IN :accountSet])
    {
        if(!assetMap.containsKey(a.AccountId))
        {
            assetMap.put(a.AccountId, new List<Asset>{a});
        } else {
            assetMap.get(a.AccountId).add(a);
        }
    }
    return assetMap;
}

public static List<Account_Product__c> matchAssets(Set<Id> accountSet, Map<Id, PriceBookEntry> pricebookEntries, Map<Id, List<Asset>> accountAssets)
{
    List<Account_Product__c> accountPractices = new List<Account_Product__c>();

    for(Id accountId : accountSet)
    {
        Map<Id, PriceBookEntry> clonePBEs = pricebookEntries.clone();

        if(accountAssets.containsKey(accountId))
        {
            for(Asset a : accountAssets.get(accountId))
            {
                clonePBEs.remove(a.Product2Id);
            }
        }

        for(PriceBookEntry pbe : clonePBEs.values())
        {
            Account_Product__c ap = new Account_Product__c();
                ap.Account__c = accountId;
                ap.Product__c = pbe.Product2Id;
                ap.Price__c = pbe.UnitPrice;
            accountPractices.add(ap);
        }
    }
    return accountPractices;
}

}

2
  • Just to confirm, when you say Query, are you saying use Get Records? In the loop, are you saying use a decision element and then assign values to a variable for any product codes in the price book entry which do not exist in assets? I have tried this (both with looping through the price book entries where the product code exists and looping through the assets on the account where the product code also exists). Both ways return a null value for the second collection variable in the decision and no records are found to be in common. Commented Feb 22 at 23:05
  • Yes I do mean that. I started writing this as apex then I thought that well we could probably do this in a flow so I started changing things. I think you're getting that because of the different collection types ones asset one products. Maybe Apex is better... Commented Feb 23 at 1:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .