How to Use OpenOffice Writer Database

If you use OpenOffice Writer and you’re not using the database function to help keep track of your work or for notes, then you’re missing out on a valuable tool. With only a little effort you’ll find the database function of Writer easy to use, and best of all it’s free.

Please note that I’m presenting just one way to create your database and make your first table in this article. There are other methods. The method I’m showing works as of the latest version of Writer in OpenOffice.org version 3.4.1 – previous methods or future methods may be different.

For this tutorial let’s assume you are writing a piece of creative fiction. We’ll add a few details to the plot as necessary for the demonstration.

Open Writer to a blank new page or a page with text.

Click File, New, Database.

createanewdatabaseFIG1

Select CREATE A NEW DATABASE and then click NEXT.

createanewdatabaseFIG2

Select NO, DO NOT REGISTER THE DATABASE and OPEN THE DATABASE FOR EDITING. Then click FINISH. Name your database CHARACTERS. The database setup screen appears.

NewDatabaseFigureTableCHAR1

Now comes the part that confuses many people. When most users see a Wizard selection, they think that’s the easiest selection on the screen. The problem that I, and other users of Writer, have with this screen is that the Wizard forces you to use one of two pre-made tables. I’ve never found Writer’s pre-made tables to be useful without changing them first, and I find modifying pre-made tables quite annoying, so I go straight to the design screen.

Check to make sure TABLES is highlighted under DATABASE, and then click CREATE TABLE IN DESKTOP VIEW. The following screen appears.

NewDatabaseFigureTableCHAR2

Database design can be tricky to the beginner, so it pays a little to think ahead. The type of data you want to store and your personal style determine much of database design. Fiction writers may want to keep track of character names, for example, and backstories and other details. A writer of fiction may want to arrange tables by chapter and story outlines. Technical writers may have part numbers, manufacturer names, versions and other details to store.

Let’s assume for now that you’re a writer of a fictional story about farming in Colorado. You have 3 main character’s in your story, Tom, Joe and Marry.  Let’s make a table that will store basic information about Tom.

Make sure the cursor is in the upper left cell, and then type CITY OF BIRTH in the cell. You’ll notice a number of selections appear, as shown below.

NewDatabaseFigureTableCHAR3

IMPORTANT: This screen sets up column names that will appear at the top of the table. CITY OF BIRTH will be the first column in the table.

Databases are designed to handle numbers and text differently. A great deal of thought has went into making databases lean and quick, and database developers have created a variety of categories for different kinds of numbers and text.

The default field  type is TEXT [VARCHAR]. At the bottom of the example screen shown above, you’ll note there is nothing in DEFAULT VALUE or FORMAT EXAMPLE. The ENTRY REQUIRED field is set to NO, which tells you that when you work on this table you don’t have to enter anything in this field. Selecting YES would force you to make an entry in the TOM column in every row. LENGTH is set to 50, which means 50 characters.  If left untouched, you could enter any city name in this field, as long as it has 50 characters or less.

The TEXT [VARCHAR] field is not suitable for every task. To change the field type, just click in the cell under Field Type, and then click the downward arrow. You’ll notice the available fields at the bottom of the screen change to reflect the new data parameters for the new Field Type. You will probably find many of these selections confusing at first. If you want to learn more about data formats there are many places on the internet that can help you. For now, leave this field set to the default value.

Let’s make another column in your table. Click the first cell in the second row, and call this field POLICE RECORD. You are gong to want to keep more than 50 characters in this field, so under field type click MEMO [LONGVARCHAR]. The “MEMO” field type allows you to put the maximum amount of text in a field (some of my databases have nothing but MEMO fields). Make a third column called AGE, and set the field type to NUMBER [NUMERIC].

NewDatabaseFigureTableCHAR4

Click FILE, SAVE, and name your table TOM. A pop-up window appears.

Writer notices that you have no primary key or unique index values in your table. Databases use keys and unique index values to keep track of data. Answer YES to the question “Should a primary key be created now?” You’ll notice a new column appears in your table, ID, with a unique icon to the left and a field type of INTEGER[INTEGER].

NewDatabaseFigureTableCHAR5

