Book Dealer database

Table of Contents
Book Dealer database........................................1 Background....................................................1 Tables, relationships, forms and reports.........2 Create the forms............................................3 Acquisitions...............................................3 Add a new Buyer.......................................3 Trading account.........................................3 Invoice.......................................................4 Invoice report.................................................6 The system in practice...................................8

Background
This aims to go beyond the sample databases provided in the Base forum. It is assumed the reader knows how to create tables, queries and create a main form and subform group. This is used by a friend to manage his part-time book dealing business. He buys books secondhand books to resell them. At any one time he has between 700 and 800 books available for sale and has about 500 regular customers. Practical experience shows those volumes have a negligible delay in populating the forms. The database has been used for about six months. There were several flaws which he was prepared to live with. One remains unresolved.

Tables, relationships, forms and reports

There are four forms Acquisitions This records the books bought. On completion a debit transaction is written. Add a new Buyer This records data about the buyers. Some common names in the UK include Smith and Evans. I used a concat field (based on Surname, First name and Post code) to help to discriminate between the instances. Practical experience shows it is very rarely required. In the UK a Post code covers about 40/50 houses. Invoice This has a list of the books available for sale. It can be searched on three fields. A selected book is ticked and transferred to the invoice. A running total is maintained. When the books are selected an amount for post and packing is added and an overall total is displayed. An invoice can be printed to check all is correct and if so a credit transaction is written. Trading account This has the transactions mentioned above plus others such as expenses. There is one report Invoice This prints a single invoice. The database shows the tables. In three cases ALTER was used as follows In INVOICE table set the SOLD column to zero, ie available for sale.

ALTER TABLE BOOK ALTER COLUMN SOLD SET DEFAULT 0 In ACQUISTION table set date to default to today. ALTER TABLE ACQUISTION ALTER COLUMN ACQUISTION_DATE SET DEFAULT CURRENT_DATE In INVOICE table set date to default to today. ALTER TABLE INVOICE ALTER COLUMN INVOICE_DATE SET DEFAULT CURRENT_DATE The database must be registered.

Create the forms

Acquisitions
This is a straightforward create with the main form using the table ACQUISITION and the subform using the table BOOK. The link fields are ACQUISTION_KEY. A push button was added to write a debit transaction. Macro is Write debit on Mouse button pressed.

Add a new Buyer

Trading account
This form uses has the table TRADING_ACCOUNT as the main form and TRANSACTION as the sub form. After creation the main form was removed. It only existed to give me a key field for the subsequent summing of credits and debits. The link fields are TRADING_ACCOUNT. I created a query, called Transaction_balance, to sum credits and debits and provide a balance. See below for the layout. I then added a sub form to the main form as follows. Open the Form navigator. Right click on MainForm then New then Form. An entry, labelled Standard appears. Overwrite it with TransactionBalance. Right click that entry and select Properties. The Form properties window opens. Select the Data tab, in Content type select Query and in Content select Transaction_balance. Now select the formatted field icon and draw three boxes. In the first right click, select Control, select Data tab, select Data field and, from the drop down list, select SumCredits. Now select General tab then select Formatting and choose Currency, then select Alignment and select Right and then select Font and select 3d. Now repeat for the other two fields using SumDebits and Balance. There is a macro that forces a recalculation of the sums after a change. Place mouse in the form, right click and select Form, then select Event tab. In After record action select the ReloadSums macro.

Invoice
This form has a main form based on the table INVOICE and a sub form based on the table INVOICE_DTL. The linking fields are INVOICE_KEY. A ListBox based on Buyer is created with CNCAT_NAME as the field to be displayed and the BUYER_KEY to be saved. The sql had to be modified to achieve that SELECT "CONCAT_BUYER", "BUYER_KEY" FROM "BUYER" To change the order so the names are alphabetical I changed the sql to SELECT "CONCAT_BUYER", "BUYER_KEY" FROM "BUYER" ORDER BY CONCAT_BUYER so that the concat names were in alphabetical order. Add a navigation bar. Use Form navigator and click MainForm, to make it the active form, select the Navigation icon and place the bar below the concat field. The navigation bar makes it easier to browse the invoices and click to open a new invoice. When I select a concat name I want to be sure I have the right person and address. So I added a sub form based on BUYER. The process is described above. Six text fields were added with the data field FIRST_NAME etc. I also set Read only, in General tab, to No so they cannot be modified. The first name selected may not be the right one so select again. To force a reload I used a macro, ReloadBuyer, on the Event Item status changed in the ListBox. When dealing with a large number of names select the list box then type one/two characters of the required name. The list box then shows the first with that one/two letters. Experiment at this point with some names and check the results. The next step was to add the form using BOOK table. This is a form unrelated to the main form and is linked to Forms. This form will display the books available for sale (SOLD = 0). There is a search/filter facility to reduce the books displayed. The search is based on Author, Book Title or Category. The search/filter is removed by a button.

