CS200 Assignment 10 – FileMaker Scripting Due Monday July 27 2020, 11:59 pm
Files
University Starter: you will use this file once again for your assignment. It is located in the Assignment 10 folder on Learn.
UniversityDemo2: This file shows you how your question 1 will look and how the scripts will work. SomeNumbers: This file will be used for question 2, and includes sample numbers for regular
expression searches.
Assignment Objectives
• To fully explore and understand the power of report making in FileMaker
• To effectively summarize information in a database report.
• To create scripts to automate repetitive, mundane tasks.
• To use regular expressions to create search and replace patterns for more efficient searches.
Assignment Strategy
Question 1 and 2 may be done independently or with a partner. If you do work with a partner, please add both of your names, Usernames and Student ID numbers to the footer of each layout in the files and name the file with both Usernames (username1_username2).
Question 3 must be done independently.
The bonus question must be done independently.
CS200 Assignment 10 Page 1 of 10
Your Tasks
1. [75%] In lecture we discussed how database reports are produced by sorting on a field to bring together (“group”) records having the same value for the sort field, and arranging to compute summary information about each resulting group.
In FileMaker, the data for each record is placed in the “body” of a report, while summary information about each group (and usually fields having the same value for all the records in a group) are placed in a “sub-summary” part whose “break field” is the field by which the group was defined, and on which the data has been sorted.
All this is straightforward. What’s annoying about FileMaker is that every time you wish to view the data organized by a report you must remember first to sort on the break field. FileMaker’s scripting facility allows us to automate this tedious process, and ensure that we always sort by the correct field(s).
In this assignment you will simultaneously explore both reports and scripting in FileMaker by writing short macros that automate the steps involved in preparing a report, as you would very likely want to do for reports you prepare on a regular basis.
You can find a password-protected demonstration solution (UniversityDemo2.fmp12) in the Week 10 content on Learn. By selecting the guest account button, FileMaker will allow you to use the solution without having access to script or layout definitions.
CS200 Assignment 10 Page 2 of 10
Here’s roughly what you have to do. Not every detail is specified—expect to do some thinking about how to carry out each step, and be aware that examination questions will test your understanding of what you do on assignments.
Start from the University Starter table (located in Assignments > Assignment 11 > Files For Assignments on Learn). Do not use your
previous assignment files for this
assignment.
A. Createalayoutcalled“Choose” (shown to the right) in the Marks table and on it place a button and global text fields named WhichCourse and WhichOrder.
The user should select a value for
WhichCourse from a popup filled by
values of the Course field. WhichOrder should be filled from a two-element custom value list containing “By Name” and “By IDN”.
By the time you’ve finished step H, the DoIt button will execute a FileMaker script that displays a list of the students taking the specified course, sorted by the indicated field and showing each student’s mark for the course. (See Figures 3 and 4.)
B. The reports you create subsequently will assume that you have done a Find to isolate the mark records for the course named by the global field WhichCourse. We will therefore need a script that does this. Write one, and give it the name “Do Find”.
Here’s what the script needs to do:
• With the Choose layout displayed, copy the contents of the global field WhichCourse to the Mac’s clipboard.
• Switch to a Marks layout containing the Course field.
• Enter Find mode, paste the clipboard’s contents into the Course field, and perform a
Find. (The resulting “found set” contains only mark records for the selected course.)
• Arrange that this macro appear on the Script menu, so that it can be executed either by a menu selection or by pressing the command key FileMaker assigns to it (as shown on the Script menu).
C. Reports are best viewed in Preview mode. Unfortunately, buttons don’t work in Preview mode. So to make it easy to return to the Choose layout from the display of a report, write a macro named GoToChoose that puts FileMaker in Browse mode and switches to the Choose layout.
• Make GoToChoose the topmost item in the Script menu so that it can be executed by pressing ⌘-1 (since that’s easy to remember).
CS200 Assignment 10 Page 3 of 10
• Make the script Do Find the 2nd item in the scripts menu so that it can be triggered by pressing ⌘-2.
You will find these scripts useful while working on the reports.
D. Create two reports in the Marks table that list the marks for each student in a particular course, together with that student’s grade for the course. One report (“By Name [Detailed]”) should list the students by name; the other should list the students by ID number (“By IDN [Detailed]”). (See Figures 1 and 2.)
• The body parts of these reports show a Marks record’s course, registration number, and assignment number to help you verify that you are reporting the correct data, although the first two would more appropriately be placed in a sub-summary field since they are (or at least should be) the same for all the records in a group.
• Test your reports by manually finding and sorting the Marks records for a particular course.
• Please put your name, student number and Username in the footer of all your reports/layouts, so the ISAs can easily identify your work.
E. Write a script called “By Name” that sorts the data appropriately, puts FileMaker in Preview mode and displays the current found set using the By Name [Detailed] layout.
• Write a similar script called “By IDN” that uses the By IDN [Detailed] layout.
• Test these macros by running them manually from the Script menu.
F. Write a script called DoIt that:
• “performs” your Do Find script to find the Marks records for the course selected on the Choose layout;
• uses FileMaker’s If – Else – End If script steps to “perform” either your By Name or By IDN scripts, depending on the current value of the WhichOrder field.
• Attach the DoIt script to the DoIt button on the Choose layout. Clicking on the DoIt button should now produce either Figure 1 or Figure 2, depending on the order last selected via the WhichOrder popup menu.
G. Duplicate the By Name [Detailed] and By IDN [Detailed] layouts to create layouts named “By Name” and “By IDN”. Modify both of these layouts by deleting the body part and all of the fields in that part, ultimately leaving only a sub-summary part, header and footer. Make any other changes needed to produce the layouts shown in Figures 3 and 4.
H. Alter your By Name and By IDN scripts so they display data using the By Name and By IDN layouts instead of the By Name [Detailed] and By IDN [Detailed] layouts.
At this point you should have duplicated the functionality of the Choose layout in the password- protected demonstration solution I have placed on Learn.
CS200 Assignment 10 Page 4 of 10
2.
[15%] Either with a partner or individually, compare and contrast named styles in MS Word with styles in FileMaker Pro. You should have a minimum of 5 points to compare and contrast. Note that FileMaker Pro implements named styles in Layout Mode.
Your approach to Question 2 should be to build models of how FileMaker Pro implements named styles, and then to draw the requested comparisons. You will need to construct this model both by reading the available documentation, and by experimenting with FileMaker Pro.
Include some examples for each application. Include screenshots of the applications if this will be helpful.
Place your answer to this question in a Word file called username_a10q2.doc (or username1_username2_a10q2.doc).
CS200 Assignment 10 Page 5 of 10
3. [10%] This is an application of Regular Expressions. This question must be done individually.
Design find and replacement regular expressions that insert commas into the numbers shown so as to group digits to the left of the decimal point in blocks of 3 (currency format, eg 71,243,425). Assume that all numbers will have 8 digits only. Put your answer in username_a10q3.doc.
Use SomeNumbers.txt on in the Assignment Files on Learn to test your find and replace patterns. You should use TextWrangler (freeware for Macintoshes). Make sure that “Use Grep” is selected.
71243425
75424677
54781475
87886172
39123426
30193577
80139671
10733189
29675544
32544418
37756090
11584051
15989387
80791863
25912684
67493203
99766586
76660544
42229077
20790370
Put your find and replace patterns in a nicely formatted MS Word file named
username_a10q3.doc.
CS200 Assignment 10
Page 6 of 10
Bonus
Would your answer to question 2 be different if the numbers were intermixed with text as follows, and if so, how? Justify your answer.
This text is not in a file and you are not required to type it out, however you may want to test your answer by entering a few lines into TextWrangler.
00035091:ygavet:Gavet, Yann::math:NN:ND 92013945:alaustaris:*Ustaris, Arsenyk Lord Alexis:math:math:4A:H
95011647:awowkodaw:Wowkodaw, Andrij:sy de:eng:4B:H 95014052:lpdcunha:D’Cunha, Larry Paul:sy de:eng:4B:H 95032773:djferraro:*Ferraro, David Joseph:sy de:eng:4B:H 95044104:majarvis:Jarvis, Michael Andrew:civ e:eng:4B:H 95075344:hzshahid:Shahid, Hasan Ziaulhaq::sci:4N:H 95082835:sfodell:O’Dell, Shane Fund::sci:4N:H 95084257:jmkonik:Konik, Jason:sy de:eng:4B:H 96001912:kmemberson:*Emberson, Kathleen Marie:math:math:4A:H
96007474:ajbehm:*Behm, Aaron Jeffrey:math:math:4B:H 96007733:tdshillington:Shillington, Tara Dawn:phys:sci:4B:H 96012020:mdoris:Doris, Matthew:phys:sci:4B:H
Please prepare your answer in a nicely formatted MS Word file named username_a10q2.doc. Please include your name and username at the top of the file.
To think about:
• Why do we need two layouts (By Name and By IDN)? They look almost identical— why can’t we manage with just one?
• We started by creating the layouts By Name [Detailed] and By IDN [Detailed] rather than going directly to By Name and By IDN because the former make it easier to see whether we’ve defined the relevant relationship correctly. We edit copies of them to create By Name and By IDN so that the [Detailed] layouts are still available if we later discover that we have a problem and want to verify that we’re finding the correct data.
• In this assignment you created a report in the Marks table that lists all the students taking a given class. We might instead have created a form in the Course table that used a portal to list all the students taking a given course.1 Are the two approaches to generating a list of grades equally good? Equally useful? Are there circumstances in which you would use one rather than the other?
• The Perform Script command is the equivalent of a “procedure call” in JavaScript or Visual Basic: it allows one script to request that a second script be executed. When the second script has completed, the first continues execution.
1 Presuming I had transferred the Course table from Watcom SQL to FileMaker, as I have the Students, Marks and Assignments tables.
CS200 Assignment 10 Page 7 of 10
• The demonstration solution displays a processing message in the Choose layout when you hit the DoIt button. You are not required to implement this (though it’s not hard); there’s a modest amount of extra credit available for doing so.
Submission Instructions
• Create a folder called username_Assign10
• Move each of the assignments files (username_A10Q1 or username1_username2_A10Q1,
username_A10Q2, username_A10Q3, and username_A10bonus) to this folder.
• Compress this folder and name it username_Assign10.zip and submit it to the Assignment 10
DropBox on Learn.
CS200 Assignment 10 Page 8 of 10
Figure 1: By Name Detailed layout
Figure 2: By IDN Detailed layout
CS200 Assignment 10 Page 9 of 10
Figure 3: By Name layout
Figure 4: By IDN layout
CS200 Assignment 10 Page 10 of 10