[SOLVED] COMM 205 FileMaker Pro Assignment

COMM 205 Winter 2017 Term 2 FileMaker Pro Assignment

Happy Harbor Financial Green Investing the Right Way

Due: Section 1 February 6, 2018 (Tue) at 11:59 pm Section 2 February 7, 2018 (Wed) in class

Marks: 4% of course total

Purpose

The purpose of this assignment is to enable you to gain some hands-on experience in creating a simple database to retrieve and generate information using FileMaker Pro 16.

Scenario

Happy Harbor Financial is a fictitious company that manages a comprehensive suite of green investment solutions designed particularly for private investors. To demonstrate your understanding of the database concepts, you are asked to create a simple database to manage data about their clients and stock transactions. The database will contain some information about the investors (clients), and the orders placed by individual investors to buy or sell stocks. For simplicity, there are only FIVE tables in the database, viz. Client, Broker, Stock, Order and Order Details. Refer to the tables and relationships shown below.

COMM 205 Winter 2017 Term 2 FileMaker Pro Assignment Page 1 Copyright 2017 Y.M. Cheung

Hand-in Requirements
Each student is required to prepare and submit this assignment individually. This is NOT a group

assignment.

The assignment is divided into THREE sections. To receive full marks for this assignment, you should complete and submit both section 1 and section 2.

• Section 1 Hands-on Project

  Submit the FileMaker Pro database file consisting of the tables and forms you create (as specified on pages 3-6 below) in the Assignment drop-box on Connect. This database file should be renamed using your student number, e.g. 12345678.fmp12.

• Section 2 Project Related Questions
  Answer some project related questions and submit your Answer Sheet in class.
• Section 3 Bonus Section
  Add an optional form (as specified on page 9 below) to the FileMaker Pro database file you

  create in Section 1 above.

  ** Important: You should download the marking guide and read the detailed requirements carefully, particularly the questions listed in Section 2, before you start.

  Students who fail to complete and submit both section 1 and section 2 described above will get zero marks for this assignment.

  Project Requirements Section 1 Hands-on Project (60%)

  Your first task is to download a sample database file, HappyHarbour_2017W2.fmp12, from the course website. This sample database contains one table, viz. Client table. You will create the other four tables, viz. Broker, Order, Order Details and Stock, by importing data from an Excel file, HappyHarbour_2017W2.xlsx (which is also available on the course website), and create the relationships among these five tables.

  Secondly, you are required to create the following layouts (forms) to allow users to view, enter and update data. Before you start, please study the relationships among these five tables and understand what data is stored in each of these tables. Make sure you read the requirements carefully.

  COMM 205 Winter 2017 Term 2 FileMaker Pro Assignment Page 2 Copyright 2017 Y.M. Cheung

1. Transaction Form The form should allow users to browse all stock transaction records. That is, the form should show a list of Order Details records which include the Order ID, Type, Status, No. of Shares, Offering Price1 fields as well as the Total Price1 (which is equal to No. of Shares * Offering Price.)

Refer to the screen dump figure shown below.

For each record, it should also show the Order Date (from Order table), the Ticker and Exchange name (from Stock table), and the Clients First Name and Last Name (from Client table).

You should include the form name in the Header section and your name in the Footer section.

Mandatory

1 For simplicity, please dont worry about the currency ($) sign for the Offering Price and Total Price fields.

COMM 205 Winter 2017 Term 2 FileMaker Pro Assignment Page 3 Copyright 2017 Y.M. Cheung

2. Client Form The form should allow users to browse all clients and their related order details records. That is, the form must contain a portal that lists the order and order details related to the client displayed in the form.

Refer to the screen dump figure shown below.

Each Client should include the First Name, Last Name, Customer since and Street Address fields (from Client table).

For each Order Details record, it should show the Order ID, Type, Status, No. of Shares and Offering Price2 (from Order Details table), plus the Ticker (from Stock table).

You should include the form name in the Header section and your name in the Footer section.

Mandatory

2 For simplicity, please dont worry about the currency ($) sign for the Offering Price field.

COMM 205 Winter 2017 Term 2 FileMaker Pro Assignment Page 4 Copyright 2017 Y.M. Cheung

3. Stock Form The form should allow users to browse all stocks and their related order details records. That is, the form must contain a portal that lists the order and order details related to the stock displayed in the form.

