+ Start a Discussion
John GerhardJohn Gerhard 

Dynamic SOQL and Dynamic fields from a variable?

Hello Everyone,

I can't figure this one out. Basically, I am assigning the API label of a field on my opportunity to a product field to easily create a list called oppFieldsList. Run a SOQL to grab all the fields and then go through and update those fields on the opportunity with the unit price. Here is my code snippet, hopefully someone has the right idea so I don't have to create if elses for 150 fields >.< Thank you in advance!
 
trigger OpportunityLineItemAfterUpdateAfterInsert on OpportunityLineItem (after update, after insert) {

    List<ID> oppIDList = new List<ID>();
    List<String> oppFieldsList = new List<String>();
    List<Opportunity> oppFieldsToUpdate = new List<Opportunity>();
    Integer i = 0;
    
    for (OpportunityLineItem oppLI : Trigger.new) {
        oppIDList.add(oppLI.OpportunityId);
        oppFieldsList.add(oppLI.Opportunity_Product_Field__c);
    }
    
    List<Opportunity> oppFields = [SELECT oppFieldsList FROM Opportunity WHERE Id IN: oppIDList];
    
    for (OpportunityLineItem oppLI : Trigger.new) {
        oppFieldsToUpdate.add(oppFields.put(oppFieldsList[i],oppLI.UnitPrice));
        i++;
    }
    
    update oppFieldsToUpdate;
}

 
Best Answer chosen by John Gerhard
John GerhardJohn Gerhard
I think I just figure it out. This will dynamically add fields and remove fields based on an API label we pass into the pricebook as a string and then looks it up accordingly and then pastes the values back to the opportunity dynamically. I tested it a few times and it looks like it works. woo.
 
trigger OpportunityLineItemAfterUpdateAfterInsert on OpportunityLineItem (after update, after insert) {

    System.debug('Price Update 1');
    List<ID> oppIDList = new List<ID>();
    List<String> oppFieldsList = new List<String>();
    Map<ID,Opportunity> oppFieldsToUpdate = new MAP<ID,Opportunity>();
    Integer i = 0;
    String soqlQueryList = ''; 
     System.debug('Price Update 2');
    for (OpportunityLineItem oppLI : Trigger.new) {
        oppIDList.add(oppLI.OpportunityId);
        oppFieldsList.add(oppLI.Opportunity_Product_Field__c);
    }
    System.debug('PriceUpdate 2.5');
    
    set<ID> oppDeDupeIDSet = new set<ID>();
    List<ID> oppIDListDeDuped = new List<ID>();
    oppDeDupeIDSet.addAll(oppIDList);
    oppIDListDeDuped.addAll(oppDeDupeIDSet);
    
    System.debug('Price Update 3');
    System.debug(oppFieldsList);
    System.debug('3.1');
    set<String> myset = new set<String>();
    List<String> oppFieldsListDeDupe = new List<String>();
    myset.addAll(oppFieldsList);
    oppFieldsListDeDupe.addAll(myset);
    System.debug('3.2');
    system.debug(oppFieldsListDeDupe);
    System.debug('3.3');
    for (Integer p=0; p < oppFieldsListDeDupe.size()-1;p++){
        soqlQueryList += oppFieldsListDeDupe[p] + ',';
    }
     System.debug('Price Update 3.5');
    
    soqlQueryList += oppFieldsListDeDupe[oppFieldsListDeDupe.size()-1];
    
     System.debug('Price Update 4');
    
    String soqlQuery = 'Select ' + soqlQueryList + ' FROM Opportunity WHERE ID IN: oppIDListDeDuped';
    
     System.debug('Price Update 5');
    System.debug(oppIDListDeDuped.size());
    
    List<Opportunity> oppsFields = database.query(soqlQuery);
    
    for(Integer p=0; p< oppIDListDeDuped.size();p++){
        System.debug('Price Update 6');
        Opportunity opp = new Opportunity();
        List<Opportunity> helperList = new List<Opportunity>();
    for (OpportunityLineItem oppLI : Trigger.new) {
        //Opportunity opp = new Opportunity();
        opp.id = oppLI.OpportunityId;
        opp.put(oppLI.Opportunity_Product_Field__c , oppLI.UnitPrice);
        helperList.add(opp);
    } 
        oppFieldsToUpdate.putAll(helperList);
}
     System.debug('Price Update 7');
    System.debug(oppFieldsToUpdate);
    update oppFieldsToUpdate.Values();
}

 

