CS200 Assignment 11 – FileMaker Advanced Due Wednesday April 4th, 11:59 pm
Files
CampWaterLooLooLoo.fmp: this file contains three tables named Employees, Weeks, & Schedule. You will properly modify the field definitions and Data Entry Options for appropriately.
Data.xlsx: This is an Excel file (.xlsx) with three sheets in it. Each sheet includes a different set of data to correspond with each of our tables in CampWaterLooLooLoo.fmp, and is labelled appropriately.
CampWaterLooLooLoo.fmpDemo: This file shows you how your final file will look and perform.
The layouts you are creating do not need to look like the ones in the Appendix. Be as creative as you would like as long as you follow the guidelines of the Non-Designers Design Book.
Assignment Objectives
To further enhance your ability to explore a new aspects of a familiar application. To expand knowledge of FileMaker
To reinforce database management system knowledge
Assignment Strategy
It is strongly recommended that this assignment be completed with a partner.
The assignment is step by step, it is recommended you complete the questions in order.
A Planning Sheet has been provided in the assignment files for your convenience when thinking about how you will be defining your tables and setting up the database.
CS200 Assignment 11 Page 1 of 14
Question 1 [15 marks] – Data
a) Rename the CampWaterLooLooLoo.fmp12 file to yHC1_yHC2_ CampWaterLooLooLoo.fmp12, then
open the file. (Remember yHC stands for Your Handin Code)
b) Modify the field definitions and data entry options to be appropriate for the data in Data.xlsx (they are currently all set as text). Value lists may be appropriate for a field. These can be based on the data within the field. You may want to modify some of these definitions after you import the data in part c.
c) Import the data from each of the Excel sheets into the the appropriate predefined table in your database. When you import be sure that you do not include the headings from Excel. You should also check that there are no empty records within the database. Be careful to match the correct data with the correct fields.
d) Add your information into the Staff Table.
e) Create the appropriate relationships between the three tables.
CS200 Assignment 11 Page 2 of 14
Question 2 [20 marks] – Layout “Staff Details”
In this step you begin work on the first of four layouts that you will be creating, namely Staff Details. As you can see below, this layout contains tabs listing three main topics per staff member: Address information, Health information and their summer Schedule.
You may choose any theme you would like (keep in mind the Non-Designer’s Design Book concepts and ensure that the theme you choose is easily read). You will save yourself some time later if you take a few minutes as you create your layout to align and space the fields attractively.
The contents of each tab can be observed on page 7.
a) From the Staff table, create an empty layout named Staff Details.
b) Add the labels, fields, and the three tabs as shown (Address, Health and Schedule)
c) The Birthdate field must be formatted as Month, Day, Year.
d) Provide the Birthdate field with a “drop-down calendar,”.
e) Arrange for the calendar icon to be visible even when the user has clicked in another field.
f) In the appropriate table, create a field called End Date that calculates the end date to be the following Saturday. The beginning of each week is a Monday.
HINT: there are five days between the beginning of the week and the end.
g) In the Schedule tab, create a portal with the relationships you defined in Question 1 to fetch the beginning and end dates for each staff member and what they will be paid each week. The Start Date and End Date fields must be formatted as DayOfWeek, Day Month, Year.
h) The Schedule records shown in the portal must be sorted by Week Number.
i) Create a field that will calculate the total pay each staff member will make over the summer.
Put this field in an appropriate place on the layout.
CS200 Assignment 11 Page 3 of 14
Question 3 [15 marks] – Find Staff Member
To make the Staff Details layout more useful, you will next add a global field and a button at the bottom of the layout that allows a user to select a Staff member from a scrollable list of all Staff Members and then find the corresponding record.
a) Create a global text field called WhichStaffMember in the Staff table.
b) Add the WhichStaffMember field to the Staff Details layout (as shown).
c) Create a second field that will ‘concatenate’ data to the format below.
115: Ross, Mike
254: Lee, Way
220: Jones, Jason
234: Shrek, Fiona
•••
HINT: You will use a calculated field to accomplish this and the result will be text..
d) Format the WhichStaffMember field to be a scrollable list with the values from this new field. When you click on this field, it will show you the list of people showing only their first and last names.
HINT: You will need a second calculated field for this and read the Value List dialog box carefully.
e) Create a script named FindStaffMember that will find the record for the staff member specified by the WhichStaffMember global field.
HINT: You may want to place one of the new concatenated fields on the original Staff layout.
f) Add a small rounded button labeled Go! just to the right of the WhichStaffMember field that
executes the FindStaffMember script when clicked.
g) Add the label Click on the Staff Member above, select a person then Go.
CS200 Assignment 11 Page 4 of 14
Question 4 [10 marks] – Creating a Schedule in the Schedule Table
In this step we will create a simple layout to view the staff members who are scheduled each week. We will create scripts to navigate to related layouts
a) In the Weeks table, create a layout called Weekly Schedule. Add and format the appropriate fields and portal as shown in the image in the Appendix. The portal should be sorted by Last
Name.
b) Create a script that when a name in the portal is clicked, it will take you to the Staff Details for
that staff member.
c) Create a script that will then take us from the Staff Details to the week of work selected(in the
Weeks table) from the portal in the Schedule tab.
d) In the Weekly Schedule add a Web Viewer that will show a map of the location of each weeks
camp.
CS200 Assignment 11 Page 5 of 14
Question 5 (15 Marks) – Summarizing Weekly Pay
In this step we will create a layout that will give us the total paid to staff each week and create a graph to display the results.
a) Create a new layout in the Schedule table with the Employee ID, the Employee Name, and pay for that week. Call this layout Pay Summary.
b) We will then summarize the information based on each week, showing the total amount paid each week. Your layout should look like this.
HINT: The weekly pay is not stored in the Schedule table, in order to create a proper summary, you will need to use a calculated field to store the Weekly Pay before creating the field for the total amount paid.
CS200 Assignment 11 Page 6 of 14
Question 6 (5 Marks) – Summarizing Weekly Pay Chart
In this step we will create a graph to display the results.
a) You will now create a chart in FileMaker of the Weekly Pay Summary in a new layout.
b) Be sure the x-axis uses the Week Number and Start Date (You may wish to create a concatenated field for this) the angle of the items is 90°.
c) Save the resulting layout as Weekly Pay Summary.
CS200 Assignment 11 Page 7 of 14
Question 7 [10 marks]: Creating a Custom Dialog Box
a) Create a button on the Staff Details layout. Label it Add to Schedule.
b) Create a custom dialog box that will allow the user to add a Staff member to a particular week.
• The Cancel button should bring us back to the Staff Details layout and delete the newly created blank record from the Schedule table.
• The OK button should bring you to the Staff Details layout and to the student just added to the schedule.
c) Attach this script to the Add to Schedule button.
HINT: The Show Custom Dialog script step is found in the Miscellaneous list.
CS200 Assignment 11 Page 8 of 14
Question 8 [10 marks]: Password Protecting File
To protect files from being modified by users, it is possible in FileMaker to create accounts with differing privileges. You will create three different accounts with varying privileges.
a) Create a user called CS200Marker, with the password cs200. This user will have full access to your database.
b) Create a user called CS200Observer, with the password test. This user will have read only access to your database.
c) Create a user called CS200Student, with the password final. This user will be able to create and modify records and value lists and print from the database. Name this privilege set Student.
There is a default user called Admin. To test your accounts and passwords turn this account off. Be sure to turn it back on at the end of the exam to ensure that we will have access and will be able to mark your assignment.
CS200 Assignment 11 Page 9 of 14
Bonus: Choose 3 of the following to enhance your database
Any of the following can be added to enhance the functionality of your database.
• Create Popovers where data entry will be necessary to give the user better direction. (2 marks)
• Create a layout that would be appropriate for a mobile device. It should have the functionality of one of the layouts we have created today. (10 marks)
• Create a report in a different table that is summarizing another aspect of the table. (10 marks)
• Create a fourth table called Themes and have a theme for each week with a corresponding craft and game. Incorporate this into your Schedule. (You will need to create the data).(10 marks)
• Add a photo to the background of a layout. (2 marks)
CS200 Assignment 11 Page 10 of 14
Question 2: Staff Details
CS200 Assignment 11 Page 11 of 14
Question 3: Staff Details with Find
Question 4:
CS200 Assignment 11 Page 12 of 14
Question5: Weekly Pay Summary
CS200 Assignment 11 Page 13 of 14
Question 6: Weekly Pay Summary Graph
Question 7: Dialog Box
CS200 Assignment 11 Page 14 of 14