Complex Mail Merge for Invoicing 2

[cranger01]

Hello All.

I have been tasked to do the following, but not sure its even possible. Wanted to check here first.

I am getting a text file from an old unix system. I need to create invoicings using word mail merge. Word 2003. I can do basic merges, but for this one here are some extra's

1) Their is a description and a charge amount. However there can be up to 50 for each person and they are labeled in the text file as desc-1 charge-1 desc-2 charge-2 etc...Is there away I can build some sort of array and then use that array field as the actual insert label?

2) Related to point 1) if I can build some sort of array, can I determine charges there are so I can determine if there will be more than one page?

3) Because of possible pages, can I create a header for each page, but only one footer at the very end?

Again, if this is possible, please let me know. Also, if there is a reference on how to do this, please please, include the link.

Thanks in advance.

 

[SkipVought]

Hi,

1) "...up to 50 for each person and they are labeled in the text file as desc-1 charge-1 desc-2 charge-2 etc..."

This is an EXTREMELY bad table design. It will be a scourge to attampt to work with. It will cause you nothing but sweat and tears.

http://r937.com/relational.html

2) why do you need to know how many pages. This is all done by Word for you, is it not?

3) Of what significance are the header and footer in this application?

Skip,
[sub]
[red][/red]
[/sub]

 

[cranger01]

1) I do understand relational design, just telling you how the text file is given to me. I do not have the power to change the table, I need to work with what I have and do not have too much time to do so. So hoping there is a dynamic way to build the array and reference desc-xx.

2) & 3) The Header is to relist the Invoice Header information on each page. The Document footer (not page), will be the "Send TO" information. In the middle is a table to put the charge description and amt. Since it is a table, wasn't sure if it would repeat automatically on new page which is why I asked.

Right now I am just creating the header, the middle table for all the detail info, then going to figure out how to do a document footer before I have to worry about the rest of this. Please, any ideas.

 

[macropod]

Hi cranger01,

Assuming each record can have up to 50 charge fields, rather than there being up to 50 records for the same client, you can use an IF test to determine which records should be displayed in the merged file.

To do this, you'd code each pair of descriptions and charges as:
{IF «desc-1»= "" "" "«desc-1» «charge-1»¶"}
{IF «desc-2»= "" "" "«desc-2» «charge-2»¶"}
...
{IF «desc-50»= "" "" "«desc-50» «charge-50»¶"}
where the '«desc-#»' and '«charge-#»' represent your mailmerge fields.

To create a tabular layout, you could replace the space between '«desc-#»' and '«charge-#»' with a tab character and format the paragraph tabs appropriately. Mailmerging to a table, per se, probably wouldn't be practical, since you wouldn't know how many table rows each client needed until the merge is run. Even so, you could incorporate additional code to calculate totals etc as part of the merge process.

Cheers

[MS MVP - Word]

 

[cranger01]

Macropod,

Thanks for your input. And yes, it is up to 50 charges per, desc-1....desc50, charge-1....charge-50

Though, the layout was predetermined. Not sure I can get around the table format that they want the appearance to be.

Also, I got the header working to repeat, but can't seem to get the footer to just appear on the last page. Can you code for that? Which is also why I wanted to determine number of charges up front so maybe I could force a page break to keep the total on the last page. I wish I could import the invoice template so you all could see what I am working with(and maybe feel my pain), but it is confidential and not sure I have the time to recreate a mock one to put up on display.

 

[SkipVought]

I'd use a UNION query to normalize the data. The work with that.

I'd recommend forgetting the last footer thing. Putting totals at the bottom is a holdover from the paper, pencil and adding machine days. Totals can be calculated and put anywhere. The TOP is the place where NO ONE need search for "the bottom line". Why force anyone to slog thru pages of detail to find this aggregation? Well, maybe it's not slogging, but you have to turn the page to get this answer. WHY?

Skip,
[sub]
[red][/red]
[/sub]

 

