Category: Advanced Database

[qsm quiz=13]

Another way to directly run a macro is to create a button in the form.

In the main Base window, right click the form and click Edit to open it in design view.

[the_ad id=”12355″]
[the_ad id=”12356″]

Select the ‘Push button’ icon from the controls toolbar.

Click and drag in the form to draw a button.

Right click on the button and select Control.

In the properties window, click the events tab.

Click the … symbol next to the ‘Mouse button pressed’ section.

Click the Macro button in the Assign section.

Navigate to the macro we created earlier and select it.

Click OK twice.

In the properties window, click the General tab.

In the label section type ‘Create New Record’.

This changes the text displayed on the button.

Close the properties window save the form and exit the design view.

Double click to from the main window to open the form.

Now, if you want to add a new record you can just click the push button.

Again, this makes it much clearer for a novice database user when inputting data through the form.

[the_ad id=’12397′]

Open the form and select Tools->Customize

Select the ‘Standard’ Toolbar from the drop-down list.

[the_ad id=”12355″]
[the_ad id=”12356″]

Click Add Command.

At the bottom of the Category section, select LibreOffice Macros.

Navigate to the ‘Newrecord’ macro we created.

Click Add.

Notice that there is now a button on the Standard form toolbar that says Newrecord.

If you click on this button it runs the macro and creates a new record.

This makes it a bit simpler and more clear for a novice user, rather than asking them to use the New record icon.

[the_ad id=’12397′]

First, make sure that macro recording is enabled.

Open a Libreoffice Base database file. From the main window taskbar select Tools->Options then select LibreOffice->Advanced.

Check the optional feature: Enable macro recording.

[the_ad id=”12355″]
[the_ad id=”12356″]

Please note that macro recording is limited in LibreOffice Base due to program stability issues.

In the current version of Base, macros can only be recorded to modify forms.

This is due to the fact that it can often be useful to add a button to a form that runs a macro, as we will see later.

We are going to record a simple macro that adds a new record.

Open any form in the database.

In the form window, go to Tools->Macros->Record Macro

Any actions you take from now on will be recorded in the macro.

Click the New record icon.

Next, click the ‘Stop Recording’ button.

Name the macro ‘Newrecord’ then click ‘Save’.

We will use this macro in the next tutorial so remember where you save it.

To run the macro, re-open the same form.

Go to Tools->Run Macro

Select the Newrecord macro and run it.

It will automatically create a new record, without having to select the icon at the bottom of the page.

[the_ad id=’12397′]

Open two separate database files.

To import data, you can simply drag and drop between the windows.

[the_ad id=”12355″]
[the_ad id=”12356″]

For example, to copy the Names table from the lower database in the example shown, left click and hold on ‘Names’ in the Tables section and drag it to the Tables section of the other database.

A dialogue box will appear in which you can choose to copy various parts of the data.

If you select ‘Definition and data’ the entire table will copy over. ‘Definition’ just copies the field names and formats. ‘As table view’ and ‘append data’ will work if the data is in already in the same format, and add new data to the table.

Select ‘Definition and data’ and click Next.

Here you can select which fields to copy over.

Click the double arrow to select them all.

Click Create.

Open the new table in the other database and it will be identical to the original one, as all of the data has been imported.

[the_ad id=’12397′]

To link data from a different database source, open a new LibreOffice Base file.

[the_ad id=”12355″]
[the_ad id=”12356″]

In step 1 of the wizard, choose ‘Connect to an existing database’ and select the format of the database e.g. Microsoft Access 2007.

Click Next and then select a database file.

Finish and save. The database will then import data from that database and any changes will automatically update.

Import spreadsheet, text (.txt, .csv), XML into a database.

There is no automatic way to import data from a spreadsheet or document into Base.

You must simply copy and paste the values into a table.

This method is outlined in full in the Using Databases module.

[the_ad id=’12397′]

For this tutorial, we are going to create a text file in LibreOffice Writer.

Open LibreOffice Writer.

