OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Beginners - Example Database - Base Tutorial
Goto page 1, 2, 3, 4, 5, 6, 7, 8  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Thu Oct 13, 2005 4:36 am    Post subject: Beginners - Example Database - Base Tutorial Reply with quote

Who is this thread for
The message will cover creating a first databse using OOBase. It is intended for those that have not used Base before, or those that have never used any database manager.

What it is.
    The table wizard is used, along with the table design editor.
    The relationship editor is shown.
    Use of the query builder.
    Use of the report wizard.
    Only functionalility supplied by the GUI components is utilized, with one minor exception.
    There is one VIEW created that requires a hand entered SQL statement, the statement is supplied for you in the text.


I have posted a couple of examples that IMO are superior to this one, both in quantity and quality one of these can be found in this thread on the board.

http://www.oooforum.org/forum/viewtopic.phtml?t=56731

What it is not.
    There is no custom Basic code.
    No macro's.
    No external function libraries are used.


If you are looking for the things that it is not you can find another example database, with these things at http://www.paintedfrogceramics.com/OpenOffice/ProjectContacts.zip
The discussion thread that shows this other databse being created is found at
http://www.oooforum.org/forum/viewtopic.phtml?t=21697&highlight=create

The text is a bit long (about 12 pages in Writer, at this moment), but I have tried to keep the pace movingt. There are screen shots as you go and a few little go-yta's along the way.

Here is the database that will be created - Before you just download it the idea is to actually make one yourself, you can use this to compare it against if you like, but just downloading it and looking at it won't be nearly as useful.

Note - at this moment 10/15 this is the database without the report - to be added tomorrow - also this database has three table, as you start building yours it will begin with four, about a mile down the page is a brick wall...it is not a loss of your time to get to that point with me..this database is what we have when we jump the wall...

10/16/05 Monday...uploaded with the finalized form..and few quries to be used for generating reports.

http://www.paintedfrogceramics.com/OpenOffice/employees/employees.odb

The database was created using 2.0.0 RC2 and tested under RC3. There are still a few bugs with these releases but non that hinder exectution.
---------------------------------------------------------------------------------------------------------------------------------

Let's create another example database, this one will manage a list of employees.

It will offer a chance to look at working with One to Many and One to One data relationships.

It will also work with Image fields, and Memo fields. You may be surprised at just how easy these are to use in Base.

Along with the items mentioned above it will also show how to use Listboxes to control data entry, and the use of a VIEW to allow one field in a table to reference another.

CREATE THE DATABASE FILE
Lets start by creating a new database. I have chosen the OOBase native database engine HSQLDB.

Tools>New>Database

Select Next

Select Finished

Save the database as Employees

OK, now we have an empty database file.

Select the Tables icon, from the icons on the left of the Base main window.

Select "Use Wizard to Create Table"

On the table wizard dialog make sure the Category is Business.

From the Sample Tables pull down box select Employees.

Select the dblChevron ">>" button to move all fields over from "Available fields" to "Selected fields"

Now scroll threw the "selected fields" and find the field named "Photo",
move it back to "Available Fields" by clicking on the "<" button.

Remove the field "Department" the same way.

Finally remove the field "Notes" also.

Click NEXT

On this second screen in the list "Selected Fields" highlight the field "EmployeeID".

Change the AutoValue drop down list from "no" to "Yes".

The Screen should change so that the control just below this now says "IDENTITY".

Click NEXT

On the Set Primary Key page choose "Use an existing field as a primary key". In the fieldname drop down list select EmployeeID. The checkbox just to its right "Auto value" should now be checked, leave as such.

Click NEXT

Leave the table name as Employees and click Finish.

OK

Now select "Create Table in Design View"

In the empty Table Design window ad the first FieldName as "EmployeeID"
Change the FieldType to "Integer". LEAVE the autovalue as false.

Right click on the row marker square just to the left of the Field name, and from the popup menu select "Primary Key".

Add a second fieldname "Photo", Fieldtype of "Image[LONGVARBINARY]"

Save the table as "EmployeePhotos"

Select "Create Table in Design View" again.

Add a fieldname "EmployeeID", fieldtype "INTEGER", and make it the primary key.

Add a fieldname "Notes", fieldtype Memo[LONGVARCHHAR].

Save this table as EmployeeNotes

Now for the final table.
Create a table using the table design dialog with the following fields. ID of type IDENTITY (Integer with AutoValue = True), and Department VARCHAR(50). Name the table "Departments"

CREATE THE RELATIONSHIPS
Open the Relationship Dialog. Tools>Relationship

The add tables box should be open. Add all FOUR tables Employees, EmployeePhotos, EmployeeNotes and Departments to the relationship window.

