Calculated Mail merge fields in Microsoft Word not calculating correctly

(0) Share

Report

Posted on by Community Member

Hi,

I am attempting to create a mail merge quote template from Microsoft dynamics CRM to a Microsoft Word table. Word has the functionality to create simple formulas from tables similar to Excel. The problem I am having is that when merge these fields ( «Quote_Product_Extended_Amount») into the table and then do a simple calculations(SUM(ABOVE)) to add these totals up Word gives me the incorrect answer. Is this because it is a merge field?

if anybody has a solution please help!

thanks in advance

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Community Member on at

Like (0)

Report

Jared:

Can you please show what you are doing.

ie what fields are you using

Thanks

_damon

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

HI Damon,

I'm using a table in word.From mail merge fields I wish to calculate an amount (total, total tax ) from a merge field («Quote_Product_Price_Per_Unit»), thus my table looks like this :

(«Quote_Product_Price_Per_Unit»),

(«Quote_Product_Price_Per_Unit»),

(«Quote_Product_Price_Per_Unit»),

total : SUM(ABOVE)

tax: SUM(ABOVE) * 0.14

Im attempting to use these calculations straight from word, it keeps giving me an incorrect value. Is it possible to calculate these merge fields?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Can't you just get the total from the total field already calculated on the Quote?

The usual approach to Quote templates is to do a merge of QuoteProducts, with only one row in the main body of the template. This is then repeated for each product in turn (because of the <Next record>). The header and footer parts are included inside IF statements so they only appear for the first and last records in the set, respectively. The totals are usually included in this "footer".

Does your error show up when you actually generate a final merged document? I have sometimes seen problems when viewing a preview of a mail merge does not actually match the real experience of the generated document, when all fields disappear and become "real" information.

Looking at the way your example is laid out (admittedly in the restricted layout of a forum such as this), your tax is calculating the sum of everything above, which includes the total line, then multiplying by 0.14. This is in effect a 28% tax - 14% of all the values plus 14% of their total (the same thing). This might be deliberate, of course; I have used this sort of thing myself when it is easier to do this and make up for the "double count" by using a different multiplier.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hello Jared:

Adam has a great suggestions. This is what I would do merge your fields for:

Detail Amount field -which will give you a total amount for all your products

The Total Tax Field -will bring in total tax on all the products

Total Amount field - will bring in the total amount

If you need to do more calculations on the tax then you can do this example

{QUOTE{SET Val1 «Total_Amount»}{SET Val2 «Total_Tax»}{=Val1*Val2 \# $,0.00}}

Note: The field brace pairs (i.e. '{ }') for the above examples are created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues. Likewise, you can't type or copy & paste the chevrons (i.e. '« »') - they're part of the actual mergefields, which you can insert from the mailmerge toolbar. The spaces represented in the field constructions are all required, too. The 'QUOTE', 'SET Val1', 'SET Val2' and '=Val1+Val2 \# $,0.00' strings for the second example are all typed.

_damon

Was this reply helpful? Yes No

Community site session details

Calculated Mail merge fields in Microsoft Word not calculating correctly

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Featured topics

Product updates

Community site session details

Calculated Mail merge fields in Microsoft Word not calculating correctly

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Featured topics

Product updates