Using Map To Convert Value - How To Access Value From This Map And Avoid SOQL In Loop At The Same Time

Question

I'm building an Import feature (any Object can use this) with Flows and Apex action. When user upload a file via Flows, Apex action will go through all row and return output. After done checking and has no error, it will insert records.

Normally, for Lookup relationship field, user has to input Id so I wrote a method to convert from Name to Id. I'm using a Map<String, String> to gather Name(key) and Id(value). To have this Map, I need to query to fetch all the suitable results.

How I defined my Map:

Map<String, String> accountIdsByAccountNames = new Map<String, String>();
for(Account a: [SELECT Id, Name FROM Account WHERE is_active__c = '1']){
    accountIdsByAccountNames.put(a.Name, a.Id);
}

Map<String, String> contractIdsByContractNames = new Map<String, String>();
for(Contract c: [SELECT Id, Name FROM Contract WHERE Status = 'Signed']){
    contractIdsByContractNames.put(c.Name, c.Id);
}

Map<String, String> oppIdsByOppNames = new Map<String, String>();
for(Opportunity o: [SELECT Id, Name FROM Opportunity WHERE Stage = 'Won']){
    oppIdsByOppNames.put(o.Name, o.Id);
}

Apex Code:

public with sharing class convertFileInputToRecords{

    //Define Request
    public class Request {
        public Request() {
        }

        public class Response {
            @InvocableVariable(label='List(collection) of Records')
            public List<SObject> convertedRows;
        }

        @InvocableMethod(label='Convert File to Records')
        public static Response[] convert(Request[] inputs){
            Response[] responseList = new List<Response>{};

            for (Request curInput : inputs) {

                Integer k = 1;
                Response output = new Response();   
                output.convertedRows = new List<SObject>{};

                if (curInput.contentDocumentId == null && curInput.contentDocumentIdList == null) {
                    // throw exception   
                }else{
                    if (curInput.contentDocumentId != null) {
                        if (curInput.contentDocumentIdList == null) {
                            curInput.contentDocumentIdList = new List<String>();
                        }

                        curInput.contentDocumentIdList.add(curInput.contentDocumentId);
                    }
                }

                SObjectType sObjType = ((SObject) Type.forName(curInput.objectApiName).newInstance()).getSObjectType();
                Map<String, SObjectField> fieldMap = sObjType.getDescribe().fields.getMap();

                for (String curContentDocumentId : curInput.contentDocumentIdList) {

                    List<SObject> curFileConvertedRows = new List<SObject>();
                    Blob fileBlobContent =  getContent(curContentDocumentId);
                    String fileStringContent = blobToString(fileBlobContent, 'UTF-8');
                    fileStringContent = fileStringContent.replaceAll('(\r\n|\r)', '\n');

                    String[] fileRows = fileStringContent.split('\n');
                    String[] apiFields = fileRows[0].split(','); 
                    //Get all the API names of the apiFields from the 1st row

                    Map<String, Schema.DisplayType> fieldToFieldTypeMap = new Map<String, Schema.DisplayType>();
                    Map<String, String> fieldName = new Map<String, String>();
                }

                for (String field : apiFields) {
                    fieldToFieldTypeMap.put(field, getFieldType(fieldMap, fieldApiName));
                    fieldName.put(field, fieldApiName);
                }

                // loop through all rows in file
                for (String row : fileRows) {

                    //skip the first 2 row for field lable
                    if (k <= 2){
                        k = k +1;
                        continue;
                    }

                    row = replaceNewline(row, '-`-');
                    SObject obj = sObjType.newSObject();
                    String[] fieldInputValues = row.split(curInput.',', -1);

                    //Loop through each API Name

                    for(Integer i=0; i < fieldApiName.size(); i++) {
                        if(fieldApiName[i] == AccountId || fieldApiName[i] == ContractId){

                            //Invoke convert method here
                            List<String> fieldNameAndId = convertNameToId(fieldValue, fieldName.get(fieldApiName[i]), objectApiName);

                            if(fieldNameAndId.size() < 1){
                            //throw exception
                            } 

                            fieldName.put(fieldNameAndId[0],fieldNameAndId[0]);
                            fieldValue = fieldNameAndId[1];
                        }
                        Schema.DisplayType fieldType = fieldToFieldTypeMap.get(fieldApiName[i]);
                        try {
                            obj.put(
                                fieldName.get(fieldApiName[i]),
                                getConvertedFieldValue(fieldType, fieldValue,curInput.objectApiName,k)
                            );
                        } catch (Exception e) {
                            System.debug(e);
                        }
                    }

                    curFileConvertedRows.add(obj);    
                }
                output.convertedRows.addAll(curFileConvertedRows);
            }
            responseList.add(output);
        }
        return responseList; 
    }

    //Method to convert
    private static List<String> convertNametoId(String valueInput, String fieldName, String objApiName){

        //Check what kind of sObject first

        if(objApiName == Opportunity){

            //Check fieldName and get value from a specific Map
            if(fieldName == 'AccountId'){
                if (accountIdsByAccountNames.get(valueInput) != null){
                    return new List<String>{AccountId, accountIdsByAccountNames.get(valueInput)};
                }
            }
            if(fieldName == 'ContractId'){
                if (contractIdsBycontractNames.get(valueInput) != null){
                    return new List<String>{ContractId, contractIdsBycontractNames.get(valueInput)};
                }
            }
        }
        if(objApiName == Case){
            if(fieldName == 'OpportunityId'){
                if (oppIdsByOppNames.get(valueInput) != null){
                    return new List<String>{OpportunityId, oppIdsByOppNames.get(valueInput)};
                }
            }
            //so on
        }
        return List<String>{};
    }
}

