function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Michael MMichael M 

Automatically add and insert parent (lookup) record


Hello,
My use case is that I am importing csv files through data import wizard to land on a custom object ("Network Statistic"), which has a lookup field to its parent object, namely "Account" (custom field called "Referral Account"). This is an insert (not upsert) import. I need the lookup field to populate for 100% of the records, and therefore if the parent account does not exist, I need my Trigger to add and insert that account, before my Network Statistic record is added. Below is some sample code that doesn't work but may be on the right track... 

trigger NetworkStatsTrigger on Network_Statistic__c (before insert) {
 Map<String,Network_Statistic__c> hospNumbers = new Map<String,Network_Statistic__c>();
 Set<String> presentHospNumber = new Set<String>();
 List<Account> accList = new List<Account>();
 for(Network_Statistic__c d : Trigger.new){
  if(d.Referral_account__c != null && d.Referral_account__r.Hospital_number__c != null){
    hospNumbers.put(d.Referral_account__r.Hospital_number__c ,d);
  }
 }
 List<Account> presentAccounts = [SELECT Id,hospital_number__c FROM Account WHERE hospital_Number__c in : hospNumbers.keySet()];
 for(Account a : presentAccounts){
     presentHospNumber.add(a.hospital_number__c);
 }
 Set<String> hospNumberToAdd = hospNumbers.keySet(); 
 hospNumberToAdd.removeAll(presentHospNumber); 
 for(String hospNum : hospNumberToAdd){
  Account a = new Account();
  a.Name = hospNumbers.get(hospNum).hospital_name__c;
  a.AccountNumber = hospNum ;
  a.hospital_number__c = hospNum ;
  accList.add(a);
 }
 insert accList;
}
 
Best Answer chosen by Michael M
Abdul KhatriAbdul Khatri
Here is the code, please make field name adjustments if needed otherwise this should resolve your issue
  
trigger NetworkStatsTrigger on Network_Statistic__c (before insert) 
{
	Map<String,Network_Statistic__c> hospNumbers = new Map<String,Network_Statistic__c>();
    Map<String, Account> accountMap = new Map<String, Account>();
    List<Account> accountToInsertList = new List<Account>();
    
    for(Network_Statistic__c ns : Trigger.new)
    {
        if(ns.Hospital_number__c != null)
        	hospNumbers.put(ns.Hospital_number__c, ns);
    }

    if(hostNumbers.isEmpty()) return;
    
    Set<String> hospNumbersSet = hospNumbers.keySet();
    for(Account account : [SELECT Id, hospital_number__c FROM Account WHERE hospital_Number__c = :hospNumbersSet])
    {
        accountMap.put(account.hospital_number__c, account);
    }
    
    for(String hostnumber : hospNumbers.keySet())
    {    
        if(!accountMap.containsKey(hostnumber))
        {
            Account accoutToInsert = new Account();
            accountToInsert.Name = hospNumbers.get(hostnumber).Hospital_Name__c;
            accountToInsert.AccountNumber = hospNumbers.get(hostnumber).hostnumber;
            accountToInsert.hospital_number__c = hospNumbers.get(hostnumber).hostnumber;
        	accountMap.put(accountToInsert.hospital_number__c, accountToInsert);            
        }
        
        accountToInsertList.add(accountToInsert);
    }
    
    if(!accountToInsertList.isEmpty()) Database.insert(accountToInsertList);

    for(Network_Statistic__c ns : Trigger.new)
    {
        ns.Referral_account__c = accountMap.get(ns.Hospital_number__c).Id;
    }    
}

 

All Answers

Abdul KhatriAbdul Khatri
Hi Michael,

Please find the attached screen shot of the tigger with the comments

User-added image


One question, if the Account (Lookup) doesn't exist, where we can find the details to insert a new account like Name etc.
Michael MMichael M

Abdul, Thank you for the comments. How would you restructure it to work properly?

The Account (Lookup) fields are mentioned there at the bottom of the code-- Name, AccountNumber, hospital_number__c. Is that what you are asking?
Abdul KhatriAbdul Khatri
Yes,

