Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Patrick Yang@SGPatrick Yang@SG 

How to realise "vlookup" function using Apex?

Dears,

I want to realize a function similar with "vlookup" with Apex and I tried coding like following but not correct.

Background：
I have 2 standard object: opportunities and quotes (lookup relationship, opportunities is father). In opportunities I have a picklist field named "City".

And I create a customized filed called "Destination information". For each value in the picklist "city", I have a record in "Destination information", named with the same picklist value.

Now I created a lookup function between Quotes and  "Destination information". I want to get the ID of that City in "Destination information" to Quotes.

For example: In opportunity there is 1 record with the "City" field value "Paris", and the record named "Paris" in "Destination information" has a ID 0062800000P6Dg6. I want to get that ID and give it to the lookup field "Destination_information" in the Quote records related to that opportunity.

Here following my code (not correct) 
trigger GetDestinationID on Quotes (before insert, before update) {
for (Quotes quotes : Trigger.new) {
    String desid = Quotes.Destination_information__c;
    List<Destination_information__c> Destination_information = [SELECT Id FROM Destination_information WHERE Quotes__r.Opportunity.City__c = :desid];
    if(Destination_information != null)
             Quotes.Destination_information__c = Destination_information[0].Id;
    }
}
Thanks in advance!
 
Best Answer chosen by Patrick Yang@SG
Ajay Ghuge 6Ajay Ghuge 6

Hi Patrick,

Assumed you have following structure in your org : 

Opportunity.City__c : Picklist  

Destinationinformation__c: a Custom object with Name or other custom field storing the city values.

Quote : Lookup to Destinationinformation__c : (Autofill this with Destinationinformation__c depend upon the value selected in related Opportunity.city__c)


You need to write before insert, before update trigger on Quote as follows :  
trigger QuoteTrigger on Quote (before insert,before update) {
	set<Id> setOpportunityId = new set<Id>();
    map<Id,string> mapOpportinutyWithCity = new map<Id,string>();
    for(Quote objQuote : trigger.new){
        setOpportunityId.add(objQuote.OpportunityId);
    }
    
    for(Opportunity objOpp : [select City__c,Id from Opportunity where Id in :setOpportunityId AND City__c != null]){
        mapOpportinutyWithCity.put(objOpp.Id,objOpp.City__c);
    }
    map<string,Id> mapCityDestinationId = new map<string,Id>();
    for(Destinationinformation__c objDestInfo : [select Name__c,Id from Destinationinformation__c where Name__c in :mapOpportinutyWithCity.values()]){
        mapCityDestinationId.put(objDestInfo.name__c,objDestInfo.Id);
    }
    
    for(Quote objQuote : trigger.new){
    	string strCity = mapOpportinutyWithCity.get(objQuote.OpportunityId);
        if(strCity !=null && mapCityDestinationId.containsKey(strCity)){
            objQuote.Destination_information__c = mapCityDestinationId.get(strCity);
        }
    }
}

Let me know if you require any help on this. Please mark it as resolved if it solves your problem so that others can get the benefit of it.

Regards,
Ajay

 

All Answers

LBKLBK
Hi Patrick,

Try the code below. 
trigger GetDestinationID on Quote (before insert, before update) {
	Map<String, Destination_information__c> mapDestinationInformation = new Map<String, Destination_information__c>();
	List<Destination_information__c> lstDestinationInformation = [SELECT Id, Name FROM lstDestinationInformation];
	for(Destination_information__c objDestinationInformation : lstDestinationInformation){
		if(!mapDestinationInformation.containsKey(objDestinationInformation.Name)) {
			mapDestinationInformation.put(objDestinationInformation.Name, objDestinationInformation);
		}
	}

	for (Quote objQuote : Trigger.new) {
		if(mapDestinationInformation.get(objQuote.Opportunity.City__c) != null)
			objQuote.Destination_information__c = (Destination_information__c)mapDestinationInformation.get(objQuote.Opportunity.City__c);
	}
}
Let me know how it goes.
 
Ajay Ghuge 6Ajay Ghuge 6

Hi Patrick,

Assumed you have following structure in your org : 

Opportunity.City__c : Picklist  

Destinationinformation__c: a Custom object with Name or other custom field storing the city values.

Quote : Lookup to Destinationinformation__c : (Autofill this with Destinationinformation__c depend upon the value selected in related Opportunity.city__c)


You need to write before insert, before update trigger on Quote as follows :  
trigger QuoteTrigger on Quote (before insert,before update) {
	set<Id> setOpportunityId = new set<Id>();
    map<Id,string> mapOpportinutyWithCity = new map<Id,string>();
    for(Quote objQuote : trigger.new){
        setOpportunityId.add(objQuote.OpportunityId);
    }
    
    for(Opportunity objOpp : [select City__c,Id from Opportunity where Id in :setOpportunityId AND City__c != null]){
        mapOpportinutyWithCity.put(objOpp.Id,objOpp.City__c);
    }
    map<string,Id> mapCityDestinationId = new map<string,Id>();
    for(Destinationinformation__c objDestInfo : [select Name__c,Id from Destinationinformation__c where Name__c in :mapOpportinutyWithCity.values()]){
        mapCityDestinationId.put(objDestInfo.name__c,objDestInfo.Id);
    }
    
    for(Quote objQuote : trigger.new){
    	string strCity = mapOpportinutyWithCity.get(objQuote.OpportunityId);
        if(strCity !=null && mapCityDestinationId.containsKey(strCity)){
            objQuote.Destination_information__c = mapCityDestinationId.get(strCity);
        }
    }
}

Let me know if you require any help on this. Please mark it as resolved if it solves your problem so that others can get the benefit of it.

Regards,
Ajay

 
This was selected as the best answer
Patrick Yang@SGPatrick Yang@SG
Thanks LBK & Ajay Ghuge 6,

I tried both of your code, if works when I made tiny modifications on Ajay's code (Name__c --> Name).


Der LBK,

For your code there seems two problems but I don't know how to correct:

Line 3： 
List<Destination_information__c> lstDestinationInformation = [SELECT Id, Name FROM lstDestinationInformation];
Invalid type: Schema.lstDestinationInformation

Line 12: 
objQuote.Destination_information__c = (Destination_information__c)mapDestinationInformation.get(objQuote.Opportunity.City__c);
Illegal assignment from Destination_information__c to Id