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

[TXShooter]

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

[TXShooter]

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

[Ratslinger]

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.

[TXShooter]

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]

@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.

[Ratslinger]

@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]

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:

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:

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 excuse my slight knowledge in mail
merge/labels. DB’s more my forte.

Hope this points you in right direction.

EDIT 7/8/17:

Sample using Fontwork:

If this answers your question please click on the (upper left area of answer).

[TXShooter]

@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]

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.

[Ratslinger]

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

[TXShooter]

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”.

[Ratslinger]

@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?

[TXShooter]

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:
https://help.libreoffice.org/Common/Importing_and_Exporting_Data_in_Base

[Ratslinger]

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.

[TXShooter]

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]

(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.

[Ratslinger]

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]

At this point a dialog appears. Give it table name & make sure “Definition and append data” is selected. The rest is just in case you want to change field names or data types. When you Finish, the table should be accessible. In a split DB you cannot modify fields in the normal UI. You can add or delete fields but modify must be done through SQL.

[TXShooter]

OK, the key point that was not illustrated anywhere (the gap) was that one must paste the spreadsheet’s clipboard data into the area of the “Tables”, which was misleading because right above that are three ways to create a table. I misunderstood it to mean that I had to create a table in some manner first, then paste the entries.

Now that I have progressed through those steps, one question comes up… I would like to set the ‘Record’ field to be the ‘Key’ field. Not sure how to do that, yet.

[Ratslinger]

Open the new table in edit mode. Right click table name & select Edit. Not sure if you created a Key during copy process. If so, delete it - right click leftmost grey box on unwanted line & select Delete. To set key field, right click leftmost grey box on line with key field wanted & select Primary Key. Save the table design.