Simple LibreOffice Database Without Code

The following article will demonstrate how to build out a useful LibreOffice database without using any programming code.

We’re going to build a simple application to manage training courses and students for these courses.

Step By Step Example- Student Course Management

This training course and students example is perfect for a tutorial, because it handles a topic that’s difficult to solve without code, that being how to manage "many to many" relationships as explained below.

Specifically a training course can have many students and a student can be enroled in many courses.

In order to pull of this complex relationship we’ll make use of a mapping table to join multiple students to courses and vice versa.

The best way to get on top of this might be to follow along and build this database in LibreOffice base, It shouldn’t take more than an hour of your time and you’ll have gained some worthwhile skills.

The end product should look something like the following image, note that the buttons at the base of the form don’t need code either!.

First lets create the tables for this database. Execute each of these create table queries seperately.

01  create table "tblCourse" (ID Integer Identity not null, "CourseName" varchar(255))
02  create table "tblStudent" (ID Integer Identity not null, "FirstName" varchar(255),"LastName" varchar(255), "LocationID" integer)
03  create table "tblMapCourseStudent" ("CourseID" Integer not null, "StudentID" Integer not null , CONSTRAINT PK_CourseMember Primary Key ("CourseID", "StudentID"))

On the LibreOffice base menu, choose "Tools" then "SQL…", then paste each of the above lines in and execute the query to create each of the tables in your new LibreOffice database.

Rinse and repeat for each of the three tables.

Now let’s create the form

This form will have many datasources (these are called forms in the LibreOffice form designer).

The main datasource will be based on the mapping table "tblMapCourseStudent".

Add a new form without using the wizard. Click on the form navigator button on the “Form Design” toolbar.

Right click on forms to add a new form (aka datasource).

Rename the form datasource to dsCourseStudent.

Open up the form in Edit mode (right click on form and choose edit) , then click on data source (dsCourseStudent), launch the properties dialog for this, then choose “tblMapCourseStudent” the mapping table.

Next we need to create datasources for courses and students, these will be linked up within this form.

Lets create the students datasource first.

This datasource is created under the dsCourseStudent datasource, so with “dsCourseStudent” highlighted right click “New” and “Form” then rename this form to “dsStudents”.

Click on the “Data” tab, set the content type to “Table” and choose “tblStudent”.

Ignore "tblStudentLocation" below (I’ve decided not to use it in this tutorial).

Now it’s time to set the Courses datasource. Click on “dsCourseStudent”, then right click for New then form, rename the form to dsCourses.

Now we need to add the courses table to dsCourses.

With all the datasources connected, it’s now time to build out the form. Let’s use a grid to add students and courses combinations to the database.

Here I’m choosing the table control, it’s important to have the correct datasource selected when choosing a control as that’s the datasource that will feed the control.

In this instance dsCourseStudent was selected (the mapping table). I’m going to want drop down functionality to add students and courses together, note however we’re going to use the listbox rather than the combobox control, I know – go figure…

So let’s add the combo boxes (sorry listboxes) now…

Position your cursor over the top bar of the grid then right click and choose Listbox.

Click on the listbox column header and set it’s properties using the properties window.

On the General tab the most important settings are "Name", "Label" and "Width".

The Data field property has the field in the mapping table that I want updated (see below), in this case “CourseID”, however the dropdown will show the course name that corresponds to the given course ID.

This is achieved by using a SQL select statement that places the course name first followed by the CourseID and specifing the bound field to be 1 (zero based, meaning the first field is 0, the second is 1) which corresponds to the course ID.

In the SQL below the second instance of "tblCourse" is for an alias, meaning you could call the table "C" for example in order to minimise typing or minimise the amount of text in a verbose SQL query.

SELECT "CourseName", "ID" FROM "tblCourse" "tblCourse"

The above SQL could also be written as…

SELECT "C"."CourseName", "C"."ID" FROM "tblCourse" "C"

The above syntax becomes very useful when dealing with joined tables which can appear complex real fast without the help of aliasing (but that’s and aside).

As the query is simple you don’t need to bother with aliasing.

Repeat the same process for students using the student table.

Right click on the top bar of the table to get the context sensitive menu below…

Here we use the pipe symbol “|” to concatenate the "LastName" and "FirstName" fields, seperated by a space, the "ID" field being in position 1.

SELECT "LastName" || ' ' || "FirstName" as "Name" FROM "tblStudent" "tblStudent"

Now it’s time to add a new record by using the drop down and then moving the record selector.

Before you do this you’ll need to add some records to the students table and the courses table, for the purposes of this tutorial just open the tables and type the records in directly, but in a more "polished" database, you’d use forms to input these records.

Sample course names and student names can be seen in the images further down.

The listboxs that you added to the table control on your form below will behave like dropdowns, which is the behaviour we want.

Add a course by choosing one of the course you added to "tblCourse" from the "Courses" dropdown below.

Choose a student from one of the students you added to "tblStudent" using the dropdown illustrated below.

If you have not yet added them, do it now 🙂

Once both records have been chosen, click in the next row of the grid or choose "next" or "previous" record to move the record selector to a new record and thus add your course and student combination to the database.

You can verify that the update happened by checking "tblMapCourseStudent" and its ids against "tblCourse" and "tblStudent".

Finishing Touches

Now to make the database form look like the first image in this tutorial including adding the codeless navigation buttons, follow the instructions below with the help of the following image.

Above image represents the form in design view.

The toolbars menu is displayed, showing you that the "Form Controls", "Form Design" and "Standard" toolbars are displayed.

These are kinda necessary when building your forms, but you most likely dont want them displayed when your database is in use, so just untick them when using the database.

If you look at the "Form Navigator" panel, you will see that various buttons have been attached to various datasources, this is easiest accomplished by clicking the datasource prior to drawing the button.

In the Navigator you can see that "dsCoursesOverview" has the buttons "pbNextCourse" and "pbPreviousCourse" attached to it and further to that there is a sub datasource (linked to dsCourseOverview via properties link master and link child fields) called "dsCourseStudent" that has the buttons "pbNextCourseStudent" and "pbPreviousCourseStudent"

"pbPreviousCourseStudent" is highlighted in yellow in the properties window with a green arrow pointing to the relevent button.

Note on the properties window that you can easily assign an "Action" to the button from the drop down list, in this instance "Previous Record" was chosen.