Close the table. You’ll notice your database setup screen now contains the table you created.

NewDatabaseFigureTableCHAR6

Close the screen, making sure you save your database when asked.

Don’t go anywhere now, you’re not done yet. One of the most important things about OpenWriter databases that you need to learn is how to connect them to OpenWriter. This lesson is necessary because you may have to restore your files at some point in the future, or move your file from one computer or folder to another. Moving files can cause OpenWriter and connected databases to disconnect. A disconnected database won’t appear in OpenOffice. It looks like you’ve lost your database. That can be scary. Understanding how to restore this connection, or register your database, will save you a lot of grief.

How to Register your OpenWriter Database

Click the the DATA SOURCES icon, the stack of silver disks in the menu bar at the top of the screen, or click F4. A screen similar to the following will appear.

NewDatabaseFigureTableCHAR7

Let’s call the upper left area “DBfiles” and the wider, upper right area “DBentry.”

Click a blank area in the DBfiles pane and a pop-up menu appears. Select REGISTERED DATABASES. A new popup-window appears showing all the currently registered databases.

Untitled-3

You may see some demonstration databases downloaded by OpenOffice. Ignore them. Your CHARACTERS database is now shown in the list, of course, since you have not registered it. Click NEW, and the Create Database Link window appears.

Untitled-153

Click BROWSE. Locate your database, which is called CHARACTERS.ODB. If you can’t find it use File Manager to locate it on your system. After you select the database, this popup-window will fill with information.

sdf

Click OK. Your new database appears in the list.

sdfsf

Click OK, and your document appears. The database appears in the left pane at the top of the screen.

uiiiii

Double-click the database name or the little “+” sign next to it, and it will expand and show QUERIES and TABLES, with their own “+” signs. Click the “+ by TABLES, and you’ll see your table (or tables) listed. Double-click TOM and the following appears.

twtetr

You can now enter data in the fields.

Moving Ahead

You’ll probably not be happy with the database we created. It has severe limitations and drawbacks.  To demonstrate one problem, let’s enter data.

Type “Largo” in the City of Birth field, “NONE” in Police Record, “26” in age, and then click on the second row (or press ENTER). You get an error message.

This error happens when you leave the index or key field blank. Your ID field is your index/key field, which is necessary for the database to function, and it needs a value. Put “1” in this field. Now you can navigate to the second row without getting the error message. Let’s change this table so the ID field is automatic.

Right-click TOM in the left pane. Click “Edit” in the context menu.

EditDatabase-1

This screen should look familiar, you saw it earlier.

Click TABLES under “Database” on the left, and the tables in the database appear.

Right-click TOM and then click “Edit”.

In the ID row, click INTEGER[INTEGER] under Field Type. Change the AUTOVALUE field at the bottom of the screen from NO to YES.

EditDatabase-2

Now your database will add index numbers automatically.

Let’s change the table structure so you can keep multiple characters in it.

On the second row, click the grey box to the left of the first field. Then right-click on the right-pointing arrow that appears.

EditDatabase-3

Click COPY in the pop-up menu that appears. Now, staying in the far-left column of grey boxes to the left of each row, go to the row under AGE and right-click again. Click PASTE. A copy of the CITY OF BIRTH field appears, with the number 1 attached to it so it is different.

EditDatabase-4

Change CITY OF BIRTH field to NAME, and leave the field type alone.

Change CITY OF BIRTH1 field to CITY OF BIRTH. Leave the other fields alone.

Save your table and close the table screen.

Right-click on the table TOM and then click RENAME, and rename the table CharacterNames. Save the database and close the screen.

Your changes will not be immediately reflected, so close that database screen by clicking the Data Sources icon, and then click the icon again to reopen the database.

Table TOM is gone, and table CharacterName is there instead. Open it and you will see the new arrangement of columns and the new column. Make a new record, but leave the ID field blank. Move to the next row. You’ll notice the ID field updates itself without your help.

Further Learning

Databases are powerful. After you begin using a database you might get hooked and want to learn more. The web has many resources for further learning if you want to build on the basic skills you learned in this article. Just use your favorite search engine and have fun!

 

Using Openoffice Writer: Creating databases for creative writing. Click To Tweet