Tutorial

How to Access and Use
Data Sources with Base

CopyrightThis document is Copyright © 2010–2013 by its contributors as listed below. You may distribute it
and/or modify it under the terms of either the GNU General Public License(http://www.gnu.org/licenses/gpl.html), version 3 or later, or the Creative Commons Attribution
License (http://creativecommons.org/licenses/by/3.0/), version 3.0 or later.
All trademarks within this guide belong to their legitimate owners.

ContributorsDan Lewis
Jean Hollis WeberHazel Russman

Feedback
Please direct any comments or suggestions about this document to:
documentation@global.libreoffice.org

Acknowledgments
This chapter is based on a portion of Chapter 8 of Getting Started with LibreOffice 4.0.

Publication date and software version
Published 7 December 2013. Based on LibreOffice 4.0.

Note for Mac users
Some keystrokes and menu items are different on a Mac from those used in Windows and Linux.
The table below gives some common substitutions for the instructions in this chapter. For a moredetailed list, see the application Help.
Windows or LinuxMac equivalentEffectTools > Options menu
selectionLibreOffice > Preferences

Access setup options

Right-click

Control+click

Opens a context menu

Ctrl (Control)⌘ (Command)

Used with other keys

F5Shift+⌘+F5Opens the Navigator

F11

⌘+T

Opens the Styles and Formatting window

Documentation for LibreOffice is available at http://www.libreoffice.org/get-help/documentation

Contents
Copyright..............................................................................................................................2
Contributors................................................................................................................................. 2Feedback..................................................................................................................................... 2
Acknowledgments........................................................................................................................ 2
Publication date and software version.........................................................................................2

Note for Mac users...............................................................................................................2Introduction..........................................................................................................................4Accessing a data source that is not a *.odb file............................................................................4
Accessing a spreadsheet as a data source..................................................................................4
Registering *.odb databases........................................................................................................4Using data sources in LibreOffice......................................................................................5
Viewing data sources................................................................................................................... 5
Editing data sources.................................................................................................................... 5Launching Base to work on data sources....................................................................................6
Using data sources in Writer and Calc.........................................................................................6
Writer documents.................................................................................................................... 6Calc spreadsheets..................................................................................................................8Getting Started with Base3

IntroductionIn addition to creating a new database (*.odb file), LibreOffice allows existing data sources to beaccessed and then linked into LibreOffice documents. For example, a mail merge links an external
document containing a list of names and addresses into a letter, with one copy of the letter being
generated for each entry.

Accessing a data source that is not a *.odb file
To access a data source that is not a *.odb file:1) File > New > Database opens the Database Wizard window.
2) Select Connect to an existing database. Click the arrow next to the Database type fieldand select the database type from the drop down list. Click Next.
3) Click Browse and select the database. Click Next.
4) Accept the default settings: Register the database for me, and Open the database forediting. Click Finish. Name and save the database in the location of your choice.

CautionOne of the choices available when you select Connect to an existing database is
LDAP Address Book. Beginning with LibreOffice 4.0.0, this option no longer works.

Accessing a spreadsheet as a data source
Accessing a spreadsheet is similar to accessing other databases:
1) Choose File > New > Database.
2) Select Connect to an existing database. Select Spreadsheet as the Database type.
3) Click Browse to locate the spreadsheet you want to access. If the spreadsheet ispassword protected, check the Password required box. Click Next.
4) If the spreadsheet requires a user’s name, enter it. If a password is also required, check its
box. Click Next.
Using this method of accessing a spreadsheet, you cannot change anything in thespreadsheet. You can only view the contents of the spreadsheet, run queries, andcreate reports based upon the data already entered into the spreadsheet.

NoteAll changes in a spreadsheet must be made in the spreadsheet itself, using Calc.
After modifying the spreadsheet and saving it, you will see the changes in thedatabase. If you create and save an additional sheet in your spreadsheet, thedatabase will have a new table the next time you access it.

