If you find these tutorials useful, Please consider making a donation.

TheFrugalComputerGuy.png
TheFrugalComputerLogo.png

Libre Office: Base
Video Tutorial
HomePage












Group 1 - Create a Database Create a Table





Group 2 - Copy from a Calc File


  • Video 3 - Import Data from a Calc File
    Fill the table created in video #2 with data from the calc file using cut and paste. Also shows how to cut and paste when fields are in different order.
    - Download files to accompany Video 3:


  • Video 4 - Create a Table Using a Calc File
    Instead of importing the data into a table that is already created, this time we look at two ways to create a LibreOffice Base table using a LibreOffice Calc file.
    - Download files to accompany Video 4:





Group 3 - Registry and Export to a Calc File





Group 4 - Creating a Form


  • Video 6 - Creating a Form
    Create a form for our employee table to view each employee individually, and how to quickly find a record you are looking for.
  • Video 7 - Design Mode Basics
    Design Mode on/off, Selecting objects, group edit, moving fields, and adding a Label.
    - Download files to accompany Video 7:

  • Video 8 - Add Delete Fields and Tab Order
    We will delete some fields off our form, then add them back and reformat them to match the fields already on the form – Then fix the tab order.
    - Download files to accompany Video 7:







Group 5 - Forms Part 2


  • Video 9 - Protect a Field, Sort, Add Buttons
    We will protect our employee-ID field, then then change the sort order of our table to sort the records by last name (instead of employe-ID), then add some buttons to our form to allow us to select the next and previous records.
    - Download files to accompany Video 9:

  • Video 10 - Filter and Background
    An introduction to the filter on the navitgation toolbar, how to change the background color/insert a background graphic also adding a logo on your form.
    - Download files to accompany Video 10:


  • Video 11 - Radio Buttons
    Add a radio button to replace the Gender Textbox on an updated version of odb10, then we take the original odb10 output change the Gender field type from Fixed Character to VarChar, then we need to update the data in that field, then we can add the radio buttons and check out the data source.
    - Download files to accompany Video 11:





Group 6 - Forms Part 3


  • Video 12 - Dates and Dollar Signs
    First we show how to do a date drop down down box, then we format a date on our table and show how to get the date formatting from the table, then we will create a new field (on our table) with a dollar value and have it show on our form with a dollar sign.
    - Download files to accompany Video 12:

  • Video 13 - Pattern Field
    We will change the phone number field to a Pattern Field to only allow numbers to be entered into our phone number field.
    - Download files to accompany Video 13:


  • Video 14 - Form Properties
    We will rename our table to tbl-employees, then we will look at the form properties to point to the updated table-name. We also look get introduced to the form navigator.
    - Download file to accompany Video 14:




Group 7 - Adding a Picture


  • Video 15 - Embedding a Picture
    We add the employee pictures to our form, the pictures will be embedded into our table – meaning the pictures themselves will be added to the table (Next video will show how to link a picture).
    - Download files to accompany Video 15:


    - Download files to accompany Video 15:


  • Video 16 - Linking a Picture
    We add the employee pictures to our form with a link, keeping the location of the picture on the table, instead of the picture itself.




Group 8 - Intro to Queries


  • Video 17 - Intro to Queries
    We will create simple query and learn some basic information about Sequel/SQL is and how SQL view is different from Design View also known as QBE or Query By Example.
    - Download files to accompany Video 17:

  • Video 18 - Intro to SQL Debugging
    We look at some errors and error messages that a newbie might encounter; and some possible solutions.
  • Video 19 - Alias and Sort
    We first see that I reduced all my pics to 300px high and embedded them, then see what an Alias is and how to use it in Design view as well as Sequel view, then we see that Sort in Design view changes to Order By in SQL view.
    - Download files to accompany Video 19:




Group 9 - Queries Part 2


  • Video 20 - Query Where clause
    How to use the Where clause to filter down our queries in design view as well as SQL view.
    - Download files to accompany Video 20:

  • Video 21 - Where Part 2
    Using the where clause with numeric values and dates.
    - Download files to accompany Video 21:

  • Video 22 - Wildcards
    Using wildcards and single character wild cards to filter the result set.
    - Download files to accompany Video 22:




