Word Templates - Intermediate features
We learned in my previous post, Word Template - Introductions, how to create a simple Word template but now we want to do a bit more. I showed how to create a simple letter but one of the best uses I’ve found for Word templates is to create quotes. But it’s not as straightforward as you may think. To create a professional quote you may want the following:
We want a repeating list of quote products.
We want product ID numbers so we can list a specific product
We want date fields for when the quote was produced.
I’m going to go through each of these requirements and show you how to build them. First up we want to build a repeating list. Download a Word Template for the Quote entity and choose any or all the relationships you want, you definitely need Quote Products (called quote_details under the 1:N relationships) but bring through any others you like. As stated in my earlier post, I tend to bring everything through.
Once you have your Word template downloaded, you can go ahead and build the basics of your quote. You can add images in, the customer's name and address and any other details you wish. Next we want to create a table in the document which we will use to contain all the quote products which will be listed in the quote. You can add as many columns as you like but you need to take into account readability and size of your document; it’s obvious but worth mentioning. Create a table of a couple of rows so you can use one as a header row and populate it with your headings.
Next in the row below, choose the quote_details relationship and then start populate it with the fields you want to bring through. You should have something which looks like the below image.
Next, select the entire row, right click on the relationship in the XML mapping pane and choose repeating. You will notice the formating now looks slightly different on your row as they are now repeating.
Save and upload your word template and you should have something like the below.
I haven’t included Product ID in the table as we need to understand why there is a restriction here. As stated in my previous post, deciding where to start your Word template from is important as you can only get data/fields which have a direct relationship to entity you started from.
Product ID is a standard field on a Product, but is not on Quote Product, Opportunity Product or Order Product. The mapping for product to quote product isn’t something you can alter or update, which causes an issue. Whereas this didn’t use to be a problem in the good old days using SSRS to create quotes, you could just query the product and get it from there. In terms of Word Templates, you need to be a bit smarter. If only someone had written a blog post about how to get data from fields on other entities when you can’t update the mapping. 😉 The method described could also be used to pull through other details to the quote like lead time for instance.
I wouldn’t overly use this method, I would suggest thinking where you will start your Word Template from carefully, but it can help get data from other entities which would be otherwise unobtainable.
The last piece of this puzzle are date fields….Date fields? Why is there an issue? You need to think about what date fields you want to use and when. If you are activating a quote and then producing a Word Template, which field would you use for when the quote was created? The created on date of the record? Maybe, but you could have started the quote and finished it after a few days. Modified on? Maybe. Or a custom Date field which we populate on activate of quote? Sounds pretty good.
It’s not the only reason to use a custom field. Look what happens when we use created on as a field.
It’s pulls through the date and time because that’s the format of the field. The custom field we create needs to be a date only field so that we only pull through the date onto the quote. I would configure a realtime workflow to run before quote status is changed to activated and populate it with the time the workflow ran.
As this is Word, you can build the rest of the template using other tables, Text Boxes, insert images, add items to the header and footers, most functions inside Word. The only aspect of Word you can’t really use is any code/scripting as when uploading it to D365, it will reject it.
What you should end up with is something like this.
Ciao for now!