0

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!

6
  • 1
    where you have defined map for account,contract and opportnity in the loop for contract and opportunity why are you referring the a loop variable ? that is wrong right ?
    – Aman Patil
    Commented Feb 25, 2023 at 19:23
  • 1
    note that unless you enforce duplicate rules, you can have multiple accountIds for a given account.name. Your maps should also be called accountIdsByAccountName etc for clarity
    – cropredy
    Commented Feb 26, 2023 at 0:02
  • Hi @AmanPatil , yes that was wrong. It was my copy mistake from the previous one.
    – Pam
    Commented Feb 26, 2023 at 0:59
  • Hi @cropredy, thank you for your comment. I fixed the Maps name for clariry.
    – Pam
    Commented Feb 26, 2023 at 1:17
  • You are missing some things - where is Request defined? where do you loop through inputs?
    – cropredy
    Commented Feb 26, 2023 at 4:58

1 Answer 1

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.

3
  • Hi @RubenDG, thank you so much for your solutions! I appreciated it. I tried both of them to insert 50 rows and received 'Apex CPU time limit exceeded'. I think because storing the Ids and Names in Map caused the 'Apex CPU time limit exceeded'. But without it, if I use dynamic query by user's input to retrieve only 1 Id and 1 Name each row, it will hit the SOQL 101. Do you have any recommendations to refactor my code? As I debug, the 'getConvertedFieldValue' execute many times.
    – Pam
    Commented Feb 26, 2023 at 13:46
  • @Pam The getter are called many times, but after the first time they return the map asap since the if block is skipped, indeed it doesn't hit SOQL limit. About Apex CPU time limit exceeded, without seeing the real code executed in the whole transaction (if there is a dml, triggers count against cpu time limit) no one can help you. Keep in mind the operations on strings are expensive since a new instance of a string is created each time. Take a look at Analysis perspective in the developer console, it may provide a lot of useful information.
    – RubenDG
    Commented Feb 26, 2023 at 14:47
  • Thank you @RubenDG, your answer helped me avoid SOQL limit! I will try to work on this problem.
    – Pam
    Commented Feb 26, 2023 at 15:56

You must log in to answer this question.

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