LibreOffice Base Database Tutorial No Code Required
Vložit
- čas přidán 8. 09. 2024
- How to create a relational database using forms and subforms without using macro code.
In this tutorial, I walk you through the creation of a useful database that manages courses and the students enrolled in these courses.
Together we build a complex form with four main sections and a total of 7 independent table grids.
This tutorial covers creating the database from scratch.
Using SQL to create the tables.
Using SQL to load some data to the tables.
Designing the forms and subforms.
Writing SQL queries to make the forms work.
Creating Useful forms in LibreOffice Base
LibreOffice Base complete playlist (all lessons):
• How To download Libre ...
☕
If you found this tutorial helpful consider supporting this channel with a steaming hot cup of coffee... ko-fi.com/sean...
☕
BlogPost Link for this tutorial www.businesspr...
Resources
============
LibreOffice Base (book is titled OpenOffice) PROGRAMMING amzn.to/3sSK3xg
Video Production
===============
📷 Sony Camera: amzn.to/3mqyqfu
Elgato HDMI Stream Capture: amzn.to/2Yfyrum
Microphones
-----------
🎤 Screen Casting: amzn.to/3a2TnHN
🎤 Microphone and Arm: amzn.to/3iwvL2S
🎤 Talking To Camera: amzn.to/3FqXI6b
Video Kit
-------------
Lights
Elgato video Lights: amzn.to/3sSK3xg
Background Light: amzn.to/3uTXyPh
Note Taking
-----------
Rocketbook reusable notebook amzn.to/3sSK3xg
Other
Rocketbook reusable notebook amzn.to/3sSK3xg
#libreoffice #openoffice #businessprogrammer #LibreOfficeBase #Databases
DISCLAIMER: This video and description contain affiliate links, this means if you click on one of the product links, I might receive a small commission.
As always, this video excels! Besides giving you my congratulations, I thank you a lot! It's so rare to find practical things with LibreOffice, not to say with Base. Your videos are very well explained and help us to understand what things are for. Thanks, again!
Glad you like them!
Dear Sir, I would like to address these few lines to you. It is very strange that just yesterday I started to study SQL commands in open office and what to see just one day you decided to continue the tutorial with SQL commands in the database. And in the free office, which is a rarity. So I must congratulate you on this. I wish you good health and success in everything you do. With thousands of respects Fekete Alexander from Romania, the city of Oradea.
Thanks Fekete, Glad I could help you.
This is a REALLY helpful video! Also, I really like the speed at which you go through things. I'm having to pause and skip back a lot, which I'm happy about because I'm sure I'm going to be coming back to it a few times to fill in the pieces and remind myself of stuff and it will be at an ideal speed for that.
Glad you found it helpful Nick
Thank you for taking the time and effort to create this tutorial.
You're welcome Terry, Glad you liked it.
Sean another great tutorial...definitely going to complete one for myself.
Glad you found it helpful Andrew.
Sean, just when I start finishing my form set using macro/VB to build the various forms and text boxes you make a tutorial on how to do it all with SQL and no VB (I prefer SQL as it is far clearer and easier to support and modify). Thanks again for a great instructional video, including the speed which I quickly slowed down from my default 1.5, though I followed it just fine at high speed, taking notes was impossible. One useful aspect of watching it at high speed is the repeating patterns emerge which is an important element of this solution you created. One aspect that would be nice to see is a pre or post explanation / display with enlargement of all the buttons you quickly press for each function, both those in the menus around the workspace and those inside the various property boxes. This exposition will calm the student as they will discover you aren't using a large number of the possible choices available. Another possible way to ease the student's learning burden is creating short 'loops' of button traversal, calling them an appropriate name, like 'DS Form Creation' and 'DS SubForm Creation' or 'Textbox Definition'. Separating these repeating patterns/traversals allows you to expand on what each step does, even if it is quickly obvious, like a button size / location specification. These traversals become 'tools' for the student (as they clearly are for you) to construct useful elements of a database solution, allowing the student to see the process at a more general level which is how they will operate once they understand what they're doing.
Hi Chas
Glad you enjoyed the video, re your points about the buttons, I had to work hard to keep the video to the half hour mark, really 8 minutes is the sweet spot for videos ;-), so I had a total fail at 32:04 :-)
Because the buttons are not related to code, but rather actions, they didn't need names as such, however if they were code related I would name them with a convention like dataasourceName_buttonFunction (as they would be attached to a particular datasource/form).
Thanks. This is a very useful tutorial. Just what I needed.
Glad it was helpful!
great Tutorial. Thank you so much.
Hi patarachai
Glad you enjoyed it!
Great tutorial. Thank You!
Glad you found it helpful, Mo
Thanks!
Many thanks for the super thanks, Much appreciated
Very informative and helpful (once I'd slowed it down to 75%)
Is there a way to show the finished form without all the LO menus etc?
Yes, I've done it for personal projects, but it requires code.
Hi Sean, I appreciate this explanation. I wanted to create add a student to a course, however your tutorial did not include that. I thinking of building a little application where the table insert has 3 reference (one to many) tables. Do you have a video that explains this type form creation?
Not sure if I have, but normally you would have to do three inserts in your case, either the code does it or you do it. for example if you wanted to add a customer for your business and he was in a state that you didn't previously have in your database, you might click a button for states, see that you don't have that state, so you add it with a SQL insert, then add the town and choose the state as it's state etc.
You can of course do this without code by having dedicated forms for each of those operations and opening them using the techniques in this video.
My dear teacher: is it possible, in Base, to create a field in a form, where, when the user types, an asterisk or other special character appears, as it happens in password-type fields? Thank you very much and congratulations for the tutorial.
It is taking me a lot of time to find a way to quickly and simply query an Excel spreadsheet or Google Sheet now that MS Office no longer has the Access database.
I happen to also have an older machine with Linux Mint 21, which means I have Libre Office.
Can Sean / anyone say whether spending another 30 minutes (here) is likely to give me a solid start, or even whether I ought to stick with Google Sheets (where the data is) and learn about Google API or Google forms.
I have a windows PC and a Linux PC, both of which can access Google online.
Thank you, I hope it's not too presumptuous to ask.
Hi Stan
(I'm guessing that's your name from your handle, unless it's short for staying ;-) ), Microsoft Access is included in all subscriptions except Home & Student.
Having said that, in my opinion (which could be incorrect), Microsoft hasn't invested In Access for 20 years, for example the database has had a 2GB files size limit, for at least the past 20 years, which is easily breached if you don't compact the database as every delete from the database isn't cleared from the database unless you compact it.
Having said that LibreOffice Base although doesn't necessarily look as nice as Access, is more powerful, and I've just discovered a way to communicate with it using a combination for VBA and Python, which I'll shortly be posting a video about.
@@BusinessProgrammer Thanks.
Apparently, I got lost at 9 minutes in on the gridMapCourseStudent where you add listbox Courses & listbox Students... My listboxes never show up as dropdowns and I didn't see you add it in, as there isn't a place in properties to change it to dropdown. What am I missing? I run LibreOffice Base on MacBook Pro, although, I wouldn't think that should make a difference.
I don't have the database in front of me right now, but go over the video starting at 9:07 minutes in,
where I insert the listbox, also note that the listbox record source is a sql query i.e. something that gives you multiple records, that's perhaps you're doing wrong.
This was really very, very helpful.
Why didn't you define relations? Apparently it works without, but just for me to understand why or why not: Isn't that necessary in general? Or has it any disydvantages/advantages? (I'm new to Libre Base, coming from Filemaker, where defining the relations is one of the first (and essential) steps in creating a project.)
Defining relations is just another helper that people use in a database for "Referential Integrity", it's a helper for getting the database to do a lot of the heavy lifting for you. It's especially useful in a production database where you've got a lot of users and the data is super important, and you can't risk any mistakes. It's a helper but it's not required.
Filemaker is a bit odd in the world of database's because it's not a SQL database, it's really an application for managing it's own properitary database (in my opinion...) when using Sql you're effectively creating different relations in every query.
For me I find defining relations has more downsides as it can lock up tables if it doesn't like what I'm doing, so it's not necessary to define them to have a working database.
There are times when they are a good idea, such as guarding against orphan records etc, but in practice use relations when you need them and don't bother about them when you don't need them.
@@BusinessProgrammer Thank you for this clarification. Will follow your advice.
Good video, I'm wanting to create a Case Workfliw system in Libre database that is automated and sends emails, what is the best way to set this up or are there any templates available for it?
For sending emails you're best bet is probably Python with MySql or if you're using Microsoft stuff Access with outlook, probably can be done with Google Sheets also using Javascript but that's going to be a time consuming learning curve.
Good morning, I am a Parish Clerk desperately trying to amalgamate historical burial records and current records. As you can imagine there are lots of rules and I'm trying to design the tables to enable me to meet those rules. I get lots of correspondence - emails, printed forms, scanned forms, document files etc - all of which can be tied back to the Plot Number as the primary key. Brilliant but can LibreOffice Base store generic documents as a data type? And if so, how do I get the document 'into' the database? TIA, Peter
Generally, one doesn't store documents in the database, but rather the path to the document, so you could have a text field that stores the path of a specific document or a related table something like tblDocInfo(ID autonumber integer, docPath varchar(255)) so your main table that references this table (via a third mapping table if you want more than one document per main record i.e. tblDocMap(id autonumber, mainID integer, docInfoID integer)) would have a foreign key field such as DocInfoID which would be related to the ID in tblDocInfo.
This would mean you could store the path and file name of one or many related records.
@@BusinessProgrammer Hmm, thats a bummer as I wanted to have a setup where I could see (and save) all the correspondence relating to a grave in one place. Thanks for the pointers though :-)
I just started using Libre Base and created a database and a table. I then successfully put data into the first record (# 0). A few days later, I tried to put data into a second record but it did not work. I saw no way to add anything to the second record. What am I missing?
Hard to say, try doing it again from scratch
I don't understand. The title of this videao is no code required, however I see a lot of SQL commands. This is not something for beginners like I am.
Sql isn't code, C# is code, Basic is Code, Python is Code, sql is structured query language it's how you get the database to do stuff.
Code will use SQL just like if you want to understand databases or have them do anything at all you have to get on top of SQL.
When you get to a higher level you then use SQL AND Code.
Off-topic - How is it possible to create a report in the same way as shown in this video czcams.com/video/AFhR2q3DxqU/video.htmlsi=ChFpxGP1P39d-UG2 ? When I try to open the report assistent always opens with the normal mode with page header, details and page footer. I wish I could open the report in the same mode (LO Writer mode) as the guy in the link I've sent here. Any idea on how to do?
Yes, I've just had a look at that video, make sure the wizard icon is ticked, (it is by default), then create your report in the detail area ignoring the header and footer, when you've completed your report click Finish. Then on the standard Toolbar on your writer report page (i.e. the report you're working on) click the icon for report navigator (mouse over for IntelliSense). The report navigator box will allow you to click the header and footer sections which you can make invisible in the properties.
Hope that helps.
I will not even try using libreoffice base, untill they replace basic with better language like python. I just hate basic.
LIke all programming languages, basic is just a tool to get stuff done, if you know how to use it properly it works just fine, basic suffers from a stigma only because people who are not programmers often write bad code. Basic if written properly is very useful, you can debug in real time just like javascript. LibreOffice has Python, it's just not as user friendly as basic, case in point you didn't know LibreOffice had python ;-)