[Example] Single Item In/Out + Costing

Some examples to be used directly
Forum rules
No question in this forum please
For any question related to a topic, create a new thread in the relevant section.

[Example] Single Item In/Out + Costing

Postby chrisb » Fri Jul 22, 2016 2:13 am

 Edit: 06-Aug-2016 23:40. was informed by forum user charlie.it that the attached ODB was faulty. the form 'fAssets_&_Hire_Rates' was corrupted.
the guilty attachment has been removed & replaced with an uncorrupted version. 

in response to those seeking help in designing a single item in/out/rental database.

download the attachment.

select 'menu:Tools>Relationships' & take a look at the table relationships.

because this is a demo i have typed the data directly into the tables 'tCategory' & 'tCustomers' i.e. no input forms.
assets & hire rates are added/manipulated using the form 'fAssets_&_Hire_Rates'.

all data in relation to orders is stored in the table 'tLog'.
orders are created using the form 'fOrder_New'.
returned assets are processed using the form 'fReceive_Asset_From_Customer'. only two editable fields here ('DueReturnDate' & 'Paid').
because 'fReceive_Asset_From_Customer' shows detailed costing it is wide & will most likely require a widescreen monitor in order to enable satisfactory viewing.

to make it easy on myself the assets i have chosen for this example are movies & the categories are Action, Crime, Horror etc.
items with categories such as Fiction, Non-Fiction, Lawn Mower, Carpet Cleaner or Cement Mixer could just as easily be used.

i have not included a field for serial numbers in the table 'tAssets'. that's because they do not influence the functionality of the database.
what i'm trying to do here is show how certain obstacles may be overcome. it's a guide, not the finished product.

CREATE ORDER:
open the form 'fOrder_New'.
we have one mainform 'fFilter_Customer+Category' which has two subforms 'sLog' & 'sOutstandingOrders'.
use the mainform list boxes to select a customer and or a category.
if changes have been made then:-
1. hit the 'SAVE' button. (saves 'tCustomer.ID' & 'tCategory.ID' to the fields 'Customer_ID_1' & 'Category_ID' in the table 'tFilter').
2. hit the'Refresh' button. (refreshes the mainform which will then update both its subforms).
NEXT:
subform 'sLog'. allows additions only.
this form uses a list box in order to select an asset.
this list must contain only those assets which are both in stock & fall into the required category.
therefore by default this form can not show any of those assets which are currently allocated to a customer.
1. 'Issue Date'. if left empty defaults to current_date. (menu:Tools>SQL >>> alter table "tLog" alter "IssueDate" set default current_date;).
2. 'Due Return Date'. should be a minimum of one calendar day greater than 'Issue Date'.
3. 'Select Asset'.
the data source for this list box is the query 'qLB_AssetsAvailable_Filtered_With_Cost_SQL'.
this query is smart. the list of assets are both in stock & possess the required category.
 Edit: 14:25 22-July-2016.
A. removed two lines of text from this post.
B. updated the content of the query 'qLB_AssetsAvailable_Filtered_With_Cost_SQL'.
C. don't know what happened but the Form 'fOrder_New' was not working properly.
have reset properties of Filter to '( "tFilter"."ID" = 0 )' HERE>>>>Form 'fOrder_New'>'fFilter_Customer+Category'>Form Properties>Data Tab>Filter
D. replaced the previously uploaded attatchment with the updated version. 

4. 'UNDO'
if you decide against saving & this button is not greyed out then hit it before proceeding (will clear/empty the controls). skip 5.
if both of the controls 'Due Return Date' & 'Select Asset' contain a value & you click elsewhere (deselect the form) then the data will be saved but neither subform will be refreshed.
if any control contains a value but 'Due Return Date' and/or 'Select Asset' does not & you click elsewhere (deselect the form) then you may receive an error message or 'Base' could crash.
5. hit the 'Refresh' button.
despite appearances this button belongs to the mainform.
when we hit it we are deselecting the subform forcing it to save its data to the table 'tLog'.
the mainform is then refreshed & updates both of its subforms.
NEXT:
the second subform 'sOutstandingOrders' is read only.
its data source is the query 'qF_OutstandingOrders'.
this query selects all open orders for all customers. that is all orders where 'ActualReturnDate' is null.
the subforms master/slave fields ('Customer_ID_1' & 'Customer_ID') filter the output.