All Answers

John GerhardJohn Gerhard
Update, I am now here. I just need to figure out how to use a variable as a field name. However, I am not getting this error "Method does not exist or incorrect signature: void add(Object) from the type List.
 
trigger OpportunityLineItemAfterUpdateAfterInsert on OpportunityLineItem (after update, after insert) {

    List<ID> oppIDList = new List<ID>();
    List<String> oppFieldsList = new List<String>();
    List<Opportunity> oppFieldsToUpdate = new List<Opportunity>();
    Integer i = 0;
    String soqlQueryList = ''; 
    
    for (OpportunityLineItem oppLI : Trigger.new) {
        oppIDList.add(oppLI.OpportunityId);
        oppFieldsList.add(oppLI.Opportunity_Product_Field__c);
    }
    
    for (Integer p=0; p < oppFieldsList.size()-1;p++){
        soqlQueryList += oppFieldsList[p] + ',';
    }
    soqlQueryList += oppFieldsList[oppFieldsList.size()-1];
    
    String soqlQuery = 'Select' + soqlQueryList + 'FROM Opportunity WHERE ID IN: oppIDList';
    
    List<Opportunity> oppsFields = database.query(soqlQuery);
    
    for (OpportunityLineItem oppLI : Trigger.new) {
        oppFieldsToUpdate.add(oppsFields[i].put(oppLI.Opportunity_Product_Field__c,oppLI.UnitPrice));
        i++;
    }
    
    update oppFieldsToUpdate;

}

 
Amit Chaudhary 8Amit Chaudhary 8
I am not sure with your requirement but may be below post will help you
1) http://amitsalesforce.blogspot.com/2014/11/dynamic-field-mapping-using-custom.html

Try to update your code like below
trigger OpportunityLineItemAfterUpdateAfterInsert on OpportunityLineItem (after update, after insert) 
{

    List<ID> oppIDList = new List<ID>();
    List<String> oppFieldsList = new List<String>();
    List<Opportunity> oppFieldsToUpdate = new List<Opportunity>();
	
    Integer i = 0;
    String soqlQueryList = ''; 
    
    for (OpportunityLineItem oppLI : Trigger.new) 
	{
        oppIDList.add(oppLI.OpportunityId);
        oppFieldsList.add(oppLI.Opportunity_Product_Field__c);
    }
    
    for (Integer p=0; p < oppFieldsList.size()-1;p++)
	{
        soqlQueryList += oppFieldsList[p] + ',';
    }
    soqlQueryList += oppFieldsList[oppFieldsList.size()-1];
    
    String soqlQuery = 'Select' + soqlQueryList + 'FROM Opportunity WHERE ID IN: oppIDList';
    
    List<Opportunity> oppsFields = database.query(soqlQuery);
    
    for (OpportunityLineItem oppLI : Trigger.new) 
	{
		Opportunity opp = new Opportunity();
		opp.id = oppLI.OpportunityId;
		opp.put(oppLI.Opportunity_Product_Field__c , oppLI.UnitPrice);
        oppFieldsToUpdate.add(opp);
        i++;
    }
	
    update oppFieldsToUpdate;
}

Let us know if this will help  you
 
John GerhardJohn Gerhard
Amit, I used your code and then added some additional code in because duplicate values were getting passed into the database query so I had to clean those up. It seems like we are finally to the update list. Sooo... I am assuming this is the last issue, but I am getting an error on the update that says "caused by: System.ListException: Duplicate id in list: 006c000000GCJ3oAAH" So I am not sure how to dedupe a list of objects to be updated...? We are updating mutliple fields on the same object but it is add them as additional entries on the list. We need to compress the list to update all the fields that need to be updated at once for each ID. Here is my updated code.
 