Registering *.odb databases
Databases created by LibreOffice are in the *.odb (OpenDocument Base) format. Other programs
can also produce databases in this format. Registering a *.odb database is simple:
1) Choose Tools > Options > LibreOffice Base > Databases.
2) Under Registered databases, click New.
3) Browse to where the database is located.
4) Make sure the registered name is correct.
5) Click OK.
Introduction

4NoteSometimes when updating LibreOffice to a newer version, your list of registereddatabase files disappears. When that happens, you need to use these steps toregister your database files with your latest version of LibreOffice.

Using data sources in LibreOffice
Having registered the data source, whether a spreadsheet, text document, external database, orother accepted file type, you can use it in other LibreOffice components including Writer and Calc.

Viewing data sources
Open a document in Writer or Calc. To view the data sources available, press F4 or select View >Data Sources from the pull-down menu. This brings up a list of registered databases, which will
include Bibliography and any other databases you have registered.
To view each database, click on the triangle to the left of the database’s name. Tables and Queriesnow show. Click on the triangle next to Tables to view the individual tables created. Now click on atable to see all the records held in it.

Figure 1: Databases

Editing data sources
Some data sources (but not spreadsheets) can be edited in the Data View window. A record can beedited, added, or deleted.
View a table’s data
If you click on a table, its rows and columns of data appear on the right side of the Data Source
window. Editing this data requires only a click in the cell whose data should be changed, change
the data, and click in the row above or below it to save the new data.
Beneath the records are five tiny buttons. The first four move backwards or forwards through therecords, or to the beginning or end. The fifth button, with a small star, inserts a new record.

Figure 2: View Data Sources navigation buttons
To delete a record, right-click on the gray box to the left of a row to highlight the entire row, andselect Delete Rows to remove the selected row.

Figure 3: Deleting a row in the Data View window
Using data sources in LibreOffice5Launching Base to work on data sources
You can launch LibreOffice Base at any time from the Data Source window. Just right-click on adatabase or its Tables or Queries icons and select Edit Database File. Once in Base, you can edit,add, and delete tables, queries, forms, and reports.

Using data sources in Writer and Calc
Data can be placed into Writer and Calc documents from the tables in the data source window. In
Writer, values from individual fields can be inserted. Or a complete table can be created in theWriter document. One common way to use a data source is to perform a mail merge.

Figure 4: Toolbar for the Data Sources window

Tip

Choosing Tools > Mail Merge Wizard or clicking on the Mail Merge icon (circled inred) in the Data Sources window launches the Mail Merge wizard which steps you
through creating a mail merge document. See Chapter 11 in the Writer Guide.

Writer documents
To insert a field from a table opened in the data source window into a Writer document, click on thefield name (the gray square at the top of the field list) and, with the left mouse button held down,drag the field onto the document. In a Writer document, it will appear as <FIELD> (where FIELD is
the name of the field you dragged).
For example, to enter the cost of meals and who paid for them on a certain date of a vacation:1) Open the list of data sources (F4) and select the Vacations table in the Automobile
database.
2) Use this sentence: “On (date), our breakfast cost (amount) paid by (name), our lunch cost(amount) paid by (name), and our supper cost (amount) paid by (name).” But only type
“On , our breakfast cost paid by , our lunch cost paid by , and our supper cost paid by .”3) To replace (date), click the field name Date in the data source window and drag it to theright of the word On. The result: On <Date>. If you have Field shadings turned on (View >Field shading), <Date> has a gray background. Otherwise it does not.
4) To replace first (amount), click the Breakfast field name and drag it to the right of ourbreakfast cost. Make sure you have the proper spacing between the field names and thewords before and after them. Result: breakfast cost <Breakfast>.
5) To replace the first (name), click the Bpayment field name and drag it to the right of paid by.
Result: paid by <Bpayment>.
6) In the same way, fill in the rest of the fields in the sentence.

•Use <Lunch> and <LPayment> for the second set of (amount) and (name) in thesentence.