Drag the field ID in Departments to the field DepartmentID in Employees. (notice that it pus a 1 just above the line and next to the table Departments and an 'n' above the line and next to table Employees. This denotes a one to many relationship between the two tables.)

Drag the field EmployeeID from the Employees table to the field EmployeeID on the EmployeePhotos table. Do the same for the EmployeeNotes table.

OK, now why did I break the image and memo fields out of the main table. Well, for performance reasons. When I am dealing with BLOB or CLOB fields I don't want to be having to bring them from the disk into memory when I am searching or joining tables. As you can see in the diagram by making the EmployeeID field in each of the secondary tables EmployeePhotos and EmployeeNotes the primary key for those tables I have created a 1 to 1 relationship. Now we need to insure that if we ever delete an employee record that we also delete its associated photo or notes entry.

Double click the line that runs from the Employees table to the EmployeePhotos table. The Relations dialog will open. Under "Update Options" select Update Cascade. (Normally we would never change a primary key, but we will just be double sure and tell it that if somehow we ever did do that, then update the key to match in the referenced table)

Now for the "Delete Options", select "Delete cascade".

Click OK

Now do the same steps for the relationship (the line connecting Employees to EmployeeNotes) with the EmployeeNotes table.

The screen should look something like this.

http://www.paintedfrogceramics.com/OpenOffice/employees/employeeRelations.png

When you are finished save and close the Relation Design window.

Our database structure is now complete.

CREATE THE EMPLOYEE FORM

At this point go ahead and save the database file.

Alright then, lets build the Employee Record Form.

Right mouse click on the table "Employees" and from the popup (context) menu select "Form Wizard".

A blank Writer document will be opened and the Form Wizard dialog will be displayed with the table Employees selected. Move all of the fields from the "Available Fields" list to the "Fields in the form" list by clicking on the button ">>".

Click NEXT

On the second page put a check mark in the "Add Subform" checkbox.

Make sure that "Sub form based on manual selection of fields" is selected.

Click NEXT

The next page lets us select the table for our sub-form. In the drop down box select EmployeePhotos.

In the list "Available Fields" you may notice that only the EmployeeID field is listed. The field "Photos" is not. The wizard will not (at the moment anyway) automatically add a Image field. But don't worry we can add it later. For now just move the EmployeeID field over to the "Fields in the form" list.

Click NEXT

This next page is "select the joins between your forms". In first row of drop down controls select EmployeeID.

Click NEXT

On this page "Arrange the controls on your forms" we have 4 choices for the fields in our Main form and our Sub form. Lets choose the format all the way on the right "In Blocks - Labels Above" for both the form and the sub-form.

Click NEXT

This next page is "Select the data entry mode". Leave the default selections.

Click NEXT

Here we can choose a background color for the form. We can change the border for the Edit Controls, en mass, but not the text labels. Changing the background color will also change the default color for our label text. Choose whatever you like.

Click NEXT

On the last page we give the form a name. The default is the table name, so lets just keep it. Also we can decide if we want to immediately edit the form layout, or go right into editing data with the form. Lets edit the layout. Select "Modify the form".

Click Finish

WELL, in the spirit of full disclosure at that moment OO.o crashed. The recovery processed reported an error when I restarted Base and the last three things I had done where gone...You gotta love it..right. In all fairness, I tried to cheat at one point and attempted to change the default value for two columns in Employees table. This generated an error because they belonged to the relations I had just created, so could not be dropped and re-added. This most likely caused the crash..but am not sure.

In fact I am sure of it. Looking at the Employee table in database now, I see where the default value had changed to what I wanted, and the Foreign key relationship is gone. So the moral of this is - If you are going to change a column that is part of a foreign key relationship, be sure to drop the relationship first...



OK, now back to the form we just created.

The form should be open in design mode.

Here is the way the form looked for me, when the wizard was done.

http://www.paintedfrogceramics.com/OpenOffice/employees/employeeForm1.png

Not exactly what I would call a well designed data entry form; and our photo and notes fields are missing.

So lets just clean it up.

WELL, once again...after cleaning up the form. Adding an image control and accidentally hitting run...OO.o crashed. Upon restart the form was completely gone. Jeepers, this is starting to feel like old home week...

OK, well...not to be deterred. Lets just press forward.

EDIT

Cleaning up the data entry form

The first thing you want to notice when you open the Employees data entry form in edit mode is that when the wizard created the controls it did so in pairs. For every text control (or other type) it also created a Label Field. The wizard then grouped these two controls together. This helps us to move the pair around on the form. If however we want to edit the properties of one of the controls we must either un-group them or from the pop-up menu select 'group->edit group'. This un-groups the controls for as long as we work with either of the two controls. As soon as we change the focus to any other grouped controls or any control the grouping is back in force. It is a very handy little feature.

