Page 1 of 1

Form to pull rental records

PostPosted: Sat Jan 12, 2019 10:02 am
by jpoblocki77
Hello all !!

I am presently creating a database for my plane-leasing business. What I would like is one for that when I type in the customer information it brings upin a little grid pattern, all the planes that person has rented from me and all the details of said lease agreement but I am not sure how to do this.

Would I have to create a separate table that I would manually have to populate with the data?

Heres what I have so far

Customers Table
Customer Number Integer field primary key
Account Number Text VarChar
First Name Text VarChar
Lastname Text VarChar
E-mail Address Text Varchar
Company Text VarChar

Aircraft Table
Stock Number Integer Primary key
Registration Number Text Varchar
Airplane Make Text fix Char
Airplane Model Text fix Char
Engine Type Text VarChar
# of Engines Text VarChar
Airframe Hours Time Time
Engine Hours Time Time
IFR Equipped Boolean
GPS Equipped Boolean
Ap Equipped Boolean
Declared Value Number Numeric
Monthly Charge Number Number
Service Fee Number Numeric
Lease Terms Text Varchar
Lease Start Date Date
Lease ENd Date Date
Leased to text varchar

I would also eventually like to get the payments to come up below that table so that when I look up a customer I see what planes he/she has leased and what payments have been made, but if someone can help me get the planes leased part working I'm pretty sure I can figure out the payments and notes parts of the form.

So just to say again what I want is to type in say:
John Johnson and it will tell me that he is leasing a Learjet 45 from me
the declared value is 560,000.00 with a service fee of 560.00 and a lease payment of 1560.00 a month with a lease start of 10.28.2019 and an end date of blah
This way if a customer calls me and says that he needs information for insurance or so I can pull it up quickly for him and don't have to wade thru table after table of data.

I'm sure that this is prolly done with subforms, but I don't fully understand them and have spent at least 4 hours trying to get them to work to no avail.

So if someone could tell me how to set this up i would greatly appericate it as I have no clue how to do it and have spent way too much time trrying to figure it out. I am thinking I will have to create a table that i manually populate with all the data to be able to acheieve this

I would also like to know how to create a form that i could go in and say change the lease end date on if the terms happen to change or something like that.

Re: Form to pull rental records

PostPosted: Sat Jan 12, 2019 8:08 pm
by UnklDonald418
Welcome to the forum.

You have a text field "Leased to" in your Aircraft table. Change that from text to Integer type so that it can can be used as a Foreign Key value. Then use Tools>Relationships to establish the relationship between the "Customer Number" in Customers table and "Leased to" in the Aircraft table.

Open the Form design Wizard. and on the first page select all the fields of the Customers table.
On the second page of the Wizard, check the Add Subform box
select Subform based on existing relation
and in answer to Which relation do you want to add? select the relationship you created.
On the third page of the Wizard select all the fields from the Aircraft table.
For your first try, on page five (Arrange the controls on your form) accept the defaults (As Data Sheet) for both main form and subform.
Accept the defaults for the remaining wizard pages.

Open the form and you can use the upper table to display/edit/add customers and their information.
The lower table allows display/edit/add operations on all aircraft associated with the customer selected in the upper table.
to get the payments to come up below that table so that when I look up a customer I see what planes he/she has leased and what payments have been made

Like the Aircraft table, the Payments table would need to have a Foreign Key field that can be used to establish a relationship between the Payments table and the Customers table.
Then a second subform would be required but since the wizard can only create one subform that will need to be done by right clicking on the form document, and select the Edit option to open the form document in the Design Mode. (There can be some confusion because the word "form" can have multiple meanings, so the form documents are what you see listed in the Forms area of the main database window. Those are actually Writer documents with database controls added)
There is a tutorial on creating a subform in the Design Mode.
[Tutorial] Creating a form in Design view
If you need detailed help please upload a sample database
[Forum] How to attach a document here

Re: Form to pull rental records

PostPosted: Sat Jan 12, 2019 9:04 pm
by jpoblocki77
Customer name seems to be the most common field in all my table, maybe i forgot to put it in there because it was late and i was tired. Could I not just use customer name to be the search topic? I messed with this for several hours today after doing some video watching and reading on forms and sub forms and think I may have gotton a working copy up and runn. Ill upload a copy of it to show what i did. That i do NOT think I created any relationships so that might have to be done.

Re: Form to pull rental records

PostPosted: Sat Jan 12, 2019 9:07 pm
by jpoblocki77
Here hopefully is the database if not I will put it up on mediafire also. here is the link ... g.odb/file

Re: Form to pull rental records