RECEIVE ASSET FROM CUSTOMER:
open the form 'fReceive_Asset_From_Customer'.
we have one mainform, one subform & one sub-subform.
1. use the mainform list box to select a customer.
2. hit the 'Refresh Grid' button.
despite appearances this button belongs to the subform 'sCustomerHistory'.
when we hit it we are deselecting the mainform forcing it to save its content to the field 'Customer_ID_2' in the table 'tFilter'.
the subform is then refreshed & in turn updates its own subform 'sCustomer_Owes' (read only. shows how much money the customer owes).
NEXT:
the subform 'sCustomerHistory' shows the full order history for selected customer.
its data source is the query 'qF_Asset_In_By_Customer'.
this query collates data from four tables.
it shows the full history of every asset ever issued to a customer.
the subforms master/slave fields ('Customer_ID_2' & 'Customer_ID') filter the output.
1. 'ActualReturnDate'. the date that the hired asset was returned.
2. 'Paid'. if the customer has paid then hit the check box.
the field 'tLog'.'Paid' is boolean. its default value = false. (menu:Tools>SQL >>> alter table "tLog" alter "Paid" set default false;).
3. save the data to table 'tLog'.
if the form contains only one record then you must use the navigation bar. hit 'Save Record' & then 'Refresh'.
if the form contains more than one record then just click on the next or previous record.
the table is updated & the subform 'sCustomer_Owes' is refreshed.
NOTE:
the 'DailyRate' (cost of hire per day) for the period between 'IssueDate' & 'DueReturnDate' (agreed loan period) is set at the level in force at time of issue ('IssueDate').
late returns. any period after 'DueReturnDate' up to & including 'ActualReturnDate' is charged at 'DailyRate' * 2 (hard coded into the query 'qF_Asset_In_By_Customer').
'Base' forms having a query which collates data from two or more tables as their data source can be temperamental. this form runs perfectly on my windows 10 machine.
the form is large only because this is a demo & i believe it is important to make clear exactly how the cost is calculated.

ASSETS & HIRE RATES:
open the form 'fAssets_&_Hire_Rates'.
we have one mainform ('Add-Edit-Delete an Asset') & one subform ('Set Hire Rate For Selected Asset').
ASSETS:
the data source for the mainform 'Add-Edit-Delete an Asset' is the table 'tAssets'.
using this form is self explanatory.
if left empty the field 'DateAquired' defaults to current_date. (menu:Tools>SQL >>> alter table "tAssets" alter "DateAquired" set default current_date;).
if an asset is deleted then all records held in the tables 'tLog' & 'tHireRate' which reference that asset will also be deleted (delete cascade).

SET HIRE RATE:
'Set Hire Rate For Selected Asset'
the data source for this subform is the table 'tHireRate'.
the subforms master/slave fields are 'tAssets.ID' & 'tHireRate.Asset_ID'.
we have three fields 'DateStart', 'DateEnd' & 'DailyRate'.
the 'DailyRate' will apply between & including 'DateStart' & 'DateEnd'.

to see this in action select 'Zootropolis (2016)' from the mainform 'Add-Edit-Delete an Asset'.
now look at the hire rates. this is a practical if not realistic example of how it should be done.
open the form 'fReceive_Asset_From_Customer' & select 'Woody Woodpecker'. compare the 'IssueDates' & 'DailyRate'. this proves that the method works.
close the form 'fReceive_Asset_From_Customer'.

back to 'Set Hire Rate For Selected Asset'.
note the last entry in the column 'DateEnd' ('31/12/2036' (UK format)).
this is a default date which has been set in the form but not the table properties. it gives a shelf life of 20 years.
the last entry in the column 'DateEnd' should always show the default date.

