Difference between revisions of "Maddie Leigh's Art Sales"

From Apache OpenOffice Wiki
Jump to: navigation, search
 
(4 intermediate revisions by 2 users not shown)
Line 14: Line 14:
  
 
The only item of note for this schema is the two forieng key relationships from Customer to Purchase Order. In order to do this the person creating the database must enter the actual ALTER TABLE commands from the SQL window, but everything else is done via the GUI.
 
The only item of note for this schema is the two forieng key relationships from Customer to Purchase Order. In order to do this the person creating the database must enter the actual ALTER TABLE commands from the SQL window, but everything else is done via the GUI.
 +
 +
I might also mention one other thing - some may think this simple table design as being flawed because a lot of data is duplicated between the Customer and Purchase Order tables. There is a reason for this however, the user has a business where the 'Buyer', who is often an employee of a firm, may change firms. The customer table holds current information for an idividual while the purchase order table must show exactly the information as it was at the time of the order and shipment. IMO, this kind of arrangement is the easiest way to handle that situation, and was the choice made by the person wanting to build the databse. So the Customer table is really used as a lookup table, and would be used for any type of marketing purposes. The data in the purchase order table is all filled in programmatically in the order entry form, this is done to eliminate human error ( typos ) in entering the data, and helps insure the data's integrity.
  
 
Also, as I mentioned my idea would be to implement the new customer buttons by calling a dialog box. I think this is a good thing to include in the example. It is also, IMO, a good example of showing where from a UI one would want to use a dialog box - as the workflow of starting an order screen and then needing to add a customer recored would benefit from the modal quality of the dialog.
 
Also, as I mentioned my idea would be to implement the new customer buttons by calling a dialog box. I think this is a good thing to include in the example. It is also, IMO, a good example of showing where from a UI one would want to use a dialog box - as the workflow of starting an order screen and then needing to add a customer recored would benefit from the modal quality of the dialog.
 +
 +
A second item would be the use of sub selects and views. The macros, such as this one, currently execute statements with sub selects, requiring the creation of a view, assigned to the Item Status Change event for one of the list boxes:
 +
<pre>
 +
sub ShipToStatusChange( oev as variant )
 +
'
 +
' Fill in the following fields in
 +
' Purchase_Order table
 +
'  SHIP_TO_CUSTOMER_ID
 +
'  SHIP_TO_CUSTOMER_NAME
 +
'  SHIP_TO_COMPANY_NAME
 +
'  SHIP_TO_ADDRESS
 +
'  SHIP_TO_CITY
 +
'  SHIP_TO_PROVINCE_STATE
 +
'  SHIP_TO_POSTALCODE_ZIPCODE
 +
'
 +
dim CustomerID as integer
 +
dim stmt as variant      ' a statement for the SQL to
 +
                          ' to run in
 +
dim PO_Rowset as variant  ' actually this is the embedded dataform
 +
dim Cust_ResultSet as variant ' CUSTOMERS table record
 +
dim strSQL as String      ' the select statement we need
 +
 +
  PO_Rowset = oEv.Source.Model.Parent
 +
  Stmt = PO_Rowset.ActiveConnection.CreateStatement
 +
  ' get the customer record
 +
  strSQL = "SELECT * from ""CUSTOMERS"" "_
 +
              & " WHERE ""ID"" = "_
 +
              &"(SELECT ""vCustomerList"".""ID"" "_
 +
              &"FROM ""vCustomerList"" "_
 +
              &"WHERE ""vCustomerList"".""NAME"" = '"_
 +
              & oEv.Source.Text & "')"   
 +
  Cust_ResultSet = stmt.executeQuery( strSQL )
 +
    ' check that we got a record
 +
  if not Cust_ResultSet.isBeforeFirst then
 +
      msgBox( "Customer record not found" )
 +
      goto ShipToStatusChangeExit
 +
  else
 +
  Cust_ResultSet.Next
 +
  endif
 +
  with PO_Rowset.Columns
 +
    .getByName("SHIP_TO_CUSTOMER_ID").updateInt(_
 +
        Cust_ResultSet.columns.getByName("ID").getInt )
 +
    .getByName("SHIP_TO_CUSTOMER_NAME").updateString(_
 +
        oEv.Source.Text )
 +
    .getByName("SHIP_TO_COMPANY_NAME").updateString(_
 +
        Cust_ResultSet.columns.getByName("COMPANY").getString )
 +
    .getByName("SHIP_TO_ADDRESS").updateString(_
 +
        Cust_ResultSet.columns.getByName("STREET").getString )
 +
    .getByName("SHIP_TO_CITY").updateString(_
 +
        Cust_ResultSet.columns.getByName("CITY").getString )
 +
    .getByName("SHIP_TO_PROVINCE_STATE").updateString(_
 +
        Cust_ResultSet.columns.getByName("PROVINCE_STATE").getString )
 +
.getByName("SHIP_TO_POSTALCODE_ZIPCODE").updateString(_
 +
    Cust_ResultSet.columns.getByName("POSTAL_ZIP_CODE").getString )
 +
  end with
 +
ShipToStatusChangeExit:
 +
  Cust_ResultSet.Dispose
 +
 +
end sub
 +
