Create Purchase Order Application Using Appaserver 1 Create

Create Purchase Order Application Using Appaserver 1. Create the purchase order database design. 2. Create the purchase order database tables. ©Tim Riley

Purchase Order Database Design Allocate 4 characters of database space for the purchase time. The format will be HH 24. For example, 1310 represents 1: 10 PM. The time will be automatically pre-populated in the data entry form. Purchase_ Order purchase_date purchase_time vendor date * time 4 * char 30 * Vendor From It's possible that two or more purchases could occur at the same minute. So include the vendor in the primary key. ©Tim Riley vendor char 30 * Allocate 30 characters of text for each vendor name.

Purchase Inventory are goods available for sale. Purchase_ Order purchase_date purchase_time vendor Inventory is recorded as an asset until sold. It is recorded in the books at cost. date * time 4 * char 30 * many Inventory inventory ©Tim Riley There is a many-to-many relationship between purchase order and inventory. char 30 * When sold, the cost of inventory is recorded in the account called cost-of-goodssold. Total cost-ofgoods sold is subtracted from total revenues to yield grossmargin.

Purchase Inventory Purchase_ Order purchase_date purchase_time vendor date * time 4 * char 30 * The new table is usually named by combining the names of both many-to-many tables. The primary key of the new table are the primary keys of both tables. Purchase_ Inventory Convert the many-to-many relationship into two manyto-one relationships. Inventory inventory ©Tim Riley char 30 * purchase_date purchase_time vendor inventory quantity cost_per_item date time 4 char 30 integer float Record the quantity purchased and the cost per item. * *

Purchase Plant and Equipment Purchase_ Order purchase_date purchase_time vendor Plant is the business term for buildings. date * time 4 * char 30 * Plant and equipment are buildings, machines, furniture, and tools that are used to add value to products and services. Their costs are material enough to warrant additional book-keeping. Purchase_ Plant_Equipment purchase_date purchase_time vendor plant_equipment quantity cost_per_item date time 4 char 30 integer float Record the quantity purchased and the cost per item. Plant_Equipment plant_equipment char 30 * ©Tim Riley Plant and equipment are recorded as an asset. They are systematically expensed over time in the account called depreciation expense. * *

Purchase Supplies Purchase_ Order purchase_date purchase_time vendor date * time 4 * char 30 * Supplies are products that cost less than a material amount. Supply supply ©Tim Riley char 30 * Purchase_ Supply purchase_date purchase_time vendor supply quantity cost_per_item date time 4 char 30 integer float Record the quantity purchased and the cost per item. Supplies are expensed when purchased into the account called supplies expense. * *

Purchase Services Purchase_ Order purchase_date purchase_time vendor date * time 4 * char 30 * Purchase_ Service purchase_date purchase_time vendor service hours cost_per_hour date time 4 char 30 integer float * * Services are consumed upon purchase. Record the hours worked and the cost per hour. Service service ©Tim Riley char 30 * Services are expensed when purchased into the account called {service} expense. Replace {service} with the actual service. For example, Lawn Mowing expense.

Receive Inventory Receive_ Inventory received_date received_time vendor inventory purchase_date purchase_time quantity date time 4 char 30 date time 4 integer Purchase_ Inventory * * Record the quantity received. purchase_date purchase_time vendor inventory quantity cost_per_item } Foreign key to purchase inventory table. Partial shipments are possible. ©Tim Riley date time 4 char 30 integer float * *

Receive Supplies Receive_ Supply received_date received_time vendor supply purchase_date purchase_time quantity date time 4 char 30 date time 4 integer Purchase_ Supply * * Record the quantity received. purchase_date purchase_time vendor supply quantity cost_per_item } Foreign key to purchase supply table. Partial shipments are possible. ©Tim Riley date time 4 char 30 integer float * *

Receive Equipment Receive_ Plant_Equipment received_date received_time vendor plant_equipment purchase_date purchase_time quantity date time 4 char 30 date time 4 integer * * Record the quantity received. Purchase_ Plant_Equipment purchase_date purchase_time vendor plant_equipment quantity cost_per_item } Foreign key to purchase plant equipment table. Partial shipments are possible. ©Tim Riley date time 4 char 30 integer float * *

