Cs 200 spring 2020 09 advanced databases if i select

CS 200 Spring 2020
09 – Advanced Databases
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?
49
Generalizing

CS 200 Spring 2020
09 – Advanced Databases
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
(By the way, FileMaker doesn’t use indices for sorting)
50
T
And while we’re here
Indexing in FileMaker

CS 200 Spring 2020
09 – Advanced Databases
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.14159265358979323846264338327950288419716939937510582097494459230
7816406286208998628034825342117067982148086513282306647093844609550
582
& note the menu items that control the appearance of
each data type
51
The Format menu...
Formats in FileMaker

CS 200 Spring 2020
09 – Advanced Databases
The Inspector
52

CS 200 Spring 2020
09 – Advanced Databases
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
•
FileMaker macros are handy for automating this (next week...)
53
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.)

CS 200 Spring 2020
09 – Advanced Databases
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?
Another study question: compare & contrast database management with Excel &
FileMaker
54
Summing Up