Fine - so done to work. Right off the bat we see that we have a control and label for the column EmployeeID. Remembering back this is an auto generated number, used to form our database key and relate records from one table to another. Now it would be fine if we just dropped this from our form completely. Why? Because we should never be entering anything in this column, only the database engine should be doing this. If we chose to leave it on the form, as I will here, then it would at least make sense to make this control 'read only'.

So - select the control group with your mouse. Click the right mouse button and choose 'Group->edit group'. Now double click on the text control itself (the box just below the label EmployeeID). This will open the control properties dialog.

http://www.paintedfrogceramics.com/OpenOffice/employees/cntrlprpEmployeeID.png

Change the property Read-only to YES.

For completeness I tend to due one other thing. See the property Tabstop, I would set this to NO. Since we can't edit the value why would we want to make the user have to step into and out of the control at all.

Go ahead and close the dialog, we are done for now. Next thing I did was move the EmployeeID control group to the bottom right corner of the screen. Again, this is a value that is not particularly important to us, it means a great deal to the database engine, but very little to us as humans.

So did you get caught...did you move the control and the label did not move with it...remember that when we did 'Group->edit group' the controls are un-grouped until we touch some control other then these two. So if you did, just move the label also back to above the edit box. No harm done.

Let's make some room now. If you click on the 'Select' tool button, (looks like an arrow) you can hold down the left mouse button and moving your mouse select a whole group of controls on the form at one time. In my case I lassoed the whole lot of them and moved them down a bit, so that I could have some room to work.

One little thing that you might not have noticed is the 'Guides When Moving' feature. It is the tool button on the far right in this picture.



This is a real plus when trying to make our form look pretty, which by the way makes it easier to work with usually.

Without going into every detail I rearranged the controls, and resized some, so that the form now looks like:

http://www.paintedfrogceramics.com/OpenOffice/employees/employeeForm2.png

Still doesn't look much like a well designed data entry form, but it is getting closer. Along the way I dragged a group of controls to the right side of the form.

Deductions, Billing Rate, DepartmentID, BirthDate...etc. We can decide if these should stay or if they should be changed to something more useful. Two controls come to mind right off, BirthDate and HireDate. Each are tied to columns of type date, but the controls look like they are just text. Lets make them act more like date fields.

Select BirthDate, do the 'Group->Edit Group' function. Now double click on the control. Here is the control dialog for a date control. What I have done is turned Dropdown to YES, Spin Button to YES and Repeat to Yes. The drop down will bring up a calendar widget when selected and the spinner will let us increment parts of the date easily. Looking at the control itself we see that we will have to enlarge it slightly to accommodate the new buttons.

http://www.paintedfrogceramics.com/OpenOffice/employees/cntrlprpBirthDate.png

Now go ahead and do the same for the HireDate control. A trick here, you don't need to close the dialog box. You can select a different control while it is open and the contents will change for this new control.

EDIT

Great - got back to the form. Here it is starting to shape up:

http://www.paintedfrogceramics.com/OpenOffice/employees/employeeForm3.png

Now, looking at this form you should see that I have adorned it to aid in grouping controls based on functional area.

Make it look nice - it never hurts

What I have done is added three frames. Frames? Yes, Frames; because the data entry form is actually based on a Writer text document. At the top of the form I have added a frame with all four borders and a drop shadow. Below it and to the left is a frame with only the right border on. Inside this is the employee's address information, along with the spouse, location and title. To the right the phone and email information is not inside a frame. Just to the right of this is the information for department, supervisor, hire data and birth date. These are inside of another frame with both the right and left borders turned on. To the right of the are the remaining controls - sans a frame.

Now there are two tricks to adding a frame like this. First - if you want the controls to belong to the frame it is a three step process. Using the selection tool lasso all the controls you want inside the frame. Copy them to the clipboard and then delete them. Now insert the frame, size and place appropriately. OK, now you must click inside the frame, this will place a cursor in the upper left corner of the frame and then do paste the controls from the clipboard. If the controls fall outside the bounds of the frame don't worry. You need simply to bring up the pop-up menu, select Anchor and change it to 'frame' and now move the controls inside the boundaries of the frame. With this done the controls now belong to the frame, if you move the frame around on the screen the controls will move with it.

Second - if you don't want the controls to belong to the frame, you just want them inside of it then you need to select the frame. (click an edge with the mouse) Bring up the pop-up menu and select 'Arrange' and 'Send to back'. This way you can work with the controls without the frame getting in the way.

