CS200 Assignment 12 – FileMaker Advanced Due Wednesday August 5th 2020, 11:59 pm
No late days can be applied to this assignment but you can hand it in as late as Monday August 10th with no penalty
Files
Vacation.fmp12: this file contains three tables named Hotels, Bookings, & Clients. You will 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 Vacation.fmp12, and is labelled appropriately.
Vacation_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.
You can also view it by logging into the VPN and going to https://filemaker.student.cs.uwaterloo.ca/fmi/webd and selecting VacationDemo.
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.
CS200 Assignment 11 Page 1 of 11
Question 1 [15%] – Data
Before attempting to complete the assignment, read through it carefully. Use the sample solution Vacation_Demo.fmp12. 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 Vacation.fmp12 file to username1_username2_Vacation.fmp12, then open the file.
b) Modify the field definitions and data entry options to be appropriate for the data in Data.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) Check any auto-enter fields to ensure that the next value is greater than the largest value in your data.
e) Add your own information into the Client Table.
f) Create the appropriate relationships between the three tables.
g) In layout mode put the three original tables into a folder ( see demo)
CS200 Assignment 11 Page 2 of 11
Question 2 [20%] – Layout “Client Information”
In this step you begin work on the first of four layouts that you will be creating, namely Client Information. As you can see on the next page, this layout contains tabs listing three main topics per client: Address information, Payment Information and their hotel Bookings.
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 on the following page.
a) From the Client table, create an empty layout named Client Information.
b) Add the labels, fields, and the three tabs as shown (Address, Payment Info and Bookings)
c) The Birthdate field must be formatted as Month, Day, Year (eg January, 31, 2000).
d) Provide the Birthdate field with a “drop-down calendar,”.
e) Create a field to calculate the client’s age.
Hint: You may need to look at FileMaker’s online help to calculate the age accurately. You will need to use the current date.
f) In the Bookings tab, create a portal with the relationships you defined in question 1 to fetch the hotel, check-in and check-out dates of each booked stay. The Bookings records shown in the portal must be sorted by Check-In date.
g) Create a field that will calculate the total amount each stay will cost. (Which table is this coming from?) Put this field in the appropriate place on the layout. Hint: You will need to first create a field to calculate the number of days of the stay.
h) Create a field that will calculate the total amount that each person has spent on their bookings.
CS200 Assignment 11 Page 3 of 11
Question 2:
CS200 Assignment 11 Page 4 of 11
Question 3 [15%] – Find Client
To make the Client Information 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 client from a scrollable list of all clients and then find the corresponding record.
a) Create a global text field called ClientSearch in the Client table.
b) Add the ClientSearch field to the Client Information layout (as shown below).
c) Create a second field that will ‘concatenate’ data to the format below.
HINT:
115: Ross, Mike 254: Lee, Way 220: Jones, Jason 234: Shrek, Fiona •••
You will use a calculated field to accomplish this and the result will be text.
d) Format the ClientSearch field to be a drop-down 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 Find Client that will find the record for the client specified by the ClientSearch global field.
HINT: You may want to place one of the new concatenated fields on the original Client layout.
f) Add a small rounded button labeled Go! just to the right of the ClientSearch field that executes the Find Client script when clicked.
g) Add the label Click on the Client Search below, select the client and click Go.
CS200 Assignment 11 Page 5 of 11
Question 4 [10%] – Creating a Schedule in the Hotel Table
In this step we will create a simple layout to view the clients who are scheduled at each hotel. We will create scripts to navigate to related layouts.
a) In the Hotel table, create a layout called Hotel Schedule. Add and format the appropriate fields and portal as shown below. The portal should be sorted by the Check-In dates
b) Create a script that when a name in the portal is clicked, it will take you to the Client Information for that client.
c) Create a script that will then take us from the Client Information to the hotel booked (in the Hotel table) from the portal in the Bookings tab.
d) In the Hotel Schedule add a Web Viewer (see below) that will show the webpage for each hotel.
CS200 Assignment 11 Page 6 of 11
Question 5 (15%) – Summarizing Hotel Earnings
In this step we will create a layout that will give us the total amount earned at each hotel and create a graph to display the results. Your layout should look like the layout below.
a) Create a new layout in the Bookings table with the Client ID, the Client Name, dates the hotel has been booked and total cost for the booking. Call this layout Hotel Earnings.
b) We will then summarize the information based on each hotel, showing the total amount earned for each hotel.
c) Create a script that will sort the layout appropriately and place a button on your layout called Sort.
CS200 Assignment 11 Page 7 of 11
Question 6 (5%) – Summarizing Hotel Earnings Chart
In this step we will create a graph to display the results.
a) You will now create a chart in FileMaker of the Hotel Earnings in a new layout.
b) Be sure the x-axis uses the Hotel the angle of the items is 45°.
c) Save the resulting layout as Hotel Earnings Chart.
CS200 Assignment 11
Page 8 of 11
Question 7 [10%]: Creating a Custom Dialog Box
a) Create a button on the Client Information layout. Label it Add a Booking.
b) Create a custom dialog box that will allow the user to add a booking at a specific hotel.
• The Cancel button should bring us back to the Client Information layout and delete the newly created blank record from the Bookings table.
• The OK button should bring you to the Client Information layout and to the Client that just added a booking.
c) Attach this script to the Add a Booking button.
HINT: The Show Custom Dialog script step is found in the Miscellaneous list. FileMaker online help will be
useful for this question.
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) 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.
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.
WARNING: Please be very sure of the passwords you are setting and follow this carefully. If you forget and lock yourself out, there is no way to get back in! I suggest making a copy of the file just in case.
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)
• 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)
• Create a report in a different table that is summarizing another aspect of the table. (5)
• Create a fourth table called Attracitons and have an attraction for each hotel with a corresponding
event. Incorporate this into your Bookings.(You will need to create the data). (5)
• Add a photo to the background of a layout. (2)
CS200 Assignment 11 Page 11 of 11