WARNING – The Federal Bureau of DataBase Investigation does not want you to read this. What you are about to read may corrupt the rules of Database Normalization.
Welcome to the “Real World”. Sometimes, for various good, and very valid reasons, some of the rules of Database Normalization are not adhered to. What I am going to tell you about is one of the instances.
Most Invoice Applications have an Invoice Header. In many cases it would really be nice to have the Invoice Total in the Invoice Header. Let’s assume a Client has an existing Alpha Four Invoicing Application. You are converting it to Alpha Five. Your Client is used to having an Invoice Total in the Header. Keeping in mind that the Client is always right, let’s go ahead and put the Invoice Total in the Header.
Here is an abbreviated Application based on an actual Invoicing sub-system of a large running Application for a Client in Long Island, New York.
Because the emphasis of this Article is to show how to save an Invoice Total into the Invoice Header, I used just three Tables, one Set and one Form.
Fig 1 – Invoice_Header Table
Invoice_No is an autoincrement field, Invoice_date has Date() as an expression
Fig 2 – Invoice_Detail Table
There is one calculated field, Ext_cost. The expression is Qty*cost.
Fig 3 – Cust_Master Table
The three tables are combined into a Set named Invoice_set. Cust_Master is linked by Cust_no and the Invoice_Detail is linked by Inv_No.
Fig 4 – Invoice_Set
Next I designed an Edit / Entry Form using the Invoice_Set.
This is just a simple form, there are 3 buttons, Close, New Invoice and Save Total. Pressing the Save Total Button executes a short Xbasic script attached to the OnPush Event. This script actually saves the Total (from the Calculated field from the form) into the Invoice_header table Invoice_total field, and refreshes the Invoice Total Field on the Form.
This is not the only way you can accomplish the desired result. I could have used the Form CanSave Event entering almost the same Script. I decided to use a Save Total Button.
This is my Form in Design Mode.
Fig – 5 Entry_Form
Just a couple of notes about the form. The Top frame contains a display field “Invoice_total”, this is the field that the Script that follows will refresh().
The bottom frame, just under the Browse has the calculated field named “Inv_total”. This is the field that will be placed into the Invoice_header “Invoice_total” field.
The Total Button OnPush Event Script follows:
''XBasic Save Total button OnPush Script
dim hdr as p
sys_send_keys("[F9]") 'this commits the record
hdr=table.current() 'sets the hdr pointer to the Invoice_header table
hdr.change_begin() 'start the change method
hdr.invoice_total=Inv_Total.value 'the calculated total from the form is
' in the Invoice_total field
hdr.change_end(.t.) 'end the change method
invoice_total.refresh() 'refreshes the total field in the form body
end
Here is the same form with some Test Data that has been entered and the “Save Total” button had been pushed.
Fig 6 – Entry Form with data
Really, there is no Federal Bureau of DataBase Investigation, or is there? Well, we did what we had to do, it’s quick, it’s easy, and I won’t tell if you won’t tell.
Barry, the president of the NY Alpha Users' Group, is an Alpha Developer based mid-town Milford Square, PA, (that’s where the 4 Way Stop is. Very soon moving to Newtown, PA. He can be reached at brochford@enter.net
last revised 3/25/99 - pkw
Don't forget, we need your feedback to make this site better!