Two other changes that you might notice are the Salary and Billing rate controls. In both cases I used the control properties dialog to change the format for the control to Currency. One thing you won't notice is that the Deductions control was changed so that the Value Min was set to 0, Value Max to 10 and default to 1. A negative number of deductions just makes no sense to me.

Who is on first, what is on second and I don't know is on third!

Well, the form is starting to look pretty good. But what about navigation. Go ahead and turn design mode off. Put the cursor in the First Name field and hit tab a few times. woow...not good, the cursor is jumping all over the place. Let's fix that.

Go ahead and turn design mode on again..oh, and if you are following along and have made all these last changes..be sure to save the form..and save the database file right now.

Back to our tab order. You can do this by first bringing up the Form Navigator window, by either selecting the tool button for it. If you hit F5 to bring up the navigator, don't this is the wrong function.

With the navigator open you will notice that all our visible controls are listed, along with the data entry form (Writer document) and something called MainForm. This MainForm item is the dataform object that is the link from the controls to the database table. Right mouse click on the word MainForm and from the pop-up menu select 'Tab Order'.

Here is what it looks like for me, when I first open it.
http://www.paintedfrogceramics.com/OpenOffice/employees/taborder.png
First thing notice that none of the labels are listed, because they do not receive focus. Only the controls that can be stepped into are listed. The form wizard named the controls in a very straight forward way. The first 3 lower case characters denote the type of control, and then the name of the column that it is bound to. This makes it very easy to know which one we are dealing with.

Now - you will see that there is a button that names 'Automatic Sort'. Yup, it does what it sounds like. Go ahead and click it. Now look at the list of controls. The first four are just what we wanted, actually the first nine are in the order we would want. But look at number ten. The automatic sort function sorts from right to left, top to bottom. If that is how you want the user to navigate the form, then you are done. If you want them to move differently, for example right to left, top to bottom inside each area of the form then you must rearrange the controls on the list by hand at this point. But at least are clumped close to one another now. So go ahead, your choice. For me, I went right to left, top to bottom by area, so starting at address, then City, State, PostalCode and Country. Then PhoneNumber, Extension, MobileNumber, FaxNumber and EMailAddress.

This is another good spot to save our form at...

Autosave is a good thing.

In case yo don't know it, OpenOffice has an automatic backup function. Select Tools>Options>Load/Save>General

Click on Always save backup copy (This happens whenever you open the document, or database)
Click on Save auto recover data and set it to a time of your choosing. I use 15 minutes.

List boxes

Hopefully your form is in good tab order. So now lets look at the two fields DepartmentID and SuperviserID. Having fields with database generated keys is not very useful, what we want is to have the user select from the actual values as words and then store the associated key values back into our employees table. There are two controls that would seem to be useful here, either the ComboBox or the ListBox. If you first thought was the Combobox sorry, in Base for this it is the ListBox. A combobox is used when we want the actual text from the selection list to be written to the attached table, not a related field.

Let's do the easy one first, the departmentID. What we want to do is list all the values from the Deapartments table, showing the actual department names and then when the user selects from the list write the value of the DepartmentID field in Departments to the DepartmentID field in Employees.

Now a few words on listboxes, they hae a few quirks. First, if you try to change a control from some other type to list box and then using the properties dialog make it work as we want, you can't.

So, the easy way is just always add a list box to a form, not change a control to a list box.

OK then let's do it, add a list box control to the form. The List Box wizard opens. On the first page you select the table to get the list data FROM. Choose departments. On the page you choose which field to display as the list from this table. Choose Department. Finally on the last page, Field Link, you will make two choices. On the left are all of the fields from the table that the value should be SAVED INTO , and on the right the fields from the display table. In our case on the left we choose DepartmentID and on the right ID. Great we have a correct list box. So, select the DepartmentID text control and delete it. Move the new listbox into its place. One last step, select the label field DepartmentID, open the control properties dialog and change the 'Label' property to "Department".

One down - one to go, SuverpvisorID. Now this one is a little more interesting. What we have is a field in the table that refers to another record in the same table. A purest would want to show a list minus the actual record we are working on, but I am not going to go to that extent. I will settle for just displaying all the records from the employees table. Now what to display. If we did the same as we did for departments then we could only display the value from one column. LastName for example, but that doesn't seem right. What if we had two supervisors with the last name Jaworzki or Smith.

One idea would be to concatenate the first and last name. But hey we also have the middle name so why not use that also. Let's look at what we can do.