In case if the account doesn't exist, I am expecting the referral account lookup to be blank and I am required to create that account with those fields information (where I can that from). After Insert I need to update the lookup on the Network_Static__c object (this code also missing in your code above)
Michael MMichael M
The data for those fields are included in the csv files that I'm importing, however my import is to Network Statistics, not Accounts. So I'm still not sure how to write this code properly... 
Abdul KhatriAbdul Khatri
So If I understood correctly you csv file contains a column Referral_account__c with 18 Character Id which is some cases is null which is what you need the solution for but in that case I guess you have the following info in bold to create an account and tie back to Network Statistics SObject.

  Account a = new Account();
  a.Name = hospNumbers.get(hospNum).hospital_name__c;
  a.AccountNumber = hospNum ;
  a.hospital_number__c = hospNum ;

Let me know if this understanding is correct?
Abdul KhatriAbdul Khatri
Also one more question since you were SOQLing the Account based on the hospital number, Is that unique per Account?
Michael MMichael M
Regarding your first comment, no, my csv does NOT have a Referral Account column, and does not have any salesforce id's in it. It has a column with the name of the account and a column with a number (hospital number) which I made an external id field. When I "insert" my Network Stats records import from data import wizard, it tries to populate the Referral Account field based on the hospital number. If there is an existing account with the hospital number, then it automatically populates that field. However, if there is no existing account, I need to insert the account before my Network Stats record is inserted, so that the Network Stats record can then auto-populate the Referral Account field. 

So regarding your second comment, yes, the hospital number is unique per account, and is an external id. 
 
Abdul KhatriAbdul Khatri
hmm
User-added image
Michael MMichael M
Right, my code as it is might be totally meaningless... 
Abdul KhatriAbdul Khatri
OK So that account and hospital number column from csv file are mapped to which field of Network Statistic Custom Object during Import?
Michael MMichael M
Network Statistic has a field called "Hospital Name" and a field called "Hospital Number". 
 
Abdul KhatriAbdul Khatri
Here is the code, please make field name adjustments if needed otherwise this should resolve your issue
  
trigger NetworkStatsTrigger on Network_Statistic__c (before insert) 
{
	Map<String,Network_Statistic__c> hospNumbers = new Map<String,Network_Statistic__c>();
    Map<String, Account> accountMap = new Map<String, Account>();
    List<Account> accountToInsertList = new List<Account>();
    
    for(Network_Statistic__c ns : Trigger.new)
    {
        if(ns.Hospital_number__c != null)
        	hospNumbers.put(ns.Hospital_number__c, ns);
    }

    if(hostNumbers.isEmpty()) return;
    
    Set<String> hospNumbersSet = hospNumbers.keySet();
    for(Account account : [SELECT Id, hospital_number__c FROM Account WHERE hospital_Number__c = :hospNumbersSet])
    {
        accountMap.put(account.hospital_number__c, account);
    }
    
    for(String hostnumber : hospNumbers.keySet())
    {    
        if(!accountMap.containsKey(hostnumber))
        {
            Account accoutToInsert = new Account();
            accountToInsert.Name = hospNumbers.get(hostnumber).Hospital_Name__c;
            accountToInsert.AccountNumber = hospNumbers.get(hostnumber).hostnumber;
            accountToInsert.hospital_number__c = hospNumbers.get(hostnumber).hostnumber;
        	accountMap.put(accountToInsert.hospital_number__c, accountToInsert);            
        }
        
        accountToInsertList.add(accountToInsert);
    }
    
    if(!accountToInsertList.isEmpty()) Database.insert(accountToInsertList);

    for(Network_Statistic__c ns : Trigger.new)
    {
        ns.Referral_account__c = accountMap.get(ns.Hospital_number__c).Id;
    }    
}

 
This was selected as the best answer
Abdul KhatriAbdul Khatri
Hi Michael,

Did you try the code?
Michael MMichael M
Hi Abdul, I tried it, but I still received this error: 
INVALID_FIELD:Foreign key external ID: 10001 not found for field Hospital_Number__c in entity Account:--


By the way, this is the code I used (the same you sent me, just substituting the real field names)---

