Following the story of the ‘trillion dollar bank letter’ comes the question – how do you avoid making the similar mistakes with Microsoft Word.
Following the story of the trillion dollar bank letter comes the question – how do you avoid such mistakes in your own business?
However it happens, there needs to be a way to ensure that customer letters doesn’t go out with mistakes – either values that are too low or too high. In this article we’ll show you the options in Word to do that effectively.
Presumably the Wachovia Bank has a sophisticated system to produce overdraft letters but the same problems can arise with a simple Word mail merge too. It’s easy for mistakes to get into complex backend systems like a SQL database, CRM system or just an Access database. There’s also the possibility of an unhappy employee deliberately putting bad data into the system.
However it happens, you need a way to catch ‘out of range’ or incorrect values before they reach customers – they can be embarrassing or worse.
Filter
The quick and dirty method is to simply filter the incoming data so that only values in the acceptable range are printed.
Edit the data source in the Word mail merge system and choose ‘Filter’ then set some boundaries for the upper and lower values.
In our example we’ve set the lower limit ‘Greater than Zero’ which prevents letters going out with nil values or worse, negative values (eg ” you owe us -$150 ” )
The upper limit prevents letters being sent with ridiculously high values – we’ve used 100,000 but it depends on the nature of your business.
Generally try to pick a value that’s so high as to be rare; even if the value is correct it deserves to be considered by management before being sent. For example if someone really owes you ten million dollars there’s probably some legal action or negotiations in place which a routine form letter might complicate.
Simple filters are effective but they hide the underlying problem – if there are extreme values in the system you want to know about them and fix it.
You could setup a ‘reverse’ report to the mail merge – ie one that lists the extreme values that are not printed out in the mail merge, but that means creating, maintaining and running another mail merge for relatively low likelihood situations. It’s usually better to integrate everything into a single mail merge run.
Sorting
If you know the letters are going to be checked by a human before sending out then you could choose to print the letters in order of value – the lowest first or vice-versa. As long as the staff know to check the values at the start and end of the letter pile that should be enough.
Tip: sort the list from high to low so the high values appear at the top of the pile where they are more likely to attract attention.
To sort a list return to the edit data source option and choose ‘Sort’.
Sorting might work for small and medium businesses where you can rely on some level of staff oversight – for larger businesses it probably won’t be good enough.
Trap the values
The ideal solution for these ‘high and low’ issues is to print the letters but in such a way as they don’t get posted. In other words, check for errant values and remove the customer’s address.
In Word mail merges there has always been basic IF statement options to vary the text according to the mail merge data. Over time those features have become more accessible.
For this example we’ll replace the customers address with an ‘in-house statement’ when the value is too high, on the theory that even if the letter is posted accidentally it won’t get to the customer (probably returned as undeliverable).
While Word has IF statements it’s not easy to insert mail merge fields into them. Copying the <
> into the IF statement doesn’t work as you’d like. In other words, this is wrong:
which translates to this field code:
{ IF {MERGEFIELD Value} < 100000 "«AddressBlock»" "ALERT - value too high" }
You need to insert the actual field codes, not the abbreviated <
Create the address or other field as you want it to appear in the final document then copy it into an IF statement. I normally do that by making a placeholder IF statement with the condition I want then replace the values with the field codes I really want to print.
For example, if you press Alt + F9 you’ll see that the <
{ ADDRESSBLOCK f “<<_FIRST0_>><< _LAST0_>><< _SUFFIX0_>>
<<_STREET1_
>><<_STREET2_
>><<_CITY_>><< _STATE_>><< _POSTAL_>><<
_COUNTRY_>>” l 3081 c 2 e “USA” d }
(The exact field code depends on your data and formatting choices.)
Copy all that code (including the curly braces at the start and end) into the IF statement.
{ IF {MERGEFIELD Value} < 100000 "{ ADDRESSBLOCK f "<<_FIRST0_>><< _LAST0_>><< _SUFFIX0_>>
<<_STREET1_
>><<_STREET2_
>><<_CITY_>><< _STATE_>><< _POSTAL_>><<
_COUNTRY_>>” l 3081 c 2 e “USA” d }” “ALERT – value too high” }
This is a screen shot of a mail merge document showing both an unfiltered and filtered address block – both in preview text and the field codes:
During testing it’s handy to show both the ‘raw’ address and the filtered one in the IF statement – it’s easier to debug. For the final letter you’d remove the unfiltered address fields and tidy things up. Here’s what we came up with, just as an example of what you can do.
Or at the extreme end …
We’ve added larger bold text to the alert so it really stands out from a normal address. The alert also contains the customer name so staff can identify the source without the full address, you could add a customer number or other reference as well. In addition we’ve formatted the value into a more readable form.