Switch back to our main base window and go again to the queries list. Select 'Create query in design view', once again. This time add the table employees. We will add four fields to our query. LastName, FirstName, MiddleName and EmployeeID. At this point select the toolbutton for Siwtch Design View On/Off. The neat GUI stuff will go away and you will have an edit control with the following SQL statement in it:

Code:
SELECT "LastName", "FirstName", "MiddleName", "EmployeeID" FROM "Employees" "Employees"


Here we can pull concatenate our name fields. So change the statement to this

Code:
SELECT "LastName" || ', ' || "FirstName" || ' ' || "MiddleName" AS "Name", "EmployeeID" FROM "Employees" "Employees"


Actually, suppose we decide that what we want to display is LastName, Firstname MiddleInitial. Could we do that. Well yes we could. HSQLDB has a standard procedure SUBSTR( s, start [,length]), so we can change our SQL statement one more time to:

Code:
SELECT "LastName" || ', ' || "FirstName" || ' ' || SUBSTR("MiddleName", 1, 1) AS "Name", "EmployeeID" FROM "Employees" "Employees"



A few items to note. The double bar characters || is the SQL 92 string concatenation character. The part AS "Name" is called an alias. It will be the name used for the new column we have created in our query that puts all three parts of the name together. Lastly the single versus double quote. In HSQLDB strings are always quoted with single quotes, while table and column names always use double quotes. In the case of the alias it is the column name of our new column, so it is double quoted.

Well, see if our query works. To do this we need some data in our employees table. We can use our form to add some. Before we do however, one last detail. The Base query builder is a general purpose query tool and we have just created a query that is to specific for it to understand so we must tell it not to try and parse it. We select the toolbutton "Run SQL Directly". Great now save the query as qrySupervisors.

Switch back to our form and turn design mode off. Add the following employee's data.
Code:

First Name   Last Name    Middle Name      Department      SupervisorID
John      Smith               Sales         0
Bob      Wilson      Thomas      Mrktng      0
Carla      Mattson   Samantha      IT         0

That should be enough. Save our form and run the query qrySupervisors. Woops, what is with this:

http://www.paintedfrogceramics.com/OpenOffice/employees/query2_super.png

Well, here is the problem. When we concatenate columns like that together, then if any one of them is NULL the whole returned column is NULL. That just won't do. There is a way around this problem however. We can make a decision - one we should have made earlier - that an employee record always has to have a first name and a last name, but we really can't assume we always have a middle name.

HSQLDB has a way around this problem. Looking at the users guide we find this in Chapter 9:

Quote:

CASE WHEN...[1]
CASE WHEN expr1 THEN v1[WHEN expr2 THEN v2] [ELSE v4] END
when expr1 is true return v1 [optionally repeated for more cases] [otherwise v4 or null if there is no ELSE]


Great so let's change the SQL command to this:

Code:

SELECT CASE WHEN "MiddleName" <> '' THEN "LastName" || ', '|| "FirstName"||' '||SUBSTR( "MiddleName", 1, 1) ELSE  "LastName" || ', '|| "FirstName"  END AS "Name", "EmployeeID"  FROM "Employees" "Employees"


This says that IF the middle name column is not equal to an empty string. [we need to do this for case statements and varchar data types as opposed to testing for null in a where clause], then use all three name columns else just use the first and last names. So if we run this query what do we get:

http://www.paintedfrogceramics.com/OpenOffice/employees/query3_super.png

That is just what we wanted. But remember when the ListBox wizard come up before, it only displayed tables to choose as list sources, not queries. This is one place a VIEW can be used where a query cannot. So, close our query with the final SQL statement. Right click on its name and form the pop-up menu select 'Create as View'. Name the view vSupervisorList.

This done, add a combo box just as we did for departments, but for the source select our new view and display the field Name. Connect it to the SupervisorId column on the employees table.


DON'T FORGET TO ACT ON BUSINESS DECISIONS

Back a few steps we made a decision. We must have a first and last name in the employees table. But when we defined the table we did not set this up. Now is the time to do so. And here we have a chicken and egg problem. We just made a view that pulls data from the employees table, in fact it pulls these columns specifically. Here is another difference between a VIEW and a QUERY. When a VIEW is created it can automatically generate constraints and/or indexes back at the database engine. This is usually a good thing for performance. A query will never do this, it can't. But in our case, right now this is a problem. If you want to see what I mean go ahead, go to the table list. Select employees for editing. Try to change the property "entry required" for FirstName from No to Yes and save the table. It will generate an error. We have to go drop the VIEW first, then make these changes and then recreate the VIEW. Not really that big a problem at this stage of the game. So just do it. One other side effect is all the information in these fields will be lost, but again we only have 3 records so what is the harm.


Now here is our form with the list boxes.