[cranger01]

Skip,

I wish you sat in the meeting with me. I asked these questions and more. The general layout was dictated as follows.

Header Section with Invoice number, due date, bill to recipient, etc... repeated on every page.

Detail information in the middle in a formated table.
Table can grow to bottom of page (or acceptable end point)

Footer on very last page with total and Remit too (the part that you tear off and send your check with).

Now to your union query, forgive me, but again, not familiar with merge other than basics...never did vba code with merge either....how would I incorporate that. Not looking for actual code, i can do that once I figure out how that fits.

 

[macropod]

Hi cranger01,

Careful paragraph formatting can give the appearance of a table without resorting to an actual table.

There's no easy way to code the footer. However, you can simply put your 'footer' text at the bottom of the last page for each record.

Cheers


[MS MVP - Word]

 

[cranger01]

macropod,

I do not understand how to do that.

 

[SkipVought]

Import into an Access DB

Perform a multiple queries, one desc-x charge-x set at a time, to normalize the data.

Then use that as your source. It would actually be much easier to do in Excel (Invoice Header level and Detail level data)

Mail Merge in Excel faq68-4223

Skip,
[sub]
[red][/red]
[/sub]

 

[cranger01]

Skip,

I am kind of on the same page as you. I left a message this morning to see if I could use another software other than word.

1)Access may be a problem, I am not sure if the end user has (not everyone does) But I like the idea and checking into it.

2)I have little experience writing reports in Excel. Can I include graphics and such as well in the document? can I create a footer on last page? Do you have a good reference link I could look at?

I really appreciate all your help. I am really feeling dumb here.

 

[SkipVought]

You could also use Excel as a database.

Using MS Query to get data from Excel faq68-5829

"Can I include graphics and such as well in the document? " YES

"can I create a footer on last page?" No, not in the sense of the built-in header/footer functionality. But code could be written to insert the necessary data after the detail table. I believe that the same would be necessary in Word also.

Skip,
[sub]
[red][/red]
[/sub]

 

[cranger01]

Thanks again, I will look into it.

I think I figured out how to code the the last part (footer) at the end, but need to figure out how to push that to the bottom of the page. That is key because of the tear off section.

Just one thing of note, this is a temporary solution to something they know they need. This will be used about 12-16 times over the next year.

 

[macropod]

Hi cranger01,

What aspect of paragraph formatting to give the appearance of a table don't you understand?

Cheers

[MS MVP - Word]

 

[cranger01]

macropod,

Well, because of some graphics and such, even if I put the the "footer" text after the last record, I wasn't sure how to make sure it was in fact the bottom of the page. Unless I am missing something.

Having said that, I would like to let you and SkipVought know that there has been a successful redesign. First, Access was thrown out. Second, the import to excel was thrown out as well because the text file exceeds 256 columns and was truncating. So, the new format of the invoice is as follows....The first page will contain the header and footer as planned and only the total of the invoice. There will be an automatic second page with a shortened header and the detail (no footer). I think this can be accomplished fairly easier and with only using the mail merge and the dataset as is. I do appreciate everyones help and I got to learn a few things along the way.

Thanks guys.!

 

[macropod]

Hi cranger01,

If, for example, you put your "footer" text after the last record and formatted the corresponding paragraph(s) as 'keep together' and 'keep with next', then Word would force them all to appear on the same page. That gows part of the way, but could leave your "footer" text anywhere on a page.

To get the "footer" text to always appear at the bottom whatever page it's on, you could put the "footer" text inside a text box attached to the last preceding paragraph, which you'd layout of format as:
. square
. locked
. Vertical alignment set to bottom margin
. wrapping set to 'top & bottom'
Note that this could result is your "footer" text being at the bottom of a page with no more than a line or two of text at the top. If your last preceding paragraph has multiple lines, things might look better if that paragraph is formatted as 'keep together'.

Cheers

[MS MVP - Word]