+ Start a Discussion
dj_0x7c0dj_0x7c0 

Create / insert mass of (Parent and Child, lookup) sObject records in a loop in Apex – reference Id issue

Hello
I'm processing flat structure object into two objects with relation, it's more complicated, but I want to show it simplified. I want to create mass of records with reference in a for loop. Issue is that Id of Account  is not populated in one transaction. To do this I would have to add insert in a loop (bad practice).

Account (one-to-many lookup relation) Marker__c sobject
public class MyProcessor implements Queueable{
	public void execute(QueueableContext context) {

		List<Stage_Object__c> counterparties = [SELECT Counterparty_Identifier__c, Full_Name__c, Processed__c
                                              	FROM Stage_Object__c
                            				   	WHERE Processed__c = false LIMIT 3300];
		List<Account> accList = new List<Account>();
		List<Marker__c> markerList = new List<Marker__c>();
		for (Stage_Object__c so : counterparties){
			Account na = new Account(Name = so.Full_Name__c);
			accList.add(na);
			Marker__c m = new Marker__c(Account_ref__c = na.Id,
										Identifier__c = so.Counterparty_Identifier__c);
			m.Account_ref__c = na.Id;
			markerList.add(m);
			so.Processed__c = true;
		}
		try {
			insert accList;
			insert markerList;
			update counterparties;
		} catch (DMLException de){
			//handling Exception
		}
	}
}

Relation is not created, as Account Id is not created in Force.com database. I want to omit many inserts in a loop. It's a process with many records so I don't want to meet the limits and write it efficient.

I tried even something like that:
Account na = new Account(Name = 'Salesforce');
Marker__c m = new Marker__c(Counterparty_Identifier__c = 'some test id',
                            Account_ref__r = na);
insert m;
ResultError: System.DmlException: Insert failed. First exception on row 0; first error: INVALID_FIELD, More than 1 field provided in an external foreign key reference in entity: Account: []
or
Marker__c m = new Marker__c(Identifier__c = 'some test id');
Account na = new Account(Name = 'Hollywood Inc.');
na.Marker_r = m;

insert na;
ResultError: Field is not writeable: Account.Marker_r

I tried also add them in one List<SObject> and insert in one transaction (two chunks), but reference also wasn't added.

Thanks for your ideas and thoughts,
DJ
Ajay Ghuge 6Ajay Ghuge 6
Hello,

As per salesforce documentation you need to use externalId to achieve this. Please refer this documentation 

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_dml_foreign_keys.htm

Please mark it resolved in case it solves your problem.

Regards,
Ajay
 
Deepak Pandey 13Deepak Pandey 13
1-
Account na = new Account(Name = 'Salesforce');
insert na;
Marker__c m = new Marker__c(Counterparty_Identifier__c = 'some test id', Account_ref__r = na);
insert m;

2- 
Marker__c m = new Marker__c(Identifier__c = 'some test id');
insert m;
Account na = new Account(Name = 'Hollywood Inc.');
na.Marker_r = m.id;
insert na;
dj_0x7c0dj_0x7c0
@Deepak Pandey 13
I showed those code examples (1-, 2-) in a loop context (0-), so my issue would be in: 
public class MyProcessor implements Queueable{
	public void execute(QueueableContext context) {

		List<Stage_Object__c> counterparties = [SELECT Counterparty_Identifier__c, Full_Name__c, Processed__c
                                              	FROM Stage_Object__c
                            				   	WHERE Processed__c = false LIMIT 3300];
		List<Account> accList = new List<Account>();
		List<Marker__c> markerList = new List<Marker__c>();
		for (Stage_Object__c so : counterparties){
		
			Account na = new Account(Name = so.Full_Name__c);
			Marker__c m = new Marker__c(Identifier__c = so.Counterparty_Identifier__c,
										Account_ref__r = na);			
			markerList.add(m);
			so.Processed__c = true;
		}
		try {
			insert markerList;
			update counterparties;
		} catch (DMLException de){
			//handling Exception
		}
	}
}
so such solution won't work because of issues above with simple transaction and multiple insert in loop.
 
