Apex Code Development You need to sign in to do that
Don't have an account?
Eager-2-Learn Basic insert trigger appending effective date to Opportunity name
Hello Everyone,
I want to append the effective date' month and year to the opportunity name when the user saves the record. For example, if the opportunity name is 'Joe's Pizza' and the effective date is '09/26/09' then when the record is saved and views it should show 'Joes Pizza 09/09'. The requirement is for the opportunity name to end with the mm/yy of the effective date and currently the users are manually typing this in and sometimes making typo's.
This is what I have but I know it does not work so I am asking for a little help. One thing I do not know is how to concatenate in Apex among many other things as you can tell. Is there a resource that gives all syntax for all built in methods, functions, etc for Apex.
trigger AppendMMYYEffectiveDate2OppName on Opportunity (before insert) {
Name = Name || Month(Effective_Date__c.month) || '/' & Effective_Date__c.year);
}
Hi Tom,
Thanks for your kind words - I feel like I have a long way to go before I reach Master status ;)
I'll try to summarise why we use the two 'for' loops:
Firstly, yes you are spot on - the 'Data Loader' could be bringing in large sets of data, it will do this by passing the data in batches, typically 200 at a time.
Now we need to understand Governer Limits. Basically, you need to minimise the number of database hits. Also, be aware that the object model does not support lazy-loading properties - that is, if you want to know what is stored in a field, you have to query it; Opportunity.AccountId may be known, but you will need to query the Account table to find out the Name of that Account object...
In your example below, you have this:
for (opportunity opp : Trigger.new) { // Code ommitted
Account a = [SELECT Name FROM Account WHERE Id =: opp.AccountId]; }
So what will happen if your trigger is firing because the Data Loader has just uploaded 200 opportunities? You will try to hit the database with 200 SELECT statements...
However, if you run through the Trigger.new collection *twice*, you can do just 1 SELECT statement. The first iteration through will build a collection (typically a Set) of Ids; then you can perform an isolated query to get a Map of data, indexed by Id; then you run the second pass through, only this time you know more about the objects in trigger.new because you have queried the missing pieces from the database.
The price for this is that your code is more verbose, and you are often duplicating effort...
it should look something like this:
// Use a Set because the Id will only be added if it does not exist // in the set already (ie no duplicates) Set<Id> accountIds; for (Opportunity opp : Trigger.new) { // Add the Account ID from the Opportunity into the set accountIds.add(opp.AccountId); } // Query the Account table for every account with an Id in the set // Do just one query, and put the results into a Map Map<Id, Account> accountMap = new Map<Id, Account> ([SELECT Name FROM Account WHERE Id in: accountIds]); for (Opportunity opp : Trigger.new) { // Retrieve the recently-queried Account details from the map, using the Account Id as the key Account a = accountMap.get(opp.AccountId); // Do work on opp }
ps Shame the formatting is so hit and miss - but it's better than all those emoticons that litter code directly in the post, eh?
I would recommend getting into the Apex developer guide - have a look at Map methods; Have a look at triggers, and also DateTime.format( ) - certainly the code I posted before was formatting dates fine for me ??
Send me a private message if you want to know any more about this... (and don't worry about taking up too much of my time! I'm happy to help if I can)
Cheers,
All Answers
I am feeling pretty good tonight--I figured most of it out by myself but I still need someone in my corner!
How do I get only the year as oppose to 08/2009, I want 08/09.
trigger AppendMMYYEffectiveDate2OppName on Opportunity (before insert) {
for (Opportunity opp : Trigger.new) {
String newName;
newName = opp.Name + ' ' + opp.Effective_Date__c.month() + '/' + opp.Effective_Date__c.year();
opp.Name = newName;
}
}
This is the Apex documentation.
Also - be aware that your trigger will be expecting some number of Opportunities to be inserted, so you need to write your code to cater for batches..
something like:
trigger AppendMMYYEffectiveDate2OppName on Opportunity (before insert) { for (Opportunity opp : trigger.new) { opp.Name += opp.Effective_Date__c.month + '/' + opp.Effective_Date__c.year; }}
You would need to put a bit more effort in if you want the leading zeros, ie 09/09 not 9/9...
HTH :)
Ian Randall
Hello HTH,
Thanks for the help; however, substring is not available from year(). So with that said, I am thinking of a cast?
String yearPart = opp.Effective_Date__c.year(); //I need to know how to cast because this give an error about integer
Let's assume you help me cast it to a string (yearPart)
Then could I do:
yearPart = yearPart.substring(2)
or
just put yearPart.substring(2) my concatenation line?
Hello again,
Here is what I have that works but I just need to keep it in the mm/yy format. For example, 8/09 needs to be 08/09. Your help is appreciated!!!
trigger AppendMMYYEffectiveDate2OppName on Opportunity (before insert, before update) { for (Opportunity opp : Trigger.new) {String newName;
String yearPart;
yearPart = String.valueOf(opp.Effective_Date__c.year()); newName = opp.
Name + ' ' + opp.Effective_Date__c.month() + '/' + yearPart.substring(2); opp.Name = newName;}
}
Hi Tom,
Sorry - I gave you a bum steer... Whilst you *can* use string manipulation to decompose the Datetime pieces, and reassemble them as required, you should really be using the format() method on the Datetime object...
your updated trigger would look something like:
trigger AppendMMYYEffectiveDate2OppName on Opportunity (before insert, before update) { for (Opportunity opp : Trigger.new) { opp.Name += ' ' + opp.Effective_Date__c.format('MM/yy'); } }
Again - apologies for the confusion (on my part!)
HTH, Ian
p.s. HTH = Hope This Helps ;)
This is what I have and it appears to work for the Update testing that I have done. I have not built actual test scripts, when I say testing I mean going into an opportunity via the interface. Creating test scripts is going to be a another story for me. :)
If anyone has suggestions that would make the code better, please reply. I appreciate your input. In addition, I hope it helps someone else who is new too.
trigger AppendMMYYEffectiveDate2OppName on Opportunity (before insert, before update) { for (Opportunity opp : Trigger.new) {String newName;
String currentName = opp.Name;Date effectiveDate = opp.Effective_Date__c;
String yearPart;
Integer removeMMYY;
Integer currentLength = currentName.length();
// Find the slash assuming the rule of 'mm/yy' appending to the end of
// the Opp name. If it exists we need to remove it so we do not
// double pad mm/yy twice.
If (currentName.substring(currentLength - 3, currentLength - 2) == '/'){removeMMYY = currentLength - 5;
currentName = currentName.substring(0, removeMMYY);
}
yearPart = String.valueOf(effectiveDate.year());
newName = currentName + ' ' + effectiveDate.month() + '/' + yearPart.substring(2); opp.Name = newName;}
}
Hi Tom,
It's starting to become pretty trickey, eh?
As an alternative suggestion, perhaps you could use a custom formula field that concatenates the Opportunity Name and the Effective_Date__c field, thereby removing the need for the trigger at all?
Otherwise, you can use isInsert on the trigger to determine if this trigger is firing before the opportunity is saved (i.e. there will not be a date-component appended) or not (i.e. there *will* be a date appended)
then use string.split(' ') // split the name by spaces, knowing that the last string in the resultant string array will be your formatted MM/yy which you can replace with the up-to-date value...
Something like:
trigger AppendMMYYEffectiveDate2OppName on Opportunity (before insert, before update) { for (Opportunity opp : Trigger.new) { if (Trigger.isInsert) { opp.Name += ' ' + opp.Effective_Date__c.format('MM/yy'); } else { string[] namePieces = opp.Name.split(' '); string newName = ''; for (integer i=0; i< namePieces.size()-1; i++) newName += namePieces[i]; newName += ' ' + opp.Effective_Date__c.format('MM/yy'); } } }
So anyway.. a little convoluted when a formula field *may* do the trick... will leave it with you :)
Cheers,
Ian
Hello lan,
Thanks for the addition suggestions. With a formula I am not sure how I would put it all together and have the formula not keep appending more mm/yy's to the end of the opp name during updates.
In addition, when I get to work tomorrow I will try to with your trigger code; however, it does not look like it would prevent multiple mm/yy from appending to the opp name during updates? Am I missing something with the tricky for loop you have? It looks like it would loop building each separate word from the original opp name followed by mm/yy.
For example, opp name, 'This is a test 08/09' would end up looking like,
'This 08/09 is 08/09 a 08/09 a 08/09 test 08/09'!
Am I reading the loop in correctly?
Thanks
Hi,
I would definitely recommend having a look at the formula field solution, simply because it is codeless and hence neat.
Basically, you would have 2 fields - Name and Effective_Date__c; you create a 3rd field - a formula which concatenates the two,
It looks a little convoluted, but it's a much more "set & forget" approach.
Create a custom field, give it a name, pick 'Text' and paste something like the following into the formula:
Name & ' ' & IF(LEN(TEXT(MONTH( Effective_Date__c )))=1, '0' & TEXT(MONTH(Effective_Date__c)), TEXT(MONTH(Effective_Date__c))) & '/' & RIGHT(TEXT(YEAR(Effective_Date__c)),2)
For the trigger code, the bit you may be missing is the -1 on the for loop's end condition... i.e. iterate through each piece of the name *apart from* the last one. This will allow you to have spaces in your opportunity names, but it does rely on the last part of the name being the MM/yy part, which opens the door to potential data corruption, where with the formula field solution, you're just concatenating the two pieces every time the record is inserted or updated...
Good luck - and let me know how it goes :)
Ian
Sorry... one last thing... in the trigger code, I'd put the 'space' in the wrong place within the loop..
it should read:
for (integer i=0; i< namePieces.size()-1; i++) newName += namePieces[i] + ' '; newName += opp.Effective_Date__c.format('MM/yy');
this is what happens when you write stuff without testing it properly ;)
cheers,
Hi lan,
I think that your formula is slick and I am trying to figure out how it works with the IF statement. It looks like it is similiar to a Visual Basic in line IIF statement, yes/no?
I have one problem with the formula so far. It only makes the change to the formula field not the actual Name field!
Am I doing something wrong.
Again, I cannot stress enough how much I appreciate your help.
Hi Tom,
thanks for your kind words - and yes, the IF statement works like a VB IIF - it's a ternary operator, that is IF(condition, trueResult, falseResult).
In terms of not setting the Name - that's the whole point!! By keeping the name to be *just* the name, you never need to decompose and re-build it; you never need to worry about data corruption, or 'how many dates are appended on the end?; because the Name will only ever be the Name.
In the meantime, if you need to display (or report on) the composite name (e.g. "Joes Pizza 08/09", you can use your new field...
It's up to you, of course, to decide if this works for you...
Cheers,
Ian
Hi lan,
The business seems to run some kind of dup report and if 'joes pizza 08/09' exists twice it could be concidered a duplicate if it is from the same department. Per the business, they have been doing naming like this since 2004. I just want to automated because some users are entering 08/2009, or 8/2009, or 08-2009, or 08-09, etc. This makes it hard to determine dups.
In addition, I was talking to a co-working and she had a great idea, since the opportunity name is always the same as the account name (per business rules) with the mm/yy appended, why not help the user out by populating the opp Name with the account name plus the mm/yy.
That is segway to my new problem:
When I access the opp.Account field and popluate it into the opp.Name field it is actually the word, null. The trigger compiles ok so it must see the field named 'Account' but it seems to contain a null value even though the Account name shows in the Account Name field on the opportunity page before I save the record!
Any ideas?
Hi Tom,
OK - So we have ruled out the extra field option, no problem - hopefully you know a little more about formula fields, now ;)
Now the problem with 'null' turning up in the Account name is because when you create the Opportunity, you only tell the system what the ID of the Account is, you do not query the Account record to discover its name (a little confusing, since the name turns up on the page, I admit...)
So what you need to do is query the Account table to find the names of all the Accounts related to the opportunities you are inserting (remember you need to write your trigger to handle a batch insert of up to, typically, 200 items)
So: Build a list of Account ID's from your trigger; build a map of Account objects (including their names); then loop through the trigger's opportunities a second time to build the name as required;
should look something like this:
trigger AppendMMYYEffectiveDate2OppName on Opportunity (before insert, before update) { // Create a unique set of Account Ids we will be querying Set<Id> accIds = new Set<Id>(); // Create a map to store the Account names Map<Id, Account> accounts; for (Opportunity opp : Trigger.new) { if (Trigger.isInsert) { // Save this Account ID - we'll need this later accIds.add(opp.AccountId); } else { string[] namePieces = opp.Name.split(' '); string newName = ''; for (integer i=0; i< namePieces.size()-1; i++) newName += namePieces[i]; newName += ' ' + opp.Effective_Date__c.format('MM/yy'); opp.Name = newName; } } // Query the Account table to get all the names of Accounts referenced by new Opportunities in our trigger. accounts = new Map<Id, Account> ( [SELECT Id, Name FROM Account WHERE Id in: accIds]); // Loop through the list again, this time we have the names to add in... for (Opportunity opp : Trigger.new) { if (Trigger.isInsert) { opp.Name = accounts.get(opp.AccountId).Name + ' ' + opp.Effective_Date__c.format('MM/yy'); } }}
HTH, Ian
:)
Wow--thank you for all this work through. You are reallying giving me much support and more to think about. My head is spinning. If you have watched enough Ku-Fu episodes then it is clear that you are the Master and I am the Grasshopper! Did I just give my age away. :smileyvery-happy:
I took your code and formatted in NotePad so that I could make sense of it and it bring more questions but I am starting to feel like I am using too much of your time and making this post extremely long. Please let me know if I should post new questions on to a seperate post even though they have to do with this one.
I heard you mention something about bulk processes and coding correctly for it. With your code having two seperate for loops it is confusing to me. I know I must read more on the ": Trigger.*" stuff and hopefully that will clear things up; however, I have my code like below and it works. Unfortunately, the format method is not working as you have shown so I used a split to get around it. Can you please explain to me why it may not work during bulk processes and give me an example, of a bulk process? Would it be like importing records in from Apex Loader, for example.
trigger AppendMMYYEffectiveDate2OppName on Opportunity (before insert) {
for (Opportunity opp : Trigger.new) {
String currentName = opp.Name;
String effectiveDate = opp.Effective_Date__c.format();
Integer removeMMYY;
Integer currentLength = currentName.length();
String []splitIt;
// Seperate into three parts mm dd yyyy
splitIt = effectiveDate.split('/',3);
// Pad zero if month is less than 10.
// This would catch 7/09 and make it 07/09
if (splitIt[0].length() == 1) {
splitIt[0] = '0' + splitIt[0];
}
// If CCYY format get only YY
if (splitIt[2].length() == 4) {
splitIt[2] = splitIt[2].substring(2);
}
// Does slash 'mm/yy' exist at the end
// If it exists we need to remove it so we do not double pad (mm/yy mm/yy)
//If (currentName.substring(currentLength - 3, currentLength - 2) == '/'){
// removeMMYY = currentLength - 5;
// currentName = currentName.substring(0, removeMMYY).trim();
//}
// get related account name from account object
Account acct = [select Name from Account where Id = :opp.AccountId];
if (currentName.length() <= 3){
// user did not enter at least three characters for an opportunity name
// It is assumed that we will auto populate the currentName with opp.Account
// and append the mm/yy.
currentName = acct.Name + ' ' + splitIt[0] + '/' + splitIt[2];
}else{
// User must have typed there own opp.Name
If (currentName.substring(currentLength - 3, currentLength - 2) <> '/'){
// We need to append the mm/yy because the user did not include it
currentName = currentName + ' ' + splitIt[0] + '/' + splitIt[2];
}
}
opp.Name = currentName;
}
}
Hi Tom,
Thanks for your kind words - I feel like I have a long way to go before I reach Master status ;)
I'll try to summarise why we use the two 'for' loops:
Firstly, yes you are spot on - the 'Data Loader' could be bringing in large sets of data, it will do this by passing the data in batches, typically 200 at a time.
Now we need to understand Governer Limits. Basically, you need to minimise the number of database hits. Also, be aware that the object model does not support lazy-loading properties - that is, if you want to know what is stored in a field, you have to query it; Opportunity.AccountId may be known, but you will need to query the Account table to find out the Name of that Account object...
In your example below, you have this:
for (opportunity opp : Trigger.new) { // Code ommitted
Account a = [SELECT Name FROM Account WHERE Id =: opp.AccountId]; }
So what will happen if your trigger is firing because the Data Loader has just uploaded 200 opportunities? You will try to hit the database with 200 SELECT statements...
However, if you run through the Trigger.new collection *twice*, you can do just 1 SELECT statement. The first iteration through will build a collection (typically a Set) of Ids; then you can perform an isolated query to get a Map of data, indexed by Id; then you run the second pass through, only this time you know more about the objects in trigger.new because you have queried the missing pieces from the database.
The price for this is that your code is more verbose, and you are often duplicating effort...
it should look something like this:
// Use a Set because the Id will only be added if it does not exist // in the set already (ie no duplicates) Set<Id> accountIds; for (Opportunity opp : Trigger.new) { // Add the Account ID from the Opportunity into the set accountIds.add(opp.AccountId); } // Query the Account table for every account with an Id in the set // Do just one query, and put the results into a Map Map<Id, Account> accountMap = new Map<Id, Account> ([SELECT Name FROM Account WHERE Id in: accountIds]); for (Opportunity opp : Trigger.new) { // Retrieve the recently-queried Account details from the map, using the Account Id as the key Account a = accountMap.get(opp.AccountId); // Do work on opp }
ps Shame the formatting is so hit and miss - but it's better than all those emoticons that litter code directly in the post, eh?
I would recommend getting into the Apex developer guide - have a look at Map methods; Have a look at triggers, and also DateTime.format( ) - certainly the code I posted before was formatting dates fine for me ??
Send me a private message if you want to know any more about this... (and don't worry about taking up too much of my time! I'm happy to help if I can)
Cheers,
I did some reading while waiting on your response and between that and your posts, the fog is not as dense! :)
Two last questions for now. :)
How do you do a private post?
What does the tag option in this forum do?
Just had a thought - if your Effective_Date__c field is a Date, not a DateTime, that would be why the format method is not working for you...
Perhaps a more elegant solution (to avoid all that messy string-parsing) would be:
DateTime dt = DateTime.newInstance( Effective_Date__c.year(), Effective_Date__c.month(), 1, 0, 0, 0); string fmtDate = dt.format('MM/yy');
Have a look at Date methods and DateTime methods in the documentation :)
Cheers,