Custom labels , part2

part one was here.

the next step was to get it into a mailmerge type of arrangement , linked to a dataabse , so editing, adding or removing entries was a simple business.
The database bit is easy - OOBase is good enough. So I set up a database of addresses ( usual stuff, firstname,lastname.address1, adress2  etc etc )
And the mailmerge works (!)  - with a bit of fiddling around - more later .

Taking the data from the Table in the database

You can add and remove names in the database viewed in the  template document   and sort entries alphabetically by the surname. but when it prints out from there , the names  are in the unsorted order.


The way to get them sorted is to add a Query  which will do the sorting

eg SELECT * FROM Addresses ORDER By "Last"  

and then get the database view to show the query - called here  inOrder

But if you do the mailmerge to the original document , the results is  blank pages.
The template for the mailmerge references the Table and field of the database table  that it was created from , not the current table/query/view even though the field names are the same .

the placeholder has the format <DB><TableName><ColumnName>

So to get it to merge from the query I had to change things from

<PDlabels><Addresses><First>  to  <PDlabels><inOrder><First>

and then copy the frame down the page 

So  the order of doing things has to be

1.  Create and populate database
2.  Write SQL query
3.  Open template document
4.  Drag column labels to the document. ( Copy/paste across as before) 
5.  I then found that I had to tell the mailmerge where to  put the next record - otherwise it was  producing a complete page of the same name . The explanation is here .

Ctrl -f2 gets you to the correct screen

So i think I'm sorted !

Multiple copies of labels in OpenOffice

A relative does some volunteer work for the Parkinsons  Disease Society .

She needs  3 copies of each address label. Currently its a MSWord/Avery labels  document where an address is copied across the sheet.   Its alphabetical  so any changes mean a lot of work  in moving rows up and down . Can OpenOffice do better. ?

Things that I've looked at and not got very far  :

• I've looked for a macro where the text in column  1 is copied across the other 2 columns.
• The list could be printed out 3 times, but that could be wasteful with the last page of each run only having a few labels.  OK there are macros around that let you use bits of a sheet. but I want to make this as easy as possible

So I've done it by defining my own label size  containing the label text replicated 3 times.  I experimented with  some sheets  I bought from WHSmith that aren't exactly the same as the default 'industry standard' Avery

1.Create a new label sheet from OpenOffice File -> New ->labels.
Check the page is the correct size for A4  - 297 X 210 mm

and then create a custom label size. A4 is 297x 210 mm. I measured the margins on the label sheet (1.5cm each side)and created a label the remaining width of the document by the measured height of the label. The frame is  3.81cm X 19.0 cm ( width of the sheet minus margins)

 

In the top frame , drag the placemarkers to the correct place. I then added the conditional formatting for missing address fields as described here

2. I then measured the distance the 2nd and 3rd column text would be and set the tabs and tabbed across and copied the  database field labels to the new columns ( a bit tedious )

 

3.I saved the template layout file at this stage

4. the copy the filled frame down the page

5. Tell it where to start the next record 

5. And then did then run the  mailmerge from the Database (this was odb file , but I suppose it  could be anything ) containing the  Address details
choose

• No address block  (I wonder if it could be done with no text and just an address block ? ) 
• No salutation

6. Run the mailmerge and do a test page .

If its not quite right then fiddle with the margins or tab spacing.