Group 10 - Queries Part 3


  • Video 23 - NULLS
    We look at what Nulls are (The value in a field before anything is assigned to it) and the difference between Nulls and Empty (or no value '').
    - Download files to accompany Video 23:


  • Video 24 - LibreOffice Base (24) Query Between
    Instead of using Less Than and Greater Than we show how to use Between or NOT Between to create a selection filter range.
    - Download files to accompany Video 24:

  • Video 25 - LibreOffice Base (25) Query IN
    How to use IN and NOT IN to filter down our result set - Instead of Less Than and Greater Than
    - Download files to accompany Video 25:




Group 11 - Queries Part 4


  • Video 26 - Limit Distinct Calculations and Constants
    We first see how to limit the number of records in our result set, then see the distinct feature, we calculate the weekly and annual salary, then we look at how to create a constant value in our output.
    - Download files to accompany Video 26:

  • Video 27 - User Parameters Part 1
    We look at how to create a query to search for a specific employee ID. Then we apply the same principles for a date range query.
    - Download files to accompany Video 27:

  • Video 28 - User Parameters Part 2
    We add wildcards to our user input query and use it to create a simple form with a user input parameter.
    - Download files to accompany Video 28:

  • Video 29 - Count and Group
    Function in design view – How to count up the number of records on a table or for a result set – We can also group by a field (like department) and count or summarize a numeric field by group.
    - Download files to accompany Video 28:




Group 12 - Why we need multiple tables


  • Video 30 - Why we need multiple tables
    We look at a situation where a single table is just not practical and we describe how a second table will resolve the problem.
    - Download files to accompany Video 30:

  • Video 31 - Query Insert
    We create the employee phone number table and populate it using the SQL command INSERT.
    - Download files to accompany Video 31:







  • Video 32 - Query Update Delete
    We look at how to update records on a table using an SQL query. We use wildcards and update for Nulls and empty. Then we delete the records using a delete query.
    - Download files to accompany Video 31:















Group 13 - Creating a Subform


  • Video 33 - Create a Subform
    We create a form with a simple subform using the employee table as the main form and the phone number table we created in videos 30 and 31 as our subform.
    - Download files to accompany Video 33:

  • Video 34 - Creating a Relationship
    We create a 1 to many relationship then we add an update cascade and a delete cascade constraint to our forgien key for that relationship, we also look at a common relationship error message and what causes it.
    - Download files to accompany Video 34:

  • Video 35 - Repositioning Fields on a Form
    We move fields around the old employee form to make room for a subform – the background is changed and some columns are aligned too.
    - Download files to accompany Video 35:

  • Video 36 - Adding a Subform (to an existing form)
    We add a subform to our original (single table) Employee form (reformatted in video 35) to make it look and work like video 33's Create a simple subform.
    - Download files to accompany Video 34:




Group 14 - Normalizing


  • Video 37 - Normalizing Part 1
    We start off by talking about what is normalizing a database(reducing redundant data) why we should normalize our tables (To have the data in one place and to reduce the file size) Then we create a separate Group table to normalize the Group Data.
    - Download files to accompany Video 37:



  • Video 38 - Normalizing Part 2
    We create a separate department table and we add a contact person to the department (and a relationship for the contact person)
    - Download files to accompany Video 38:



  • Video 39 - Normalizing Part 3
    We rename the original employees table and copy the information into a new employees table with new constraints then we review and create relationships for the new employee table.
    - Download files to accompany Video 39:







Group 15 - SQL Join


  • Video 40 - SQL Join Part 1
    We see how to create a query to join the department and group tables to the employee table in both design view and SQL view.
    - Download files to accompany Video 40:

  • Video 41 - SQL Join Part 2
    The difference between an inner and Left (or Right) outer join and how to do them in LibreOffice Base.
    - Download files to accompany Video 41:


  • Video 42 - SQL Subquerys
    How to find all the foreign key records that do not have a primary key using a subquery (a query inside a query). Subquerys can produce the same result set as a Join as well the opposite of a Join result set
    - Download files to accompany Video 42:





Group 16 - SQL View/Update Constraints


  • Video 43 - SQL Edit a Constraint
    We look at a few queries that shows information about the tables and fields we created from the HSQL database . Then we update a CHECK CONSTRAINT.
    - Download files to accompany Video 43:



  • Video 44 - Review of Relationships
    We see the Default value does not work properly LibreOffice (v4.2.0.4) GUI screens, then we review how Update Cascade and Delete: Set to Default work.
    - Download files to accompany Video 43:

  • Video 45 - More about Constraints
    We want to change the Department ID and Group ID fields to not null (set entry required to Yes), but we have to get around the constraint (as well as an issue with LibreOffice 4.2.02 gui screens)
    - Download files to accompany Video 45:






