Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
tsalbtsalb 

SOQL - query related contact - update field - most recent date

So here's the use case.

 

I have a custom object (Order__c) which has a lookup to a Contact (Vendor_Name__c, on the Order Object). Contacts do not have lookups to Order.

 

On the Order, there is a Valuation_Contracted_Date__c field. A contact can have multiple Orders (10, 50, 150) - and i'd like for this trigger to always set on the Contact (Last_Engaged_Date__c) to the latest Contracted Date.

 

So I think I have something...but it needs help. Right now the error is "Save error: Didn't understand relationship 'Order__c' in FROM part of query call."

 

 

tldr; query all orders on that contact, order by valuation contracted date descending, limit 1. take that date and update the contact with it.

 

trigger lastEngagedDate on Order__c (before insert, before update) {

	try {
		for(Order__c o : Trigger.new) {
			
			Contact c = [SELECT Last_Engaged_Date__c, 
				(SELECT Valuation_Contracted_Date__c FROM Order__c WHERE c.Id = :Vendor_Name__c ORDER BY                                        Valuation_Contracted_Date__c DESC LIMIT 1) 
				FROM Contact 
					WHERE Id = :o.Vendor_Name__c];
			
			if(o.Status__c == 'Valuation Received') {		
				c.Last_Engaged_Date__c = o.Valuation_Contracted_Date__c;
				update c;			
			} 
		}
	}
	catch(Exception e) {		
	}	
	
}

 

Attempt #2 - still doesn't work?

 

trigger lastEngagedDate on Order__c (before insert, before update) {

	try {
		for(Order__c o : Trigger.new) {
			Contact c = [SELECT Id, Last_Engaged_Date__c FROM Contact WHERE Id = :o.Vendor_Name__c];	
			Order__c o = [SELECT Valuation_Contracted_Date__c FROM Order__c WHERE c.Id =:o.Vendor_Name__c
							ORDER BY Valuation_Contracted_Date__c
							LIMIT 1];
			
			if(o.Status__c == 'Valuation Received') {
				c.Last_Engaged_Date__c = o.Valuation_Contracted_Date__c;
				update c;
			} 
		}
	}
	catch(Exception e) {
	}
	
}

 


 


Best Answer chosen by Admin (Salesforce Developers) 
trictric

Hi ,

 

Try below given trigger.it should work for you .

 

 

trigger lastEngagedDate on Order__c (before insert, before update) {

set<id>must=new set<id>();
    
        for(Order__c o : Trigger.new) 
        {
            must.add(o.Vendor_Name__c);
        }
            
            Contact []arr=[select Last_Engaged_Date__c, (SELECT Valuation_Contracted_Date__c,status__c FROM Orders__r WHERE vendor_name__c in:must ORDER BY Valuation_Contracted_Date__c DESC LIMIT 1)from contact];
            
            System.debug('The value in the arr is'+arr);
            
            
            for(contact c:arr)
            {
                for(order__c copy:c.orders__r)
                {
                System.debug('The value in the copy type of variable is'+copy);
                
                       If(copy.status__c=='Valuation Received')
                        {
                        c.Last_Engaged_Date__c = copy.Valuation_Contracted_Date__c;
                        System.debug('The value in the'+c.Last_Engaged_Date__c); 
//                      Do something here
update arr;
                        }                
                }
            }
            }
            

All Answers

trictric

Since it is a custon object and you are using parent to chilg relationship so intead of Order__c' try using order__r.

I am guessing iy might work.Just give it a shot.

 

Thanks,

Trick
tsalbtsalb

Didn't work - i'm not even sure my query is right.

 

Contact has no lookup to order.

 

Order has a lookup to contact.
trictric

In place of order__c ,you need to type orders__r.

 

Order should be orders(Plural of order) and then append __(2 underscores and r to it).

 

again orders__r
trictric

Hi ,

 

Try below given trigger.it should work for you .

 

 

trigger lastEngagedDate on Order__c (before insert, before update) {

set<id>must=new set<id>();
    
        for(Order__c o : Trigger.new) 
        {
            must.add(o.Vendor_Name__c);
        }
            
            Contact []arr=[select Last_Engaged_Date__c, (SELECT Valuation_Contracted_Date__c,status__c FROM Orders__r WHERE vendor_name__c in:must ORDER BY Valuation_Contracted_Date__c DESC LIMIT 1)from contact];
            
            System.debug('The value in the arr is'+arr);
            
            
            for(contact c:arr)
            {
                for(order__c copy:c.orders__r)
                {
                System.debug('The value in the copy type of variable is'+copy);
                
                       If(copy.status__c=='Valuation Received')
                        {
                        c.Last_Engaged_Date__c = copy.Valuation_Contracted_Date__c;
                        System.debug('The value in the'+c.Last_Engaged_Date__c); 
//                      Do something here
update arr;
                        }                
                }
            }
            }
            

This was selected as the best answer
tsalbtsalb