[the_ad id=”12355″]
[the_ad id=”12356″]

Enter the text shown. The first line designates field names, separated by commas. Each new line represents a new record with fields in the same order as the top line.

Select Save As from the File menu.

Name the file and set Save as Type: to ‘Text (.txt)(*.txt)’

Exit writer and open a new database in LibreOffice Base.

In the wizard, select Connect to and existing database -> Text

Click Next.

Click Browse in the ‘Set up a connection to text files’ section.

Select the folder that contains the text file we created.

Click OK.

Click Next.

Select to register the database and open it for editing.

Click Finish, name the database and click Save.

The database will automatically open for editing.

Go to the Tables section and double-click the table with the same name as the text file as we created.

Notice that the table has been populated with the text that was saved in the document.

As the database is connected to the text file, you cannot add tables directly to the database.

To add a table, create a new LibreOffice Writer file and add some data in the same format as previously.

Save the file in the same folder as the previous text file (it must be the same folder for it to work).

Exit Writer and re-open the connected database file.

As the database is linked to the folder, the text file we just created will automatically create an identical table.

We can also add or edit the records within these text files and it will automatically update the database.

[the_ad id=’12397′]

For this tutorial, first we are going to create a LibreOffice Calc spreadsheet. Open a new Calc file.

[the_ad id=”12355″]
[the_ad id=”12356″]

In sheet 1, enter the data shown to the left. Then right click on the tab for the sheet and select ‘Rename Sheet’

Type Customer Names and Ages, click OK.

Save the sheet as ‘Example 1’

Click the + button to add a new workbook.

Enter data for phone numbers as shown and rename the sheet to Phone Numbers.

Save the spreadsheet, then exit.

Open a new Libreoffice Base file.

Linking data (unlike importing data) allows users to work on the same data in different applications and changes are updated between the two.

In the Base wizard, select ‘Connect to an existing database’ and select ‘Spreadsheet’.

Click Next.

Browse to the Calc spreadsheet we just created and select it to set up a connection.

Click Next.

Select Yes, to register the database.

Check the box for ‘Open the database for editing’.

Click Finish and name the database and save it.

Notice that the database has automatically created two tables based on the two workbooks in the Calc spreadsheet.

Open one of them to see that the data has been automatically transferred.

Save the database, exit Base and re-open the Calc file.

Add a new row to the Customer Names and Ages workbook.

Save the spreadsheet, exit and re-open the Base file we just created.

Open the Customer Name and Age table.

Notice that the data we added in the Calc spreadsheet has been automatically added to the database.

As they are linked any changes made in the Calc file will update in the Base file and vice versa.

Note: you cannot create new tables in the database, but you can create them by adding workbooks in Calc.

Exit Base and open the Calc file again.

Add a workbook, add some data and rename it to Addresses.

Save and exit Calc.

Open the database in Base again.

Notice that the Addresses table has been automatically added to the database.

Also notice that the Create Table options are greyed out, meaning you cannot add tables through the Base application.

[the_ad id=’12397′]

Select the Surname group section on the left hand side (in blue)

In the properties section on the right hand side, where it says ‘Force New Page’ select ‘Before Section’.

This will insert a page break before each new section.

Execute the report to see the effect of this change.

Notice that a page break has been inserted between each group record.

To change the sorting and grouping in a report, click the sorting and grouping icon.

In the window that pops up, you can select the grouped fields and change their properties.

You can choose to sort ascending or descending, as well as choose whether to display headers and footers.

Execute the report with the settings shown by clicking the execute report icon.

Notice that the report displays the surname in ascending alphabetical order.

Close the report and return to the report builder view.

Open the sorting and grouping window.

For Surname change sorting to Descending.

Select ‘First Name’.

For Group Header select ‘Not present’

Execute the report.

Notice that the report is sorted by Surname in reverse alphabetical order (descending).

Also notice that the First Name group is no longer present as we turned off the group header.

This means that it is still grouping the field, but not displaying it in the report.