to change/format this default:-
close the form then open it for edit.
if necessary select 'menu:View>Toolbars>Form Design' to show form design toolbar.
move the mouse over the design toolbar & hit 'Form Navigator' (opens form navigator).
move the mouse over the design toolbar & hit 'Form' (opens form properties).
hit General tab in properties window.
click on the column header 'Date End' in the table grid.
now you can change and/or format the default date.

use the form navigator & form properties windows to select & examine the various form structures.

FILTER TABLE:
i have previously referenced the table 'tFilter' several times. it is the only table which does not appear in 'menu:Tools>Relationships'.
we use a list box to select a value from a list.
a list box has two fields (visible field/bound field)
the visible field appears in the list. the bound field (usually the integer value of a primary key field) is hidden.
we select from the visible field but when we hit save it is the value of the bound field which is stored in a table.

we use the list box 'Select Customer' in the mainform of 'fReceive_Asset_From_Customer' to select from a list of Customers.
its data source is the query 'qLB_Customers_All'.
the visible field is 'tCustomers.Name'. the bound field is 'tCustomers.ID'.
the bound field must be saved to a table before its value can be passed to the subform.
this is the purpose of a filter table (to store the bound field from a list box).

the filter table we use has four fields (all integers) but only one row.
1. 'ID'. integer primary key.
2. 'Customer_ID_1' stores the value 'tCustomers.ID' from the list box 'Select Customer' in the mainform of 'fOrder_New'.
3. 'Customer_ID_2' stores the value 'tCustomers.ID' from the list box 'Select Customer' in the mainform of 'fReceive_Asset_From_Customer'.
4. 'Category_ID' stores the value 'tCategory.ID' from the list box 'Select Category' in the mainform of 'fOrder_New'.
we have two fields which hold the value of 'tCustomers.ID'. this is done to enable the use of the forms 'fOrder_New' & 'fReceive_Asset_From_Customer' simultaneously without conflict.
i believe that forum user Villeroy is the originator of the filter table. where would we be without it?

QUERIES:
all query names are preceded by the letter 'q'.
a query name preceded by 'qF' denotes that this query is the data source of a form.
a query name preceded by 'qLB' denotes that this query is the data source of a list box.
a query name preceded by 'q_INFO' denotes that this query when executed will display useful information. it is not required in order for the database to function.
a query name followed by '_SQL' denotes that this query has been saved in direct mode.
DIRECT MODE:
when creating a query i rarely use the the base GUI query editor 'Database>Queries>Create Queries in Design View...'.
i prefer to use 'Database>Queries>Create Query in SQL View...' & type the code directly into the 'Query Design' window.
direct mode is enabled from the 'Query Design' window by selecting 'menu:Edit>Run SQL command directly' or just hitting the 'SQL' icon on the toolbar.
the base parser is now disabled.
when we open our queries in edit mode then the formatting will be exactly as saved making the code far easier to read/edit/debug.
we can use REM statements (any text preceded by '--', two minus signs). this aids the users memory when returning to a query at some time in the future.
however when using forms the base parser must be enabled in order for all options to function. this includes the passing of parameters between forms (master field/slave field) & the sort/filter options.
this database contains three queries which are used as the data source of a form. that is three queries saved with direct mode disabled.
these three queries are also saved with direct mode enabled.
these duplicates have names which begin with 'qF' & end in '_SQL' they are included for the purpose of clarity & are not required in order for the database to function.

it's worth taking a look at the query 'q_INFO_Assets_Inventory_EveryItem' which shows the current location of every asset. its contents can be both sorted & filtered.

no questions on this forum but issues & comments are welcome.
Attachments
Rental_Log Uncorrupted.odb
(43.6 KiB) Downloaded 515 times
open office 4.1.5 & LibreOffice 6.0.5.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 176
Joined: Mon Jun 07, 2010 4:16 pm

Return to Database Examples

Who is online

Users browsing this forum: No registered users and 1 guest