word automation - customer mail merge including CLE listings

jversusj

Hello,
Following the example layed forth by the Application Designers Guide, I have a codeunit that populates a word .dot file for me with our address, the customer's info, the Accounts Receivable person's name and contact info. This works just fine. Now I would like to add a listing of related Customer Ledger Entries via merge into my template, but it is not working.

Background information:
We have a field on the CLE called "Short Pay Code" and this is updated by A/R when an invoice is short-paid by a customer. Codes include Tax, Freight, unknown, etc. The idea is to have NAV spit out a nice form letter that communicates the short-paid document to the customer. "Dear sirs, We thank you for your payment but noticed you failed to pay the sales tax due on your invoice...(and so on)"

This is simple in a 1 short-paid invoice to 1 letter relationship, as I could trigger my codeunit from the specific CLE record I have selected and the letter would be created via Word Automation, where I have Mergefields for doc. date, doc. no., original amount, and remainining amount. however, the accounting department often processes customer payments that apply to numerous invoices and when they fail to pay tax on 1 invoice, they often fail to pay tax on all of them. Accounting then wonders if the letter can reference all of the documents that were coded with that particular short-pay code.

Example of my Word .dot file.

«Name»
«Address»
«City», «State» «PostCode»
		
«Date»

Customer # «CustomerNo»
«CustomerName»

Dear Sirs,

We would like to take this opportunity to thank you for your recent payment on your account.  We noticed that you short paid the invoices below for the sales tax you were charged.  Unfortunately, we do not have the appropriate sales tax exemption certificates on file to clear the sales tax from your account.  In order to credit the sales tax from your account, we will need the attached tax exemption certificates completed and returned to us.  Once we have received the completed documents, we will credit the sales tax from your account.  

If the short payment of the sales tax was just an oversight or if you do not wish to complete the attached form, you may remit the payment for the sales tax on your next check.

If you have any questions, please feel free to contact me at «CAPhone» or email me at «CAEmail».   Thank you in advance for your cooperation.

Best Regards,

«CAName»
Our Company, Inc.
----------------------------------------------------------------
Doc. Date	Doc. No.	  Original Amount	  Remaining Amount
«DocDate»          «DocNo»     «Original Amount» «RemainingAmount»

I want to loop at the end and output all of the documents that were coded with the Tax Short Pay Code. Can this be done?

jversusj

hello,
does anyone have any advice on this subject?

i basically want to know if i can use automation to create a letter like the following (red indicates merge field in my .dot file):

11/20/08

Customer # Customer.No.
Customer.Name

Dear Sirs,

We would like to take this opportunity to thank you for your recent payment on your account. We noticed that you short paid the invoice(s) below for the freight you were charged. If the short payment of the freight charge was just an oversight please remit the payment on your next check run or to pay by credit card, complete the attached authorization and return to us. If you feel the freight was charged in error, please document your reasons and return to us to research further.

If you have any questions, please feel free to contact me at Credit Analyst Phone, fax Credit Analyst Fax, or email me at Credit Analyst Email. Thank you in advance for your cooperation.

Best Regards,

Credit Analyst Name
Company Name



Doc. Date Doc. No. Original Amount Remaining Amount
1/1/08 ABC $123.45 $23.45
1/15/08 DEF $200.10 $10.10
1/30/08 GHI $98.07 $14.23


i have the body of the letter sort out just fine, but can i loop a variable no. of customer ledger entries after the Signature to show all documents coded as short-paid by this particular reason code? it could be 1 CLE or 30 CLE or something else altogether.

jversusj

Hi,
just bumping this thread in case any users with some insight have been absent for the past week.

i would love to know if it is possible to do what I have asked here. In my .dot file, i attempted to add a NEXT records call (like used when you mail merge to labels), but it would not work.

I am trying to look at Word Mgmt, but having a very hard time with getting it to work in its native capacity. If i can't get it to work natively, i have no hope of attempting to reverse engineer some of its functionality. I cannot get the CU to fire at all. i was having some luck with opening attachments from a segment, but now i cannot even get that to work - can't remember what i had done to achieve that originally. in any case, while i was able to open the attachment, it would never call WordMgmt to do the mail merge field substitutions.

thanks in advance for any tips or advice...

jversusj

i finally figured this out by piecing together a few different ideas.

the bulk of my automation was based on what is described in the application designer's guide. to get the CLE to print at the bottom, i used the concept of printing at a bookmark. i then inserted a bookmark in my .dot file, and in my code called a loop on CLE with calls such as wdRange.InsertAfter(FORMAT(tempCLE2."Document Date")) to get my detail lines.

So, we have CLE entries with a Short Pay Code. When you click a menu item on the customer card, it searches for short pay codes, and will generate the appropriate letter (or letters), with the appropriate CLE detail on each letter.

even though no direct assistance in this thread, thanks to the community and this resource. i may have had to search 10 different subjects, but it call came together! =D>

attardc

I have a report which mail merges with Word to output customised lists. I have to add a new field, which I managed to do from C/side, but I have no idea how to add it to word! source of my existing field placeholders is { DATABASE \*MERGEFORMAT }

Any ideas please?

thanks a lot!

madsmorre

Can I ask how you got the word automation to change to the next line?
I'm trying a lot of things but when working with mergefields it just keeps overwriting the same line again and again.