•Use <Supper> and <SPayment> for the third set of (amount) and (name) in thesentence.
7) Final result: On <Date>, our breakfast cost <Breakfast> paid by <BPayment>, our lunch
cost <Lunch> paid by <LPayment>, and our supper cost <Supper> paid by <SPayment>.
8) Add data to the fields of the sentence:•Click the gray box to the left of the row of data you want to add. That row should behighlighted like the second row of Figure 5.

Using data sources in LibreOffice6

•Click the Data to Fields icon (circled). This should fill the fields with the data from therow you chose.

•

Click another row and then click this icon again. The data in the sentence changes tothis selected row of data.

•Save the document if you want to use it as an example later.

Figure 5: Selected row in data source window
Adding data in table format is a little easier and takes perhaps fewer steps. Some of the steps will
be quite similar.
1) Navigate to the place you want to place the table and click the location.
2) Control+Click the gray box to the left of each row of the data source that you want to be arow in your table if the rows are not consecutive. To select consecutive rows, click the graybox to the left of the top desired row and Shift+click the bottom desired row.
3) Click the Data to text icon to open the Insert Database Columns dialog (Figure 6). (The
Data to text icon is to the left of the Data to Fields icon in Figure 5.)

Figure 6: Insert Database Columns dialog
4) Move the fields you want in your table from the Database Columns list to the Table
column(s) list.

• To place the fields in the order you select, click the field and use the single arrow tomove the fields in the order you desire. You can also limit the fields you use to less than
all of the fields available.

• If you want to use all of the fields, use the double arrow pointing to the right to move allof them at one time. The order of the fields in the table you create will be the same as inthe data source table.

• To remove a single field from the Table Column(s) list, click the field and use the single
arrow pointing to the left.
5) To start over, click the double arrow pointing to the left.
Using data sources in LibreOffice76) Select the settings for your table. Use the default settings as in Figure 6.
7) Click OK. Save the document.

Calc spreadsheets
There are two ways to transfer data into a Calc spreadsheet. One enters the data into thespreadsheet cells. The other creates complete new records in the spreadsheet. While you candirectly access the data inserted into the spreadsheet cells, new records created in thespreadsheet are read-only.
Entering data directly to the spreadsheet cells uses the Data to Text icon as when making a table
in a Writer document. But there are certain differences.
The steps are straightforward.
1) Click the cell of the spreadsheet which you want to be the top left cell of your data,including the column names.
2) Use F4 to open the database source window and select the table whose data you want touse.
3) Select the rows of data you want to add to the spreadsheet:•Click the gray box to the left of the row you want to select if only selecting one row.
That row is highlighted.

•To select multiple rows, hold down the Control key while clicking the gray box of therows you need. Those rows are highlighted.

•To select all the rows, click the gray box in the upper left corner. All rows arehighlighted.

4) Click the Data to text icon to insert the data into the spreadsheet cells.
5) Save the spreadsheet.
Adding records to a spreadsheet is fairly easy. You need to have the Data Source window open,
your spreadsheet open, and the table you want to use selected.
1) Click the gray box containing the field name for the table’s ID field.
2) Drag and drop the gray box for the table’s ID field to where you want the record to appear
in the spreadsheet.
3) Repeat until you have moved all of the fields you need to where you want them.
4) Name and save the spreadsheet.
5) Click a row of the table in the Data Source window.
6) Drag the data in the ID field in the selected row onto the ID field in the spreadsheet. The
Save icon should activate.
7) Click the Edit File button to make the spreadsheet read-only. Click Save when asked if you
want to save the file.
The Data Sources window goes blank, the fields in the spreadsheet are populated withdata from the row you selected, and the Form Navigation toolbar appears at the bottom ofthe spreadsheet.
8) Click the arrows on the Form Navigation toolbar to view the different records of the table.
(The arrows are circled in red.) The number in the box changes when you change therecord number by clicking an arrow. The data in the fields changes correspondingly to thedata for that particular record number.

Figure 7: Navigation arrows of a form
Using data sources in LibreOffice8