When I mentioned that I could put together some running commentary as I
build the example DB what I had in mind would be something like this.
I have a number of people asking about images in Base and I am just
about wrapped up with an example. Here is the text file that will go up
to the
forum board along with it. Something similar is what I would expect to
post up with the sampe DB. Of course I won't go into any crashes along
the way :-(
------------------------------employees.txt----------------------------------
I am going to create another example database, this one will let us
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.
[b][i]CREATE THE DATABASE FILE[/i][/b]
Lets start by creating a new database. I have choosen the OOBase native
database engine HSQLDB.
Tools>New>Database
Select Next
Seletct 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 thru 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 Veiw" 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"
[b][i]CREATE THE RELATIONSHIPS[/i][/b]
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 Embployees. 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.
[b][i]CREATE THE EMPLOYEE FORM[/i][/b]
At this point go ahead and save the database file.
Alright then, lests 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 imediatly edit
the form layout, or go right into editing data with the form. Lets edit
the latyout. Select "Modify the form".
Click Finish
[i]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
Foregin key relastionship is gone. So the moral of this is - If you are
going to change a column that is part of a foregin key relationship, be
sure to drop the relationship first...[/i]
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 layed out 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 accidentaly hitting run...OO.o crashed. Upon restart the form was
completely gone. Jees, this is starting to feel like old home week...
OK, well...not to be detered. Lets just press forward.