My problem is because I invoke that method (have to) inside the loop, I tried:

• Placed those Map before the loop => Did not work, the method cannot access it.
• Placed it inside the method => Will exceed limit depending on user's input.
• Defined it as a private static Map => Still exceeds limit because to get key, I will call it like this.

//Define Map as private static
private static Map<String, String> accountIdsByAccountNames(){
        Map<String, String> accIdsByAccNames = new Map<String, String>();
        for(Account a: [SELECT Id, Name FROM Account WHERE is_active__c = '1']){
          accIdsByAccNames.put(a.Name, a.Id);
        }
        return accIdsByAccNames;
}

//Access Map inside method

if(objApiName == Opportunity){
    //Check fieldName and get value from a specific Map
    if(fieldName == 'AccountId'){
        if (accountIdsByAccountNames().get(valueInput) != null){
             return new List<String>{AccountId, accountIdsByAccountNames().get(valueInput)};
        }
    }
}

How can I/Where can I define those Map and store them so that I can reference them without exceeding SOQL limit?

Thank you so much!

Answer 1

Placed those Map before the loop => Did not work, the method cannot access it.

Since the convertNametoId method is private and it seems to be called only from convert method, you could just add the maps as parameters, i.e.

private static List<String> convertNametoId(String valueInput, String fieldName, String objApiName, Map<String, String> accountIdsByAccountNames, Map<String, String> contractIdsByContractNames, Map<String, String> oppIdsByOppNames)

Then you can place the map before the loop and pass them to the method.
Probably this is the fastest way to solve the issue.

Defined it as a private static Map => Still exceeds limit because to get key, I will call it like this

This could be another solution, but in your implementation the accountIdsByAccountNames method does a query every time you call it.
Instead, you could define a private static parameter and initialize it only once, then retrieve it via a method:

private static Map<String, String> accountIdsByAccountNames;
private static Map<String, String> getAccountIdsByAccountNames(){
    if (accountIdsByAccountNames == null) {
        accountIdsByAccountNames = new Map<String, String>();
        for(Account a: [SELECT Id, Name FROM Account WHERE is_active__c = '1']){
          accountIdsByAccountNames.put(a.Name, a.Id);
        }
    }
    return accountIdsByAccountNames;
}

private static Map<String, String> contractIdsByContractNames;
private static Map<String, String> getContractIdsByContractNames(){
    if (contractIdsByContractNames == null) {
        contractIdsByContractNames = new Map<String, String>();
        for(Contract c: [SELECT Id, Name FROM Contract WHERE Status = 'Signed']){
            contractIdsByContractNames.put(c.Name, c.Id);
        }
    }
    return contractIdsByContractNames;
}

private static Map<String, String> oppIdsByOppNames;
private static Map<String, String> getOppIdsByOppNames(){
    if (oppIdsByOppNames == null) {
        oppIdsByOppNames = new Map<String, String>();
        for(Opportunity o: [SELECT Id, Name FROM Opportunity WHERE Stage = 'Won']){
            oppIdsByOppNames.put(o.Name, o.Id);
        }
    }
    return oppIdsByOppNames;
}

This way the query will be executed only once per transaction, no matters how many times the method is called.

That code could be written also as

private static Map<String, String> accountIdsByAccountNames {
    get {
        if (accountIdsByAccountNames == null) {
            accountIdsByAccountNames = new Map<String, String>();
            for(Account a: [SELECT Id, Name FROM Account WHERE is_active__c = '1']){
              accountIdsByAccountNames.put(a.Name, a.Id);
            }
        }
        return accountIdsByAccountNames;
    }
    set;
}

private static Map<String, String> contractIdsByContractNames {
    get {
        if (contractIdsByContractNames == null) {
            contractIdsByContractNames = new Map<String, String>();
            for(Contract c: [SELECT Id, Name FROM Contract WHERE Status = 'Signed']){
                contractIdsByContractNames.put(c.Name, c.Id);
            }
        }
        return contractIdsByContractNames;
    }
    set;
}

private static Map<String, String> oppIdsByOppNames {
    get {
        if (oppIdsByOppNames == null) {
            oppIdsByOppNames = new Map<String, String>();
            for(Opportunity o: [SELECT Id, Name FROM Opportunity WHERE Stage = 'Won']){
                oppIdsByOppNames.put(o.Name, o.Id);
            }
        }
        return oppIdsByOppNames;
    }
    set;
}

In this case you can access the property as usual and the get accessor executes when the property is read. Again the query is executed only once. This is called Lazy initialization.

---

Keep in mind that, as cropredy pointed out in comment, a single name could be related to multiple records, so, unless you enforce duplicate rules, you're going to store in those maps just the id of the last record retrieved for each name.

P.S. Developer console offers several perspectives, that may help debugging your code.