+ Start a Discussion
JeremyBJeremyB 

$100 offer for help writing "simple" trigger?

Looking for a trigger solution to overcome one or both of these point-n-click drawbacks:

 

1) standard workflow not smart enough to update child records. (Doesn't know which child records to update. In this case, I want to update ALL child records (Quote Line Items).

 

2) Workflows cannot be fired off a formula field change. 

 

If you think you can help teach me how to write this relatively simple trigger, please contact me at jbardet @ gmail.com.

 

I'd be happy to offer $100 for your time, and I apologize for my ignorance if I have underestimated the complexity of this trigger.

 

Regards,

Jeremy

skype: jeremybardet

mtbclimbermtbclimber

 


JeremyB wrote:

 

....and I apologize for my ignorance if I have underestimated the complexity of this trigger.


Depending on your requirements this may actually be significantly more than just "a simple trigger". Do you want it to run in production, for example? :)  How many records are in your object? More than 10k, if so then this is really one or more batch operations you are asking for not just a trigger - at least for 2)? How do you want errors to be handled? 

 

The logic sound fairly simple but this is anywhere from a few hours to a few days of effort depending on exactly what you want.

 

If you haven't already you might want to post this to the jobs board.

 

JeremyBJeremyB

mtbclimber - I want this trigger to affect only the record being modified, not all records in the ojbect.  We just implemented Real Time Quotes, so the Quote object is very new to us. In fact, we have less than 100 records in the object!

 

Update to project.. some more specifics.

 

Quote Object Fields:

Status  (picklist standard field)

 

Quote Line Item Fields:

standardprice__c   (currency)

Expedited_Price__c (currency)

UnitPrice   (Sales Price standard field)

Incl_Quote_Estimate__c  (Checkbox)

 

Goals:

For all line items that have Incl_Quote_Estimate__c = TRUE; when Quote Status = "Accepted - Standard" , update UnitPrice with value from standardprice__c.

For all line items that have Incl_Quote_Estimate__c = TRUE; when Quote Status = "Accepted - Expedited" , update UnitPrice with value from Expedited_Price__c.

 

 

I already heard back very promptly from Ritesh who drafted some sample code, but it didn't seem to work as intended.

 