http://www.paintedfrogceramics.com/OpenOffice/employees/employeeForm4.png

That pretty much wraps it for the Employee part of the form

sooooo

Bring on the BLOB

--------off to bed with me for tonight...but maybe a little teaser before I go -----------

http://www.paintedfrogceramics.com/OpenOffice/employees/employeeForm5.png

Well - back to it..and before I go on I will post up the example db as it sits now.

http://www.paintedfrogceramics.com/OpenOffice/employees/employees_1.odb

If you have created your own, you don't need to download this. But if not and you want it, then grab it.

OK - before we go on one item It Does Not Work

It does not work because of two decisions I made. Decision 1t - when I started I decided that I wanted to make this example using ZERO lines of basic code. Just standard UI controls and standard functionalilty.
Decision 2 - The split I did on the Photo column to a new table.

These two decisions together will not work. I can make the data model work as is, with a very few lines of basic code, or I can change the data model.

Well - I think the first decision, to not require any basic code is the one I want to keep. So I will end up droping the table with the BLOB and moving it back intoe the employees table.

For those that are interested I will go into why it won't work the way it is designed, that is without code. It is actually worth a review.

---sunday 10/16
What is the problem? Under windows at least, I don't know for Linux GUI Managers, there are two types of controls. Those that can receive focus and those that cannot. Now, I know that if you open the Control properties dialog for an ImageControl is lists for events "When receiving focus" and "When lossing focus", but there is no way to fire these events. One way to see this is simply to place two controls on a form, one a text box the other an image control. Place the cursor in the image control and then right mouse click on the image control. You can insert a graphic file, and yet the cursor never moved from the text control. Graphic controls under windows do not receive focus.

Now, when I split the Photo column out of the employees table and created the emploueePhotos table with just 2 fields I was setting up a lack of focus problem. To add the image control to our form I had to to do this.
    Open the Form Navigator dialog.
    Select the MainForm object, bring up the pop-up menu and select New>form.
    Rename the new form Photo.
    Open the form properties dialog for the Photo form.
    On the Data tab set the following:
      Content Type = Table
      Content = EmployeePhotos
      Navigation Bar = Yes
      On the property for Link Master Fields, click on the button labeled ...
      This brings up a dialog box with the fields form both employees and employeePhotos, for both table choose the column EmployeeID.



The same is done for the employeeNotes table.

Here is a screen shot fo the form properties dialog for the Notes subform:

http://www.paintedfrogceramics.com/OpenOffice/employees/formProperties.png

Now at run time notice that as I move from one of the controls on the main form to the notes control the form navigation bar updates itself to reflect the different record counts, etc. between the two distinct dataform objects. Each assigned to a different physical table. This change on the navigation bar is triggered by a change of focus amongst the controls. And here is the problem, the image control can't receive focus, so the navigation bar never knows to switch over to its dataform object. I can add images it it (the image control), remove images from it all I want, but the form navigation bar will never allow me to save the controls contents to to the underlying table.

If I had any control attached to the employeePhotos table, that could receive focus, then moving into it would activate the navigation bar and it would immediately recognize the change in state of the image control allowing me to save the data. But I have no column that would make sense, and even if I did it would be a very awkward process then for the user. One other solution, using basic code, would be to assign one of the Mouse Button events to a procedure and in the procedure change the status of the dataform object for employeePhotos table. Remember though, this example was to use now basic code.

So - as is often the case we start out with a normalized and decomposed data model and when we need to we denormalize or recompose tables as required.

I did the following - switched to the Base main window. Brought up the relationship dialog, selected the employeePhotos by clicking on it. Then deleted it form the dialog. The system warned me that this would remove any relationships that the table participates in, which is just what I want.

I then went to the tables list panel and deleted the table employeePhots.

Edit the employees table and add the column Photo Image(longvarbinary) to the end.

Saved the database at this point.

Back to our form:
    In edit mode of course
    open the form navigator dialog.
    Grab the object for the image control and dragged it up to the MainForm object.
    Do the same for the photo label field.
    Delete the Photo sub form.

Because the column name is still photo I do not need to do anything further. I can save the form and all is fixed.

All employees must report for work, so lets make a report also


Last edited by DrewJensen on Sun May 06, 2007 6:45 pm; edited 19 times in total
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Corfy
Moderator
Moderator


Joined: 14 Jun 2005
Posts: 1117
Location: Near Indianapolis, IN, USA

PostPosted: Thu Oct 13, 2005 6:46 am    Post subject: Reply with quote

Wow, that looks extremely helpful. Thanks for the directions.

Unfortunately, I don't have time to read through them very carefully right now, and I certainly don't have time to try it right now, but I will come back to this later. I have been struggling with making one-to-many relationship databases for some time.

