CS200 Assignment 11 – FileMaker Advanced Due Tuesday July 30th 2019, 11:59 pm
No late days can be applied to this assignment
Files
CampWaterLooLooLoo.fmp12: this file contains three tables named Employees, Weeks, & Schedule. You will properly modify the field definitions and validation options 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.fmp12, and is labelled appropriately.
CampWaterLooLooLoo Demo.fmp12: This file shows you how your finished file will look and perform. The layouts you are creating do not need to look exactly like the examples provided. 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 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.
However, you should still read through the entire assignment first.
• 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.
• You may also choose to use your own database tables and data to create a similar result.
CS200 Assignment 11 Page 1 of 11
Question 1 [15%] – Data
Before attempting to answer the questions, first read through the entire assignment carefully. Use the CampWaterLooLooLoo Demo.fmp12, it is a sample solution. When you are finished, your assignment should function the same way that the demo does. Understanding the database and the FileMaker functionality you are required to implement is crucial.
a) Rename the CampWaterLooLooLoo.fmp12 file to username1_username2_ CampWaterLooLooLoo.fmp12, then open the file.
b) ModifythefielddefinitionsanddataentryoptionstobeappropriateforthedatainData.xlsx. 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 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 11
Question 2 [20%] – 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 in the Appendix, 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 in of the Appendix.
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) 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.
f) 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.
g) The Schedule records shown in the portal must be sorted by Week Number.
h) 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 11
Question 2, Part b:
CS200 Assignment 11 Page 4 of 11
Question 3 [15%] – 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 below).
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 (as shown to the right).
HINT: You will need a second calculated field for this. 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 5 of 11
Question 4 [10%] – 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 (see below) that will show a map of the location of each week’s camp.
CS200 Assignment 11 Page 6 of 11
Question 5 (15%) – 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.
c) Create a script that will sort the layout appropriately and place a button on your layout called Sort.
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 7 of 11
Question 6 (5%) – 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 45°.
c) Save the resulting layout as Weekly Pay Summary.
CS200 Assignment 11 Page 8 of 11
Question 7 [10%]: 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 9 of 11
Question 8 [10%]: 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) CreateausercalledCS200Observer,withthepasswordtest.Thisuserwillhavereadonly 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.
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. Any changes will need to be verified with a full access username and password.
CS200 Assignment 11 Page 10 of 11
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 in this assignment. (5 marks)
• Create a report in a different table that is summarizing another aspect of the table. (5 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). (5 marks)
• Add a photo to the background of a layout. (2 marks)
• Create a runtime solution to allow your “app” to be used on a machine that doesn’t have FileMaker.
https://fmhelp.filemaker.com/help/16/fmp/en/index.html#page/FMP_Help/runtime.ht ml (5 marks)
CS200 Assignment 11 Page 11 of 11