09_FileMaker2
CS 200
CS 200 Winter 2018 FileMaker vs SQL + Reports1
Lecture 09
FileMaker vs SQL
& Reports
FileMaker vs SQL + ReportsCS 200 Winter 2018
Abbreviations
• aka also known as
• DBMS DataBase Management System
• mutatis mutantis with the necessary changes having been made
2
Miscellaneous Notes
FileMaker vs SQL + ReportsCS 200 Winter 2018
Please read and highlight BEFORE lab
• Assignment 9
• This week’s lecture slides
Reading
• Database Design (on learn.uwaterloo.ca)
Adapted from Access Database Design and Programming by Steven Roman
Today
• SQL vs FileMaker
• Reports in FileMaker
• Read and Reason Pearl
Please ask questions!
3
Administrivia
FileMaker vs SQL + ReportsCS 200 Winter 2018
This is your second pass at FileMaker
• The first time around, you got a rough idea of what it does
• This time round we’ll
fill in some important details
evaluate its power & flexibility
Our strategy
• SQL as an application model for FileMaker
• so we’ll “compare and contrast…” the two
Recall the Albums and Songs database
Arrows go FROM a foreign key TO a corresponding
primary key (they DO NOT have to go between fields
with the same name!)
4
Albums
Album_ID
Title
Group
Medium
Category
Price
Purchase Date
Copyright
Label
Sample
Songs
Song_ID
Side
Track
Title
Mins
Secs
Album_ID
Databases–FileMaker
FileMaker vs SQL + ReportsCS 200 Winter 2018
In SQL:
• select Title, Artist, Price from Albums
In FileMaker:
• make a new layout
• choose, position, label and format fields
• enter browse mode
In each vs the other …
• what’s different?
• what’s better?
• what’s worse?
• what’s easier?
• what’s harder?
• what can’t be done?
5
Simple One-Table Queries (1)
FileMaker vs SQL + ReportsCS 200 Winter 2018
In SQL:
• select Title, Artist from Albums where Price < 10.00
In FileMaker:
• make another new layout
• choose, position, label, & format fields
• enter find mode & type the condition (“query by example”)
• then perform the find
In each vs the other ...
• what’s different? better? worse? easier? harder? can’t be done?
6
Simple One-Table Queries (2)
FileMaker vs SQL + ReportsCS 200 Winter 2018
How about...
select Title, Artist from Albums
where (Price < 14.00) and (Artist = ‘Mozart’) ?
v
7
One-Table Queries (and)
Hmm ... what’s the “Omit” check box do?
(Read and Remember...)
T
FileMaker vs SQL + ReportsCS 200 Winter 2018
How about
select Title, Artist from Albums
where (Price < 10.00) or (Artist = ‘Mozart’) ?
8
One-Table Queries (or)
In SQL I didn’t have to display Price to use it in a query; must I in FileMaker?
FileMaker calls each line of a Find a
“request”
FileMaker vs SQL + ReportsCS 200 Winter 2018
How about
select Title, Artist from Albums
where (Price < 10.00) or (Price > 15) ?
Are there any SQL queries that FileMaker can’t do?
How about (a < b) or [ (c < d) and (e < f) ]?
Roughly speaking, there’s a theorem that you can express all boolean expressions with all
or’s or with all and’s
In the above example, (a < b) or [ (c < d) and (e < f) ] = (a < b) or ((c ≥ d) or (e ≥ f))
But what about sub-selects??? Recall
select ... from ... where ( ... ( select ... from ... where ... ) ... )
9
One-Table Queries (Using a Field Twice – or)
FileMaker vs SQL + ReportsCS 200 Winter 2018
Suppose I want to use Price twice in a single “request”?
select Title, Artist from Albums
where (Price > 10.00) and (Price < 15)
order by Price ?
10
One-Table Queries (Using a Field Twice – and) T
FileMaker vs SQL + ReportsCS 200 Winter 2018
Select Albums.Title as "Album Title", Songs.Title as "Song Title"
From Albums, Songs
Where Albums.Album_ID = Songs.Album_ID
Order By Albums.Album_ID, Song_ID
11
Two Table Queries/Joins in SQL
FileMaker vs SQL + ReportsCS 200 Winter 2018
From the Songs Table
... where Songs.Album ID = Albums.Album ID
Songs is the “master table” in FileMaker-speak
This is a “many-to-one” relationship from Song to Album in FileMaker-speak
12
Two-Table Queries/Joins in FileMaker (From the Songs Table)
FileMaker vs SQL + ReportsCS 200 Winter 2018 13
The Result (Made to Look Like SQL Output)
Of course, you’d almost never actually DO this in FileMaker,
since you can make much more readable and attractive layouts.
FileMaker vs SQL + ReportsCS 200 Winter 2018
But from the Album Table’s point of view (it’s now the “master table”)
...where Albums.Album ID = Songs.Album ID
This is a “one-to-many” relationship from Albums to Songs
Could I create the preceding layout in the Albums table?
14
Two-Table Queries/Joins in FileMaker
(From the Albums Table)
FileMaker vs SQL + ReportsCS 200 Winter 2018
One to Many
15
The Result — First Formatted to Look Like SQL
But notice the use of a portal.
A layout that’s more natural for FileMaker, and easier
to read.
Album fields. Song fields.
FileMaker vs SQL + ReportsCS 200 Winter 2018
From last week’s University sample database
List of assignment due dates for student
99001122
select M.StudNum, M.Course, M.AssignNum,
Date Due
from Mark M, Assignment A
where ( M.StudNum = 99001122 )
and ( M.Course = A.Course )
and ( M.AssignNum= A.AssignNum)
How is this done in FileMaker?
grab a field in one table, drag it to a field
in another or use buttons, selected fields,
and popups
16
Mark Assignment
StudNum
Course
AssignNum
Mark
Course
AssignNum
Weighting
Description
Date Due
Multi-Key (Two-Table) Queries/Joins
Notice how the structure
of this dialog reflects the
natural syntax of a where.
FileMaker vs SQL + ReportsCS 200 Winter 2018
Are there 2-table SQL queries that can’t be done in FileMaker ?
Can these?
select * from Albums, Songs
select * from Albums, Songs where Album_ID < Songs_ID
How about using a table twice in an SQL join (eg “select ... from Song as S1, Song as S2”)?
In Filemaker, create “copies” of a table in FileMaker’s relationships dialog
when you would list the table > once in the corresponding SQL where clause.
Are there 2-table FileMaker queries that can’t be done in SQL ?
17
FileMaker vs SQL — Two-Table Joins
FileMaker vs SQL + ReportsCS 200 Winter 2018
FileMaker File or Document
a collection of one or more database tables,
including field definitions
+ layouts (aka “views”)
+ scripts
Master Table
the file that accesses & displays data from
another file
one or more records in a
“related file” that is/are identified “via a relationship”
Related Table
a file containing related data you want to access in the master file
Match Fields
a field in the master file and a field in the related file
that contain values you want to use
to find matching records
aka “link fields”
18
FileMaker Terminology (1)
FileMaker vs SQL + ReportsCS 200 Winter 2018
Master Record
A record in the master table
for which you wish to find matching records
in a related table.
Related Record
A record in the related file whose link field
(according to the relationship used)
contains a value equal to that of the link field
of the master record.
Related Field
A field in a related record.
May be placed directly on a layout of the master table,
or in the first row of a portal (also in a layout of the master table).
Portal
A layout object in the master file in which you place related fields.
Use a portal when the relation involved is one-to-many.
(You don’t need it when the relation involved is many to one.)
19
FileMaker Terminology (2)
FileMaker vs SQL + ReportsCS 200 Winter 2018
From Last Week’s Lecture
List the rooms in which students
have lectures
select S.IDN, S.Name, R.Course, C.Room
from Students S, Register R, Courses C
where (S.IDN = R.IDN)
and (R.Course = C.Name)
order by S.Name, C.Course
20
Three-Table Queries/Joins in FileMaker
Students Register Courses
IDN IDN Name
Name Course Room
Term Time
Mark Description
T
Here’s what we want for each
student…
with Students as the master table
with Courses as the master table
(+ a query for Aaron)
FileMaker vs SQL + ReportsCS 200 Winter 2018 21
Hmm … in a Students layout, how do you access Courses
Thus
you can relate (link) A to B, relate B to C,
then on a layout in A use fields from C as well as B
If you position the field in a portal, FileMaker will show all the related values
If you position the field outside a portal, FileMaker will show you the first related value
it finds
which is just what you want if the relationship is many-to-one
but “wrong” if the relationship is one-to-many
however, FileMaker doesn’t care — it just does what you tell it to…
FileMaker vs SQL + ReportsCS 200 Winter 2018
What about 4-table joins? 5-table joins? etc
Which is more powerful: SQL or FileMaker?
Which is easier to use: SQL or FileMaker?
If SQL is the definition of a (truly) “relational database” …
is FileMaker a relational database?
if it isn’t, why do so many people buy FileMaker?
22
Study Questions
FileMaker vs SQL + ReportsCS 200 Winter 2018
Reports are something SQL barely does
• a possible reason for preferring FileMaker
• (though note that you can purchase “report generators” that hook to SQL DBMSs)
A report is just a list of records
• ie field values in each of those records
• and possibly in linked records from linked tables
You lay out how you want those fields arranged
• FileMaker prints that layout repeatedly,
once for each record in the “found set”
• usually you don’t want to report on every record,
so you do a query first; what’s printed is data from records in the “found set”
But usually you’d like to
• order the records
• and/or to group them
• and print summary information about each group, eg
the number of records in each group
the average / total / etc of some field
23
Reports in FileMaker
FileMaker vs SQL + ReportsCS 200 Winter 2018
And usually some of the information is the same within a group
• it would be nice to only print it once
• (eg the Album Title is the same for all the songs on an album)
So…
We’d like a way to
“group” records and
“do something” once for each group
What’s a “group” in FileMaker?
• sort on some field’s value
• each sequence of records with the same field value is a group
What does it mean to “do something” for each group?
print once
the fields that don’t change and/or
summary (“aggregate”) information
between the records for one group
and the records for the next group
24
Groups
FileMaker vs SQL + ReportsCS 200 Winter 2018 25
At the top of every page Header
printed once
on each
page
Precedes the first leading subsummary Leading Grand Summary printed once
Precedes every change
in the sorted field’s value Leading SubSummary
Repeated for Every Record BODY
printed once
for each
group
Follows every change
the sorted field’s value Trailing SubSummary
Follows the last trailing subsummary Trailing Grand Summary printed once
At the bottom of every page Footer
printed once
on each
page
Organization of a Report Layout
FileMaker vs SQL + ReportsCS 200 Winter 2018
In FileMaker . . .
• Each sub-summary part is associated with a “break field”
whose distinct values define groups
But using the sub-summary does not cause a sort to be done on the break field
so as to create the groups!
— you must do that yourself, separately
• Browse mode only shows sub-summaries if it’s sorted by the break field
For that you must be in Preview mode (yuch)
And you must have sorted by the relevant break field
Remember “Group By” in SQL?
• it told SQL what field(s) to group records by
when using an aggregate function [eg count() and sum()]
• it’s the same idea in FileMaker
except that sorting serves as both Order By & Group By
• SQL aggregate functions = FileMaker summary fields
26
Warning
FileMaker vs SQL + ReportsCS 200 Winter 2018
Example of a Report Layout
27
FileMaker vs SQL + ReportsCS 200 Winter 2018 28
The Resulting Report
FileMaker vs SQL + ReportsCS 200 Winter 2018
Suppose you want to break a group into subgroups
and print some summary information for each subgroup?
eg separately for side 1 and side 2 of an album
It works just as you’d expect
sort first by the outer group, then by the inner
put a (another) sub-summary part between the
outer sub-summary part & the body part, and
set its break field to be the second sort key
29
Multi-Level Reports – Subgroups
FileMaker vs SQL + ReportsCS 200 Winter 2018 30
The Resulting Two-Level
Report
(by Album and Side)
At the top of every page Header
Precedes the first
leading subsummary Leading Grand Summary
Precedes every change in
the
primary sort field’s value
Leading Outer
SubSummary
Precedes every change in
the
secondary sort field’s
value
Leading Inner
SubSummary
Repeated for every record BODY
Follows every change in
the
secondary sort field’s
value
Trailing Inner
SubSummary
Follows every change in
the
primary sort field’s value
Trailing Outer
SubSummary
Follows the last
trailing subsummary Trailing Grand Summary
At the bottom of every
page Footer
FileMaker vs SQL + ReportsCS 200 Winter 2018
Use the Format menu to control the appearance of data values
If a field appears in a Header
the value for the first record on the page is used
similarly for the footer, mutatis mutandis
Use the Define Fields dialog box
to create a summary field (SQL aggregate function)
that you place in a grand summary or sub-summary part
31
Reports — Some Details
FileMaker vs SQL + ReportsCS 200 Winter 2018
CHECK( … ) in SQL
You can’t mistype something that’s entered
for you!
About “Looked-up values”
• “copy the contents of a related field
when the match field is initialized
or when I say so (Records > Relookup)”
• these were FM’s first try at a relational
database
• usually not what you want
32
Data Validation in FileMaker (1)
FileMaker vs SQL + ReportsCS 200 Winter 2018
An Important Principle:
33
Data Validation in FileMaker (2)
It’s easier to catch & correct errors
when data are entered
than it is to find and correct them later
FileMaker vs SQL + ReportsCS 200 Winter 2018
Remember SQL’s Create Index command?
Here’s how FileMaker does it
FileMaker will automatically index fields
• to implement “Unique” or “Existing” data validation
on the matched field in a related table
when the field is used to create a value list
provided you haven’t manually forced indexing off
Otherwise, by default:
• “Automatically turn indexing on if needed”
In FileMaker
• it’s unlikely you’ll ever need to manually turn indexing on
but you may want to turn it off to save file space
because FileMaker never will!
• Other databases make up their own rules
34
T
And while we’re here
Indexing in FileMaker
FileMaker vs SQL + ReportsCS 200 Winter 2018
Note especially the difference between
• how a data value appears on a form
eg 3.14
• and what’s stored in the database
eg
3.14159265358979323846264338327950288419716939937510582097494459230781640628620899862803482534211706798214808651328230664
7093844609550582
& note the menu items that control the appearance of
each data type
35
The Format menu…
Formats in FileMaker
FileMaker vs SQL + ReportsCS 200 Winter 2018
The Inspector
36
FileMaker vs SQL + ReportsCS 200 Winter 2018
What’s the difference?
37
TDefine Field Value Lists vs Format Field Value Lists
FileMaker vs SQL + ReportsCS 200 Winter 2018
And there are radio buttons and checkboxes…
which also allow users to choose from a value list
but display the choice differently
38
Data Validation (3) — Pop-up Menus / Drop-down Lists
A drop-down list…
A pop-up menu… .
T
FileMaker vs SQL + ReportsCS 200 Winter 2018
If I select a field on a layout
• FileMaker grays out inappropriate Format menu items
• selecting one of the active menu items
lets me change the appearance of the selected field
What happens if nothing is selected & I select a Format menu item? what does it
mean?
Why is this an important question?
39
Generalizing
FileMaker vs SQL + ReportsCS 200 Winter 2018
For every foreign key
• there is a record in the referenced table having that primary key
• eg for every Album_ID value appearing in a Song record there is an Album with that
Album_ID.
A — “Cascading deletes”
• if you delete an album,
the related songs are
also automatically deleted
B — auto-creation of related song records
• just by typing into related fields
on a layout
• the foreign key is automatically set, too
• be smart: auto-enter the song’s pKey
C — like B, but from Songs to Albums
D — like A, but from Songs to Albums
• would not be smart here …
• what would happen if you check it, & later delete a song?
40
Referential Integrity
FileMaker vs SQL + ReportsCS 200 Winter 2018
You can define as many layouts at you want
• these are also called “views” of the data (SQL-speak)
• give them meaningful names!!!
You don’t have to include all fields on all layouts
• defining a new field doesn’t cause it to appear on all
layouts
• by default, a new field is placed on the “current layout,”
although there’s a preference to prevent that
In FileMaker
• data entry options are associated with a field’s definition
• the appearance of a field is specified on the layout, and can vary
• other databases may choose different conventions
You can only see sub-summaries
• when in Preview Mode or Browse Mode
and if you have sorted by the relevant break field(s)
• doing both — and remembering to do both — is a pain
41
Things That Might Confuse You in FileMaker
(Incidentally, FileMaker
allows you to userid-/
password-protect a table,
layouts, etc, and to access a
database over the internet
(there’s a more expensive
version tuned for this.)
FileMaker vs SQL + ReportsCS 200 Winter 2018
SQL joins vs FileMaker joins (“relationships”)
• FileMaker is now built on top of an SQL engine
• but does FileMaker’s GUI provide a way to generate and display all possible SQL
selects?
FileMaker features typical of GUI-flavoured DBMS’s
• forms with
formatting of displayed values
data validation
input widgets (radio buttons, check boxes, pop-ups, etc)
point-and-click-to-open (next week)
query by example
• reports and
formatting of displayed values
sorting
summaries, sub-summaries, aggregate functions
Why might someone prefer SQL?
Why might someone prefer FileMaker?
42
Summing Up
CS 200 Winter 2018 Read and Reason Pearl
When something goes wrong
• take the time to read the error message(s) carefully
• list all the symptoms as precisely as you can
• think about what this info says and implies
A good general strategy is to
•make a list of possible causes
•figure out how to test whether each is the cause
43
The Read and Reason Pearl
CS 200 Winter 2018 Read and Reason Pearl
Example
What might be the problem here?
How do you test each possibility?
44
This is how you embed graphic images in a web page:
CS 200 Winter 2018 Read and Reason Pearl
Read And Reason — The Methodology
Given a failure of some kind
• read what’s on the screen
•gather relevant data
•design a test for each
to determine if it’s the actual cause
•perform the tests
Often this is an iterative process
•you narrow down the set of possible causes at each iteration
•eg
client, server, or network problem?
system vs application
application itself or 3rd party plugins
etc
45