PostPosted: Mon Jan 14, 2019 5:52 am
by UnklDonald418
Names make poor primary keys because they are often not as unique as you might expect. Database engines like HSQL used by OO Base, process integer values much more efficiently than text strings, so it is best to establish relationships between tables using integer fields for primary and foreign keys. So, I replaced "Customer Name" in the tables Account Notes and Payments with "CustomerID", and in the Airplanes table I used an integer for "Leased to". With those changes, most of the needed relationships can be established in your database.
I added more fields to your Customers table and populated it with some test data.
There were enough issues with the Airplanes table that I created a new one, by executing an SQL command at Tools>SQL. A copy of the CREATE TABLE command is saved in the form document named SQL.
The data type Time refers to time of day, so the maximum hours would be 23 making it inappropriate for "Airframe Hours" and "Engine Hours". I changed them both to Decimal(8,1).
Also I changed "Engine Type" from text to Integer so it could be used as a Foreign Key referencing the Engine Types table.
Select Tools>Relationships to see all the relationships that have been established in the modified version I uploaded.
Those relationships make it possible to access data from all the tables using Form/Subform combinations.

The form document New Customer Entry was updated to reflect the changes to the Customers table. Likewise, New Airplane Entry was updated, plus there are two list box controls for "Engine Type" and "Leased to".
The form document Customer Record Lookup now displays the Airplanes leased to the selected Customer. The grid controls allow Payments Information and Account Memo's to display information from the Payments and Account Notes tables. Those controls can also be used the enter new or edit existing Payments and Account Notes, so I didn't bother with Pay Entry or Account Notes form documents.
Also, be aware that the primary key must appear on a Form if the form will be used to edit the underlying data. If the primary key is auto generated it still must be on the form, but it can be hidden from the user. For instance the "Notes Id" field doesn't appear on the Account Memo's table control, but because "Notes Id" is on the list of hidden columns editing is possible.
One problem with the form document Customer Record Lookup is that the only way to change the selected customer is to use the navigation controls at the bottom of the window, which is rather clumsy.
Customer Record Lookup01 solves that problem. Choose a customer name from the listbox at the top of the form and press the Display Selection button to update the form with the selected customer information. The listbox stores the selection in the FILTER table and Query_FilterSelect uses that to create a result set for MainForm.

An Embedded database is a handy format for learning, designing and sharing Base databases, but due to a potential data loss problem it is not really suitable for use in a real world setting. A split database model minimizes the data loss problem and allows the database engine to be updated. Before converting be sure to understand that there are some side effects.
[Wizard] Create a new 'split' HSQL 2.x database

Jackson Leasing_Modified01.odb
(56.19 KiB) Downloaded 62 times

Re: Form to pull rental records

PostPosted: Wed Jan 16, 2019 9:09 pm
by jpoblocki77
NEgine hour and airframe hours are the total hours on each. Its used for rebuilds and maintenance records. The why they were set to a time componet. I have one plane that has over 800 hours on it and per faa regulations i need to track the time on the aircraft for my reports. Will I cause any damage Reverting those fields? And I rather lost on what i should be doing im guessing i shouldd:

got to Tools>SQL. A copy of the CREATE TABLE command is saved in the form document named SQL. I guess i should execute this?
and then do a split model database?

Or maybe i read this wrong. Ill dl the database and check it out and see what was all done and see if i can figurei t out for future databases that might need to be created

Re: Form to pull rental records

PostPosted: Wed Jan 16, 2019 11:22 pm
by UnklDonald418
A Time data type is actually part of a datetime value that can be used to store timestamp values. They can be used to calculate an elapsed time, for instance DATEDIFF ( 'hour', TIMESTAMP '2008-11-22 20:30:40', TIMESTAMP '2008-11-22 00:30:40' ) would return 20 hours.
If you are just storing an elapsed time like 200 hours then it is better to use either an integer or decimal type. I'm not familiar with aircraft, but I do know that most hour meters on industrial applications measure time in .1 hour increments so I used DECIMAL(8,1) for both "Airframe Hours" and "Engine Hours" in my example. If you only need to keep track of whole hours you could change them to INTEGER type.
I'm not sure what would happen if you have already entered data into the Time field. You could try copying the table using Cut and Paste and on the Type Formatting page of the Copy Table dialog you could change the data type for those fields. Then compare the values between the two tables.
It is always a good idea to save a backup anytime you are making changes to your database, so you can revert back to your starting place if things go awry. Everything is in the .odb file when using an embedded database, but wWhen backing up a split database be sure to copy the entire directory including the sub-directories.

The CREATE TABLE statement makes it fairly easy edit existing fields or add new ones and create a new table to use for testing.
When you use Tools>SQL you are bypassing the Base front end and working directly with the underlying database engine, so after executing SQL statements there, be sure to select View>Refresh Tables so that the Base front end knows about the changes. Otherwise you can may get error messages or strange things happening.

Creating a split database isn't very difficult. Put the Wizard in its own directory and open it to create an empty database. Then you can use cut and paste or drag and drop to easily copy the tables. queries, forms and reports from the embedded to the split database. If you need to rely on the GUI to edit tables then get everything working in the embedded database before moving it to the split database.