Make Payment Cash_Account Bank bank char 30 * account_number char 30 * bank Purchase_ Order_Payment payment_date payment_time vendor purchase_date purchase_time bank account_number amount_paid ©Tim Riley date * time 4 * char 30 * date time 4 char 30 float char 30 * Purchase_ Order } purchase_date purchase_time vendor amount_due Foreign key to purchase order table. Partial payments are allowed. date * time 4 * char 30 * float

Purchase Order Database Tables Log in with your email address and password of “changeit”. ©Tim Riley

Become Administrator Role Choose the administrator role. ©Tim Riley

Purchase Order Database Tables To preserve relational integrity, create all ”one” tables first. Purchase_ Order purchase_date purchase_time vendor date * time 4 * char 30 * Vendor From vendor Insert the vendor attribute very first. ©Tim Riley char 30 *

Insert Attribute Choose the insert attribute menu item. ©Tim Riley

Insert Vendor Attribute The attribute name is “vendor”, the datatype is ”Text”, and the size is 30 characters. Leave everything else blank. ©Tim Riley

Insert Vendor Attribute Press the <Submit> button. ©Tim Riley Scroll to the bottom of the top frame.

Insert Vendor Attribute The new vendor attribute is inserted into the attribute table. ©Tim Riley

Purchase Order Database Tables Second, insert the vendor folder. Purchase_ Order purchase_date purchase_time vendor ©Tim Riley date * time 4 * char 30 * Vendor From vendor char 30 *

Insert Folder Choose the insert folder menu item. ©Tim Riley

Insert Vendor Folder The folder name is “vendor”, the form is “Prompt”, and the insert screen will have 5 blank rows for data entry. Leave everything else blank. When the user chooses to lookup this folder, the Prompt form displays a query screen to select subsets. The alternative to the Prompt form is the Table form. The Table form displays all of the rows without a query screen first. ©Tim Riley

Insert Vendor Folder Press the <Submit> button. ©Tim Riley Scroll to the bottom of the top frame.

Insert Vendor Folder Attribute Upon pressing <Submit>, the bottom frame displays rows to insert the new folder's attributes. ©Tim Riley

Insert Vendor Folder Attribute The press the <Submit> button. Currently, the vendor folder only has one attribute, and it's the primary key. To show Appaserver that an attribute is not participating in the primary key, enter an integer in the display order column and leave the primary key index blank. Choose “Vendor” from the attribute drop-down, and enter “ 1” for the primary key index. Leave everything else blank. ©Tim Riley

Insert Vendor Relationship This one-to-many relationship will be inserted into the relation folder when we insert the purchase order folder. When we insert the purchase order folder, the relationship will be manyto-one. Purchase_ Order purchase_date purchase_time vendor date * time 4 * char 30 * The next screen that appears in the bottom frame allows you to insert relations for the vendor folder. However, this folder doesn't have any many-to-one relationships. Vendor From Instead, the vendor folder has a one-to-many relationship. ©Tim Riley vendor char 30 *

Insert Vendor Relationship Press the <Submit> button without filling out this form. . ©Tim Riley The bottom frame allows you to insert relations for the vendor folder. However, this folder doesn't have any many-to-one relationships.

Insert Vendor Permissions The press the <Submit> button. The permissions for a folder are assigned to roles. This screen allows you to insert permissions for the vendor folder. The permissions are insert, update, and lookup. Since the update permission has more authority than the lookup permission, the update permission assumes the user has lookup permission. ©Tim Riley Only a supervisor should be allowed to insert and update vendors.

Insert Vendor Role Operation The press the <Submit> button. An operation places a yes/no check-box at the beginning of each row following a query. The delete operation places a trash-can check-box on each row. Allow the supervisor to be able to delete vendors. ©Tim Riley

Insert Vendor Folder Complete The next step is to create the vendor table in the RDBMS. We have just inserted rows into the following tables: 1) folder 2) folder_attribute 3) role_folder 4) role_operation ©Tim Riley

Create Vendor Table Scroll to the top of the top frame. This will expose the menu choices. We're going to run a process. ©Tim Riley

Create Vendor Table Choose the Create Table process. ©Tim Riley

Create Vendor Table Choose the “Finance” application and the “Vendor” folder. This will create the vendor table in the RDBMS. All processes that manipulate the database have a ”Really yn” safety mechanism. Choosing “No” (or leaving at “Select”) will only display the work that will take place. Choosing “Yes” will really do the work. Finally press <Submit>. ©Tim Riley Choose “No” the first time.

