In order to code in MSWord you have to open up the "fields" in which you can code. In a right click menu option this appears as "Toggle Fields." In order to see/access these "fields" you will need to press ALT + F9. If you open a new Word document and click ALT + F9 you won't see anything because there are no field codes on the page. You can add one however by pressing CTRL + F9.
So, a couple of key keystrokes we just learned:
ALT + F9 = Show Field Codes
CTRL + F9 = Add a new Field Code area
Once you know that you can look at all of Microsoft's templates and see what they do with Field Codes in MSWord to get their documents to look as nice as they do.
Now, the one specific point that I wanted to touch on was in regards to Quotes and line items. First just set up your document like any other form. Then when you get to the point in which you want to put the line items, you will need to insert a Field Code. This field code will check one of the parameters in the row of data, if the condition is met then it will insert the data from the row. If it does not it will move on to the next row and check it. Typically I like to check the Quantity field. If the Quantity is greater than 0 then they must be ordering it, so it must need to be on the quote.
So, your Field Code will look something like:
{ if {MERGEFIELD "Quote_Quantity"} > 0 "
< INSERT TABLE/DATA HERE >
"
}
We're almost done. We need to put an ending check on our document to make sure we don't print out the closing piece of our document over and over again, once for each line item on the quote. The key here is to check to see if the line item is the last item in the list.
It should look something like this:
{ if {MERGEFIELD "LastItem" } = "1" "
< CLOSING STATEMENTS/COMMENTS HERE >
"
}
Run through the mail merge and you should have documents that look similar to this:
Happy MSWord coding!
David Fronk
Dynamic Methods Inc.
28 comments:
This is a good post. Could you maybe go into more detail on how you got the last bit to print just once instead of over and over again? Maybe telling what the jargon of < or whatever you had there. I'm trying to do something similar but I'm getting stumped so any info you could give would be greatly appreciated! Thanks!
Steven,
The key to that bit of code is to have a block of code that checks for quantity and everything in that block of code should be for the quote line items.
Your next block of code, that should come right after your line items, should check to see if the LastItem = 1. If it does then it should do everything inside of this code block. Otherwise don't do anything.
By checking for the LastItem attribute you will only get 1 result and it will always be the last row of data being passed in.
That's really the key, just two separate code blocks and the last one checks for "LastItem = 1".
David Fronk
Dynamic Methods Inc.
David,
Thanks so much for the info. I'm trying to throw in the LastItem IF statement but when I go to merge I get an Invalid Merge Field. Is this something that I need to put in my data source or is it a default flag that is automatically set? Thanks, you've been really helpful.
Steven,
The LastItem column is automatically inculded in your datasource, you don't have to select it. If you look when you see the screen that shows you all of the data rows you are bringing into your Mail Merge the very last column is the LastItem column. You'll notice that they are all 0's except for the last row (the last item) which is 1.
Your invalid MergeField is probably coming from a misspelling or misreference. Or, typically I get this most frequently because I forget to put quotes around the field name. See if one of those isn't causing your problem.
Good luck,
David Fronk
Dynamic Methods Inc.
hey david this was an extremely useful post. however could you please explain how you managed to print all the quote products in a table format(in the sample screenshot)! any quick help is really appreciated
To get all of the quote products in a table format you need to place the table you create inside of a Merge Field. And that table with your fields of data needs to have your logic set such that it checks for the quantity that is set in the line item. That if statement that does the check will actually get applied to all line items and throw them into the table. Then, after that logic block, you have another that checks the LastItem field, if it equals 1 then print out your closing block of text, otherwise, don't print anything else.
If that doesn't make sense, you may need to look into finding some MS Word reference books or sites that go into coding in Word in more detail than I can in a comment on a blog.
Probably the best way to figure out how to do it is to pull down one of the out of the box Quote Word Mail Merge docs that Microsoft provides with MSCRM 4.0, show it's code and reverse engineer it. That will probably give you the information you need to complete your quote template.
Good luck,
David Fronk
Dynamic Methods Inc.
Hi Daivd
I am using the if last item code you suggested. However i can see the value appearing on the document but when I merge and preview it , the value does not show. I would appreciate your suggestion.
Regards
CRM User
Nice job, but how do you get the individual line items to space properly so that there's no space between the multiple items? I've been beating on this for some time and can't get the pretty alignment that you got.
Stevarino,
This is what most of the other comments have been about. It's not the easiest thing to explain as you can see. So, my best suggestion is to look at the Word Mail Merge that comes out of the box for the Quote Entity. Look at the code there and you will see an IF statement around a table item and another IF statement after the table looking at the LastItem condition. That little bit of code is the key to getting all of the line items on one page and looking nice an neat.
David Fronk
Dynamic Methods Inc.
I think I'm going to have to do another post on this, in the hopes I can be more clear.
Sorry for being vague or unclear in any of my comments.
David Fronk
Dynamic Methods Inc.
Hi - this topic is perfect, it's just the execution i'm struggling with. Inserting your example code isn't just a case of copying & pasting - but I don't know how to create the 'IF' statement.
I do know how to insert single fields that represent varying values, but unsure how to start the IF statement which would include the single fields.
Thanks
Matt,
Unfortunately, no my code is not just copy and paste. That's mostly due to the fact that fields and set up could be different between any given system. I just wanted to make sure the concept came across.
As for adding an "IF" statement in Word you will want to you the "ALT + F9" keystroke to show all code. Then you can right click inside one of the coding areas and choose "Edit Field" you can edit the lgic within that field. Otherwise just hit Ctrl + F9 and that will add a field that you can code in. Then you add your IF and put in your logic. From the "Edit Fields" window there are even helpful prompts to help you. Then its just a matter of getting what you want inside the IF.
If you haven't seen this post (http://dmcrm.blogspot.com/2008/04/word-mail-merge-part-2-getting-line.html), give it a look over and see if it doesn't answer your question any better.
David Fronk
Dynamic Methods Inc.
Word files I use very often,and one day I had important corrupted word file and I remembered about-fix damaged docx,which I found in global network,it is free as far as I remember,in addition tool has as well many probabilities,it export recovered data into a new Word document,program scans damaged document, fix docx open and analyzes the data,will recover it and show in a preview window, where the user can check it and make sure, that it is recovered properly,can fix damaged docx and recover documents of different formats (*.doc, *.docx, *.dot, and *.dotx) as well as *.rtf (rich text files),can extract the data and fix docx file from any removable media even via local network of your organization.
Hello David,
Is there any limitations to what you describe in Dynamics CRM 4.0 online? And as an example on how this works, could you show the Word code that comes with the neat quote you showed us?
Charles,
There are no limitations between the On Premise versus the Online versions that I am currently aware of.
The easiest way for you to look at the code for this document is to look at the sample Quote that comes in the list of Word Mail Merge Templates. Everyone has that template in their install of CRM 4.0 and it only takes a little bit of reverse engineering or just swapping of images on the Quote and you're all set.
Hopefully that gets you on your way.
David Fronk
Dynamic Methods Inc.
Hi, Im trying the method above but running into issues. When I use the code against "Quote_Product_Quantity" which Im pulling from CRM 4.0, it gives me an error since I am using Next Record>> to advance records within the code. I have something like this:
{ if {MERGEFIELD "Quote_Product_Quantity"} > 0 "
Quote_Product_Quantity>> Product_ID>> Quote_Product_Product>>
Next Record>>
Quote_Product_Quantity>> Product_ID>> Quote_Product_Product>>
Next Record>>
*etc*
"
}
When I do this, it says I cant use a Next Record within the field code. Any ideas? Thanks
-Steve
I wanted to note, on my last post I removed all the '<<'s before my mail merge fields since this comment system would not let me post them
Steve,
You shouldn't need to tell your code to advance to the next record because the Word Mail Merge function will already to that for you. I've actually never had to use that command in Mail Merge code before. If you're familiar with SQL I believe that this is the same concept, you go through the data row by row and evaluate it as the system reads through the list. Trying to peak at the next row typically just doesn't fly without some complex coding. Try doing what you are trying to do without the NextRecord code and see how you do. If you are repeating your IF logic after NextRecord you'll probably want to remove that to, since each record will have the same code applied to it individually already.
Hope that helps,
David Fronk
Dynamic Methods Inc.
In the 'CRM 4.0 Sample Quote template using tables', there is a portion of the code which has line spacing set to 0.7pt Exactly. This is very difficult to spot as it appears as if there is a screen refresh problem (not the case, however).
Alt+F9. It sits below the Quote ID table and just above the Product heading (supposedly the heading row for the following table (although it is split).
Change the paragraph line spacing to Single spacing. It reveals the following:
}{ IF { MERGEFILED Quote_Product_Quantity } > 0 ".
This could be why some of the 2nd IF statement is not visible, and why results are not given that is expected.
Hi David,
I have designed the mail merge the way you have instructed and it is working good till the time I dont send it through outlook. I can see all the line items falling in 1 table. But the issue arises when I am sending it through outlook, for every line item a separate email is being send from outlook. The recepient is receiving 3 emails if there are 3 line items in the mail merge.
Any help pls wld be highly appreciated!!!
Regards
Prince
Hi David,
I have designed the mail merge the way you have instructed and it is working good till the time I dont send it through outlook. I can see all the line items falling in 1 table. But the issue arises when I am sending it through outlook, for every line item a separate email is being send from outlook. The recepient is receiving 3 emails if there are 3 line items in the mail merge.
Any help pls wld be highly appreciated!!!
Regards
Prince
Prince,
I haven't seen this occur, I always run the Word Merge all the way through Word and then send out. Word makes each merge its own document and emails each individual one out.
Sorry I don't have any guidance for you, check the forums and see if anyone else has had a similar issue.
David Fronk
Dynamic Methods Inc.
Hi David,
I have the same problem as Steven wherein the I can't print the details the way printed it. The products where printed separately on another page instead of listing it as a table row. Here's a copy of my code for your reference :
{IF {MERGEREC }="1""
------------------------------------------------
- PRODUCT -
------------------------------------------------
"}{ IF { MERGEFIELD QUOTE_PRODUCT_QUANTITY }>"0" "
------------------------------------------------
- {MERGEFIELD QUOTE_PRODUCT_PRODUCT} -
------------------------------------------------
"}{ IF {MERGEFIELD LASTITEM }="1" "
------------------------------------------------
- { MERGEFIELD TOTAL_AMOUNT} -
"}
Arlene,
Can you try and save the merge as a Word Doc and then email it out? From what your code looks like, it looks like it should have
-PRODUCT-
-QUOTE_PRODUCTS-
-QUOTE_PRODUCTS-
-QUOTE_PRODUCTS-
-TOTAL_AMOUNT-
Obviously the QUOTE_PRODUCTS- would repeat as many times as there are products on the given quote. But my experience has been that if you are getting things showing up on multiple pages then that's due to an issue with your QUOTE_PRODUCT_QUANTITY > 0 clause.
I believe that someone else found that the version of Office they were using gave them problems. But from what you have written out in your comment, it looks like you have everything correct, you may just have to fiddle with what's in and out of brackets.
David Fronk
Dynamic Methods Inc.
Hi David,
What's your email address so I could email you the template?
Arlene,
If you send it to dfronk at dynamicmethods dot com I'll see if I can take a look. I've been rather busy as of late so I can't guarantee a quick turn around but I'll help where I can.
David Fronk
Dynamic Methods Inc.
I downloaded the templates but having all kinds of problems since it is in word 2003 format to use in 2007 - any suggestions on how to save as or modify? Not allowing me to use macro or enable CRM Macro's - tried all standard settings but no luck. Please email aneal@nealabc.com for suggestions - great post and info....
All,
I was helping someone with writing a mail merge document where they were having the lines show up on their own pages. He too had followed my post but was still having issues. I looked over the document with him and all of his logic was fine. However, what ended up in being the solution, was to REMOVE EXCESS WHITE SPACE. There were two carriage returns after the table but before the check of LASTITEM. That meant that the extra carriage returns (or page breaks, or whatever) was repeated between each line. Once we cleaned up the excess white space everything looked good.
Sorry I didn't see this sooner and let everyone know, as I'm sure it would have saved a lot of people some grief.
David Fronk
Dynamic Methods Inc.
Post a Comment