trigger OpportunityLineItemAfterUpdateAfterInsert on OpportunityLineItem (after update, after insert) {

    System.debug('Price Update 1');
    List<ID> oppIDList = new List<ID>();
    List<String> oppFieldsList = new List<String>();
    List<Opportunity> oppFieldsToUpdate = new List<Opportunity>();
    Integer i = 0;
    String soqlQueryList = ''; 
     System.debug('Price Update 2');
    for (OpportunityLineItem oppLI : Trigger.new) {
        oppIDList.add(oppLI.OpportunityId);
        oppFieldsList.add(oppLI.Opportunity_Product_Field__c);
    }
    System.debug('PriceUpdate 2.5');
    
    set<ID> oppDeDupeIDSet = new set<ID>();
    List<ID> oppIDListDeDuped = new List<ID>();
    oppDeDupeIDSet.addAll(oppIDList);
    oppIDListDeDuped.addAll(oppDeDupeIDSet);
    
    System.debug('Price Update 3');
    System.debug(oppFieldsList);
    System.debug('3.1');
    set<String> myset = new set<String>();
    List<String> oppFieldsListDeDupe = new List<String>();
    myset.addAll(oppFieldsList);
    oppFieldsListDeDupe.addAll(myset);
    System.debug('3.2');
    system.debug(oppFieldsListDeDupe);
    System.debug('3.3');
    for (Integer p=0; p < oppFieldsListDeDupe.size()-1;p++){
        soqlQueryList += oppFieldsListDeDupe[p] + ',';
    }
     System.debug('Price Update 3.5');
    
    soqlQueryList += oppFieldsListDeDupe[oppFieldsListDeDupe.size()-1];
    
     System.debug('Price Update 4');
    
    String soqlQuery = 'Select ' + soqlQueryList + ' FROM Opportunity WHERE ID IN: oppIDListDeDuped';
    
     System.debug('Price Update 5');
    
    List<Opportunity> oppsFields = database.query(soqlQuery);
    
     System.debug('Price Update 6');
    for (OpportunityLineItem oppLI : Trigger.new) {
        Opportunity opp = new Opportunity();
        opp.id = oppLI.OpportunityId;
        opp.put(oppLI.Opportunity_Product_Field__c , oppLI.UnitPrice);
        oppFieldsToUpdate.add(opp);
        i++;
    } 
     System.debug('Price Update 7');

    update oppFieldsToUpdate;
}

 
Amit Chaudhary 8Amit Chaudhary 8
Please try to update code like below
trigger OpportunityLineItemAfterUpdateAfterInsert on OpportunityLineItem (after update, after insert) {

    System.debug('Price Update 1');
    List<ID> oppIDList = new List<ID>();
    List<String> oppFieldsList = new List<String>();
    List<Opportunity> oppFieldsToUpdate = new List<Opportunity>();
    Integer i = 0;
    String soqlQueryList = ''; 
     System.debug('Price Update 2');
    for (OpportunityLineItem oppLI : Trigger.new) {
        oppIDList.add(oppLI.OpportunityId);
        oppFieldsList.add(oppLI.Opportunity_Product_Field__c);
    }
    System.debug('PriceUpdate 2.5');
    
    set<ID> oppDeDupeIDSet = new set<ID>();
    List<ID> oppIDListDeDuped = new List<ID>();
    oppDeDupeIDSet.addAll(oppIDList);
    oppIDListDeDuped.addAll(oppDeDupeIDSet);
    
    System.debug('Price Update 3');
    System.debug(oppFieldsList);
    System.debug('3.1');
    set<String> myset = new set<String>();
    List<String> oppFieldsListDeDupe = new List<String>();
    myset.addAll(oppFieldsList);
    oppFieldsListDeDupe.addAll(myset);
    System.debug('3.2');
    system.debug(oppFieldsListDeDupe);
    System.debug('3.3');
    for (Integer p=0; p < oppFieldsListDeDupe.size()-1;p++){
        soqlQueryList += oppFieldsListDeDupe[p] + ',';
    }
     System.debug('Price Update 3.5');
    
    soqlQueryList += oppFieldsListDeDupe[oppFieldsListDeDupe.size()-1];
    
     System.debug('Price Update 4');
    
    String soqlQuery = 'Select ' + soqlQueryList + ' FROM Opportunity WHERE ID IN: oppIDListDeDuped';
    
     System.debug('Price Update 5');
    
    List<Opportunity> oppsFields = database.query(soqlQuery);
    
     System.debug('Price Update 6');
	Set<Id> setOppId = new Set<Id>();  
    for (OpportunityLineItem oppLI : Trigger.new) 
	{
		if( setOppId.contains(oppLI.OpportunityId) == false )
		{
			Opportunity opp = new Opportunity();
			opp.id = oppLI.OpportunityId;
			opp.put(oppLI.Opportunity_Product_Field__c , oppLI.UnitPrice);
			oppFieldsToUpdate.add(opp);
			i++;
			setOppId.add(oppLI.OpportunityId);
		}	
    }
    System.debug('Price Update 7');
	if(oppFieldsToUpdate.size() > 0 )
	{
		update oppFieldsToUpdate;
	}	
}