Deepak Pandey 13Deepak Pandey 13
Try this -
public class MyProcessor implements Queueable{
    public void execute(QueueableContext context) {

        List<Stage_Object__c> counterparties = [SELECT Counterparty_Identifier__c, Full_Name__c, Processed__c
                                                  FROM Stage_Object__c
                                                   WHERE Processed__c = false LIMIT 3300];
        List<Account> accList = new List<Account>();
        List<Marker__c> markerList = new List<Marker__c>();
        for (Stage_Object__c so : counterparties){
        
            Account na = new Account(Name = so.Full_Name__c);
            insert na;
            Marker__c m = new Marker__c(Identifier__c = so.Counterparty_Identifier__c,
                                        Account_ref__r = na.id);            
            markerList.add(m);
            so.Processed__c = true;
        }
        try {
            insert markerList;
            update counterparties;
        } catch (DMLException de){
            //handling Exception
        }
    }
}
If this will not worked than please explain the flow.
dj_0x7c0dj_0x7c0
@Deepak Pandey 13

With such solution you will meet 150 DML statements issued limit (https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_gov_limits.htm" target="_blank). I'm processing e.g. 3000 records (counterparties=Accounts).

Thanks for trying.
DJ
dj_0x7c0dj_0x7c0
@Ajay Ghuge 6
I did it, but it shows drawback of Force.com :|
Readers please choose better solution, or propose another.

1. solution (Single Statement Using Foreign Keys):
public class MyProcessor implements Queueable{
	public void execute(QueueableContext context) {

		List<Stage_Object__c> counterparties = [SELECT Counterparty_Identifier__c,
													   Full_Name__c, Processed__c
                                              	FROM Stage_Object__c
                            				   	WHERE Processed__c = false LIMIT 3300];
		List<Account> accList = new List<Account>();
		List<Marker__c> markerList = new List<Marker__c>();
		for (Stage_Object__c so : counterparties){
			Account aReference = new Account(techExtId__c = so.Counterparty_Identifier__c);
			Account na = new Account(techExtId__c = so.Counterparty_Identifier__c,
									 Name = so.Full_Name__c);
			accList.add(na);
			Marker__c m = new Marker__c(Account_ref__r = aReference,
										Identifier__c = so.Counterparty_Identifier__c);
			markerList.add(na);
			so.Processed__c = true;
		}
		try {
			insert accList;
			insert markerList;
			update counterparties;
		} catch (DMLException de){
			//handling Exception
		}
	}
}

 
dj_0x7c0dj_0x7c0
2. solution (looping the same twice and List order usage):
public class MyProcessor implements Queueable{
	public void execute(QueueableContext context) {

		List<Stage_Object__c> counterparties = [SELECT Counterparty_Identifier__c,
													   Full_Name__c, Processed__c
                                              	FROM Stage_Object__c
                            				   	WHERE Processed__c = false LIMIT 3300];
		List<Account> accList = new List<Account>();
		List<Marker__c> markerList = new List<Marker__c>();
//first loop
		for (Integer i=0; i < counterparties.size() ; i += 1){
			Account na = new Account(Name = counterparties[i].Full_Name__c);
			accList(na);
		}
		try {
			insert accList;
		} catch (DMLException de){
			//handling Exception
		}
//second loop
		for (Integer i=0; i < counterparties.size() ; i += 1){
			Marker__c m = new Marker__c(Account_ref__c = accList[i].Id,
										Identifier__c = counterparties[i].Counterparty_Identifier__c);
			markerList.add(m);
			counterparties[i].Processed__c = true;
		}
		try {
			insert markerList;
			update counterparties;
		} catch (DMLException de){
			//handling Exception
		}
	}
}
Deepak Pandey 13Deepak Pandey 13
public class MyProcessor implements Queueable{
    public void execute(QueueableContext context) 
    {

        List<Stage_Object__c> counterparties = [SELECT Counterparty_Identifier__c, Full_Name__c, Processed__c
                                                  FROM Stage_Object__c
                                                   WHERE Processed__c = false LIMIT 3300];
                                                
        list<Stage_Object__c> lststro = new list<Stage_Object__c>();
        List<Account> accList = new List<Account>();
        List<Marker__c> markerList = new List<Marker__c>();
        map<Name,Stage_Object__c> mapstring =  new map<Name,Stage_Object__c>();
        for (Stage_Object__c so : counterparties){
            mapstring.put(so.name,so);
            Account na = new Account(Name = so.Full_Name__c);
            accList.add(na);
            so.Processed__c = true;
            lststro.add(so);
        }
        
        if(lststro.size()>0)
            update lststro;
        
        if(accList.size()>0)
            insert accList;    
        
        for(Account ObjAcc : accList)
        {
            
            Marker__c m = new Marker__c();
            m.Identifier__c = mapstring.get(ObjAcc.name).Counterparty_Identifier__c;
            m.Account_ref__c = ObjAcc.id;            
            markerList.add(m);
        }
        
        if(markerList.size()>0)
            insert markerList;    
        
    }
}