LibreOffice Base Database Tutorial

in this video i'm going to create a complete version 1.0 libreoffice database for a personal training business and the great thing about this particular database is it's going to have a lot of functionality but very little code i say very little code because i'm going to put in some cool features but they're nice to have not necessary to build a database let's get to it the database will have one main form the top half of the form holding static client data and then the bottom elements in the form will manage things like notes and photos note details blood pressure body mass index glucose and pulse the database form will be organized into eight tables the main table being table clients table clients will hold the client id which will be automatically incremented whenever a new client is added to the database in addition to that table clients will hold information that doesn't change pertaining to the client such as their address date of birth the first name last name etc when essay doesn't change i mean it's not we're not going to be keeping a version history of it the other elements of the form will be driven by these subsidiary tables blood pressure bmi dates glucose pulse notes and photos all of these tables will have their own generic id columns because tables need to have a unique record in addition to that they'll have what's known as a foreign key this foreign key will be the client id and it's the client id that ties all of these tables together just like a vlookup table we will have a small amount of code in the database but as i've said it's not necessary it just gives you this extra functionality the functionality that you'll get from the code is the ability to have a photo appear whenever you change client provided you've taken the photo the photo will be based on the client id so what you do is when you take a photo you save the photo with the client id number in a subfolder of the database and it will automatically appear for you the other element that's helpful is for example if you're adding a note or adding a new record the code will give you the ability to have the date field automatically filled in for you code like this could be used to automatically populate any column in the table that you want i'm going to launch libreoffice base first so here we go libreoffice open create a new database next i'm not registering the database click finish and then where do i want the database to be i want it to be here and i'm going to call it publishing manager now we create the tables so create the first table so we're going to have client id and its data type is going to be integer it will have yes for its auto value then first name last name date of birth that's going to be a date type gender and email address and notice the client id has been put down as being a key field you'll see the key mainly because we chose autovalue yes close that and it will prompt me to save it do you want to save it yes table clients now all of these names fields names and table names are case sensitive so purposely put in the case sensitivity that you want so next we're going to do bmi so again create table in design view and we're going to put in id client id date because we're recording more than one bmi it's measuring bmis over time by the mass index so id is going to be integer and auto value is going to be yes meaning that it will automatically increment its number as new records are added client id is going to be what's known as a foreign field and it's going to get its id from the client table which the database will handle for you automatically date will be a data type and bmi will be a double and that's that so we close that it will prompt me to save table bmi now for table blood pressure id client id date bp blood pressure systali blood pressure diastolic make this integer first and then auto value yes client id integer and we're good now table glucose integer for glucose id so i just copied that stuff in from another table which is something worthwhile knowing that you can do i'm just going to save it and close it now we're going to do table notes but so watch this i'm going to paste in some of the fields already done i'm going to put in a subject for the note and then a note and the note will be a memo field and this allows the trainer to take lots of different notes for any given client so one client could have 10 notes you'll have a subject and the actual note now a table to record the pulse again paste in the fields that we're using all the time and pulse and that again is going to be an integer and finally the photo table now technically the photo fields could have been put into the clients table but i'm working on the basis that maybe not all clients will have a photo so for efficiency saying i've put it as a separate table you could quite easily put it into the other table for simplicity if you wanted but let's do that now great table i'm again going to paste in the fields that we know about i'm going to change the date to photo and the data type is going to be image and we save that and that's the tables created now let's get to the form so we click on forms then create form in design view then click on the form navigator after clicking on the form navigator click forms right click new form and we're going to give this form a name because it's actually a data source and what we're going to do is give it the name ds for data source clients because this will tie to the clients table so then click on data table for the content type is already highlighted you could have query of an sql command but table and we're going to take table clients and that's that particular form created all other data sources are going to be subsidiary data sources of this mainly because when you make a change on the client data source by choosing a different client the data sources that are connected to it will update which is what you want so now i'm going to add a subsidiary data source to the client's data source with clients highlighters right click then you get new form and then i click on the properties window go to general rename the form to ds notes lowercase ds notes ds for data source and this will link to the notes table notice that you've got link master field and link slave field this is telling the libreoffice database that you want the data source that's above it in the hierarchy in this instance clients ds clients to drive shall we say this particular data source and what we're going to drive it on is the client id so if i were to click on the ellipsis here and i click on notes you'll see client id or client id here so we're just telling it that client id is the driver you can actually just type them in in text as well and that will work just fine now we're going to do the same for blood pressure again clicking on clients right click new form click on your properties go to general and then this is going to be ds blood pressure again go to your data tab and set the table to the blood pressure table and again the data source is going to be client id the link field is going to be client id and to make this quicker i'm just going to control c and copy that to the clipboard so that i can just paste them in quickly from here on in going back to clients new form and it's going to be bmi click on the properties window we've already loaded ds bmi go to data and i'm just going to paste in the client id to both fields because they're all working against this cloud this ds clients data source so as you saw in that graphic area clients is controlling all of this now we're going to do glucose choose the glucose table link master fields click out and click out again and that's glucose done and now pulse and choosing the table again and finally ds photo and that's our data sources set up now it's time to put the controls onto the form now bear in mind that the relationship of the control to the form is dependent upon which of the data sources are select when you're putting the control on the form that selection binds that data source to the control now i'm going to be using the table control for nearly everything on this form so notice i'm now going to put on the client data because i've got ds client select so now i'm just going to drag the control on the form and notice that i had the wizard selected here which makes it easier to populate the control so what i'm going to do is i'm going to put all fields into the control click ok client id normally wouldn't need to be displayed but i guess it's useful information first name last name date of birth gender and email address the next thing you need to do when you're putting a control onto the form is you need to go to the anchor property and i recommend setting the anchor to the page rather than to the paragraph that way it's easier to place your controls so this has got a position x of 0.6 and we need to remember that because i'm going to give all the other controllers that position as well that is that one put on the client controller put on and now i'm going to put on the notes controls the idea here is that for any given client you can have multiple notes in date order and subject order so first of all i'm going to use another table control for your notes so let's get the table control and this is going to have the date of the notes and the subject so we're going to want to show date and subject and i'm going to make the data a bit bigger and the subject a bit bigger and what i'm also going to want to have is an area to type your notes and to view your notes so what we also will need is a text box and i'm going to draw the text box next to it and let's just get the selector here so it's position y is six centimeters but the position of this well i'll just make this six centimeters then as well just so that the um it aligns properly and the position x of this was 0.6 i'm going to make this position x 0.6 as well and that's just aligned both of those nicely and you don't really need to bother naming these because we're not using any code with these so it doesn't matter but if you are referring to them in code it's worthwhile giving them decent names there so the next thing i'm going to do is put on the image control first of all i click on ds photo and we got to find our image control and here it is so i'm assuming we're going with a headshot photo the other thing again is we we need to anchor it to the page to make it easier to move and i'm just going to shift it right a little bit it's important to name the image control because we will be referring to it in code we're going to refer to image photo in code the other thing is you've got a data element don't fill out anything on this because we need the data of this to be blank otherwise we'll commit to problems when we're moving through various photos because we want to select the photos in code i now need to add blood pressure bmi glucose and pulse so let's do blood pressure select blood pressure and we need to get the control and i'm going to want the date i'll just send them all across and i'll send these to back so now it's time for the bmi so it looks like i neglected to put a data source onto the bmi data source so let's do that now and the table we want it to have is going to be bmi and now with data source bmi selected i now click on the table draw the bmi data source and we want date and we want bmi and now we want glucose clicking on glucose and dragging another table because a table control seems to already be selected so date and glucose finish and now finally what we want is pulse click on pulse draw it again given that the table is already selected the table control date and pulse now that's the basics created i'm just going to save my form so if i close the form it'll prompt me to save it save changes so i'm going to call this form just for main basically and i don't want to put it in a subfolder so i'm just going to do cancel and click save and there's four main i've pre-prepared some photos with the client id starting with ten thousand then ten thousand one ten thousand and two the reason for this is when algorithms sort they sort left to right so i like to just keep everything neat ocd i know what can i say what we gotta do then is ensure that the first id of the database comes out as 10 000. and in order to and you know i've got so many tongue twisters here and in order to do that on our main database window we need to go to tools sql and type in the following piece of sequel all to a table table name being table client with quotes alter column column name being client id restart with 10 000 and click execute and we're good to go so what we've done is we've reset the order number sequence to begin with 10 000 when we add a new record the other thing you need to sometimes do is check that you have set up your that any fields that have a number in them have their decimal places set to zero and i've just checked that a bit earlier but just to double check pull up properties click on a field and then click on the general tab and you can see that decimal accuracy is zero it defaults to two so you want to make sure that it comes through as being a decimal accuracy of zero for the note details part of the notes area i noticed that i also made a mistake and that was in the data i did not assign this to the note field and we've done that now so when you when you're building your forms there's so much to do you probably make a mistake someplace just go back and fix it after the fact so having made those changes i'm just going to close the form and it will prompt me to save so let's get started with some initial data launch the form and let's stick in a name and i'm just going to leave the other stuff blank for the moment so you can put in dates of birth gender email addresses etc there so i'll make up a date and although this is 2021 i'm just going to put in some dates in for last year the 12th of the third 21 and so this is a personal trainer making notes needs a lot of work and let's put in another date 15th of june to north to north i wanna be him okay that sounds about life so let's just make sure this is actually working so as you can see when i click on different dates the different note came up and remember no code was hurt in the filming of this video i try to be funny let's pretend that the 12th of march 2020 is when the person started so he had an initial assessment i'm not sure i'm sure you can have a laugh when i put in some of these numbers i'm going to put in 50 for glucose i haven't got a clue but we're just getting numbers into databases here this video's taking me long enough to actually put together without researching all of that stuff and uh pulse uh well he started off quite unfit let's say 110 and let's say when a second assessment was done because i've been working out so really really hard really going for triathlons the whole works i got a pulse of 55 yay so what we can see right now is that this particular table here hasn't been set up correctly so let's quickly see what happened here i'm just going to close this i'm going to open it in edit mode again to have a look see what possibly could have gone wrong so i'm going to click on the form navigator again and we know that pulse is misbehaving so we've got a table control here attached to pulse so if i first look at the data set for pulse i need to get the properties and this this is quite useful for you because this is how you debug things when they're not working correctly and pulse if i go to general i forgot a data ah so the mistake was that the link master fields and the link slave fields were not saved here for some reason if i click on a different data source for example glucose and i click on data you can see client id has been saved but the reason this wasn't working was because the master and stay fields have not been saved so i'm just going to put that in now and here as well and hopefully everything should be okay so if i now just close this it'll prompt me to save open it again and now of course what's happened is that because i put in data when the link master field was not actually entered the client id has not gone into the table so let's just look at what happened so we go to table pulse and you can see the client id was not put in by the database mainly because we had not linked the master field so i'm just going to manually fix that by just putting in the client id for myself which was 10 000. and now if i close that sometimes having mistakes happen in these tutorials is actually good because it'll give you hints on how to sort problems when they go along for you and they will especially when you're getting started so let's now click on forms four main and if i click on mine on my douglas click on myself and you can see pulse has gone in click out of myself speaking to someone else so now for example if i do mike douglas so mike douglas has some notes note to self getting better i've got getting started great progress mike douglas says no to self click here this is the detail on note 2 self just so you can see stuff working here click onto the next record so you see you're beginning to get quite a nice application we don't have a photo yet but we will sort that out in the next section so if i add some blood pressure from mike douglas i've got to put in a date so let's say he had an assessment on the 15th of july 202 north and his blood pressure was 80 and his diastolic was one by five back onto mike douglas he's had one review and we'll have to assume that his bmi insurance he put in a date we have to assume that date was the same as that so you get the idea it's working so now let's add the code to get this extra functionality this code will be available for you to download so there'll be a link appearing somewhere in this video later on perhaps showing you how to get this particular code in addition to that there should be another video popping up up here i'm referring you to a previous video of my libreoffice base insert dynamic image that will give you all the details of making an image dynamic which is what i'm doing to make these photos appear here let's get into the code okay so let's get the photos working so what we're going to do is go to edit on the form to go into design mode and now so when we change a client record by clicking on a new client what happens is the data source client updates so we click on data source clients and what we've got to do then is go and click on the after record change event so what happens is you tie a piece of code into the after record change event because we want to run code when a record changes so we click on the ellipses here and what we've done is used sensible names for our forms and functions so for example we're working with our main database form for main so i've named the module that we're using m for main and we're going to be working with ds clients so what i've done is when i named the function i called the ds clients after record change believe me when you got a lot of forms and a lot of functions you will be glad you've used this methodology so i click ok so just to double check what we've done we've got the after record change event and we're running on the after record change piece of code that i wrote let's click ok and let's just test the code now and see if it works hopefully the image will change as we change records so let's go to design mode and go from design mode to run mode so we've got we now have the photos working with a little piece of code which is available with the video that's linked above here the next step is what we want to do is we want to be able to have the date element filled out automatically with today's date that's coming up next so the final thing we need to do with this now is to ensure that when we're adding records to each of these grids the date gets put in automatically so we don't have to go through the laborious element of typing in the date for every day so what's going to happen is the current day's date will be logged in each of these tables and this is the code that we're going to use here the one good thing about this code is this particular piece of code can be reused by all of these forms so they will all call it now notice also that i've got a sensible name it's dealing with four main which is that form i've generically named it as data source mainly because it's not just one data source so if it was just been used by ds clients it would be called ds clients of ds pulse ds glucose but the idea here is it's going to be called by multiple data sources so i've just called it data source and the actual event that's going to call this code to insert the date is going to be the before record action event so let's go do it now if i go to my form i'm going to click on the pulse data source and then i'm going to make sure events is selected i then go to before record action on my pulse event go to the right click the ellipses click macro find my code before record action that's the one and click ok again now i'm going to go to glucose get the before record action event again click on that macro expand everything before record action and click ok again and for the other ones to speed things up i've already filled that out and gone through that process so now if i just click close on the form it will prompt me to save it i click save now i'm going to launch the form again and when i click on let's see mike douglas let's give him some glucose let's say 150 probably a totally crazy number click enter and the date automatically goes in little housekeeping things to be done is maybe make that date a bit wider uh let's give him some blood pressure measurements for today and so let's say he's 85 and uh 1 45 so things have got better quick someplace else and the date has automatically gone in as well so uh putting in a date can be is quite difficult not many people know how to do it now you do it'll be our secret finally let's add two more records just to round this off so i'll put in mary frank just to make a name up and and i think let's go for a rock star here with robert plant and go out of that and go back and there's robert plant and i think that will do it so if you'd like to use the code that i've already typed and just put it into your database project to try this out you can go to businessprogrammer.com forward slash go forward slash 115 and follow the instructions that are there you enter your email into the opt-in box and instructions will be emailed to you now once you get the code listing it will be in a text file and to put it into your database follow the following instructions which is open your database go to tools macros and then you can go to organize macros and basic and what you want to do then is click on your database expand it click on standard then click new and you're going to want new module and if you're following exactly what i'm doing it's m underscore form main so you click ok then and your module will appear and then all you got to do is go to your text file click ctrl a or command a if you're on the mac control or command c to copy it go to your module control a to select everything control v pastes it in and then that's it basically click save close window double check to open it again so click edit macros again just to double check see what's happening click on client manager standard and there we have four main and there is all your code and then you're good to go everything explained previously should work thanks for watching

Channel: Sean Johnson

Views: 9,809

Rating: 4.9540229 out of 5

Keywords: LibreOffice, libreoffice base, libreoffice base tutorial, LibreOffice base create database, libreoffice, base, open office

Id: Du1WjROQBpE

Length: 25min 31sec (1531 seconds)

Published: Mon Mar 01 2021