Group 17 - Multi-Table Forms


  • Video 46 - Form from Query
    We create queries to capture the data we want to show on an employee view form (with a subform) and see why one query allows updates and the other does not.
    - Download files to accompany Video 46:

  • Video 47 - Edit Form Part 1
    We start creating the Employee Edit form by placing the fields we want to see on the form, we add our sub-form from our existing relations, and re-position our fields. We'll change the Department-ID and Group-Id to Department and Group Name in part 2.
    - Download files to accompany Video 47:

  • Video 48 - Edit Form Part 2(ListBox)
    We use List boxes to edit the Department Name and Group Name and make a few other changes to complete our employee edit form
    - Download files to accompany Video 48:




Group 18 - ListBox


  • Video 49 - ListBox No Wizard
    We create another Department ListBox just like the previous video, only this time we don't use the wizard and we get into a little more detail about how the ListBox works.
    - Download files to accompany Video 49:



  • Video 50 - Single Table ListBox
    We show how to create a ListBox for a Single Table and how to get around some common problems creating a single table ListBox.
    - Download files to accompany Video 50:

  • Video 51 - ListBox ValueList
    We create a ValueList for a ListBox on a form with a single table ListBox and then for a form with a normalized ListBox
    - Download files to accompany Video 51:

  • Video 52 - Combo Box
    We look at how to create a Combo Box and then how it is different than a List Box.
    - Download files to accompany Video 52:




Group 19 - Subform Filter


  • Video 53 - View by Department pt 1
    We use design view to start to create a form that will show our employees list, in a subform, filtered by a department that we choose from a dropdown list (part 1 of 2).
    - Download files to accompany Video 53:

  • Video 54 - View by Department pt 2
    We complete the form that will show us the employees filtered by a department that we choose from a Listbox dropdown. (part 2 of 2)
    - Download files to accompany Video 54:

  • Video 55 - Add a Wildcard
    We add a wildcard filter for the last name to the department filter created in video 54. This will allow us to filter by all or part of the last name as well as the department filter.
    - Download files to accompany Video 55:



  • Video 56 - Employee Search Form pt1
    We add some more fields on to our employee search form (dates) and try to make it look a little nicer – part 1 adds the new fields (dates).
    - Download files to accompany Video 56:

  • Video 57 - Employee Search Form pt2
    We complete our Employee Search Form by reformatting our form to look nicer and completing the Less Than and Greater Than Start Date feature.
    - Download files to accompany Video 57:





Group 20 - Cleanup / add Current Date


  • Video 58 - Cleanup and Review pt 1
    We clean up the database by removing unused Tables, Queries, and Forms. Then we review how to get the most out of what we've learned so far, using 3 forms. An Insert Form, an Edit Form, and a Search Form.
    - Download files to accompany Video 58:


  • Video 59 - Cleanup and Review pt 2
    We clean up the Search for Employee Form and Edit Employee Form and talk about how they work together.
    - Download files to accompany Video 59:

  • Video 60 - Add Current Date
    We add a 'Date Entered' field – To record the current date and time, every time a new employee record is added.
    - Download files to accompany Video 60:







Group 21 - Standalone Forms


  • Video 61 - Standalone Forms
    We learn how to run the forms we created straight from our desktop as a standalone form. Note: Macros will NOT work in standalone forms
    - Download files to accompany Video 61:

  • Video 62 - Standalone Switchboard
    We look at how to open our standalone forms from a button from the database as well as from a standalone form.



Group 22 - Junction Tables


  • Video 63 - Junction Tables part 1
    We add a new table for training and we want to list the training by employee as well as the employees by the training – this requires a junction table – that we create in this video and show how it works.
    - Download files to accompany Video 63:


  • Video 64 - Junction Tables part 2
    We start the Junction Table Form showing all the training for an employee using the junction table.
    - Download files to accompany Video 64:


  • Video 65 - Junction Tables part 3
    We complete the Junction Table Form by adding a subform to select employees by training.
    - Download files to accompany Video 65:




Group 23 - Reports 1


  • Video 66 - Intro to reports
    As in introduction to the report builder feature, we build 2 small simple reports.
    - Download files to accompany Video 66:

  • Video 67 - Report Headers
    We add some basic headers and footers to the reports we created in the previous video including current date/time and page numbers.
    - Download files to accompany Video 67:

  • Video 68 - Reports from Design View
    We create the same reports we created in the previous videos in design view (instead of using the Wizards).
    - Download files to accompany Video 68:




Group 24 - Reports 2


  • Video 69 - Reports Intro to Grouping
    We look at grouping our report by employee ID – We also add in some space and separator lines.
    - Download files to accompany Video 69:

  • Video 70 - Reports Conditional
    We create a report from the Employees table and use “IF()” to change the Department ID to a Department Name. Then we use the "ISBLANK()" in an IF to set a value for a field that is NULL. We also highlight values greater than 40 by making them bold and changing the color.
    - Download files to accompany Video 70:


  • Video 71 - Reports More Grouping pt 1
    We create a report that groups employees by their group, which is also grouped by department with each department on a separate page.
    - Download files to accompany Video 71:

  • Video 72 - Reports More Grouping pt 2
    We add a count for the Group and Department groups, we also accumulate the hourly wages by Group and Department.
    - Download files to accompany Video 72:




Group 25 - Home Invoice


  • Video 73 - Home Invoice pt1 - Tables
    We want to manage and create invoices (similar to the sample invoice we created using LibreOffice Draw). In this video, we look at the data on the sample invoice and create tables to hold the information that we want on our invoice.
    - Download files to accompany Video 73:


  • Video 74 - Home Invoice pt2 - Forms
    We add the Customer Form and an Invoice Form to our database.
    - Download files to accompany Video 74:

  • Video 75 - Home Invoice pt3 - Add Data
    We add some data to our tables and fix a couple of issued with our forms.
    - Download files to accompany Video 75:

  • Video 76 - Home Invoice pt4a - The Invoice
    We start the report that will create the actual invoices (The invoices that will look like the LibreOffice Draw Invoice we saw in part 1)
    - Download files to accompany Video 76:

  • Video 77 - Home Invoice pt4b - The Invoice
    We continue working the form making the address, state, and zip code look nice (in a single field) and we start adding in the box outlines.
    - Download files to accompany Video 77:

  • Video 78 - Home Invoice pt4c - The Invoice
    We complete our invoice report.

    Working on the invoice report, I try to re-size a label by using the handles and LibreOffice crashes. I restore from my backup, and I am able to complete the report. We finish by comparing the LibreOffice Draw sample invoice to the completed invoice.
    - Download files to accompany Video 78:




Group 26 - Email from LibreOffice


  • Video 79 - Email from LibreOffice
    We look at the possibilities of sending an e-mail from LibreOffice. (The idea of e-mailing the invoices completed in the previous tutorial)

    At this moment, LibreOffice cannot e-mail a document out by itself, it can open Thunderbird and set a LibreOffice document up as an e-mail or as an attachment(in various formats). LibreOffice has started an e-mail feature; this can be seen by clicking on the "Mail Merge Wizard..." feature in LibreOffice Writer. This functionality is not (yet) working



Group 27 - MySQL


  • Video 80 - Install MySQL
    In this video we see how to install MySQL.

    Over the next few videos, we are going to look at how to connect LibreOffice Base to a MySQL database. This video will show you the options I chose when installing MySQL on my computer.
  • Video 81 - Connect with ODBC
    We see how to use the ODBC connector to connect LibreOffice Base to a MySQL Database
  • Video 82 - Connect Directly with problem
    We create another MySQL database and connect directly to the database. We are able to successfully create tables for this database, proving LibreOffice is connecting properly, but we can't view the tables, so we can't create any data for the tables or create any forms using LibreOffice. In the past, I have gotten this to work properly, on this computer, but for some reason, right now, I cannot view my tables.
  • Video 83 - Connect Directly
    On a different computer, I connect directly to MySQL and we see how to create tables, add data, and create a form (with a subform) by connecting to MySQL directly
  • Video 84 - Connect with JDBC
    We see how to connect LibreOffice Base to a MySQL database using JDBC (Java Database Connectivity)

    When we first attempt to connect LibreOffice to a MySQL database using the JDBC connector, we get the error “The JDBC driver could not be loaded” for com.mysql.jdbc.driver.

    We see how to solve this problem by identifying the mysql-connector-java-5.1.34-bin.jar file, then in LibreOffice Tools, Advanced, Class Path, adding that folder and getting the proper file name into the open file area.

    Having resolved the problem, we then go on with the same tests we did with the Direct and ODBC connections showing the Java connection seem to work best of the three connection types.
  • Video 85 - Multiple MySQL Users
    We add Multiple users to MySQL and give different authority to each user.

    First we add a user from the MySQL Command Line Client, this user can only view a specific table on a database.

    Then we use the MySQL Workbench, Users and Permissions to add a MySQL user with full access to any database that starts with the first three letters of odb.

    We add one more user using the MySQL Workbench, Users and Permissions to give a user the authority to view a specific database.