Thank you again.
_________________
Laugh at life or life will laugh at you.
Back to top
View user's profile Send private message Visit poster's website
revnomad
OOo Advocate
OOo Advocate


Joined: 23 Feb 2003
Posts: 211
Location: NE Nebraska

PostPosted: Thu Oct 13, 2005 6:48 am    Post subject: Reply with quote

I tried your cascade delete (might have been in a earlier post). Anyway it did not work. I'm certain (within human limitations for really messing up) I did everything right.

Also is it just me or does Base crash on SQL errors instead of giving error messages?
NTP
Back to top
View user's profile Send private message
xirontask
Power User
Power User


Joined: 25 Aug 2004
Posts: 56

PostPosted: Thu Oct 13, 2005 7:45 am    Post subject: Reply with quote

Hi, D.J.,
thank you for your very helpful example. javascript:emoticon('Very Happy')
Very Happy
I got exactly the same form as yours, without Base crashing, I suppose because I hadn't "tried to cheat at one point and attempted to change the default value for two columns in Employees table". javascript:emoticon('Smile')
Smile
I went then forward with saving the form and at that moment, OOo crashed instantaneously and completely. No form was saved. I suppose I'll have the create form procedure, again. Doesn't matter. But what could have been the error?
_________________
Xirontask
Back to top
View user's profile Send private message
frazelle09
OOo Enthusiast
OOo Enthusiast


Joined: 24 May 2004
Posts: 142
Location: Mexicali, Baja California

PostPosted: Tue Oct 18, 2005 4:23 pm    Post subject: Shifting from Edit Form to Input Form crashes Base Reply with quote

Hi guys! This looks like a great thread. Tried about half of the instructions, and got the tables set up, linked and the form started. Didn't do too much to the form other than just change the Date Control items. When i went to Use the form, Base crashed and i lost the whole form. Did it again, this time saving the form as well as the db and even closed the db. Opened it again and the form had been saved. Tried Using the form. It ran but and came up, but Base crashed again. What's up?
_________________
Now using 3.2.1 on 4 PCLinuxOS 2010 boxes (two are laptops).

"The earth is one country and mankind its citizens."
Bahá'u'lláh

"La tierra es un sólo país y la humanidad sus ciudadanos."
Bahá'u'lláh
Back to top
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger
maxqnz
Super User
Super User


Joined: 24 Mar 2003
Posts: 1290
Location: Te Ika a Maui, Aotearoa

PostPosted: Fri Oct 21, 2005 4:32 pm    Post subject: Reply with quote

This thread is GREAT! I have a real kl00less newbie typ question, though: In the original form design screenshot, there are TWO instances of "Employee ID", in the later screenshot, only one. Does this mean it's safe to remove one of them, and, if so, does it matter which one?
_________________
Noho ora mai, ka kite ano.
What Is A Pieriansipist?

OOo 2.4/XP Pro SP2 / OOo 2.3.0.1/OpenSuse 10.3
Back to top
View user's profile Send private message Visit poster's website
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Fri Oct 21, 2005 4:43 pm    Post subject: Reply with quote

You could remove them both. One is the EmpoyeeId column from the Employees table and is Autoincremented. The other is the FK to our notes table, so again you don't actually assign anything to it.

Now, you need to have the column in the resultsets used to feed the form controls, but no reason to actually have them tied to a control on the screen.

Drew
Back to top
View user's profile Send private message Send e-mail Visit poster's website
maxqnz
Super User
Super User


Joined: 24 Mar 2003
Posts: 1290
Location: Te Ika a Maui, Aotearoa

PostPosted: Fri Oct 21, 2005 8:44 pm    Post subject: Reply with quote

For some reason, exiting edit mode brings my OOo silently crashing down. Here's what my form looks like in edit mode. I had saved both the form and the odb, and can open it in edit mode and tinker merrily, but attempting to exit edit mode, or "open" the form rather than "edit" it, automatically causes OOo to simply quit. The only things I changed were removing the "salary", "deductions" and "Billing Rate" control groups. Could that have caused it?
_________________
Noho ora mai, ka kite ano.
What Is A Pieriansipist?

OOo 2.4/XP Pro SP2 / OOo 2.3.0.1/OpenSuse 10.3
Back to top
View user's profile Send private message Visit poster's website
maxqnz
Super User
Super User


Joined: 24 Mar 2003
Posts: 1290
Location: Te Ika a Maui, Aotearoa

PostPosted: Sat Oct 22, 2005 2:13 am    Post subject: Reply with quote