Amazing Sir, that worked beautifully. I don't know if it's too much to ask, but how would I go about writing a test class for this? I'm currently reviewing the different pieces so I can better understand where I went wrong.

 

Thanks again!
tsalbtsalb

Ah, i've seem to run into an error on deployment. I think the problem is the Contact query is returning too many hits - how do i limit it down to only the contact on found on the order the trigger runs off of?

 

"Failure Message: "System.LimitException: Too many query rows: 50001", Failure Stack Trace: "Trigger.lastEngagedDate: line 7, column 1"

 

trigger lastEngagedDate on Order__c (before insert, before update) {

	set<id>must=new set<id>();
		for(Order__c o : Trigger.new) {
        	must.add(o.Vendor_Name__c);
        }
            Contact []arr=[SELECT Last_Engaged_Date__c, //Line 7 Where the error is
            		(SELECT Valuation_Contracted_Date__c, Status__c 
            							FROM Orders__r 
            							WHERE Vendor_Name__c IN:must 
            							ORDER BY Valuation_Contracted_Date__c DESC 
            							LIMIT 1)
            				FROM contact];
            System.debug('The value in the arr is'+arr);  
            for(contact c:arr) {
                for(order__c copy:c.orders__r) {
                	System.debug('The value in the copy type of variable is'+copy);
                    	if(copy.status__c=='Valuation Received') {
                        	c.Last_Engaged_Date__c = copy.Valuation_Contracted_Date__c;
                        	System.debug('The value in the'+c.Last_Engaged_Date__c); 
							//Do something here
							update arr;
                        }                
                }
            }

}

 Is this somethign close?

 

Contact []arr=[SELECT Last_Engaged_Date__c, 
		(SELECT Valuation_Contracted_Date__c, Status__c 
			FROM Orders__r ors
			WHERE Vendor_Name__c IN:must 
			ORDER BY Valuation_Contracted_Date__c DESC 
			LIMIT 1)
		FROM Contact
		WHERE Id IN:must]; //Where Clause?

 
Starz26Starz26

You need to filter the contact records based on the contacts in the order not the ID in must (vendor Name) that is unless vendor name is the contact lookup, if thats the case then your first solution should work

 

try

 

Contact []arr=[select Last_Engaged_Date__c, (SELECT Valuation_Contracted_Date__c,status__c FROM Orders__r WHERE vendor_name__c in:must ORDER BY Valuation_Contracted_Date__c DESC LIMIT 1)from contact Where ID IN (Select Contact__c **Or whatever you API name is for the contact lookup** From Order__c Where ID IN trigger.new)];

 
trictric

 

Hi,

 

That should work for you.It will only get you the contact associated with the order..

Just check if your trigger needs old and new contextual varible as you have been using update trigger.

 

There are two scenarios:-

 

1)You click on the edit button on the order table.But you did nit edit anything in the record and just saved it.It will still fire your update trigger which it should not

 

2)Secondly,Somebody clicked on edit and made changes to the record and then saved it which fires your update trigger.In this case it makes sense.

 

All I am trying to say is that you want your update trigger to fire only when somebody has actually made changes to the record and then saved it.

 

 

Thanks,

Trick,

 

 

 

Thanks,

Trick
tsalbtsalb

Makes sense guys - I streamlined the trigger query a little bit, and made it simpler. This is what is (currently) working. I don't have time to deploy or validate so i'll follow up after i get some of that done this weekend. THanks again all.

 

trigger lastEngagedDate on Order__c (after insert, after update) {

	set<id>must=new set<id>();
		for(Order__c o : Trigger.new) {
        	must.add(o.Vendor_Name__c);
        }
            Order__c o = [SELECT Valuation_Contracted_Date__c, Status__c, Vendor_Name__c
            				FROM Order__c
            				WHERE Vendor_Name__c IN:must 
            				ORDER BY Valuation_Contracted_Date__c DESC 
            				LIMIT 1];
		List<Contact> vendor = new List<Contact>();
        	if(o.Status__c=='Valuation Received') {
		vendor.add(new Contact(Id = o.Vendor_name__c, Last_Engaged_Date__c =   o.Valuation_Contracted_Date__c));
				update vendor;
           	}

}

 
tsalbtsalb

More intuitive - someone pointed out to me the point of the List is moot.

 

trigger lastEngagedDate on Order__c (after insert, after update) {

	set<id>must=new set<id>();
		for(Order__c o : Trigger.new) {
        	must.add(o.Vendor_Name__c);
        }
        Order__c ord = [SELECT Valuation_Contracted_Date__c, Status__c, Vendor_Name__c, Vendor_Name__r.Last_Engaged_Date__c
        				FROM Order__c
        				WHERE Vendor_Name__c IN:must 
        				ORDER BY Valuation_Contracted_Date__c DESC 
        				LIMIT 1];			
		if(ord.Valuation_Contracted_Date__c != null) {
			ord.Vendor_Name__r.Last_Engaged_Date__c = ord.Valuation_Contracted_Date__c;
			update ord.Vendor_Name__r;
		}

}