</pre>
 +
 +
Now if I where to move forward with this database I would go back and change these to work with the QiQ build and utilize derived queries.
  
  
[[Category:Base Example]]
+
[[Category:Database Scenarios]]

Latest revision as of 07:18, 10 August 2006

This database is not one actually in use. It comes from some help sessions on the OOOForum. It might be a good base to work up from however.

The database used was kept as simple as possible to support the development of one form and a few macros. The schema is

Maddies art schema 1.png

The form that was developed is this. ( notice the name will change, as Juliana was the users name and I thought it best to use a different one - so I will get to making that change if necessary. )

Order entry 3.png

The macros created do a lot of filling of data when a button is clicked, or an item in a list box is selected. The Form uses a number of sub forms to generate the required calculated fields. The two buttons named 'new' currently do nothing, but would be updated to bring up a data entry screen ( dialog box ) for entering new customer information. The execution of the dialog would trigger a validation routine to insure both completeness and to stop any duplicate entries.

The database should lend itself to the creation of a couple of reasonable reports. Pretty much the other screens that would be needed would require little or no macro coding.

The only item of note for this schema is the two forieng key relationships from Customer to Purchase Order. In order to do this the person creating the database must enter the actual ALTER TABLE commands from the SQL window, but everything else is done via the GUI.

I might also mention one other thing - some may think this simple table design as being flawed because a lot of data is duplicated between the Customer and Purchase Order tables. There is a reason for this however, the user has a business where the 'Buyer', who is often an employee of a firm, may change firms. The customer table holds current information for an idividual while the purchase order table must show exactly the information as it was at the time of the order and shipment. IMO, this kind of arrangement is the easiest way to handle that situation, and was the choice made by the person wanting to build the databse. So the Customer table is really used as a lookup table, and would be used for any type of marketing purposes. The data in the purchase order table is all filled in programmatically in the order entry form, this is done to eliminate human error ( typos ) in entering the data, and helps insure the data's integrity.

Also, as I mentioned my idea would be to implement the new customer buttons by calling a dialog box. I think this is a good thing to include in the example. It is also, IMO, a good example of showing where from a UI one would want to use a dialog box - as the workflow of starting an order screen and then needing to add a customer recored would benefit from the modal quality of the dialog.

A second item would be the use of sub selects and views. The macros, such as this one, currently execute statements with sub selects, requiring the creation of a view, assigned to the Item Status Change event for one of the list boxes:

sub ShipToStatusChange( oev as variant )
'
' Fill in the following fields in
' Purchase_Order table
'  SHIP_TO_CUSTOMER_ID
'  SHIP_TO_CUSTOMER_NAME
'  SHIP_TO_COMPANY_NAME
'  SHIP_TO_ADDRESS
'  SHIP_TO_CITY
'  SHIP_TO_PROVINCE_STATE
'  SHIP_TO_POSTALCODE_ZIPCODE
'
dim CustomerID as integer
dim stmt as variant       ' a statement for the SQL to 
                          ' to run in
dim PO_Rowset as variant  ' actually this is the embedded dataform
dim Cust_ResultSet as variant ' CUSTOMERS table record
dim strSQL as String      ' the select statement we need

  PO_Rowset = oEv.Source.Model.Parent
  Stmt = PO_Rowset.ActiveConnection.CreateStatement
  ' get the customer record
  strSQL = "SELECT * from ""CUSTOMERS"" "_
               & " WHERE ""ID"" = "_
               &"(SELECT ""vCustomerList"".""ID"" "_
               &"FROM ""vCustomerList"" "_
               &"WHERE ""vCustomerList"".""NAME"" = '"_
               & oEv.Source.Text & "')"     
  Cust_ResultSet = stmt.executeQuery( strSQL )
    ' check that we got a record
  if not Cust_ResultSet.isBeforeFirst then
      msgBox( "Customer record not found" )
      goto ShipToStatusChangeExit
  else
  	Cust_ResultSet.Next
  endif
  with PO_Rowset.Columns
    .getByName("SHIP_TO_CUSTOMER_ID").updateInt(_
        Cust_ResultSet.columns.getByName("ID").getInt )
    .getByName("SHIP_TO_CUSTOMER_NAME").updateString(_
        oEv.Source.Text )
    .getByName("SHIP_TO_COMPANY_NAME").updateString(_
        Cust_ResultSet.columns.getByName("COMPANY").getString )
    .getByName("SHIP_TO_ADDRESS").updateString(_
        Cust_ResultSet.columns.getByName("STREET").getString )
    .getByName("SHIP_TO_CITY").updateString(_
        Cust_ResultSet.columns.getByName("CITY").getString )
    .getByName("SHIP_TO_PROVINCE_STATE").updateString(_
        Cust_ResultSet.columns.getByName("PROVINCE_STATE").getString )
	.getByName("SHIP_TO_POSTALCODE_ZIPCODE").updateString(_
	    Cust_ResultSet.columns.getByName("POSTAL_ZIP_CODE").getString )
  end with						
ShipToStatusChangeExit:
  Cust_ResultSet.Dispose

end sub

Now if I where to move forward with this database I would go back and change these to work with the QiQ build and utilize derived queries.

Personal tools