OK, so I came back to this after a few hours, and tried again, setting up the Department ID ListBox, and the SupervisorID query, then I went to switch out of edit mode. Same result - instantaneous, totally silent crash. This is 2.0 RC3 on W2K SP4, if that makes a difference.
_________________
Noho ora mai, ka kite ano.
What Is A Pieriansipist?

OOo 2.4/XP Pro SP2 / OOo 2.3.0.1/OpenSuse 10.3


Last edited by maxqnz on Sat Oct 22, 2005 11:50 am; edited 1 time in total
Back to top
View user's profile Send private message Visit poster's website
mangi
Newbie
Newbie


Joined: 22 Oct 2005
Posts: 1

PostPosted: Sat Oct 22, 2005 5:20 am    Post subject: Reply with quote

I get same error (crash) as maxqnz. Tried JRE 1.5 and 1.4 with no result. Only when I uninstall JRE completly I'm able to get into my form. Rolling Eyes
Back to top
View user's profile Send private message
maxqnz
Super User
Super User


Joined: 24 Mar 2003
Posts: 1290
Location: Te Ika a Maui, Aotearoa

PostPosted: Sat Oct 22, 2005 11:50 am    Post subject: Reply with quote

mangi wrote:
I get same error (crash) as maxqnz. Tried JRE 1.5 and 1.4 with no result. Only when I uninstall JRE completly I'm able to get into my form. Rolling Eyes


You had to uninstall the JRE to get it to work? Sheesh, I never thought Javawould the problem.I don't think I'll bother if that's the only solution. Thanks for the heads-up! Crying or Very sad
_________________
Noho ora mai, ka kite ano.
What Is A Pieriansipist?

OOo 2.4/XP Pro SP2 / OOo 2.3.0.1/OpenSuse 10.3
Back to top
View user's profile Send private message Visit poster's website
maxqnz
Super User
Super User


Joined: 24 Mar 2003
Posts: 1290
Location: Te Ika a Maui, Aotearoa

PostPosted: Sat Oct 22, 2005 12:03 pm    Post subject: Reply with quote

I apologise for cluttering up this thread, but this is really strange. I downloaded your employees.odb, Drew, and the form in it works just fine. WTF?
_________________
Noho ora mai, ka kite ano.
What Is A Pieriansipist?

OOo 2.4/XP Pro SP2 / OOo 2.3.0.1/OpenSuse 10.3
Back to top
View user's profile Send private message Visit poster's website
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Sat Oct 22, 2005 12:26 pm    Post subject: Reply with quote

You aren't cluttering...I haven't said much on this, because frankly I am baffled.

I have noticed this. Base is most fragile when you are developing things, table defs, query defs, forms and reports. It seems that things work well, once everything is built.

When I built the database that you downloaded I suppose it crashed on my 2 times more then I recount in the text. Also at one point I needed to recreate one of the list boxes. The other example db I have out for download and has been downloaded a fair bit had the same problems, but pretty much all the feed back I have gotten on that one has been that it runs fine when folks pull it down.

I also have 2 small db applications being used. One for about 5 weeks now, used every day, but just once a day. The other for about 3 weeks. They are very simple, but again the users are not having any problems.

It is darn fustrating however when you are trying to actually build something. All I can tell you is...well, think of it as a character building exercise.

Drew
Back to top
View user's profile Send private message Send e-mail Visit poster's website
maxqnz
Super User
Super User


Joined: 24 Mar 2003
Posts: 1290
Location: Te Ika a Maui, Aotearoa

PostPosted: Sat Oct 22, 2005 1:25 pm    Post subject: Reply with quote

Well, this is my last comment, I promise. Obviously a database whose forms can't be used is no good to me. I just don't understand why I can open your employees.odb, then open the form in data-entry mode, and toggle back and forth between Design Mode On/Off, tinkering with design elements at will, switching Design Mode off, switching it back on, etc., etc, and everything works as it it supposed to If I try in my form, crash.
_________________
Noho ora mai, ka kite ano.
What Is A Pieriansipist?

OOo 2.4/XP Pro SP2 / OOo 2.3.0.1/OpenSuse 10.3
Back to top
View user's profile Send private message Visit poster's website
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Sat Oct 22, 2005 1:29 pm    Post subject: Reply with quote

One last thing you might try.

Create a new blank form, Open the one that doesn't work. Lasso all of the controls copy them to the clip board and paste then into the new blank form. I had to do this a few times in earlier builds, never did figure out what went wrong with the first forms, as if they where malformed and that was that. Anyway, the new form seemed to work just fine. I did have to do a little clean up on the controls but very little.

Drew
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2, 3, 4, 5, 6, 7, 8  Next
Page 1 of 8

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group