I’m learning on how to code in Apex, and I’m trying to figure out how I can bulkify my code in a better way so that I won’t get SOQLExceptions.

Scenario: Whenever I upload a lot of records using data loader to a child custom object called Inventory Snapshot, the system should check if there is a product record in the system. If there is a product record, then I should map the value with the Inventory Snapshot record. If not, then I should create a new Product record.

This is my apex trigger code:

trigger UpdateCreateProducts on Inventory_Snapshot__c (before insert, after insert) {

This is my apex class:

publicclass FindOrCreateProducts {
    public static void findProduct(List<Inventory_Snapshot__c> snapshotList){
        List<Product2> prds = new List<Product2>();
        for (Inventory_Snapshot__c inv: snapshotList){
            prds = [Select Id,ProductCode from Product2 where ProductCode =:inv.Item_Number__c Limit 1];
                if (!prds.isEmpty()){
                    for(Product2 prd: prds){
                        inv.Product__c = prd.Id;
    public static void createProducts(List<Inventory_Snapshot__c> invList){
        String prodDescr, newProdName, descr;
        List<Product2> prds = new List<Product2>();
        List<Product2> newPrds = new List<Product2>();
        for(Inventory_Snapshot__c invt: invList){
            prds = [Select Id,ProductCode from Product2 where ProductCode =:invt.Item_Number__c Limit 1];
            if (prds.isEmpty()){
                descr = invt.Product_Description__c;
                Integer colonValue = descr.indexOf(':');
                System.debug('colonValue ' + colonValue);
                if (colonValue != -1){
                    prodDescr = descr.substringAfter(':');
                    newProdName = invt.Item_Number__c + ' : ' + prodDescr;
                    System.debug('Product Name: ' + newProdName);
                    newProdName = invt.Item_Number__c + ' : ' + invt.Product_Description__c;
                Product2 newProd = new Product2();
                newProd.Name = newProdName;
                newProd.Description = descr;
                newProd.ProductCode = invt.Item_Number__c;
            }if(!newPrds.isEmpty() && prds.isEmpty()){
                insert newPrds;
                for(Product2 prd: newPrds){
                    invt.Product__c = prd.id;

The code will work if I manually test, but won’t work if I’m inserting records using Dataloader. Can anyone help me out on how I should bulkify my code?


1 Answer 1


Those queries and dml inside loop are the problem.
In order to bulkify your code you need a map, as sfdcfox said.

In findProduct method you have to create a map to associate an item_number with every Inventory_Snapshot__c that are in the current trigger scope. So you need a map of list: Map<String, List<Inventory_Snapshot__c>>.
Once that map has been populated, you can use its keySet to fetch the Product2 you need.
This way no matter how many Inventory_Snapshot__c are in that list, you'll make only one SOQL.

Beware: what will happen if inv.Item_Number__c = 'ABC' and the prod.ProductCode = 'abc'? The query will return it, but the map.get('abc') will return null. A simple workaround is putting and getting the key as lowercase (or uppercase).

I'm going to change its signature:

public static Map<String, List<Inventory_Snapshot__c>> findProduct(List<Inventory_Snapshot__c> snapshotList){
    Map<String, List<Inventory_Snapshot__c>> mapItemNumberInventoryList = new Map<String, List<Inventory_Snapshot__c>>();
    for (Inventory_Snapshot__c inv: snapshotList) {
        if (String.isNotBlank(inv.Item_Number__c)) {
            List<Inventory_Snapshot__c> inventoryList = mapItemNumberInventoryList.get(inv.Item_Number__c.toLowerCase());
            if (inventoryList == null) {
                inventoryList = new List<Inventory_Snapshot__c>();
                mapItemNumberInventoryList.put(inv.Item_Number__c.toLowerCase(), inventoryList);

    for (Product2 prod : [SELECT Id, ProductCode FROM Product2 WHERE ProductCode IN mapItemNumberInventoryList.keySet()]) {
        // remove from the map the Inventory_Snapshot__c for which already exists a Product2
        for (Inventory_Snapshot__c inv : mapItemNumberInventoryList.remove(prod.ProductCode.toLowerCase())) {
            inv.Product__c = prod.Id;

    return mapItemNumberInventoryList; // This map will contains only the Inventory_Snapshot__c records whose Item_Number__c isn't a valid ProductCode

Currently in createProducts method if you have two or more Inventory_Snapshot__c records with the same Item_Number__c you'll create a different product for each one and those products will share the ProductCode.
The map of list will solve this issue too.
First of all you can use the map returned by findProduct in order to iterate on fewer records, moreover this will let you avoid a SOQL and the if blocks, because you already know that those records are the one without a Product2.
You have to declare a list to hold the new products, the ones you're going to insert, then you can loop over the map to create them.
Outside the for-loop you'll have the dml, then you could loop over those products in order to link them to the Inventory_Snapshot__c records.

public static void createProducts(Map<String, List<Inventory_Snapshot__c>> mapItemNumberInventoryList) {
    List<Product2> prodToInsert = new List<Product2>();
    for (String prodCode : mapItemNumberInventoryList.keySet()) {
        for (Inventory_Snapshot__c invt : mapItemNumberInventoryList.get(prodCode)) {
            String descr = invt.Product_Description__c;
            Integer colonValue = descr.indexOf(':');
            String prodName = invt.Item_Number__c + ' : ';
            prodName += (colonValue != -1) ? descr.substringAfter(':') : descr;

            Product2 prod = new Product2();
            prod.Name = prodName;
            prod.Description = descr;
            prod.ProductCode = invt.Item_Number__c;

    insert prodToInsert;

    for (Product2 prod : prodToInsert) {
        for (Inventory_Snapshot__c invt : mapItemNumberInventoryList.get(prod.ProductCode.toLowerCase())) {
            invt.Product__c = prod.id;

That's it: 0 SOQL and only 1 dml no matter how many records you're going to insert.

Finally a small change to the trigger:

trigger UpdateCreateProducts on Inventory_Snapshot__c (before insert) {
        Map<String, List<Inventory_Snapshot__c>> mapItemNumberInventoryList = FindOrCreateProducts.findProduct(Trigger.new);

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