The search is insensitive to case. Searching for New has the same results as searching for new. The search can be based on a part of the word. Searching for poy provides results for all T & AD Poyser. Open Form navigator click Forms, then repeat what was described above using BOOK as the table, draw a table grid then add a text field and two buttons. The SOLD column has to be converted to a Check box. Right click the SOLD heading, select Replace with and select Check box. Right click the column again and select Control, select Data tab and in Reference (on) type 1 and in Reference value (off) type 0. Select the Event tab and in Item status changed browse to the macro AddBookToInvoice2. The text field will be used to hold filter data. Right click the box, select Control, select general tab and in Name type Search content and in Border select 3D then exit. The first button is used to initiate the search. Right click the button, select Control, select the General tab and in Name type Search. Click the Events tab and in Mouse button pressed browse to the macro BookSearch2. The second button is used to clear the search. Right click the button, select Control, select the General tab and in Name type Press to clear the search and in Word break select Yes. Can now drag the button to hold the word. Click the Events tab and in Mouse button pressed browse to the macro ClearSearch. I wanted to know the value of the books in the invoice so I created a query, InvoiceValue.

I then added that to the form. Here is where care is needed to place it in the correct form. The summing is on the INVOICE_DTL table and so has to be linked to the SubForm. Use the Form navigator, right click SubForm, select New then select Form and overwrite Standard with InvoiceValue. Then repeat what was described earlier with Data tab having Content type as Query and Content being InvoiceValue. Place a formatted field next to the SubForm (ie the invoice details). That formatted field has to be linked to the query so in Data tab select Sum_Selling_Prices and in the General tab change the Name to Sum_Selling_Prices. The AddToInvoice2 macro has a line to force a reload a summing of the prices. Experiment with some books and the search. To add a book to the invoice select a book and click the Sold check box. The book details are transferred to the invoice. Now want to add a charge for post and packing and a total cost. Those last two fields exist in the INVOICE table and will be on the form. Drag them down below the Sum_Selling_Prices field. Right click the fmtINVOICE_POST_PACKING, select Control, select Events tab and in When losing focus browse to macro TotalCost2. (This is the flaw I referred to. There must be a more automatic solution). I tried other Events for this eg Text modified but it was activated on the first number. Not what I wanted. There must be a better one than that selected. Experiment at this point. Add a charge for post and packing and click outside the box and the total field will be populated. Two final steps. Add two buttons and it is not important which form they are added to. In the database I have them with two different forms. That was careless but I have left it to show it was not critical. For the first button right click and select Control. In the General tab over type Name with Print invoice then select Events tab and in Mouse button pressed browse to PrintInvoice macro. For the second button repeat the above buy using Write a credit in Name and WriteCredit for the macro. The form is now complete.

Invoice report
This uses Sun Report Builder and is based on the query, Invoice_for_printing. The query looks like this.

When it is run there is a request to input an invoice number.

The print can be executed in the Invoice form. When I first created this I used a macro that printed the report but also opened another copy of the database. A post by VooBase
http://www.oooforum.org/forum/viewtopic.phtml?t=76964&sid=26f0800ea999456f163d20484ea8d529

showed how to do the printing without opening the second copy.

The system in practice

Select Sheep, Shaun from the drop down list and check the address is correct.

He wants to buy Population ecology of raptors, Ospreys a natural and unnatural history and Finches. Click the Sold box for each. The form is now looks like this. The summed price is shown correctly.

I add a post and packing charge. The form is now like this. Click outside the post and packing box to activate the macro that updates the value.

Then print an invoice to ensure the data is correct and then write a credit. Two copies are printed. One is sent with the books, and the other is used for the annual tax return. The next time the form is loaded the three books are not displayed as they are no longer for sale. They can aslo be removed by clicking the Press to clear the search button.