Let us know if this will work
John GerhardJohn Gerhard
I think I just figure it out. This will dynamically add fields and remove fields based on an API label we pass into the pricebook as a string and then looks it up accordingly and then pastes the values back to the opportunity dynamically. I tested it a few times and it looks like it works. woo.
 
trigger OpportunityLineItemAfterUpdateAfterInsert on OpportunityLineItem (after update, after insert) {

    System.debug('Price Update 1');
    List<ID> oppIDList = new List<ID>();
    List<String> oppFieldsList = new List<String>();
    Map<ID,Opportunity> oppFieldsToUpdate = new MAP<ID,Opportunity>();
    Integer i = 0;
    String soqlQueryList = ''; 
     System.debug('Price Update 2');
    for (OpportunityLineItem oppLI : Trigger.new) {
        oppIDList.add(oppLI.OpportunityId);
        oppFieldsList.add(oppLI.Opportunity_Product_Field__c);
    }
    System.debug('PriceUpdate 2.5');
    
    set<ID> oppDeDupeIDSet = new set<ID>();
    List<ID> oppIDListDeDuped = new List<ID>();
    oppDeDupeIDSet.addAll(oppIDList);
    oppIDListDeDuped.addAll(oppDeDupeIDSet);
    
    System.debug('Price Update 3');
    System.debug(oppFieldsList);
    System.debug('3.1');
    set<String> myset = new set<String>();
    List<String> oppFieldsListDeDupe = new List<String>();
    myset.addAll(oppFieldsList);
    oppFieldsListDeDupe.addAll(myset);
    System.debug('3.2');
    system.debug(oppFieldsListDeDupe);
    System.debug('3.3');
    for (Integer p=0; p < oppFieldsListDeDupe.size()-1;p++){
        soqlQueryList += oppFieldsListDeDupe[p] + ',';
    }
     System.debug('Price Update 3.5');
    
    soqlQueryList += oppFieldsListDeDupe[oppFieldsListDeDupe.size()-1];
    
     System.debug('Price Update 4');
    
    String soqlQuery = 'Select ' + soqlQueryList + ' FROM Opportunity WHERE ID IN: oppIDListDeDuped';
    
     System.debug('Price Update 5');
    System.debug(oppIDListDeDuped.size());
    
    List<Opportunity> oppsFields = database.query(soqlQuery);
    
    for(Integer p=0; p< oppIDListDeDuped.size();p++){
        System.debug('Price Update 6');
        Opportunity opp = new Opportunity();
        List<Opportunity> helperList = new List<Opportunity>();
    for (OpportunityLineItem oppLI : Trigger.new) {
        //Opportunity opp = new Opportunity();
        opp.id = oppLI.OpportunityId;
        opp.put(oppLI.Opportunity_Product_Field__c , oppLI.UnitPrice);
        helperList.add(opp);
    } 
        oppFieldsToUpdate.putAll(helperList);
}
     System.debug('Price Update 7');
    System.debug(oppFieldsToUpdate);
    update oppFieldsToUpdate.Values();
}

 
This was selected as the best answer