Ask Your Question

How do I design a template for labels for CD/DVD that use a database?

asked 2017-07-01 00:29:46 +0200

TXShooter gravatar image

updated 2017-07-08 13:36:52 +0200

Hello, I am a new user to LibreOffice, and I'm finding my current quest to be a challenge. I am needing to design a form in Base (or any combination of software within LO if that be the case) to display and print CD labels that use a database which contains over 3000 records (not the spinning kind that makes sound). My database is about 15 years worth of sermons for my church, and it is used for when someone wants to get a copy of a sermon burned to a CD (or a copy of the video onto a DVD), and you would be surprised how often that is.

I have been trying to work around a couple of serious drawbacks to the Avery Designer Pro software that I would normally use on their 5697 label...

  1. I can not go to a specific record (I can go to the very first record, or the very last record, and then advance one by one up or down, but not to a specific record). With over 3000 records, that tends to get old real quick.

  2. It would appear that the software can not handle more than about 80-90 records at a time... it either crashes, or doesn't process the records after some arbitrary point, and Avery's online help is a step below dismal in this area.

I was ecstatic to find out that LibreOffice supports many different labels, but I fell flat on my face when I realized that this specific label (5697) is not in the template list.

I did attempt to search this forum, but got frustrated that this specific question / feat / topic hasn't been addressed yet (unless I missed it, for which I am truly sorry if that's the case).

Am I looking in the right area by coming to this forum? I could really use some pointers and direction on this one.

Many thanks, TXShooter

PS. I did find one template (I think it was 5824) already made, but I could not for the life of me figure out how to apply it to a database recordset.!

CD Label Layout

Database Screenshot

Label Field Positioning

edit retag flag offensive close merge delete


Hmm.... must either be a slow weekend for solutions, or this one is a toughy.

TXShooter gravatar imageTXShooter ( 2017-07-02 02:09:30 +0200 )edit

You say you are new to LO. Your stated DB of 3000+ records, what DB is it currently in? Base is just a front end to a database. LO comes with Embedded HSQL but this is not recommended for larger or critical DB's and something different should be used. How familiar are you with databases? Not very familiar with templates but #5894 looks square and for a case. Is this what you want? Typically data is stored in DB & selected records then printed in Writer using DB as source.

Ratslinger gravatar imageRatslinger ( 2017-07-02 15:50:24 +0200 )edit

Database is in Excel and / or Calc (take your pick) (with named columns), imported into Base, and I have been attempting to design a form with the field names of each column in a layout consistent with my CD Label.

Your last sentence is where I face my problem... getting them printed (using the layout in the newly attached Photobucket Image in my OP).

You mentioned using Writer... how do I go about setting this up to do this?

TXShooter gravatar imageTXShooter ( 2017-07-02 22:10:47 +0200 )edit

@Ratslinger... I have no idea what template #5894 is... never tried using it before. I'm attempting to use the ones that I bought, Avery #5697.

I am attempting to attach a link to a screenshot of my database. Maybe from there you can tell me if I have something wrong about it.

TXShooter gravatar imageTXShooter ( 2017-07-02 22:23:53 +0200 )edit

@TXShooter Sorry meant #5824 (moot point as I found Avery template). You mentioned already in Base - is this embedded DB (default)? How many labels are typically at one time? One-at-a-time adequate? Never mentioned if experienced with DB's. Already working on an answer for you but will take a bit of time. These answers will fill in some gaps.