Here is his code, (written without the logic of Incl_Quote_Estimate__c = TRUE since I didn't know I wanted this piece yesterday).

 

I'm not certain this code follows the same logic as I described above. Maybe you can help me determine that.

 

Thanks very much,

Jeremy

 

 

 

trigger QuoteAfter on Quote(after update)

{

 

Quote[] quotes = [Select Id, Status, Name, (Select Id, UnitPrice, standardprice__c, Expedited_Price__c from QuoteLineItems) from Quote where Id in :trigger.newMap.keySet()];

QuoteLineItem[] qlis = new QuoteLineItem[]{};

 

for(Quote q : quotes)

{

if(q.status == 'Approved - Standard')

for(QuoteLineItem qli : q.QuoteLineItems)

{

qli.UnitPrice = qli.standardprice__c;

qlis.add(qli);

}

 

else if(q.Status == 'Approved - Expedited')

for(QuoteLineItem qli : q.QuoteLineItems)

{

qli.UnitPrice = qli.Expedited_Price__c;

qlis.add(qli);

}

 

}

 

if(qlis != null && !qlis.isEmpty())

Database.update(qlis);

}

sfdcfoxsfdcfox

Here's my version.

 

 

trigger updateUnitPricesOnQuoteLineItems on Quote(after update) {
	// Items that we will update.
	List<QuoteLineItem> itemsToUpdate = new List<QuoteLineItem>();
	// Find all quote line items that match our interest.
	for(QuoteLineItem qli:[SELECT ID,QuoteId,StandardPrice__c,Expedited_Price__c FROM QuoteLineItem WHRE QuoteId IN :Trigger.new and Quote.Status IN ('Approved - Standard','Approved - Expedited') and Incl_Quote_Estimate__c = TRUE]) {
		// If Status is 'Standard', copy value to unit price.
		if(Trigger.newMap.get(qli.QuoteId).Status == 'Approved - Standard') {
			qli.UnitPrice = qli.StandardPrice__c;
			itemsToUpdate.add(qli);
		}
		// If Status is 'Expedited', copy value to unit price.
		if(Trigger.newMap.get(qli.QuoteId).Status == 'Approved - Expedited') {
			qli.UnitPrice = qli.Expedited_Price__c;
			itemsToUpdate.add(qli);
		}
		// Create batches of 200 line items.
		if(itemsToUpdate.size()==200) {
			update itemsToUpdate;
			itemsToUpdate.clear();
		}
	}
	// update any items left over (partial batch).
	update itemsToUpdate;
}

Note that this does not cover fringe cases like "what happens if the status is changed back to an unapproved state" or such. Try this in your Sandbox, and if you like the behavior, let me know, I'll do up your test method and such for you.

 

 

sfdcfoxsfdcfox

There's nothing terribly unsound about Ritesh's submission, however, but there were some unnecessary extras.

 

Clarifications: Ritesh uses a "subquery", which results in an extra SOQL call. This might not be a problem now, but if later you have many triggers, you might need that one extra query. Also, The query I use limits the items that are returned (there is also a row-item limit). The actual "logic" should have worked as expected.

 

Some other notes: You need an additional trigger on QuoteLineItems if you're allowing them to be modified after the status is approved, etc. Honestly, you might be best off getting in touch with a developer and having a live conversation.

JeremyBJeremyB

sfdcfox, thank you so much for your input.

Yes, I will need the records modifiable after intiial trigger.

 

re: your're version.

Error: Compile Error: expecting right square bracket, found 'QuoteId' at line 5 column 106

 

Tried troubleshooting myself comparing to syntax structure with Ritesh's code, but coudln't get it. 

 

 

sfdcfoxsfdcfox

I had a typo... "WHRE" should be WHERE. My "E" key is sticky.

JeremyBJeremyB

shame on me for not spotting that!
i'm a bit embarrassed to ask, but...
All I do with the code at this point in the sandbox is go to setup>customize>quote>triggers, create a new trigger, paste in the code, then make sure it's active, correct? I did that, and not seeing the sales price update.

 

-Jeremy

sfdcfoxsfdcfox

Here's actually a better way:

 

 

trigger updateUnitPricesOnQuoteLineItems on Quote(after update) {
    for(List<QuoteLineItem> qlis:[SELECT Id FROM QuoteLineItem WHERE QuoteId = :Trigger.new])
        update qlis;
}

 

trigger updateQuoteLineItems on QuoteLineItem (before insert, before update) {
    Map<Id,Quote> quotes = new Map<Id,Quote>();
    for(QuoteLineItem qli:Trigger.new)
        quotes.put(qli.QuoteId,null);
    quotes = new Map<Id,Quote>([SELECT Id,Status FROM Quote WHERE Id IN :quotes.keySet()]);
    for(QuoteLineItem qli:Trigger.new) {
        if(qli.Incl_Quote_Estimate__c && (quotes.get(qli.QuoteId).Status=='Approved - Standard' || quotes.get(qli.QuoteId).Status=='Approved - Expedited')) {
            if(quotes.get(qli.QuoteId).Status=='Approved - Standard') {
                qli.UnitPrice = qli.StandardPrice__c;
            }
            if(quotes.get(qli.QuoteId).Status=='Approvd - Expedited') {
                qli.UnitPrice = qli.Expedited_Price__c;
            }
        }
        else {
            qli.UnitPrice = null;
        }
    }
}

The quote line items update their own values, and all the quotes have to do is advise their children members to update. This still uses two queries, but most importantly, if the include checkbox is checked on a single item, it will properly update its unit price values.

 

 

 

Let me know what you think.

sfdcfoxsfdcfox

It's not "auto-magical" in the sense that just having the code will be enough. You have to actually trigger a change that would cause the trigger to fire. Since you have such a small set of data, you could do this: Your Name > Debug Log > Execute Apex....

 

 

 

for(Quote[] q:[select id from quote]) update q;

 

This works for up to about 7000 records (the transaction is often aborted around this size, despite governor limits).