Create Vendor Table This is the create table command that will be sent to the RDBMS. Next, set “Really yn” to “Yes”, and press <Submit>. The create unique index command enforces the element uniqueness integrity constraint. ©Tim Riley

Create Vendor Table Any error messages would have appeared in the bottom frame. The table is created. ©Tim Riley

Populate Vendor Table Switch to the supervisor role. ©Tim Riley

Populate Vendor Table Choose the menu item of insert vendor. ©Tim Riley

Populate Vendor Table This asterisk means vendor is the primary key. Optionally, we could enter in each vendor one at a time by pressing <Submit> each time. Remember when we set the insert rows number to 5? Press <Submit> without entering in a vendor name. ©Tim Riley

Populate Vendor Table If we need to add more than 5 vendors in the bottom frame, then press <Submit> again in the top frame to get 5 more empty rows. By leaving any primary key attribute blank on the top frame, you get rows in the bottom frame to insert new data. Enter in all the new rows, then press <Submit> in the bottom frame once. ©Tim Riley

Populate Vendor Table Type in some vendor names. Then press <Submit>. ©Tim Riley

Populate Vendor Table The insert succeeded. ©Tim Riley

Lookup Vendor Table Choose the menu item lookup vendor. The table was empty prior to our insert. However, this count won't get updated until the top frame get repainted. ©Tim Riley

Lookup Vendor Table This is the prompt form. Remember when we chose this form over the table form when we inserted this folder? Each attribute will have some mechanism to query subsets. ©Tim Riley

Lookup Vendor Table Then press <Submit>. Lookup all vendors that begin with “a”. ©Tim Riley

Lookup Vendor Table All rows matching the lookup query in the top frame are displayed in the bottom frame. Remember, we inserted the “Delete” operation to the supervisor role into the role operation folder for the vendor folder? ©Tim Riley

Create Purchase Order Table Purchase_ Order purchase_date purchase_time vendor date * time 4 * char 30 * Vendor From Insert the purchase order attributes. ©Tim Riley vendor char 30 *

Insert Purchase Order Attributes Choose the insert attribute menu item. ©Tim Riley

Insert Purchase Order Attributes The attribute name is purchase_date. The attribute datatype is current date. The only difference between datatypes of date and current date is current date will prepopulate the field with the current date. The attribute width is 10 characters. The standard format for date is yyyy-mm-dd. However, users can change the way dates are displayed to the American format – mm/dd/yyyy. ©Tim Riley

Insert Purchase Order Attributes Press the <Submit> button in the top frame. Leave everything else blank, and scroll down to the bottom of the top frame. ©Tim Riley

Insert Purchase Order Attributes Press the <Top> button so we don't have to use the scroll bar to get to the top. Press the <Reset> button in the top frame. This will clear the form so we can insert the purchase time attribute. The bottom frame shows the results. ©Tim Riley

Insert Purchase Order Attributes The attribute name is purchase_time. The attribute datatype is current time. The only difference between datatypes of time and current time is current time will prepopulate the field with the current time. The attribute width is 4 characters. The standard format for time is HH 24, which is down to the minute. However, you can have times down to the second by making the width 7. Then the format is HH 24: SS. For example 1310: 15 is 1: 10 PM and 15 seconds. ©Tim Riley

Insert Purchase Order Attributes Press the <Submit> button in the top frame. Leave everything else blank, and scroll down to the bottom of the top frame. The bottom frame shows the results. ©Tim Riley

Create Purchase Order Table Insert the purchase order folder. Purchase_ Order purchase_date purchase_time vendor ©Tim Riley date * time 4 * char 30 * Vendor From vendor char 30 *

Insert Purchase Order Folder Choose the insert folder menu item. ©Tim Riley

Insert Purchase Order Folder The behavior will be just like inserting folders. The top frame inserts the folder. Then the bottom frame inserts many rows in each related folder: 1) Attribute 2) Relation 3) Role Folder 4) Role Operation The folder name is purchase_order. The form type is Prompt. The number of rows to insert is 1. We will generate a one-to-many data entry screen. The top frame will be to insert the one purchase order. The bottom frame will be to insert many rows in each related folder: 1) Purchase Inventory 2) Purchase Plant Equipment 3) Purchase Supply 4) Purchase Service ©Tim Riley