trigger NetworkStatsTrigger on Network_Statistic__c (before insert) 
{
    Map<String,Network_Statistic__c> hospNumbers = new Map<String,Network_Statistic__c>();
    Map<String, Account> accountMap = new Map<String, Account>();
    List<Account> accountToInsertList = new List<Account>();
    
    for(Network_Statistic__c ns : Trigger.new)
    {
        if(ns.Hospital_number__c != null)
            hospNumbers.put(ns.Hospital_number__c, ns);
    }

    if(hospNumbers.isEmpty()) return;
    
    Set<String> hospNumbersSet = hospNumbers.keySet();
    for(Account account : [SELECT Id, hospital_number__c FROM Account WHERE hospital_Number__c = :hospNumbersSet])
    {
        accountMap.put(account.hospital_number__c, account);
    }
    
    for(String hostnumber : hospNumbers.keySet())
    {    
        if(!accountMap.containsKey(hostnumber))
        {
            Account accountToInsert = new Account();
            accountToInsert.Name = hospNumbers.get(hostnumber).Hospital_Name__c;
            accountToInsert.AccountNumber = hospNumbers.get(hostnumber).Account_Number__c;
            accountToInsert.hospital_number__c = hospNumbers.get(hostnumber).Account_Number__c;
            accountMap.put(accountToInsert.hospital_number__c, accountToInsert);            
        
        
            accountToInsertList.add(accountToInsert); }
    }
    
    if(!accountToInsertList.isEmpty()) Database.insert(accountToInsertList);

    for(Network_Statistic__c ns : Trigger.new)
    {
        ns.Referral_account__c = accountMap.get(ns.Hospital_number__c).Id;
    }    
}

 
Abdul KhatriAbdul Khatri
Can you please share the updated code, Schema for the Network Statistics and is possible debug log for the line number the error is happening?
Michael MMichael M
Yes. Here is the schema:
User-added image

Here is a sample of the csv:
User-added image
Here is what seems to be the source of the problem, the Data Import Wizard where I need to choose a field to map the Referral Account field based on:
User-added image
And here is the code:
trigger NetworkStatsTrigger on Network_Statistic__c (before insert) 
{
    Map<String,Network_Statistic__c> hospNumbers = new Map<String,Network_Statistic__c>();
    Map<String, Account> accountMap = new Map<String, Account>();
    List<Account> accountToInsertList = new List<Account>();
    
    for(Network_Statistic__c ns : Trigger.new)
    {
        if(ns.Hospital_number__c != null)
            hospNumbers.put(ns.Hospital_number__c, ns);
    }

    if(hospNumbers.isEmpty()) return;
    
    Set<String> hospNumbersSet = hospNumbers.keySet();
    for(Account account : [SELECT Id, hospital_number__c FROM Account WHERE hospital_Number__c = :hospNumbersSet])
    {
        accountMap.put(account.hospital_number__c, account);
    }
    
    for(String hostnumber : hospNumbers.keySet())
    {    
        if(!accountMap.containsKey(hostnumber))
        {
            Account accountToInsert = new Account();
            accountToInsert.Name = hospNumbers.get(hostnumber).Hospital_Name__c;
            accountToInsert.AccountNumber = hospNumbers.get(hostnumber).Account_Number__c;
            accountToInsert.hospital_number__c = hospNumbers.get(hostnumber).Account_Number__c;
            accountMap.put(accountToInsert.hospital_number__c, accountToInsert);            
        
        
            accountToInsertList.add(accountToInsert); }
    }
    
    if(!accountToInsertList.isEmpty()) Database.insert(accountToInsertList);

    for(Network_Statistic__c ns : Trigger.new)
    {
        ns.Referral_account__c = accountMap.get(ns.Hospital_number__c).Id;
    }    
}
Abdul KhatriAbdul Khatri
Can you run the Import Wizard without assigning that field and see how the trigger works?

User-added image
Michael MMichael M

It does! Excellent! Thank you. 

If my csv contains some records that already have a parent Account, and some that don't, is the solution to NOT assign that field? What would happen if the parent Account already exists? Would it add a duplicate?
Abdul KhatriAbdul Khatri
It should not create duplicated as it is checking before creating it, so we should be good.

Can you please be kind enough to mark the answer best?
Michael MMichael M
Great. Just marked it as best. Thanks again.
Mark StvenMark Stven
Can i use this on my Sub contracting service (https://networkcableinstall.cablingcompany.co.za/sub-contracting/) base website? Actually i have huge data of my clients that contain large number of information regarding differnet type , i also want to update regularly . So, can i use this algorithm?