Refer to the screen dump figure shown below.

Each Stock should include the Ticker, Company Name and Exchange fields (from Stock table).

For each Order Details record, it should show the Order Date, Type, Status, No. of Shares and Offering Price3 (from Order and Order Details tables), plus the Clients First Name and Last Name (from Client table).

You should include the form name in the Header section and your name in the Footer section.

Mandatory

3 For simplicity, please dont worry about the currency ($) sign for the Offering Price field.

COMM 205 Winter 2017 Term 2 FileMaker Pro Assignment Page 5 Copyright 2017 Y.M. Cheung

4. Order Form The form should allow users to browse all orders and the related order details records. That is, the form must contain a portal that lists the order details related to the order displayed in the form.

Refer to the screen dump figure shown below.

Each Order should include the Order ID, Order Date (from Order table), the Clients First Name and Last Name (from Client table), and the Brokers First Name and Last Name (from Broker table).

For each Order Details record, it should show the Type, Status, No. of Shares, Offering Price4 fields as well as the Total Price2 (which is equal to No. of Shares * Offering Price) plus the Ticker and Exchange fields (from Stock table). The Order ID and Stock ID (from Order Details table) should also be displayed for testing purposes.

In addition, there should be a radio button set for the Type field that allows users to select either Buy or Sell and a pop-up menu for the Status field that allows users to select either Approved or Rejected when they are updating a record. In other words, the value selected should be saved in the corresponding field in the Order Details table.

You should also include the form name in the Header section and your name in the Footer section.

Mandatory

4 For simplicity, please dont worry about the currency ($) sign for the Offering Price and Total Price fields.

COMM 205 Winter 2017 Term 2 FileMaker Pro Assignment Page 6 Copyright 2017 Y.M. Cheung

Radio button set for Type field

Popup menu for Status field

Section 2 Project Related Questions (40%) General

Q1. How many primary key(s) and foreign key(s) is/are there in the Order Table? (4%) Transaction Form

Q2. How many records (rows) are displayed in the Transaction Form? (4%) Stock Form

Q3. How many Buy and Sell transactions are displayed for the Ticker code CYN in the Stock Form? (6%)

Client Form

Q4. Find all the order details belonging to a client named Olympia Alvarez in the Client Form. (12%)

(Your answer should correspond to what you see in the Client Form.)

COMM 205 Winter 2017 Term 2 FileMaker Pro Assignment Page 7 Copyright 2017 Y.M. Cheung

Order Form
Q5. Find all the order details related to Order ID number 67 in the Order Form. (14%)

(Your answer should correspond to what you see in the Order Form.)

COMM 205 Winter 2017 Term 2 FileMaker Pro Assignment Page 8 Copyright 2017 Y.M. Cheung

Section 3 Bonus Section (10%)

This bonus section has been added in this assignment to provide some challenges for those who want to gain more hands-on experience in FileMaker Pro. Only minimum help will be given for this section.

If this bonus section is completed, it is possible to score up to 110% for this assignment.

1. Edit Order Form (Optional) You should make a copy of the Order Form that you created above and add the following requirements to this form. In other words, the features that you added to the Order Form should continue to work.

1. a) There should be a drop-down calendar for the Order Date field.
2. b) The Clients first name and last name fields as well as the Brokers first name and last name fields should NOT be editable (i.e. the users cannot change the clients and brokers names in this form.)
3. c) There should be a pop-up menu for the Ticker field that allows users to search for a Ticker from the Stock table (parent table), and store the corresponding Stock ID (primary key) to the Stock ID field (foreign key) of the Order Details table (child table).
4. d) The No. of Shares and Offering Price fields must be entered (i.e. cannot be blank) and must be greater than zero. Otherwise, a meaningful message should be displayed rather than the system generated error message.
5. e) A Grand Total field should be added to show the sum of Total Price of all the order details records in the portal.

Refer to the screen dump figure shown below.

Dropdown calendar

Fields not editable, i.e. the clients and brokers names cannot be changed.

Popup menu to allow users to search for a Ticker from Stock table.

end of document

COMM 205 Winter 2017 Term 2 FileMaker Pro Assignment Page 9 Copyright 2017 Y.M. Cheung

These two fields must be entered (i.e. not blank) and must be greater than 0

Sum of Total Price