Ratslinger gravatar imageRatslinger ( 2017-07-02 23:44:12 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-07-03 01:33:02 +0200

Ratslinger gravatar image

updated 2017-07-08 20:51:17 +0200

Hello @TXShooter

Answers to my additional questions can be addressed when provided.

First let's start with Base. With 3000+ records I would definitely recommend creating a split DB to contain the data. As briefly mentioned, Embedded is default but has its' problems. It is in the .odb which can & has lost/damaged data. Therefore data should be kept separate from the .odb file. See this post on creation: click here. Don't be alarmed, it is really quite simple.

Once you have an existing DB in place:

  • New table SELECTION (for record selection) - two fields: ID (auto-increment); Selected Record (will use Record Number from your image). Example:

  • You will have two forms: one for regular entry maintenance; one for record selection of labels to be printed.

EDIT: For the two forms - Base the entry/maintenance form on your converted data table. Base the Selected record form on the newly created (and empty) SELECTION table. This entries here will be used by the query to select records for printing from your master records.

  • You'll also need a query for the actual selection.

  • Finally, make sure the database is registered. This is necessary to use the data in other modules such as Writer, Calc, etc.

To make this a bit easier, I have attached as a sample - CDLabels.odb

There are only two items in Master & only set up for selection for the query. With this setup you can print as many different labels as wanted in one process. But each time you use Selection, delete the old entries.

Now for Writer. Since I have no knowledge in adding a new label template to Avery list, I chose using the mail merge method here.

First I downloaded the #5697 template from the Avery site. Double clicking on that file opened it in Writer. Then did a Save as as a Template (.ott file). Now we have the starting point.

Using the newly created template (.ott) and the query in Base, you can position fields for printing on the label. Make sure you place the same fields in both upper & lower labels so as to use both. Now this explanation is already covered in the docs so I won't go into detail here. See Chapter 11 - Using Mail Merge as a guide (click here for docs). Here is a partial view I set up:

image description

You can see a couple of fields I inserted & also some fixed text ("Copy" & "Media").

Once all the necessary fields are applied (both upper & lower) and the query result records selected to be merged, from toolbar select Tools->Mail Merge Wizard, you shoud have record(s) available to print or save. My sample result:

image description

Again, the docs should help along the way. Important items are getting the DB query correct & the Writer template in the wanted format ( UPPER & LOWER - can't stress enough).

EDIT: You will also need to add a field (New Record) between the two labels. Please ... (more)

edit flag offensive delete link more


@Ratslinger Thank you for this information. Alas, however, it is the end of a very long work day for me, so I will need to look this over tomorrow when me brain isn't quite as mushy (or as they say in the south, "Your brain is FRIED like Aunt Bessie's chicken after she's nipped a few!")

One thing that I can already point out... on the average, I print one, maybe two labels at a time when members want a copy. It's only when I am creating 'master' labels for storage do I print several in a row.

TXShooter gravatar imageTXShooter ( 2017-07-04 01:12:51 +0200 )edit

I'm sorry, but life has it's ups and downs... I'm still trying to get to performing the steps in your answer. Please continue to bear with me.

TXShooter gravatar imageTXShooter ( 2017-07-06 00:04:41 +0200 )edit

@TXShooter It's not a problem. Take care of what is necessary first.

Ratslinger gravatar imageRatslinger ( 2017-07-06 00:34:49 +0200 )edit

Ok... I'm almost to a point of uninstalling LibreOffice. I can't seem to import my spreadsheet data... keeps wanting to paste it all into a single cell. And while I was attempting to cancel an operation to Convert my Excel Spreadsheet, (didn't want to overwrite the existing file), I received an Access Denied to the .xlsx file, and that spun me into a "BASIC runtime error" "Type Detection Failed" error, and that put me into LibreOffice Basic, to which I could not close because "BASIC is running".

TXShooter gravatar imageTXShooter ( 2017-07-07 01:02:46 +0200 )edit

@TXShooter WOW. Your comment came out of nowhere. The topic has been about printing labels from a database and the next thing you mention is problems importing a spreadsheet. Now I'll be glad to try to help but I can't see what you are doing. One step at a time. Sounds like you are trying to import data from an Excel Spreadsheet. But to where? What actual procedure are you following? What end result are you attempting to achieve?

Ratslinger gravatar imageRatslinger ( 2017-07-07 01:43:28 +0200 )edit

The ultimate goal is to print labels, but I still need to update and keep the database of sermons, so why store and maintain it in a spreadsheet?

I was attempting to follow these instructions:

TXShooter gravatar imageTXShooter ( 2017-07-07 02:33:39 +0200 )edit

I have used that process numerous times for tests & ask answers. Just did it again in about 30 seconds. Two things trouble me. First, are you going to use a split or embedded DB for Base. This was the first item I discussed in my answer concerning Base. Personally I would only consider split for the number of records you have. Second, the instructions you pointed to say to copy data from CALC. Are you? You can open your Excel file in Calc and just save as Calc .ods file. Then proceed.

Ratslinger gravatar imageRatslinger ( 2017-07-07 03:12:44 +0200 )edit

As I indicated before about the "Convert my Excel Spreadsheet" (into .ods) comment before, I was attempting to convert my Excel file into a Calc file a second time (after re-saving my .xlxs file) when the troubles above took place with BASIC. I am attempting to utilize the split DB your instructions had me create. There's a gap of instruction in transferring / using my data in / with that split HSQLDB_2.3.2 database, in which I attempted to follow those instructions on the link I provided above.

TXShooter gravatar imageTXShooter ( 2017-07-07 13:43:33 +0200 )edit

(By re-saving, I mean to say that I had forgotten to update the file by Ctrl-S after I had added a few new entries. I noticed these missed entries in Calc. And since I still had Excel open with the new entries in it, I chose the path of saving that file, and attempted to re-convert it into an .ODS file when that BASIC issue arose.)

The issue of getting that data into the DB is still not working as expected... but I'm not even sure that I'm following the correct set of instructions either.

TXShooter gravatar imageTXShooter ( 2017-07-07 13:47:58 +0200 )edit

I don't see any gap. You create the split DB. Have the Base file (split DB) open at at the Tables section which is blank - no tables yet created. Now the spreadsheet. The first row of the sheet should have column descriptions. These will be used for Field names. The rest of the rows are your data. Select ALL rows starting with the descriptions. When all selected, right mouse click & select Copy. Then on the blank table area, right click & select Paste.

Ratslinger gravatar imageRatslinger ( 2017-07-07 15:03:39 +0200 )edit

answered 2017-07-02 09:31:37 +0200

BlueBike gravatar image

I think it is not just that this is so tough, but you should break your question into peaces. To do that you probably have to educate yourself more, you cannot expect that other people do all the thinking for you. If I am allowed to do a little suggestion and please correct me if I am wrong: What kind of database should I create mysql or hsqldb or other? How exactly do i create that DB and fill it? DB design question. If you got all your data in that DB the last question would be how to print the labels from that DB?

edit flag offensive delete link more


I see where I went wrong in my OP. I mentioned "... print CD labels that use a database which contains over 3000 records". I failed to mention that I have already imported into Base said database (originally from Excel) with fields and everything.

And you are absolutely correct... I cannot expect that other people do the thinking for me. However, I can expect some decorum when it comes to asking a question to a that hopefully yields an answer.

Your last question is where I am now.

TXShooter gravatar imageTXShooter ( 2017-07-02 22:16:46 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-07-01 00:29:46 +0200

Seen: 1,737 times

Last updated: Jul 08 '17