I have tried to explain it but unless you see the code and know how crazy coding in Word can be (at least it seems kind of crazy to me), explaining it in words doesn't do it justice.
So I have tried to do the next best thing and post a screenshot of the code I wrote for the Word Mail Merge Quote that I posted about previously. Please note that I have moved some of the fields around but that was only to help keep everything on one page so that it was easier to see in this screenshot. Also, I made the top table visible instead of hiding all of the table lines so that you can see how the nice straight formatting was accomplished.
I realize that you cannot read anything from the screenshot on the webpage. Please be sure to right click the image and do a Save As onto you desktop. It will look much better.
I hope this helps answer some of the questions I have not been able to fully answer.
Take note that the table with the fields from the quote line items is inside an IF statement. That IF statement is the key to keeping your line items together.
The last bit of advice I can give is to download the out of the box Mail Merge Quote template and reverse engineer it yourself. Microsoft puts those templates up there not just to show the capabilities or give you a quote that you'll never use, but to see how they did it so you can reproduce it. It doesn't help Microsoft in the least to hold onto that code and not let people figure out how to do what they did. So, check it out and learn all you can from it.
Happy Merging!
David Fronk
Dynamic Methods Inc.
59 comments:
Had already pretty much what you described, but matched it character by character. Still can't get the spacing right. I get a blank line after header table, 3 after each detail table line except only 2 between last detail line and total table. There must be some formatting setting you have set to make the tables line up like that
Never mind. I got it. Cancel both of these an I will post samples and explanation shortly.
David: Thanks for your help. Finally got the trick for the alignment, posted the actual template on my blog: http://www.bizitpro.com/weblog/entry/152/
Great work Steve. Thanks for your post as well. For anyone looking for an example of how to do this Steve has posted the actual XML file on his blog. Check it out at
http://www.bizitpro.com/weblog/entry/152/
and see if that helps any more.
David Fronk
Dynamic Methods Inc.
Dave,
How do you feel about using XML based templates? I know Steve has done some stuff along these line. I get so frustrated with that word mail merge field level stuff. Doing math, or having a natural break, or how about not repeating the header on the second page. All total frustration.
I have been very encouraged by the xml template stuff coming out of the guys in Austria.
XML templates are definitely the way to go. By using XML templates that you set up in Word you can preset the fields to fill where you want them, code your document to only have the header show up on the first page, get all of your line items on the page, etc. I haven't done too much with math calculations as it is much easier to deal with before it comes into Word. Anyway, XML templates work great for assisting in Word Mail Merges out of CRM 4.0
David Fronk
Dynamic Methods Inc.
Does this work with 2003 as well?
Sander,
If you mean Office 2003, then yes this will work as well. Otherwise I'm not sure what 2003 your you are referring to.
David Fronk
Dynamic Methods Inc.
Hi
Thanx for your nice post.I have a problem with quote line items.I have two categories line items and i need to show them in a word document in two different tables. E.g. Category A items should show in Table A and Category B items should show in Table B.
Please help me.
For getting line items to show in different tables you will need to include some logic around your tables. Meaning that you start your IF statement and depending on the condition you set, you either show the next table or not. You either embed your code to encompass the entire table or just the field.
The key is figuring out how to insert code into the Word Doc and then determining how work with tables insode of your logic block of code.
If you have any questions please let me know.
David Fronk
Dynamic Methods Inc.
Steve,
I've been able to modify the quote template to what we need. However everything I go to add in our Terms and Conditions at the end it repeats each line item. Is there a way around this? I just need the T/C at the end once. I've tried a couple of different things but nothing seems to work.
Thanks,
KD
KD,
You need to take your footer out of the repeating IF that you've set up. I ran into the same problem when first messing around with these kinds of templates. The problem is that you put your footer inside the IF that you want to repeat, so the footer then gets repeated. However, if you move your footer text to just below the closeing bracket for your IF all your problems will go away.
I hope that helps,
David Fronk
Dynamic Methods Inc.
Thanks David, but I must be missing something. This is what I have at the end of the document
{IF {MERGEFIELD "LastItem"}="1""
then shows the Total Amount, Discount, Freight, Tax, Total Box
Ends with " ""}
At the end is where I tried to add it in after the last set of " ""}
Thank you!
KD
KD,
Here's how you want your text to appear:
{IF {MERGEFIELD "LastItem"}="1""
then shows the Total Amount, Discount, Freight, Tax, Total Box
Ends with " ""}
{if {MERGEFIELD LastItem}="1" "FOOTER TEXT (Sincerely, KD)"}
Sorry I didn't specify last time, or even mention the LastItem. This should do it for you.
Hope this fixes it for you,
David Fronk
Dynamic Methods Inc.
David,
This still didn't work. I even went back the original quote template, tried again and even tried switching some things around.
It repeats everything after that last set of " ""} including the text you gave me.
My company doesn't have any form of outside support for CRM. So, are there any books I might find this in? I've tried online and your blog is the best I've found so far.
Thanks again,
KD
KD,
In terms of books and things the best place for you to look for a solution on this is actually a Microsoft Word that covers mail merges and Microsoft Word programming. Not one that shows you how to program add-ons and things like, just coding inside of Word. Most of the CRM books out there will only show you CRM specific things (like how to enable the mail merge functionality for custom entities). But I've found that most MSCRM books don't cover this kind of topic because it's a whole new world of programming.
Why don't I have you send your template to me at support at dynamicmethods dot com. I could take a look and see if I can help you out a bit. Coding in Microsoft Word is really strange, I've tried training some of my team members on this as well and it always takes a lot of hand holding for the first few templates until people get the basics down. If you would like to pass that template over here I would be happy to help you out. If you would prefer not to, I understand. The only other thing I can suggest is this, that you really need to pay attention to your IF statements. In my quotes the IF statement I use to repeat checks for a quantity or price. Then when I want to get out and into my finishing text I check the LASTITEM column to see if it equals 1. If it does then my closing text goes inside of that IF's set of brackets "{}". I actually misspoke when I commented last time, I have both IF's checking the LASTITEM and the first one should check something else, like Quote_Product_Quantity.
I hope this helps you a bit. Personally I first got into this bit of work because one of my clients was using the MSCRM-Addons WordMailMerge product in MSCRM 3.0 and I had to figure out how to code. I was in correspondence with one of the developers there and he helped me through figuring these out as well. And he does have a knowledge base article that is very detailed and may just give you what you need. Those guys know mail merges extremely well, so check out this link:
http://www.mscrm-addons.com/KnowledgeBase/tabid/103/Default.aspx
That's the best source I know of out there. Check that out and if that article doesn't help you let me know and/or send over your template and I'll see if I can help.
Thanks,
David Fronk
Dynamic Methods Inc.
David:
When using multiple field codes, how do you control the order of execution? I put a text box containing 13 Ask fields with IF statements on a document. The fields are in the execution order I need; however, question #9 executes before question #1.
My guess is that the order of operations is from the inside, out. It must follow some kind of order of operation one way or the other. I can't image anything would randomly walk through code and execute IF statements in a random order. My guess is that you either just need to reorder, or verify your logic. I know I've done a lot of long IF/Else stuff where I get lost after the 5th IF and suddenly things aren't doing exactly what I thought they should have done. But typically, inside, out is the logic that gets followed with IF/Else statements in applications like Word.
David Fronk
Dynamic Methods Inc.
Hi David,
This is a very helpful post, thanks. I wonder if you have ran into any of the following scenarios?
1. Get the line items on the quote grouped (e.g. items flagged as Optional to be shown at the bottom together).
2. Control the sort order of line items on the quote, preferably sort by two sort columns primary and secondary.
3. Show all line items repeated in two tables on the same document (there are many columns and lengthy descriptions to display and one table would not leave enough room and even if it did it would be showing apples and oranges together).
Here is the description of the desired layout for item 3:
Document Header
Table 1
ProductA DescriptionA SerialNumberA
ProductB DescriptionA SerialNumberB
-- some text (footer for table1 and header for table2)--
Table 2
ProductA Monthly ServicesABC
ProductB Quarterly ServicesCDE
Document Footer
Your insight is greatly appreciated.
1. You would have to do this to your dataset before you send it to Word. All of the Word code gets applied to each item of the dataset as it gets processed. So, either from Advanced Find, or when you preview the list of items coming in you might be able to use sorting to group your items together.
2. Same as answer 1. Since all the code outlined in this post gets applied as each item in the dataset gets processed, there is nothing that I am aware of from the Word "Update Field" coding that would allow this occur. Sorting it beforehand may be what you need to do.
3. Everything that I use are IF's, no WHILE's. Meaning that one item from the dataset goes through all of the logic first, then once item 1 gets to the end, item 2 goes through the logic. So, you should just need to set up a second table under the first table, but most likely inside the final check of whether LastItem = 1. For quotes and similar objects we know that everything in the dataset should be applied to the one document so keeping everything in the logic outside of the LastItem check should work.
Give that a try, hopefully it works for you.
David Fronk
Dynamic Methods Inc.
Hi David,
Thanks for the comments.
On items 1 and 2, I do not believe we have control over how the line items are read from the db, sorting line items on the quote in the UI does not make any difference, the lines are read in the order of their GUIDs (which matches the order in which they were created I believe).
On item 3,
I could not get the second table in and if it scrolls through the whole document for each line item I am not sure if it can be done with IF LastItem = 1.
The variations I was able to get are
FirstTableHeader
Product1
Product2
SecondTableHeader
Product2
or
FirstTableHeader
Product1
SecondTableHeader
Product1
Product2
SecondTableHeader
Product2
Thanks for checking into this.
This may go beyond my Microsoft Office programming expertise. If you cannot control the way that the data comes in we may be stuck with this solution. If we could use a grouping in Word that would definitely help but I haven't seen any such function. That's not to say that it doesn't exist, I just haven't come across it.
One other option that I'm going to put on the table is that perhaps an SSRS report might be a better solution. If you need complex grouping it does a much better job, and you can control the way your data comes in. Also, in SSRS 2008 you can export to Word. If you are doing this for email blasts this may not be the best solution, but if you're using it for one off's then it would work just fine.
I hate coming back with no solution for you but hopefully another option might work just as well for you.
David Fronk
Dynamic Methods Inc.
Thanks David,
Another method we have come accorss is XML based integration to MS Word. It is definitely more costly than Mail Merge, but depending on the number of documents and with the toolset below, it can sometimes be comparable to SSRS and is often a better fit than SSRS.
One toolset that can greatly help with this is
http://www.xpertdoc.com/
although some SDK coding would still be required for the next little while until they inroduce a full integration to Dynamics CRM.
Thanks.
Thanks for the update, perhaps this is a tool that will fill the gaps for people who need a little more. MSCRM-Addons also has a product that does mail merges through MS Word with more features than the out of the box option.
Hopefully one option or another helps someone get the solution they're looking for.
Thanks for the comments.
David Fronk
Dynamic Methods Inc.
hello, i m new with microsoft crm 4.0, i would like to know how to preview many products in the same quote in printing ( the same with order and invoices), i created a merge mail template and it worked, but it shows only one product, so if i have 5 products i have to print 5 quote, this is not usuful, i have 4 days left for the deadlines... thanks
There are a number of comments here on this post that go over this question you are asking directly. If those comments don't get you what you need your best bet will be to look at the out of the box template that Microsoft provides in the Mail Merge Templates in the Settings->Templates->Mail Merge Templates area. The template is called "Quote for Customer" on the Quote object. Open it in Word and if Word doesn't show you the code press ALT+F9 to view it. Then just reverse engineer what they do and make it work for you. Or else you could just copy their template, modify it to what you need the template to look like, upload it as a different template and you're all set.
Hopefully that gets you going.
David Fronk
Dynamic Methods Inc.
hello,
i tryed the code that u proposed using my own fields, but it shows only the 1st line , and it doesn''t show the rest of products, i created a test quote including 2 products and each product has quantity unit price ... lastitem is 0 for the 1st product and is "1" for the second, in printing, only the 1st product apears because the "if {mergefield lastitem}="1" doesn't work... i need help plz
I have been given a comment from one of my sales staff that they do not want to enter the product description in a text field, it has been hinted that they want to enter the data into a ntext field. When I tried to create the field in the product item entity, I tried to import the field but it was crashing the mail merge template window.
How can I create an ntext field in the product item entity to import into a mail merge?
K-PAX,
Typically whenever I see the issue that you are describing one of two things is happening.
1. The items you want to print out are outside of the braces of the IF MERGEFIELD LASTITEM check.
2. There is a problem with the IF statement itself (syntax, typo, etc)
I've run into this problem personally numerous times and with other people and 9 times out of 10 the issue is one of two I mentioned. That's not to say that there isn't another reason, but without diving into your code with you I can't make a good guess as to what it is.
That's my suggestion, if you have more details I'll see how else I can help.
David Fronk
Dynamic Methods Inc.
Aaron,
I have not had an issue with getting ntext/memo fields to appear in a Mail Merge. There may be a text limitation on the number of characters that Word will allow CRM to push to it. And if the number is exceeded, then perhaps the entire field doesn't go through in the Mail Merge.
However, I have successfully displayed an ntext/memo field in a Mail Merge from CRM.
David Fronk
Dynamic Methods Inc.
K-PAX,
I did just have one other thought. I walked through exporting a template one more time and I realized that in stepping through it each item appeared to be the next recipient. It's not until you push everything "To New Document" that you see it all come together. Have you taken your Mail Merges that far? Maybe that's all that you're missing.
David Fronk
Dynamic Methods Inc.
How do you write an If statement and include items in seperate rows in the same IF statement? When I try to remove the closing bracket, it tries to delete the entire mail merge field. What am I doing wrong?
Aaron,
You need to put the IF logic inside those two curly braces. Something like:
{IF {MERGESEQ} = "1" "True Logic" "False Logic"}
Inside your true logic you put in all of the tables, fields, etc that you want to show. And typically I leave the false logic blank ("").
If you haven't gone into Settings --> Templates --> Mail Merge Templates and opened up the "Quote for Customer" example that Microsoft has provided, do it. The attached Word document does EXACTLY what you are trying to figure out. Just copy their logic, or just copy their page, and format it to look how you want it to and you'll be all set.
David Fronk
Dynamic Methods Inc.
hello
isn't it possible to print orders and invoices using mailmerge as it possible with quotes?
K-PAX,
No, out of the box, it is not possible to print orders and invoices using mail merge just like quotes. There are ways to tweak that and allow for some capabilities by modifying the Entity XML of whatever entity you want to use but the quote has some built in queries that pull in the line items that most likely aren't built in for orders and invoices. Hopefully this gets addressed in the next version.
David Fronk
Dynamic Methods Inc.
hello
thanks david for your answer, unfortunaly we have to wait untill microsoft decides to make a new version...
anyways, i m still having problems with Quote printing, i just have only either the 1st or the last product, i ceated 5 templates each one included a number of product, it s what i could do for now, so i have to create templates as much as max of products in a quote...
the code that u posted ddn't work with me i copyed it and just modifyed the fields,do u mind posting it clearer than the first time please?
thank you so much david,
cordinaly K-PAX
hello;
i have another question if you don't mind, can we use cristal report with crm 4.0? if we can then how? if we can't then what can we use instead?
thank u david for giving us from your time.
K-PAX,
Have you tried running the out of the box Word Mail Merge Quote document? Does that do what you want? Or come any where near what you want? The code that I would post would be the code that is in that document. If you want me to post that in comments I can do that.
As for your other question, I have not tried to use Crystal Reports with CRM 4.0. I don't think it will work, but that's not to say you couldn't try. SQL Reporting Services (SRS) is what CRM uses and it should be able to do everything you need for your reporting needs. Another option for your reporting needs would be to export data to Excel, have it dynamically link so that when you open it, it gets updated with the latest data, and use Excel graphs, charts, and/or pivot tables to handle some of your reporting needs.
If you are still having problems with the Mail Merge document, then you may just need someone to give you a hand over the phone or remoting your PC. Microsoft has support that is willing to help with that, or if you want to solicit some help from me and my team, you could submit a request to support "at" dynamicmethods.com.
Thanks,
David Fronk
Dynamic Methods Inc.
hello
i have this error while intallin ms crm 4.0
the "{0}" SQL server is not available (in frensh Le "{0}"SQL server n'est pas disponible ) what can be the problem? help please
thanx from CO2 :):)
CO2,
First off, make sure that SQL is running. Secondly, make sure that the SQL server you are connecting to has the CRM databases on it. I have seen an error similar to this when someone restored the MSCRM_CONFIG database over the top of the Organization (_MSCRM) database by accident and there were really 2 config databases. If SQL is running and your databases are correct make sure that you are not blocking the SQL ports that you are using between the computers (assuming you have multiple computers running CRM).
Hopefully this gives you a few things to try.
David Fronk
Dynamic Methods Inc.
hello,
how can we count automaticaly, the tax in quotes or invoices, or at least instert the tax in percent%, can the ms crm 4.0 so that?
thank you
Tax should roll up into the quotes, order and invoices (as long as you are using out of the box fields). The tax on the items rolls into the extended amounts which then roll up to the total cost. In order to get the tax to be a certain percentage you would have to write some kind of script to handle that for you. It should be possible. And really, at the end of the day, if out of the box functionality doesn't do exactly what you want you can always write a plugin to get around that.
David Fronk
Dynamic Methods Inc.
David,
I have finally been successful getting a custom mail merge document created with multiple line items. My only question that remains is, how do you remove the space between the line items in the table? My quote shows about an inch of space between line items.
Alicia,
My guess is that you have a carriage return at the end (but inside) your if statement that is doing the repeating. Just clean that up and you should be all set.
David Fronk
Dynamic Methods Inc.
david,
how can i count the tax automaticaly in the quote? i created a field named tax and users fill it manualy... hope i ll find an answer here
thx david
CO2.
CO2,
If you've added the tax field to the quote then you will most likely want to use javascript to perform your calculations. If for some reason the information for the math isn't on the quote form then you may need to use a plugin. Either way, the calculation is going to require a little custom programming.
If you added the tax field on the Word template then you'll have to use Word code to do the math. Though, personally I recommend that you have all data in CRM and then push it out to Word and keep Word from doing much more than presenting the data in the manner you want.
Hope that helps,
David Fronk
Dynamic Methods Inc.
Hi David,
Have you seen the problem with an extra line getting added to the very top of the document at run time when you get run mail merge from a CRM record? This extra line, because it is not included in the IF tag for the header gets repeated for each line item (we are printing a quote) and makes the spacing between quote lines unacceptably large. Users can delete the line before merging into a new document, but its a pain to do that every time. Can this behavior be changed?
I saw this with Word 2003, not sure if this is different with Word 2007.
Thanks for your help.
I have seen an extra line getting added to the very top (and the bottom as well) when I would run a mail merge. The cause is, well a carriage return. Everytime I've seent this, it has been because the formatting in my IF statement had additional carriage returns (typically used to make my code look neater) and until I got rid of them I would have tons of white space on my pages. Check for that and I bet you'll get it all cleaned up.
David Fronk
Dynamic Methods Inc.
The problem is that our template does not have any extra lines. The line gets added when the template is opened from within CRM.
Here is a very simple example that is easy to repeat.
The template looks like this when you create it:
{IF { MERGESEQ }="1"1 "Text {MERGEFIELD "Potential_Customer"}"}[ParagraphSign]
It contains only one line, the only paragraph sign in it is at the end of the line after the closing brakcet "}". That paragraph sign is impossible to include in the brackets as far as i know, and THAT sign is not the problem.
Load this template into CRM4.0 as a quote template.
When you do mail merge from a CRM quote and select this template you get another paragraph sign added in front of the opening "{" bracket:
[ParagraphSign]
{IF { MERGESEQ }="1"1 "Text {MERGEFIELD "Potential_Customer"}"}[ParagraphSign]
which results in the following printed document:
[ParagraphSign]
Text ABC Inc.[ParagraphSign]
[ParagraphSign]
[ParagraphSign]
depending on how many lines your quote has you will get a corresponding number of paragraph signs.
which makes sense as you have a paragraph sign added outside of the IF brackets. It makes sense, however it should not be happening.
Any ideas are appreciated.
Thanks
I have my mail merge written it has the initial check, like yours, but then I perform another.
{IF{MERGESEQ}="1" "TEXT {MERGEFIELD "Potential_Customer"}"}{IF{MERGEFIELD "Quote_Product_Quantity"}>"0" "LineItem Text"}{IF {MERGEFIELD LastItem}="1" "LastItem text to finish document"}
Since the extra spaces are not casued by extraneous paragraph signs, most likely it is each data item being pushed through your mail merge that is causing it. Check the logic that you are using to evaluate whether the data item you are using is the first, any middle or last item. If you don't have the LastItem check then everything after the check that you posted will get repeated with each item.
That's where I would look next.
David Fronk
Dynamic Methods Inc.
Hi David,
I have done some additional tests and the issue is specific to Word 2003, there is no issue with Word 2007.
There is no issue if you create your template in Word 2007 and even if you take an old Word 2003 mail merge template xml document open it in WOrd 2007 and then convert it to Word 2007 xml format (to just xml fomat, from Word 2003 xml format) and run mail merge using Word 2007 the issue goes away.
And the problem is caused by the extraneous paragraph sign that gets magically added to the beginning of the template when you perform mail merge in Word 2003.
Not sure if this can be fixed in Word 2003.
Thanks.
That's a great find, and I'm sure it took you some time to find it, so thanks for sharing it. I am unaware of any fix for 2003, but if you find one I'd love to know.
Thanks,
David Fronk
Dynamic Methods Inc.
Hi David,
I've spent way too much time trying to teach myself how to do exactly what you and your posters have discussed on this site. I need to create a packing slip template that mail merges data from excel. All of the fields are straight forward, but I can't seem to get it to work. Do you have a recommendation of someone that I can pay for help in accomplishing this? Thanks.
Tom
Hi David,
I have designed the mail merge as per your instructions and it is showing all the line items in 1 box.
The problem is that when I am sending it through outlook the recepient is receving a separate template for each line item. For eg if 1 have 3 line items the email template is send as 3 emails for each line item. I dont know how to fix this . Any help wld be highly appreciated.
Thanx
Prince
Tom,
If you are looking for someone to do this for you, you can either look for a Microsoft Dynamics CRM partner in your area (or anywhere really) and they will be your best resource. Dynamic Methods does this kind of work and you could put in a request through the Dynamic Methods website.
David Fronk
Dynamic Methods Inc.
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,
Not able to figure this one out on my own, so I'll try and pick your brain :)
I have a dataset of order lines (sorted) say on the category RecurringCost. If a record is not of type RecurringCost it's a one-time fee.
My headache is, how to split this dataset between two tables containing records of type RecurringCost and non-RecurringCost.
I'm able to print the Header of the first record and fill the first table, and also print the header of the second table, but all subsequent records also print the table header (of table #2). I can't figure out how to not print the header of the second table for all the remaining records.
Any ideas?
Thanks.
Rune
Rune,
If you don't want two headers then you will need to have all of your two table logic within the body of your document. I would try an IF statement that checks whether RecurringCost is true. If it is, fill in the first table. Then have a check after that to see if RecurringCost is false. If it isn't, nothing will be added. But if it is, then you fill out your second table.
Be sure to have both IF statements between your Header and Footer text and that should get everything without printing the table header each time for the second table.
Good luck.
David Fronk
Dynamic Methods Inc.
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