Insert Purchase Order Folder Press the <Submit> button. Scroll to the bottom of the top frame. ©Tim Riley

Insert Purchase Order Folder Attributes Press <Submit> in the bottom frame. Insert the purchase order's 3 primary key attributes. ©Tim Riley

Insert Purchase Order Relation Press <Submit> in the bottom frame. Choose Null for the related attribute. The related attribute is part of the relation table's primary key. So it must be populated with something. The Null string provides this placeholder. Purchase order has a many-to-one relation to vendor. ©Tim Riley

Insert Purchase Order Permissions Press <Submit> in the bottom frame. Give both insert and update permissions to supervisors and users. ©Tim Riley

Insert Purchase Order Operations Press <Submit> in the bottom frame. The delete operation places a trash can next to each row following a query. ©Tim Riley The detail operation places a magnifying glass next to each row following a query. By checking the magnifying glass, the bottom screen will then display for this purchase order the related folders: 1) Purchase Inventory 2) Purchase Plant Equipment 3) Purchase Supply 4) Purchase Service

Insert Purchase Order Complete The next step is to create the purchase order table in the RDBMS. We have just inserted rows into the following tables: 1) folder 2) relation 3) folder_attribute 4) role_folder 5) role_operation ©Tim Riley

Create Purchase Order Table ©Tim Riley

Create Purchase Order Table ©Tim Riley

Purchase Order Design Purchase_Order Vendor Each screen title will provide you with a guide. We created these 2 tables. Purchase_ Inventory The rest of the tutorial will create these 2 tables with only minimum callouts. Inventory ©Tim Riley

Insert Purchase Inventory Attributes ©Tim Riley

Insert Purchase Inventory Attributes ©Tim Riley

Insert Purchase Inventory Attributes ©Tim Riley

Insert Purchase Inventory Attributes ©Tim Riley

Insert Purchase Inventory Attributes ©Tim Riley

Insert Inventory Folder ©Tim Riley

Insert Inventory Folder Now we're going to insert the inventory folder. ©Tim Riley

Insert Inventory Folder Attribute ©Tim Riley

Insert Inventory Relation The inventory folder doesn't have any many-to-one relationships. ©Tim Riley

Insert Inventory Permissions ©Tim Riley

Insert Inventory Operations ©Tim Riley

Insert Purchase Inventory Folder Now we're going to insert the purchase inventory folder. ©Tim Riley

Insert Purchase Inventory Attributes ©Tim Riley

Insert Purchase Inventory Relations Pair 1 tom order is the feature that automatically displays each many related table in sequence when inserting into the one table. ©Tim Riley

Insert Purchase Inventory Permissions ©Tim Riley

Insert Purchase Inventory Operations ©Tim Riley

Insert Purchase Inventory Completed ©Tim Riley

Create Inventory Table ©Tim Riley

Create Purchase Inventory Table ©Tim Riley

Insert Inventory Item The two new tables are created. Now populate them. ©Tim Riley

Insert Inventory Item ©Tim Riley

Insert Inventory Item ©Tim Riley

Insert Purchase Order ©Tim Riley

Insert Purchase Order ©Tim Riley

Insert Purchase Order Inventory ©Tim Riley

Insert Purchase Order Completed ©Tim Riley

Lookup Purchase Order The insert screen has a lookup feature. Let's lookup the purchase order we just inserted. ©Tim Riley

Lookup Purchase Order ©Tim Riley

Purchase Order Detail If you were to delete this purchase order, then all of the rows in the one-to-many related tables would be deleted also. This is necessary to enforce the relational integrity constraint. ©Tim Riley The detail operation allows you to view all of the rows in each one-to-many related table. Currently, we've only created the purchase inventory table.

Purchase Order Detail ©Tim Riley

Insert Purchase Order Inventory ©Tim Riley You can insert new inventory purchases for this purchase order here.

Insert Purchase Order Inventory You can insert new inventory purchases for this purchase order here. However, we only inserted one inventory item – widget. Inserting another widget for this purchase order would be rejected because it would violate the element uniqueness integrity constraint. ©Tim Riley

Purchase Order Design Purchase_Order Vendor We created these 4 tables. The other purchase tables plus the receive and payment tables still need to be created. Purchase_ Inventory Purchase_ Plant Equipment Purchase_ Supply Purchase_ Service Inventory Plant Equipment Supply Service ©Tim Riley