Group 28 - MySQL Workbench


  • Video 86 - MySQL Workbench (1)
    We take a first look at MySQL Workbench a tool that can help us manage our LibreOffice Base MySQL databases.

    We see we can view all of our LibreOffice MySQL databases as well as see information about the database or information about a table on the database.

    We update a MySQL table using MySQL Workbench – and that it creates SQL to apply the change.

    Then we use MySQL Workbench to update information on a MySQL table and that MySQL Workbench creates SQL to apply our update.

    We also see MySQL Workbench has a button to reformat SQL, similar to the way I would pause the video and reformat the SQL in the LibreOffice Base SQL videos.
  • Video 87 - MySQL Workbench (2)
    We learn how to reverse engineer a MySQL Database using MySQL Workbench.

    We reverse engineer a MySQL database, which means create a model and an EER (Enhanced Entity Relationship) Diagram from an active MySQL database (schema).

    We look at how to add a field to the model as well as add a table, then we synchronize that model with the table on the database.

    We also see how to save our model to our desktop.
  • Video 88 - mysql_searchform (1)
    We create a new MySQL database called mysql_searchform and all the tables for it.

    We create a mysql workbench model called mysql_searchform.

    We create tables for our new model similar to the tables we used in LibreOffice Base tutorial (57) Employe search Form pt2.

    We then forward engineer a database from our model to create the new MySQL database mysql_searchform
  • Video 89 - Create .csv files
    We create .CSV backup files from the odb57 LibreOffce Base tables to upload into the new MySQL files.


    We need to edit the data a little bit to have it load in properly to the MySQLfiles. We need to delete the Header Records and change the Date Format to be YYYY-MM-DD.
  • Video 90 - Upload .csv files

    We update MySQL tables with the .csv files we created in the last video.

    We see the SQL used to upload a .csv file into a MySQL table using MySQL Workbench.

    When we upload the employee table, everywhere MySQL Workbench finds a NULL for the End Date, a date of 0000-00-00 is forced in. This date will not work with LibreOffice Base and we need to create some SQL to reset the zero date to null.

    We also learn we need to turn off the MySQL Safe Update feature by using SET SQL_SAFE_UPDATES=0; before running a MySQL UPDATE.

  • Video 91 - MySQL Relationships

    We create some relationsips for the MySQL tables using MySQL Workbench.

    We see how to create a basic 1 to many relationship and set the foreign key parameters when the primary key is deleted (Cascade, Set to Null, No Action).

    Then we look at how to change the colors of the tables and add layers to group like tables (for visual model use only – no effect on MySQL performance)

    We finish by applying the relationship changes to the database.

  • Video 92 - MySQL Pictures

    We add pictures into the MySQL employee table.

    We look at the MySQL employee table and see what type of blob (Binary Large Object) we should use to store our pictures (Tiny Blob, Blob, Medium Blob, or LongBlob)

    Then we add a picture into the MySQL database using MySQL Workbench.

    We end by creating a LibreOffice Base database form that will display the picture from the MySQL database and we will add another picture to the MySQL table using that same LibreOffice Base form.

  • Video 93 - odb93 MySQL Searchform

    We start to create the LibreOffice Base form to search the for employees on the MySQL database.

    We start creating the LibreOffice base document by connecting it to a MySQL database using a JDBC connection.

    We connect to the filter table to the form and the employee table to the subform.

    We add a header, arrange and format fields on the form, and add a refresh button.

  • Video 94 - odb94 MySQL Searchform

    We complete the LibreOffice Base form, that searches for employees on a MySQL database, that we started in the previous video.

    First we add a dropdowns to the Department and Group text boxes in the search criteria. Then we change the Department-ID and Group-ID to Department and Group in the subform and reformat the Start Date in the subform.

    We add the variable link fields for the SQL join and add the SQL from odb57 into the SQL command area.

    We test the form and find that most of the SQL is good, but the MySQL SQL is different than the HSQLDB SQL for upper case and concatenate. We need to use UCASE instead of UPPER to get our data to the upper case and CONCAT() in place of || to concatenate the fields together.