IntroductionWelcome| 00:04 | Hi! I'm Cris Ippolite, and this is
Relational Database Design for FileMaker Pro.
| | 00:09 | In this course I'll discuss the
concepts and techniques that you can use to
| | 00:13 | design a relational Database in FileMaker.
| | 00:16 | Using real-world examples, I'll show
you how to determine which tables you need
| | 00:20 | to define in your relationship graph,
as well as how and why to resolve those
| | 00:24 | pesky many-to-many relationships.
| | 00:27 | I'll explain how to use relationships
in your scripts and calculations, and
| | 00:31 | I'll also show you how to use portals,
filter portals, and related fields in your layout.
| | 00:36 | And now it's time for us to dive
into Relational Database Design with
| | 00:40 | FileMaker Pro.
| | Collapse this transcript |
| Why relational databases?| 00:00 | Every database is a collection of data
which are organized into files called tables.
| | 00:05 | These tables provide a systematic way
of accessing, managing, and updating the
| | 00:09 | data stored within them.
| | 00:10 | A relational database is one that
contains these multiple tables, but the data
| | 00:15 | in the tables are related to
each other using special key fields.
| | 00:19 | Conversely, a flat-file database is a
database that's designed around a single table.
| | 00:25 | The flat file design puts all the
database information to one table or list with
| | 00:30 | fields that represent all
the different parameters.
| | 00:33 | Spreadsheets are a common example of flat files.
| | 00:35 | They store data in different
worksheets, and often these different worksheets
| | 00:39 | contain similar or redundant data.
| | 00:41 | A relational database, on the other
hand, incorporates multiple tables with
| | 00:45 | methods for these tables to work together.
| | 00:47 | The relationships between table data
can be collated, merged, and displayed in
| | 00:51 | database forms, or layouts in the
case of FileMaker, all representing the
| | 00:55 | stored data accurately.
| | 00:56 | FileMaker Pro is an example of a
related database system and can store the data
| | 01:00 | just as easily as a spreadsheet but
with data entry and integrity advantages
| | 01:04 | over its flat-file counterparts.
| | 01:06 | To understand the advantages of a
related database, imagine the needs of
| | 01:10 | two small companies.
| | 01:11 | One company is using a series of
spreadsheets like the ones I have on screen.
| | 01:15 | You'll see I've got one flat file
storing just customer information and another
| | 01:19 | flat file storing order information.
| | 01:22 | Each time an order is placed with this
company they have to create a new role in
| | 01:26 | their Orders table and this means
that customer's general information, for
| | 01:29 | example the first and last name in
this case, need to be stored in the same
| | 01:33 | table as the orders.
| | 01:35 | If a customer places more than one
order, for example in the case of Dennis
| | 01:40 | Estrella, we have to reenter his first
and last name for each order. And wherever
| | 01:46 | there's duplicate data, as in the case
here, many inconsistencies may arise when
| | 01:50 | users try to search within this database.
| | 01:52 | Additionally, any change, for example,
in the change of the last name of one of
| | 01:56 | these customers would require the
manager of this database to find all the
| | 02:00 | different rows inside the Orders table
and change that name manually each time.
| | 02:06 | Let's say another organization
has the same information, but instead
| | 02:09 | they're using FileMaker Pro.
| | 02:11 | I've got an example of FileMaker
database that stores all the same information--
| | 02:14 | customers and orders--and each of the
customers has one and only one record of
| | 02:19 | general information stored
inside the Customers table.
| | 02:22 | We can see those different
records as we navigate through them.
| | 02:25 | When the customer places an order,
we create a new record, but what we do is we
| | 02:31 | pull in related
information from the Customer table.
| | 02:35 | This approach to entering data solves
problems of duplicate data and also it makes
| | 02:39 | making changes to customer
information a lot easier.
| | 02:42 | So now I can just go into Customers,
and if I want to change someone's name or
| | 02:46 | their title or email, I just
change it in one spot and then it propagates
| | 02:50 | appropriately through all the related
order records where it's being used.
| | 02:54 | In addition since the relationships
between tables follow the business logic of
| | 02:58 | the organization using a relational
database, it'll make tasks like sharing,
| | 03:02 | retrieving, and reporting on
data much easier and more efficient,
| | 03:06 | while minimizing data-integrity errors.
| | 03:08 | We're going to see various different
examples of this as we go through the
| | 03:12 | title, but it's important to know the
advantages of having a database like
| | 03:15 | FileMaker Pro, because your
data is going to be related.
| | 03:18 | This is what we call a related database system.
| | Collapse this transcript |
| Using the exercise files| 00:00 | If you're a Premium member of the
lynda.com Online Training Library, or if you're
| | 00:04 | watching this tutorial on a DVD, then
you'll have access to the exercise files
| | 00:08 | used throughout this title.
| | 00:09 | For example, if you want to just jump
to a specific movie, you can find the
| | 00:14 | specific exercise files that correspond with it.
| | 00:16 | The way that the exercise files are laid
out is that they all come in one folder,
| | 00:20 | as you see here, and each folder
is broken into different chapters.
| | 00:24 | For example, if you're watching the
movie in Chapter 5 movie number two, you
| | 00:28 | can just click on the subfolder and then
double-click on the exercise file to launch it.
| | 00:33 | In addition, I've added three bonus
files to this title under Chapter 6.
| | 00:37 | These are fully developed
sample files that correspond with the
| | 00:40 | real-world example movies.
| | 00:42 | If you're a Monthly subscriber or an
Annual subscriber to lynda.com, then you
| | 00:46 | don't have access to the exercise files,
but you can easily follow along from
| | 00:49 | scratch with your own files, so
let's go ahead and get started.
| | Collapse this transcript |
|
|
1. Reviewing Data ModelingIntroduction to data modeling| 00:00 | In the relational model, data is
broken down into small chunks which are
| | 00:03 | connected to each other by relationships.
| | 00:06 | By breaking data into smaller pieces
accuracy is ensured by making data entry
| | 00:11 | a one-time process and then reusing
the data by connecting it with other pieces.
| | 00:16 | Using this process, a large collection
of unorganized data is simplified into
| | 00:21 | its smallest units and then related
with many other smaller units to re-create
| | 00:25 | the big picture in a more organized format.
| | 00:28 | The relational model provides a
simple and flexible way to describe data.
| | 00:32 | Data modeling is the
initial purpose of any database.
| | 00:36 | The data has to be modeled or described
so that it can be input and output in a
| | 00:40 | method that can be duplicated and
applied to the business rules of the
| | 00:43 | organization using the system. And you
actually see data organized like this in
| | 00:48 | the real world all around you.
| | 00:49 | For example, if you go into to any office,
you'll see several file cabinets that
| | 00:54 | store different files about different things.
| | 00:56 | One file cabinet, for example, may store
information about customers and another
| | 01:00 | file cabinet may store information
just about products, with all that
| | 01:03 | information organized into their own
cabinets, and then within those cabinets
| | 01:07 | are drawers where the data may be
defined in even smaller categories so that
| | 01:11 | it's more easily accessed.
| | 01:12 | So just like in this real-world
example, the small pieces of data in a
| | 01:17 | relational model are known as entities.
| | 01:19 | Once all the information is broken into
entities, which will become tables inside
| | 01:23 | of our database, putting it back
together into its original form is done by
| | 01:27 | creating a relationship
between the different tables.
| | 01:31 | Entities also contain something that
are called attributes, which are pieces of
| | 01:35 | data that describe an entity.
| | 01:37 | Attributes eventually will become
fields inside your FileMaker database.
| | 01:42 | The process of observing collections
of data and then breaking them into
| | 01:45 | smaller groups and then reassembling them in
a logical way is called relational modeling.
| | 01:49 | The exercise of relational modeling is
the first step in constructing the database.
| | 01:53 | And one important note here is that when
you sit down at a computer and you open
| | 01:57 | up FileMaker Pro to start a new
database what you're actually doing there is
| | 02:01 | constructing something. Much like any
other construction project that you would
| | 02:05 | embark on, like let's say a
home construction project,
| | 02:08 | your database will proceed more smoothly
if you first begin with some type of plan.
| | 02:13 | Relational modeling is like
constructing a blueprint for your
| | 02:15 | FileMaker database.
| | 02:17 | So the first thing that you really want
to do, even before you open up FileMaker,
| | 02:20 | is come up with a plan, the type of
data that you're going to be managing your
| | 02:23 | system, and therefore what tables
you're going to need, and then after you've
| | 02:27 | determined those things, you'll
create the database in FileMaker Pro.
| | 02:29 | And a lot of people think that they
don't have a very complex database solution,
| | 02:34 | so they can just skip the planning
stage, but it's just as important to plan
| | 02:38 | ahead of time in a simple database as
it would be with something very complex.
| | Collapse this transcript |
| Reviewing relationships types| 00:00 | When we talk about relationships
in database terms, we're talking
| | 00:03 | specifically about how the different
tables are related to each other and how
| | 00:07 | the relationships between those tables
really define the data that's stored within them.
| | 00:11 | In order to decide in the data model for
your database system, it's important to
| | 00:14 | first determine what the possible kinds
of connections are that you could have
| | 00:17 | between your tables, and then from
those, we'll choose the types of connections
| | 00:21 | that we actually have within the
database that we're working on.
| | 00:24 | So in this movie we're going to
quickly review the different types of
| | 00:26 | relationships, and I'll
give explanations of each.
| | 00:29 | To keep things simple, we're going to
focus on three types of relationships that
| | 00:33 | account for all the situations you're
going to encounter within your database
| | 00:35 | and your data-modeling exercises.
| | 00:37 | They're called the one-to-one
relationship, the one-to-many, or also known as
| | 00:42 | the man-to-one relationship, and
finally the many-to-many relationship.
| | 00:47 | The first one is called a
one-to-one relationship.
| | 00:50 | It's very rare, and what it means is
that you've actually created a table for
| | 00:54 | something that should actually just be a field.
| | 00:56 | So, for example, in this case we've got
one customer can have only one address
| | 01:01 | and one address can only
be related to one customer.
| | 01:04 | So the relationship that we have
between these two items is just a one to one,
| | 01:07 | and in this case, as in the other
case you've identified a one-to-one
| | 01:12 | relationship, that means that address is
really an attribute of the customer and
| | 01:17 | therefore should just be a field
defined inside the customer table, not its own
| | 01:21 | table related to the customer table.
| | 01:23 | So one-to-one relationship simply
means an attribute has been improperly
| | 01:27 | identified as an entity.
| | 01:28 | So if you go through the process and
find out you've got a one-to-one,
| | 01:31 | you should really just take a look at it,
because what you actually might have is
| | 01:34 | just a field. But the main
relationship that you'll want to be familiar with are
| | 01:37 | the ones that are called
one-to-many relationships.
| | 01:40 | These indicate that you have a parent table
that's related to records in a child table.
| | 01:46 | The parent/child vernacular is common to
data modeling and I'm going to be using
| | 01:49 | a quite a bit in this title.
| | 01:51 | So what I'm talking about there is
that on the one side of the one to many we
| | 01:54 | call that the parent table and on the
many side of them one to many we call
| | 01:58 | that the child table.
| | 02:00 | So one parent can have many related children.
| | 02:02 | You may also encounter relationships
that are called many-to-many relationships,
| | 02:06 | and they should be eliminated.
| | 02:08 | Many to many means that you've got
two related parent tables, but their
| | 02:11 | relationship is not actually direct,
and instead it needs to be related through
| | 02:15 | an additional child table, so in fact
you'll have a one-to-many relationship.
| | 02:19 | We'll discuss this in detail in the
later chapter on many-to-many relationships.
| | 02:24 | The way that you'll know that you're
done with the data-modeling portion of your
| | 02:26 | database planning is because you
will have evaluated all of the possible
| | 02:30 | relationships between your tables and
then determined that all you've got left
| | 02:34 | are one-to-many relationships.
| | 02:36 | So that's the first notable learning
point here is that you must resolve all of
| | 02:40 | your relationships into
one-to-many relationships, thus removing all
| | 02:43 | one to ones and all many to manys and
leaving yourself with just a series of
| | 02:48 | connected one-to-many relationships.
| | 02:51 | Later in this title we're going to look
at some examples of relationships that
| | 02:53 | we might find in our modeling process
so we can identify their specific types.
| | 02:57 | I'm going to use the terminology I have
established here a lot in those exercises,
| | 03:01 | so it's important to define them first.
| | 03:03 | Whenever I talk about a child table or a
child record, I'm talking about a table
| | 03:08 | or records stored in a table on the
many side of a one-to-many relationship.
| | 03:12 | Whenever I talk about a parent table or
parent record, I'm talking about a table
| | 03:17 | or a record on the one side of
the one-to-many relationship.
| | 03:21 | Once you understand these relationship
types and can identify them then you can
| | 03:25 | move on to diagramming the
relationships during your database-planning stages.
| | Collapse this transcript |
| Diagramming relationships| 00:01 | So far, we've learned
about basic relationship types.
| | 00:04 | Now let's expand on these
techniques and discuss the general method for
| | 00:07 | diagramming relationships for database systems.
| | 00:10 | The method we will learn is called
creating an entity relationship diagram.
| | 00:15 | These diagrams are often referred to as ERDs.
| | 00:18 | It's a simple process that's useful for
all types of relational database systems.
| | 00:22 | Data modeling breaks into three phases.
| | 00:25 | First, you will look at your
information in order to develop a list of entities
| | 00:29 | which will become tables inside your database.
| | 00:31 | Next, determine and diagram the
relationships between those entities.
| | 00:37 | Then finally, you will audit the
diagram following a couple of rules I will
| | 00:40 | layout later in this movie.
| | 00:42 | Let's try one together.
| | 00:44 | Here's a process description
describing requirements of a database system
| | 00:47 | that we need to build.
| | 00:49 | In this description, we see the data
we need to be storing in our database.
| | 00:54 | In order to find the entities,
it's important for us to identify discrete and
| | 00:58 | unique buckets of information.
| | 01:01 | A popular technique for identifying
the potential entities is to look for the
| | 01:04 | nouns within the text of our description.
| | 01:06 | So go ahead and pause this video and
take a moment to read this through.
| | 01:11 | Using the find-the-nouns technique,
we can identify potential entities here as
| | 01:17 | orders, companies, and contacts.
| | 01:21 | These each work as entities because
they're different, discrete types of data,
| | 01:25 | and each can be described
differently from the others.
| | 01:27 | So now we have our entity candidate list.
| | 01:31 | However, just identifying the
entities is only part of the process.
| | 01:35 | Before these entities can become tables
in our database, we will need to first
| | 01:39 | determine how they are related to each other.
| | 01:41 | And to help us visualize these potential
relationships, we will want to create an ERD.
| | 01:46 | But before we jump into this, I want to show
you how entities are represented on an ERD.
| | 01:52 | In your diagrams it's common to show the
entities as a box with the entity name inside of it.
| | 01:58 | These boxes will eventually
represent the tables in your database.
| | 02:02 | To visually represent these
relationships between entities on the diagram, it's
| | 02:07 | common to draw a line between the two
boxes with a double hash mark on the side
| | 02:11 | that represents the many, or the child, table.
| | 02:15 | Now we are ready to start exploring
the relationships between entities in
| | 02:18 | our example system.
| | 02:19 | When you are sketching out potential
tables it helps to start asking yourself
| | 02:23 | questions about how they
might relate to each other.
| | 02:25 | For example, you may want to
pick two entities and evaluate the
| | 02:29 | relationship between them first.
| | 02:31 | Let's start with companies and contacts.
| | 02:35 | And the questions we would ask
will follow a one-to-many format.
| | 02:38 | For example, we might ask, can each
company potentially have many contacts? Yes.
| | 02:45 | Let's say that each company should
have many individuals working there.
| | 02:49 | Can each contact be part
of more than one company?
| | 02:53 | No, let's assume that a person can
only work for one company at a time.
| | 02:56 | So data modelers would call the
relationship between companies and contacts a
| | 03:00 | one-to-many relationship and will
visually represent the relationship between
| | 03:04 | these two entities with a
line between the two boxes.
| | 03:08 | The double hash is going to be on the
side of contacts because it's the many, or
| | 03:12 | Child, table in this relationship.
| | 03:15 | Next, let's evaluate the rest of
the relationships by adding the orders
| | 03:19 | entity to the discussion.
| | 03:20 | We will start by evaluating whether there
is relationship between contacts and orders.
| | 03:27 | In this situation, we would ask
these same one-to-many questions.
| | 03:31 | Can each contact potentially
have many orders in the system?
| | 03:35 | We will say yes, that could be the goal,
| | 03:37 | as many orders as possible really.
| | 03:39 | But can an order be
associated with more than one contact?
| | 03:43 | No, we will say that each order
has to be from one contact only.
| | 03:48 | This tells us that we have another
one-to-many relationship between these two
| | 03:51 | entities with orders as the many.
| | 03:54 | We will want to represent this on our graph.
| | 03:58 | Next, we would consider evaluating the
relationship between companies and orders.
| | 04:02 | They are actually already related.
Although relationships are not drawn between
| | 04:07 | the companies and the orders tables directly,
| | 04:10 | these relationships in between
entities are still creating a chain of
| | 04:14 | associated relationships;
| | 04:16 | therefore the table on the top of the chain
is still related to the table on the bottom.
| | 04:20 | For example, a company could have a
related contact who then places an order;
| | 04:26 | therefore that order is related to
the company associated with that contact
| | 04:31 | through this chain of relationships.
| | 04:34 | After you go through these steps
determining the relationships between each
| | 04:38 | entity then your ERD is done.
| | 04:40 | If you have a small project, kind of
like the one that we have used in
| | 04:43 | this example, it might not seem to you that
you need to sketch things out ahead of time.
| | 04:47 | But from personal experience, I can
assure you that taking the time to diagram
| | 04:52 | your relationships during the planning
stage will require a lot less work than
| | 04:56 | doing so after you've
already begun your programming.
| | Collapse this transcript |
|
|
2. Resolving Many-to-Many RelationshipUnderstanding problems caused by many-to-many relationships| 00:00 | Earlier our discussion on identifying
relationships, I mentioned that you could
| | 00:03 | potentially identify a many-to-many
relationship during your data modeling and
| | 00:08 | that when you do you should resolve
them by adding a join table, which we will
| | 00:11 | discuss in our next movie.
| | 00:13 | However, I'm frequently asked why
many-to-many relationships need to be resolved at all.
| | 00:17 | After all, FileMaker lets you create them,
and they appear to work, at least up to a point.
| | 00:21 | So I am going to show you some
examples of where a many-to-many-related
| | 00:24 | structure would break down
inside of a real FileMaker database.
| | 00:27 | In the file that I have got here,
if you open it up, you'll see that we have two
| | 00:33 | tables, an Actors table and a Movie table.
| | 00:38 | In the Actor table one record
represents any individual actor and we've got
| | 00:42 | unique IDs set up as a primary key field.
| | 00:46 | In the Movie database one
record represents one movie.
| | 00:49 | Same thing here, primary key
field to uniquely identify each movie.
| | 00:53 | So what type of relationship might
we have between these two tables?
| | 00:57 | So consider the statement
"one actor may act in many movies."
| | 01:01 | So between the two tables one actor
could appear in many movies, which on the
| | 01:06 | surface appears that it's
a one-to-many relationship.
| | 01:09 | If this were in fact a one-to-many
relationship, we could simply add a foreign
| | 01:13 | key field, or Actor ID, to the Movie
table and create a direct relationship
| | 01:17 | between the two of them.
| | 01:18 | For example, if we look at this movie
record here, I've got an Actor ID field
| | 01:23 | already on the layout, and let's say we
determine that actor with the ID of 1, Boo
| | 01:28 | Radley, appears in the movie Yet Another Sequel.
| | 01:31 | Simple enough, I can just put their ID in
there and now these two records are related.
| | 01:36 | However, it's also true that one movie
almost invariably involves many actors,
| | 01:41 | so one movie could have
many related actor records.
| | 01:44 | So what you really have here are two
one-to-many relationships crossing over
| | 01:48 | between two tables and not a
true one to many in either case.
| | 01:52 | And it's not so obvious how to solve
that problem using the structure that I
| | 01:55 | proposed a moment ago. So, for
example, this actor with the primary key of
| | 02:00 | number 2 also appeared in yet another sequel.
| | 02:03 | You'd think that you could simply put
another number in this field or a comma
| | 02:08 | and another number, but in this scenario
the Actor ID field in the Movie entity--
| | 02:13 | otherwise known as the foreign key in
the proposed relationship--would have to
| | 02:17 | have more than one value at once.
| | 02:19 | The technical term for this is a
multi-valued attribute, and most database
| | 02:23 | designers will argue strongly
against the use of multi-valued attributes,
| | 02:27 | especially inside of a key
field in a relationship.
| | 02:29 | So these multi-valued
attributes are a bad choice for creating
| | 02:33 | many-to-many relationships. Why is that?
| | 02:35 | Well, for example, it could be very
difficult to create a FileMaker Pro
| | 02:38 | subsummary report listing actors in
their individual roles or even an account of
| | 02:42 | all the roles that they had ever played
| | 02:44 | if all you're doing is simply just
adding comma-delimited numbers to a field
| | 02:47 | inside of an Actor record.
| | 02:49 | For example, what table would
you use for a report like that?
| | 02:52 | Our report would need to indicate one
record per role that an actor has played
| | 02:55 | in a movie, and we don't even
have a table to support that.
| | 02:59 | How could I create counts of
actors inside of a movie, for example,
| | 03:02 | manually count all the different
IDs inside of a field, or even do
| | 03:05 | something programmatically?
| | 03:07 | This wouldn't necessarily be
accurate as the number of actors in a
| | 03:10 | movie continue to grow.
| | 03:11 | You would need something more dynamic.
| | 03:12 | Another issue, what about the
fact that you might want to store a
| | 03:16 | character name that the actor played
in each role and maybe even the salary
| | 03:20 | they received for doing so?
| | 03:21 | These would actually be
attributes, but attributes of what?
| | 03:24 | Is a character name an
attribute of a movie? No.
| | 03:28 | Is a character that an actor played
in a movie an attribute of an actor?
| | 03:32 | Maybe if they have only appeared in one film,
but what if they have appeared in multiple films?
| | 03:36 | Many developers who aren't comfortable
with how to properly resolve this issue,
| | 03:39 | they then try to create multiple
instances of the Actor ID in the movie record.
| | 03:45 | Here is an example of that.
| | 03:47 | This seems to easily accommodate the
Actor with primary ID 1, Actor2, and we
| | 03:53 | still have some room.
| | 03:54 | And sure, in this case you could
store various Actor IDs separately.
| | 03:58 | However, this is still wrong.
| | 03:59 | Now if you wanted to search for all
the movies that Jaxson Caymus appeared in
| | 04:03 | as an actor, you would have to do four
different searches, one in each one of
| | 04:06 | these fields, just to make sure that
you're not missing one of the IDs because
| | 04:10 | you don't know whether or not you've put his
ID of 2 in Actor1, Actor2, Actor3, Actor4.
| | 04:15 | You are not even sure
which field to search upon.
| | 04:18 | That alone should tell
you that something is wrong.
| | 04:20 | Also, what if one actor appeared
in 50 films, or a movie had a hundred
| | 04:24 | different actors in it?
| | 04:25 | Are you going to create fifty or a
hundred different films in your table
| | 04:28 | just for that one actor in their own
special layouts inside of your database?
| | 04:32 | Plus then you would have to create
fifty character name fields, as well as
| | 04:35 | fifty salary fields.
| | 04:37 | You should start to get the idea here
that something is definitely wrong if you
| | 04:40 | have gone down this path.
| | 04:41 | In many database systems, FileMaker Pro
included, you're much better off using a
| | 04:46 | different strategy to resolve
these types of relationships,
| | 04:49 | and this strategy I am
proposing is actually a very easy one.
| | 04:51 | You simply create a third table that we
call a join table, and we will discuss
| | 04:55 | this approach in the next movie.
| | 04:56 | By attempting to join two tables
directly, you present issues where we
| | 05:00 | can't find data properly,
| | 05:02 | we can't report on it
properly, or even view it efficiently.
| | 05:05 | The modeling phase is a great place
to find out that you have these issues
| | 05:08 | because you are just doing it on a
piece of paper with pencil marks at this
| | 05:11 | point, rather than having to go through
several hours of development inside your
| | 05:14 | database before you discover that you
have a many-to-many relationship and all
| | 05:17 | of its corresponding issues.
| | 05:19 | Eventually, you will.
| | 05:20 | Instead, you would want to create a
joint table to fix all these issues quickly
| | 05:24 | and permanently within your solution.
| | Collapse this transcript |
| Adding a join table| 00:00 | We have discussed the potential
problems with leaving a many-to-many
| | 00:03 | relationship in your database solution.
And again, FileMaker allows many-to-many
| | 00:07 | relationships, so why
would we want to avoid them?
| | 00:09 | To review, the issues that we
mentioned in the actor movies many-to-many
| | 00:13 | relationship were, that we wanted to
create a report that lists all the actors,
| | 00:17 | their roles, salary, and total salary
for all the roles they have ever played,
| | 00:21 | but we didn't have a context or a
table to be able to base that off of.
| | 00:25 | And also we have ran into issues with
storing character names and salaries for
| | 00:28 | each role, because again,
no table exists for a role.
| | 00:32 | And then also how do we manage actors
working in different numbers of movies?
| | 00:35 | One actor might be working on 10 movies,
| | 00:38 | another working on five, and then
those lists can constantly grow.
| | 00:41 | So if we define a field for each one
of those roles for the actors, we would
| | 00:44 | never know how many
different fields we would need.
| | 00:47 | We also discussed the searching issues
that we would run into in our database.
| | 00:50 | How would we find an actor that
played a certain role via search?
| | 00:53 | In order to correct these issues,
let's start first by looking at the tables.
| | 00:57 | Our problem was identified when we
determined that one actor can act in many
| | 01:01 | movies but also that one
movie can have many actors.
| | 01:05 | Our rule is that all relationships need
to be one-to-many relationships, not the
| | 01:08 | many-to-many relationship that we see here.
| | 01:11 | And really a many-to-many relationship
is just two one-to-many relationships
| | 01:16 | combined together into one.
| | 01:17 | So to get just two one-to-many
relationships then we will want to break the
| | 01:21 | many-to-many
relationship into two separate ones.
| | 01:24 | First, let's get rid of the
one relationship that we do have.
| | 01:27 | Now to be able to create two more
one-to-many relationships, we are going to
| | 01:31 | need add another table to the
mix, and this table we'll call roles.
| | 01:34 | We could just call it actor movies,
but in this case it can act as its own
| | 01:38 | entity because it will have its own
attributes, and we'll get to that in just a minute.
| | 01:42 | After we add the new table, we can now
create two one-to-many relationships to it.
| | 01:47 | One actor can have many roles, so we
make the actor the one and the roles the
| | 01:51 | many, and one movie can have many roles,
| | 01:54 | so we make movie the one and roles the many.
| | 01:57 | Now we can have our original two
one-to-many relationships and no more
| | 02:00 | many-to-many relationship.
| | 02:02 | So now that we have these two new
relationships in place, let's look at how this
| | 02:05 | opens up the door to solve our
problems that we identified earlier.
| | 02:08 | I am inside the exercise file 02_02,
and you will see that I've already added
| | 02:12 | the new roles table.
| | 02:13 | If you look at File > Manage > Database,
you will see that I have created the
| | 02:18 | new table Roles where one record
equals one actor in a movie, and I have
| | 02:23 | created the appropriate key fields and
even connected these relationships in
| | 02:27 | the Relationship graph.
| | 02:28 | This file is simply a preview of
lessons that you will learn in more detail
| | 02:31 | later in the title, like lessons on
keys, portals, and related fields,
| | 02:35 | but we are using it here to
illustrate the benefits of a join table,
| | 02:38 | so feel free to come back to this file
after you watched the entire title to see
| | 02:41 | how it's all put together.
| | 02:42 | For now, just use it to follow along.
| | 02:45 | So the first problem that we wanted to
address was the problem that we have no
| | 02:48 | table that had one record per actor in a movie.
| | 02:51 | Well, creating the Roles
table solved that for us.
| | 02:54 | A record in the Roles table is an
intersection between an actor and a movie, and
| | 02:58 | they contain both an ActorID and the
MovieID, but in addition they also can have
| | 03:02 | their own attributes,
| | 03:03 | for example, the character that that
actor played in a movie and perhaps the
| | 03:07 | salary that they received
for portraying that character.
| | 03:10 | You'll see that displayed
here in a layout called Roles.
| | 03:13 | You will also notice that this
layout solves our second problem, storing
| | 03:17 | character names and salaries for each role.
| | 03:20 | Previous to this, no table existed for
storing that information and now one does.
| | 03:24 | So now each time an actor appears in a
movie we simply create a role record, and
| | 03:29 | then we can add character name and
salary to that new role record that we
| | 03:33 | created, and we can do this
in unlimited number of times.
| | 03:36 | So how ever many times an actor
appears in a movie, we just create that
| | 03:39 | number of role records.
| | 03:41 | So now that we do have a table with
roles, each with their own character name
| | 03:44 | and salary, we can finally create this report
that we intended to create in the first place.
| | 03:48 | I have already created the
report in this sample database
| | 03:50 | if you want to take a look go under
Actor Subsummary. This report has an actor
| | 03:55 | and then dynamically lists the number
of roles, no matter how many roles they
| | 03:59 | have, and then even
totals up the salary for each.
| | 04:02 | This report would simply be impossible
if we do not resolve our many-to-many
| | 04:05 | relationship by adding a third table.
| | 04:08 | This report also shows a solution to
the last problem that we have with our
| | 04:11 | many-to-many, how do we manage actors
working on different numbers of movies?
| | 04:15 | Well, we notice that the first actor, Boo
Radley, has 10 roles, so does Jaxson Caymus.
| | 04:21 | But the actor Francis Schrodinger only
has six, yet this report doesn't have to
| | 04:24 | show four blank fields for Francis.
| | 04:27 | If we were to go with the strategy of
simply defining a field in an Actor table
| | 04:31 | every time an actor appeared in a movie,
we would end up with empty blank spaces
| | 04:35 | here in this report.
| | 04:36 | If you navigate over to a layout called
Actors, you will see another example of this.
| | 04:42 | Here's the Boo Radley actor, and we are
only taking up so much screen space to
| | 04:46 | fill their 10 roles. Same thing with
Jaxson Caymus and Francis Schrodinger.
| | 04:51 | On this layout we used something
called a portal, which allows us to see any
| | 04:55 | number of related role records per
actor, with a scrollbar here that you see on
| | 04:59 | the right-hand side whenever the
number of related records exceeds the space
| | 05:03 | that's allotted on the screen.
| | 05:05 | If we move under the old layout that we
are working with, Multiple Actor Fields,
| | 05:09 | we will see that using the old
many-to-many strategy of defining a field every
| | 05:14 | time a movie had an actor in it would
not only require us to enter data twice
| | 05:19 | for each time an actor is in a movie,
but we would also have to go and define a
| | 05:22 | field every time that they
added a new movie to their resume.
| | 05:25 | And that simply doesn't make sense,
especially if they end up having 50, 60, or
| | 05:29 | 100 different movies that they are a part of.
| | 05:31 | And then finally, our last issue:
| | 05:33 | how do we find an actor that played a
certain role via a search in FileMaker?
| | 05:37 | Well, if you go into the Actors
layout, we can enter Find mode and we can
| | 05:43 | use these related layout objects,
like a related field inside of a portal, to
| | 05:47 | simply do a search.
| | 05:50 | For example, if I wanted to find all
the actors who played the character of
| | 05:53 | Romeo, I could simply type that into
the related Character field and perform a
| | 05:58 | find and it comes back with just one
record in the found set and tells me that
| | 06:02 | the actor Boo Radley played that character.
| | 06:04 | This again, would be impossible in
the many-to-many relationship scenario.
| | 06:09 | Additionally, I can also do a search for
which actors appeared in a certain movie.
| | 06:15 | I type in the movie name, and then
it finds all three actors happened to
| | 06:21 | appear in that film.
| | 06:22 | Just a couple of different ways
that you can use a proper relationship
| | 06:25 | structure to isolate
different found sets of data.
| | 06:28 | Rather than just remembering the rule
of having to eliminate a many-to-many
| | 06:32 | relationship and add a third join table,
think instead of the benefits you will
| | 06:36 | receive by having this table in place,
and at the same time you won't have to
| | 06:39 | encounter the plethora of issues
associated with many-to-many relationships.
| | Collapse this transcript |
|
|
3. Introducing Key FieldsUsing primary and foreign key fields| 00:00 | Once you've completed your
data-modeling exercise, you'll have determined what
| | 00:03 | tables you're going to need in your
FileMaker database, as well as what
| | 00:06 | relationships are going to be
needed between those tables.
| | 00:09 | What you will need in order to create
relationships is something that could link
| | 00:12 | each related table together so that
each table has to have a little hook in
| | 00:16 | there that allows that connection.
| | 00:19 | In relational databases you are going
to connect two related tables together
| | 00:22 | using special fields defined in each
table, and these fields are called keys.
| | 00:27 | A key field is just like any other
field that you're defining in your database
| | 00:30 | but unlike these other fields,
they perform special functions.
| | 00:33 | Key fields come in two different
types and each type has its own function.
| | 00:37 | There is the primary key and the
foreign key and these are two more pieces of
| | 00:41 | vocabulary that you are going to hear a
lot in this title, so it's a good thing
| | 00:44 | to understand what they are now.
| | 00:46 | Let's look first at primary keys.
| | 00:48 | A primary key is a field that gets
defined in the table acting as the one in the
| | 00:52 | one-to-many relationship,
otherwise referred to as the parent table.
| | 00:57 | Remember that there's always a parent table
in a relationship and always a child table.
| | 01:01 | Because our relationships are always
going to one-to-many, the parent is the one
| | 01:06 | and the child is the many.
| | 01:07 | So the rule to follow when determining
which tables need a primary key field is
| | 01:11 | that we need to define the
primary key field in every parent table.
| | 01:15 | Primary key field's main purpose is to
ensure the uniqueness of each record in
| | 01:19 | this table, so they must contain a
unique serialized value inside every record.
| | 01:23 | These records must never be duplicated
within a single table and each parent
| | 01:28 | must have its own Primary Key field defined.
| | 01:30 | Since any one relationship will be
between two tables and we already determined
| | 01:34 | that there needs to be a primary key
field defined in each parent, then there is
| | 01:38 | also needs to be a field defined in
each child table so that these two related
| | 01:41 | tables can be linked.
| | 01:43 | The type of field defined in the
child table is called a foreign key field.
| | 01:47 | This is going to be used to link a child
record or a record in a child table back
| | 01:51 | to its parent record in the parent table.
| | 01:53 | This is how we actually link the
tables together or know how one record is
| | 01:57 | related to any other records.
| | 01:59 | The rule to remember for foreign keys
is that each child table is going to have
| | 02:02 | a foreign key field defined within it.
| | 02:05 | However, these values do not need to be
unique because anyone parent could have
| | 02:09 | multiple related records in a child table.
| | 02:12 | Understanding the concept of keys and
the roles of primary and foreign keys
| | 02:15 | specifically are critical to
understanding how to link your related
| | 02:19 | databases together.
| | 02:20 | After you understand the two types of
keys, you can then move on to identifying
| | 02:24 | which of your tables need
a primary or a foreign key.
| | Collapse this transcript |
| Determining which tables need keys| 00:00 | We've discussed the concept of keys,
both primary and foreign, and how they're
| | 00:04 | critical to building
relationships in your database.
| | 00:07 | The next step is understanding which of
your tables will need these key fields defined.
| | 00:12 | Let's take a look at the ERD that we've
been talking about so far in this title
| | 00:15 | and determine what types of keys we'll need.
| | 00:18 | In review, we have five tables,
each with the relationships already defined.
| | 00:24 | The primary key decisions are easy:
every table acting as a parent needs a primary key.
| | 00:30 | Let's look at which tables are
acting as parents. So here we can see that
| | 00:34 | companies are the parent to contacts,
contacts are the parent to orders, orders
| | 00:40 | are the parents to order products, and
products are also acting as the parents to
| | 00:44 | order products, and therefore
all these need primary key fields.
| | 00:49 | Now how do we determine which
tables will require a foreign key field?
| | 00:53 | Well, an easy rule to follow here is
that every child table needs a foreign key,
| | 00:59 | and if you notice, you can identify
the child tables easily because they all
| | 01:02 | have a double hash touching them.
| | 01:05 | With this in mind, remember that each
table that has a double hash touching it
| | 01:10 | will require a foreign key field.
| | 01:12 | Let's take a closer look at our example
ERD to see which tables have double hash
| | 01:16 | marks touching them.
| | 01:18 | We can see that three
tables have double hash marks.
| | 01:23 | Contacts is the child to Companies,
Orders are the child to Contacts, Order
| | 01:31 | products are the child to both Products
and Orders. But if we look closer at the
| | 01:37 | Order/Products entity, we see that
it has two sets of double hash marks,
| | 01:41 | thus, it's acting as a child
in two different relationships.
| | 01:45 | Therefore it'll require two foreign
key fields defined, one for products and
| | 01:50 | one for orders. Looking at our diagram
we see that some tables play different
| | 01:56 | roles in different relationships.
Some are parent tables in one relationship
| | 02:01 | and child tables in others.
| | 02:04 | The way that we keep all of the
straight in our database is by defining the
| | 02:08 | proper primary and foreign key fields.
| | 02:11 | Determining which tables perform
which roles will help you figure out which
| | 02:14 | tables need primary keys and
which are going to need foreign keys.
| | 02:18 | And if you take the time to set
these up before you start working in
| | 02:21 | FileMaker Pro, it's going to save you
tons of time creating relationships in
| | 02:25 | your database.
| | Collapse this transcript |
|
|
4. Using the FileMaker Relationship GraphDefining tables| 00:00 | In the data-modeling exercise, we determined
that we needed four tables, one for each entity.
| | 00:05 | In this case it was the Customer table,
an Orders table, and a Products table.
| | 00:10 | We also determined that we had a many
to many relationship which required us to
| | 00:14 | add a new join table that we're
going to call the Order Line Item.
| | 00:18 | So in this movie we're going to talk
about how to add these four tables to your
| | 00:21 | FileMaker database, and this is also the
first time in the data-modeling process
| | 00:25 | that you're actually opening up
the FileMaker Pro application.
| | 00:29 | So after opening up FileMaker Pro,
you'll go to the File menu and select New Database.
| | 00:34 | You'll see a dialog appear on screen,
and FileMaker is asking you what you want
| | 00:39 | to name your new file and
where you want to save it.
| | 00:42 | So we'll choose the Desktop and
we'll leave the name Untitled.
| | 00:45 | And once we've made those decisions,
we can hit Save, and now you'll notice that
| | 00:49 | it's created a FileMaker Pro database
for us and taken us to the quick table
| | 00:53 | view for our untitled layout, but
we're not done with our table setup.
| | 00:57 | So from here, let's go back into the
File menu and we're going to pull up the
| | 01:02 | File > Manage > Database window again,
and we're going to concentrate over here
| | 01:06 | on the tab for tables, since we can't
create things like fields and relationships
| | 01:11 | until we've already got our tables in place.
| | 01:14 | And right now you'll see that we've got
one table in our list, but it's called
| | 01:17 | Untitled. So it's a good practice to
name the tables in your database after the
| | 01:22 | entities that they represent, so since
we don't have any entities for untitled,
| | 01:26 | why don't we rename this one Customer?
| | 01:29 | So if we select the existing table names,
you see we've highlighted, go down to
| | 01:35 | the box next to table name and type in
the word Customers. And instead of hitting
| | 01:41 | the Create button, we're going to hit
the Change button, and you'll see that
| | 01:44 | that table has been changed to Customers.
| | 01:46 | So now we have a table in our database
that represents the Customers entity.
| | 01:49 | And we have got three more entities
that we need to represent as tables, so
| | 01:54 | let's add those to our database.
| | 01:56 | The next one will be Orders, and this
time I'm going to hit Create, and it's
| | 02:01 | created a new table for us, and we'll
say Products. And I can either hit the
| | 02:08 | Return key or click on Create and then
finally OrderLineItem, our fourth and final table.
| | 02:18 | So we've created one table in our
database for each bucket of data that we
| | 02:22 | discovered during our data-modeling exercise.
| | 02:24 | FileMaker Pro has now created a table
inside the database and one default layout
| | 02:29 | for each table that
we'll see in later exercises.
| | 02:32 | Once we've created these tables, we can
move on to creating things like fields,
| | 02:36 | and in the case of having multiple
related tables, we can now move on to
| | 02:39 | creating relationships between these
tables, but all of that begins first with
| | 02:43 | creating the tables inside
of our FileMaker database.
| | Collapse this transcript |
| Defining key fields| 00:00 | In order to create relationships in
FileMaker, you first need to determine which
| | 00:03 | tables you're going to have in your
database and then create those tables.
| | 00:06 | As you'll see inside of our exercise
files, if you go into the 04_02 files, and
| | 00:13 | under the File > Manage > Database,
you'll see that we've already got four
| | 00:18 | tables created inside of our database.
| | 00:20 | It's one table for each entity
that we've identified throughout our
| | 00:23 | data-modeling exercise.
| | 00:25 | In that exercise, we identified
that we had four different entities--
| | 00:29 | Products, Orders, Customers, and
OrderLineItem--and we've created a table
| | 00:33 | for each of those here.
| | 00:35 | By evaluating the one-to-many
relationships between each of these tables, we
| | 00:38 | determine that every child table in a
one-to-many relationship which is on the
| | 00:43 | many side will need to have a foreign
key field and every parent, which is the
| | 00:48 | one in the one-to-many, will need
to have a primary key field defined.
| | 00:52 | If we look at our relationships here,
we see that we've got relationships
| | 00:56 | between Products and OrderLineItems,
and the products are the one and the
| | 01:00 | OrderLineItems are the many.
| | 01:02 | So the Products table is going to
require a primary key field and the
| | 01:05 | OrderLineItem table will require a
foreign key field, in this case the
| | 01:09 | Products ID foreign key.
| | 01:11 | We also have a one-to-many
relationship between Orders and OrderLineItems.
| | 01:15 | So Orders being the parent will
need a Order ID primary key field, and
| | 01:19 | OrderLineItems being the child will
require a Order ID foreign key field.
| | 01:24 | Then finally, the last relationship
is between Customers and Orders, where
| | 01:28 | Customers is the one, or the parent, so we
will need a Customer ID primary key field.
| | 01:33 | And Orders acts as the
child in this relationship,
| | 01:36 | so it's going to need to have a
Customer ID foreign key field defined.
| | 01:40 | So this shows the roadmap of the primary
and foreign key fields that we need to define.
| | 01:45 | So let's move into FileMaker
Pro and define those fields.
| | 01:48 | You define key fields in the same way
that you would define any other field.
| | 01:52 | Inside of your Manage Database window,
you'll notice that we're looking at the tables.
| | 01:57 | All four of our tables are already defined.
| | 01:59 | By double-clicking on one of these
tables, it will take you over to the Fields
| | 02:03 | tab inside the Manage Database window,
and we currently don't have any fields
| | 02:07 | defined, but we're going to
go ahead and create these.
| | 02:09 | I'm going to start with the Customer
table where we need to define just one
| | 02:12 | field, a primary key field.
| | 02:14 | Before we define any fields in our
database, we're going to need to decide what
| | 02:18 | name we're going to give our field.
| | 02:20 | For key fields it's a good practice to
follow some naming conventions, so that
| | 02:24 | later, you can easily identify the key
fields in your database, as opposed to
| | 02:27 | some of the other fields
that represent attributes.
| | 02:30 | There are various different
naming conventions to use.
| | 02:32 | None of them are right or wrong.
| | 02:34 | But the convention that I like to use
is to add a suffix to the field name that
| | 02:37 | indicates what type of key that it is.
| | 02:40 | So since these are primary keys, for
example, I'd like to use pk as the suffix.
| | 02:45 | I also like to have my key fields
appear at the top of my field list when I
| | 02:50 | sort them by field name,
| | 02:52 | so I like to use a prefix of characters
that will have them show up in the top
| | 02:56 | of that sort order, and the ones
that work for me are the underscore.
| | 03:00 | So if I type in the single underscore,
that will have this field show up at
| | 03:03 | the top of my list.
| | 03:04 | But I'll also have foreign key fields
in some tables, and to make sure that
| | 03:08 | primary keys show up above foreign keys,
the naming convention I like to use is
| | 03:13 | a double underscore
followed by the name of the field.
| | 03:17 | The name of the field that I like to use
after the underscore is the name of the
| | 03:21 | table with the letters ID
after it, so we know it's a key.
| | 03:25 | So in this case since we're in the
Customer table, I'll call it Customer ID, and
| | 03:30 | then I'll end it with the pk suffix,
indicating that this is a primary key field.
| | 03:33 | I'll also put some comments in here--
| | 03:39 | Customers--and then we'll hit the Create button.
| | 03:42 | Now the field has been created,
but there are some additional options that we
| | 03:45 | need to set up to make it
act like a primary key field.
| | 03:48 | Selecting the Options button, you'll
see that it opens us up to the Auto-Enter
| | 03:53 | tab option, and here what we want to
do is ensure that the value inside this
| | 03:56 | field is going to be unique and
cannot be modified by any users.
| | 04:00 | So first, in the Auto-Enter tab, there
is an option inside of FileMaker called
| | 04:04 | Serial Number which means that every
time a new record is created, FileMaker
| | 04:08 | will go and allocate a next value
based on whatever you have in these two
| | 04:12 | fields and increment that next value
by whatever you put in this field here.
| | 04:17 | So we simply just need a number
that's unique, so we can leave the default
| | 04:20 | next value one, increment by one in
place, and each time FileMaker crates a
| | 04:24 | record it's going to give it a new
number, and it will ensure that those
| | 04:28 | numbers are never a duplicate.
| | 04:30 | In addition, we want to make sure that
no users change these values, which would
| | 04:33 | mess up all of our
relationships between related records,
| | 04:36 | so we'll hit Prohibit
modification of value during data entry.
| | 04:40 | We hit OK and now we've got our
Customer ID primary key field defined to give
| | 04:45 | us a unique value every time a record is
created and not allow users to make those changes.
| | 04:50 | If we go back to our ERD, we see
that in addition to customers, we also
| | 04:56 | have orders acting as a parent and
products, so both of those tables will
| | 05:00 | require primary key fields.
| | 05:02 | So let's click on the dropdown menu
to go over to Orders, and let's follow
| | 05:07 | the same convention.
| | 05:08 | __OrdersIDpk, give it a comment,
"primary key for Orders," hit Create, check our
| | 05:21 | options, and simply choose Serial Number,
and Prohibit during data entry. Pretty simple.
| | 05:28 | Now finally, one last time
for Products, same thing,
| | 05:31 | __ProductsIDpk, primary key for
Products, choose Create, and hit Options,
| | 05:44 | selecting Serial Number and Prohibit
modification of value during data entry.
| | 05:48 | So now we've got all of our primary keys
in place, but we're still going to need
| | 05:52 | to define our foreign keys.
| | 05:54 | We see if we look at our entity
relationship diagram that the OrderLineItem
| | 05:59 | table has two foreign keys we need
to define, and the Order table has one
| | 06:03 | foreign key that we need to define.
| | 06:04 | So let's start off with Orders.
| | 06:06 | In the Order table we want to define a
foreign key for the relationship between
| | 06:11 | Customers and Orders, and instead of
the double underscore in this field, I'm
| | 06:15 | going to do a single underscore,
because this is going to be a foreign key.
| | 06:19 | When we name foreign key fields, I
like to name them after the parent in the
| | 06:23 | relationship that they're a part of.
| | 06:25 | So in this case it's a relationship
between customers and orders, so I'm going
| | 06:29 | to name this foreign key field fk to
indicate that it's a foreign key field.
| | 06:33 | I'm going to enter in to the comment
that this is a foreign key field for the
| | 06:38 | Customers relationship and hit Create.
| | 06:41 | Foreign key fields will have no
options because we're going to use other
| | 06:44 | mechanisms to populate these values
and therefore create the relationship
| | 06:47 | between the records.
| | 06:48 | So for now we just want to name it using
the single underscore, ID, and then the
| | 06:53 | fk suffix as our convention.
| | 06:58 | The only other table that acts as
a child in the relationship is the
| | 07:00 | OrderLineItem which is the
foreign key field in the Orders in the
| | 07:08 | OrderLineItem relationship.
| | 07:10 | We hit Create and we have one more as well.
| | 07:19 | The OrderLineItem also is a child to Products,
| | 07:23 | so we will create the
ProductsID foreign key and hit Create.
| | 07:28 | And again, foreign key fields
do not need any text options.
| | 07:30 | These values will be populated manually
or through some other mechanisms, not by
| | 07:34 | the Auto-Enter options as
in the case of primary keys.
| | 07:38 | Now we have all our key fields. We can hit OK.
| | 07:42 | Once you have your database created or
the shell of the database created, you
| | 07:46 | can start by adding things like fields,
layouts, and relationships, and all of
| | 07:49 | the different things that
we'll cover in upcoming movies.
| | 07:51 | Now, our next step is to create the
links between these related tables now that
| | 07:55 | we have our hooks, otherwise
known as keys, already in place.
| | Collapse this transcript |
| Introducing the Relationship Graph| 00:00 | At this point in the process of
creating a related database in FileMaker we are
| | 00:03 | assuming that you have already gone
through the data-modeling exercise, got your
| | 00:06 | tables determined and defined, and also
gone into your database and defined all
| | 00:11 | of your primary and foreign key fields.
| | 00:13 | If you open up your exercise files,
you will see that we have got a file called 04_03.
| | 00:19 | Opening up 04_03 and going into the
Manage > Database option and you'll see that
| | 00:27 | we've got our four tables defined,
and double-clicking each table shows different
| | 00:32 | fields that are defined.
| | 00:33 | You can also use the dropdown list to
show those field names, and you see that
| | 00:38 | we've got all of our primary keys and
our foreign keys defined within our table.
| | 00:42 | Now you will notice that there's
another tab on the far right here and that one
| | 00:45 | is called Relationships.
| | 00:47 | Go ahead and click on the Relationships
tab, and what you are seeing here is your
| | 00:50 | first glimpse of what's called the
relationship graph. And inside this
| | 00:54 | relationship graph is where we can
establish the relationships between the
| | 00:58 | tables and also manage them in the future.
| | 01:00 | You will see that we have four
different blocks, or squares, within this
| | 01:03 | relationship diagram.
| | 01:05 | Each one of these blocks is known as
a table occurrence, and every time you
| | 01:09 | define a table in FileMaker Pro
over in the Tables tab FileMaker will
| | 01:13 | automatically add a table
occurrence to the relationship graph for you.
| | 01:18 | It's important not to think of these
occurrences as tables, because we are going
| | 01:22 | to learn later in this title that a
single table could be represented on the
| | 01:25 | relationship graph by
multiple table occurrences.
| | 01:28 | We are not going to worry about that
right now, but instead we are going to
| | 01:30 | focus on there being one table
occurrence per table defined in our database.
| | 01:35 | Now our goal in this exercise is to
create relationships between these tables.
| | 01:39 | We have already got our key fields
defined, and our goal here is to create
| | 01:43 | relationships, and that's really the
easy part, as far as the task inside the
| | 01:46 | database is concerned, because to
physically create these relationships inside
| | 01:50 | of the FileMaker relationship
graph doesn't really take much effort.
| | 01:53 | You will just take your cursor and
you will go into the parent table, in
| | 01:57 | this case Customers.
| | 01:59 | You'll click down on the field that you
want to use as your primary key and you
| | 02:03 | will notice that the cursor turns into a
cursor with a little dumbbell below it,
| | 02:06 | and as you drag outside of that box over
to the table occurrence that's going to
| | 02:12 | represent the child table, you will
have to pick between the two fields.
| | 02:15 | And this is why I like to use the
naming convention that we've chosen for the
| | 02:19 | foreign key fields because you
will see, it's pretty obvious,
| | 02:22 | you do CustomerID to CustomerID with the
pk on the parent side, fk on the child side.
| | 02:28 | So after you select the foreign key
field in the relationship, you'll release
| | 02:31 | your cursor, and you will see that
FileMaker then isolates which of the two
| | 02:36 | fields are your key fields in your
relationship and it also draws a little line
| | 02:41 | that looks like a single-to-triple hash
crow's foot showing the crow's foot on
| | 02:45 | the many side and the single on
the parent side. So, real easy.
| | 02:49 | Just click and drag between the
different foreign keys. And we have made this an
| | 02:52 | even easier task because of the names
that we've chosen for our key fields.
| | 02:56 | So as you see here, we've
got Orders and OrderLineItem.
| | 02:59 | We click on the primary key for Orders.
| | 03:02 | Deciding which of these key fields
is going to be the foreign key in this
| | 03:05 | relationship is pretty easy,
because it's got the similar name to the
| | 03:08 | primary key fields.
| | 03:09 | We will go OrdersIDpk to OrdersIDfk.
| | 03:14 | FileMaker adjust that and shows that primary
key field and foreign ID key field isolated.
| | 03:20 | And then finally, the last one,
the ProductID, very simple, ProductIDpk to ProductIDfk.
| | 03:29 | So not only are your table occurrences
visually linked within the database, but
| | 03:33 | now FileMaker has actually created
relationships between these tables and also
| | 03:38 | identified the specific fields defined
within the tables as the key fields to
| | 03:42 | link those tables together.
| | 03:43 | So once you have gone through this
process that we have just done here, you can
| | 03:47 | then move on to start defining
other things inside of your database.
| | 03:50 | Later in this title I am going to show
you the various different ways that you
| | 03:53 | can use the relationships
that we have set up here.
| | 03:56 | All the different relationships that
you are managing inside of FileMaker are
| | 03:59 | all done through the Relationships tab.
| | Collapse this transcript |
|
|
5. Working with RelationshipsUnderstanding related fields | 00:00 | We've been talking a lot about creating
relationships between a parent and child
| | 00:04 | table, but for the rest of this title
we're going to talk about the many things
| | 00:07 | you can do inside of your database
once those relationships are in place.
| | 00:10 | One of the primary benefits behind the
concept of relational databases is to
| | 00:14 | reduce redundancy and even
borrow data from other related tables.
| | 00:18 | For example, in this file that I've got
on my Orders layout, if I go into Layout
| | 00:23 | mode, I can see that this
relationship has what I like to call the context.
| | 00:29 | And let me explain that further by
clicking on the Modify Layout button.
| | 00:33 | And you'll see the first thing that we
notice on any layout in the Layout Setup
| | 00:37 | dialog is the name of the layout,
which is immediately followed by the table
| | 00:41 | that it's based on.
| | 00:43 | That means that the Orders layout
has the context of the Orders table.
| | 00:47 | Another way to look at this is under
the File > Manage > Database option, over
| | 00:53 | in the Relationships tab.
| | 00:54 | You should always remember that at
any given point when a user is in your
| | 00:58 | database, they're in the
context of one of the tables on your
| | 01:01 | relationship graph.
| | 01:03 | So in the case of the Orders layout,
we are in the context of the Orders table.
| | 01:08 | And this is relevant when you're
working with any kind of related fields or
| | 01:11 | related layout objects on your layout.
| | 01:14 | You'll have to know what you're looking
at and from where you're looking at those.
| | 01:17 | So, for example, if I have a layout that
is based on the Orders table and I want
| | 01:21 | to display data from Customers, I
know that I them in the context of Orders,
| | 01:25 | which is the child of the Customers table.
| | 01:28 | Let me show you how
that's relevant. Let's hit OK.
| | 01:31 | So let's look at this concept in
action back in the Orders layout.
| | 01:34 | You'll notice that we already have
several fields on this layout. Some of them
| | 01:37 | are showing field names and some of them aren't.
| | 01:40 | Ones that are showing field names, if
we click on them, we'll see over in our
| | 01:44 | Inspector which is on right-hand side
that we are displaying data from the
| | 01:49 | Orders table and it's a field called Sale Date.
| | 01:52 | So these are all fields that
have the same context as the layout.
| | 01:56 | Your field is defined inside the Orders table.
| | 02:00 | In some of these cases though,
we don't have fields yet.
| | 02:03 | So we'll look at Email, Company, Address,
City, State, ZIP. All of this entire
| | 02:07 | cluster of fields, that's actually
data that describes the customer for this
| | 02:12 | order, not the order itself.
| | 02:14 | So these are fields that are actually
already defined over in our Customer table.
| | 02:19 | So in order to have this information
displayed on our Orders layout and anything
| | 02:23 | that we print off based on this layout,
| | 02:25 | we can do one of two things.
| | 02:26 | We can define these fields inside of
Order, but then we would have two different
| | 02:31 | places where the Company, Address, City,
State, ZIP is being stored inside of
| | 02:35 | our database, which would
present a redundancy issue.
| | 02:39 | For example, if I have a Company
field defined inside Order and inside the
| | 02:44 | Customer table--if we look, we already
have one defined in the Customer table--
| | 02:48 | if I define it in two different places
then I have to manage the data inside
| | 02:54 | that field in two different places,
which could create a lot of error and have
| | 02:57 | some inconsistencies in the data.
| | 02:59 | So instead what we're going to do is
take advantage of the fact that the
| | 03:02 | Orders table, from which this layout is
based off of, is related to the Customers table.
| | 03:07 | And what we're going to do there,
because they're related, is we're going to
| | 03:09 | borrow the fields from the other table,
thus having the data only stored in one
| | 03:14 | place but displayed wherever
we need it to be displayed.
| | 03:17 | So let's take a look at
that a little bit closer here.
| | 03:19 | So concentrating on this Email field,
this is actually the email of the customer
| | 03:24 | for which this order belongs.
| | 03:25 | So if we look over here in the Inspector,
we don't have any data being displayed.
| | 03:29 | So if I click on the little Edit icon,
it will allow me to specify a field.
| | 03:33 | So any time you add a field to a
layout, you'll notice that you've got the
| | 03:36 | option to pick fields from the same
table that the layout is based on,
| | 03:41 | in this case, Orders.
| | 03:42 | But what you might not know is if you
click on this little dropdown here, you
| | 03:45 | see that you can pick from
fields that are from related tables,
| | 03:49 | in this case, Customers.
| | 03:51 | So if I choose Customers, you'll
notice that on the left-hand side they all
| | 03:54 | have a double colon which indicates that
they're what is called a related field.
| | 03:58 | And here if I select Email and hit OK,
| | 04:02 | now we've got a related field,
a field that actually lives inside of the
| | 04:06 | Customer table but is placed on a
layout inside of the Orders table.
| | 04:09 | So it's a related field, but
how are these two records related?
| | 04:12 | We do have a relationship between
Customers and Orders where Customers is the
| | 04:16 | parent and Orders is the child,
| | 04:18 | but how are the actual records related?
| | 04:20 | Well, we've got a field here.
| | 04:21 | You see this as our foreign key
field, and of course, we know that two
| | 04:25 | records will be related when the value
inside the foreign key matches a value
| | 04:29 | inside a parent key.
| | 04:30 | So what needs to happen in order
for these related fields to show data?
| | 04:34 | Well, we need to populate the
foreign key value with a valid primary key
| | 04:39 | from the Customer table.
| | 04:41 | And the way that I've set that up,
here we have the foreign key value.
| | 04:44 | If you look at the field, I've got a
Pop-up menu assigned, and I've put a value
| | 04:50 | list on the pop-up menu.
| | 04:51 | And this is a pretty common technique, so
I'm going to show you how I've got this set up.
| | 04:54 | If you click on the Edit icon,
| | 04:56 | you'll notice that I have a value list
set up in the database already, and what
| | 05:01 | it's doing is showing me all the
values from the CustomerID primary key field
| | 05:07 | back in the parent table of Customers.
| | 05:10 | So that way I'm choosing from only
valid primary key values, and I'm populating
| | 05:14 | the foreign key field,
thus making these two records related.
| | 05:17 | Now, since users could look at a list
of primary keys and not even know what
| | 05:21 | customer they're associated with,
I've gone with the option to display values
| | 05:25 | from the second field as well.
| | 05:26 | So let's look at what that
looks like in Browse mode.
| | 05:29 | Again, a very common technique.
| | 05:31 | We'll go into Browse mode.
| | 05:34 | After saving your changes, you'll see
now I have a dropdown list of all the
| | 05:37 | valid customer records with all their
primary key values, thus allowing me to
| | 05:41 | choose a valid CustomerIDpk value to
place inside of the foreign key field.
| | 05:48 | This is a common method for
linking a child record to a valid parent.
| | 05:51 | And you see in Browse mode if we
choose one of these options, we see now that
| | 05:55 | our related Email field populates.
| | 05:58 | That's because it knows which customer
record to go grab the email data from.
| | 06:02 | You'll see that changes depending on
which one of these values we select.
| | 06:07 | So this is a great way to show related
data without having to duplicate related
| | 06:12 | data on an order record.
| | 06:13 | And as the result, we see in email
address on the order record, even though the
| | 06:17 | email address data actually lives and
is stored inside the Customer table.
| | 06:21 | Back in Layout mode, we see that
there's a double colon there, and we can add
| | 06:27 | all the other related fields as necessary.
| | 06:29 | Pull down to Customers, and in
this case we're looking for Company.
| | 06:34 | See that shows up with the double
colons in front, indicating related fields.
| | 06:37 | Let's go again from the Customer table.
| | 06:40 | What you would likely do is drop them
on the layout as a related field in the
| | 06:47 | first place, but here we had some
placeholder fields, and I will choose these
| | 06:53 | other options for you.
| | 06:54 | So, now all of the fields that we see
here with the double colons in front of
| | 07:08 | them are related customer fields that
we are borrowing from the parent table.
| | 07:14 | So I like to call this
technique borrowing from your parent.
| | 07:16 | And if we go back into Browse mode and
save our changes, we'll see that they
| | 07:21 | will all update every time we pick a new
value to put inside of our foreign key field.
| | 07:28 | So, why does this work?
| | 07:29 | How do we know that this is
the correct data in these fields?
| | 07:31 | Well, this works because the Order
table is the child, or the many, table in the
| | 07:35 | relationship between Customers and Orders.
| | 07:38 | This means that any given order record
will only ever have one valid related
| | 07:42 | parent customer record.
| | 07:44 | So that way, since only one piece of
data can fit inside of a FileMaker field,
| | 07:48 | we know that it will have the correct
value showing in these related fields
| | 07:51 | because we only ever have one
related customer record to pull data from.
| | 07:55 | So that way we can rely on this data
after populating the foreign key field and
| | 08:00 | know that it is correct and it is in
fact the data that's coming from the proper
| | 08:03 | customer parent record.
| | 08:06 | Since a field in FileMaker can only
store one value, we know that we will have
| | 08:09 | the correct value showing in these
related fields because there were only ever
| | 08:13 | be one related customer record to pull
data from, and not only that, there's a
| | 08:17 | persistent connection between this data.
| | 08:19 | So, for example, if I look at this
related piece of data and I go into it and
| | 08:24 | I update it, and now if I go into
the Customer record and do a search for
| | 08:30 | Orthopedic Specialists, you'll see that the
data has been updated on the parent record as well.
| | 08:38 | So if I change it back to St.
| | 08:42 | and go back into Orders,
we'll see that it's updated.
| | 08:45 | So this way I can change a piece of
data when I'm looking at it inside the
| | 08:49 | context of Orders and it will
update the parent when necessary.
| | 08:52 | So again, I like to call this
technique borrowing from the parent.
| | 08:55 | It's an easy term to remember, and it
reminds us how we can use related parent
| | 08:59 | fields in a layout with the context
of a child table, allowing us to reuse
| | 09:03 | related parent data without having
to double enter and to also have it
| | 09:07 | faithfully update when the
parent record is updated.
| | Collapse this transcript |
| Understanding portals| 00:00 | One of the primary benefits of relating
tables together is the ability to share
| | 00:03 | live data between them.
| | 00:05 | We've already looked at how we can
place related fields on a layout so that a
| | 00:09 | child table can borrow values
from any related parent table.
| | 00:13 | This of course eliminates the need
for redundant data inside your database.
| | 00:17 | But thus far we've only been looking
at using related fields on a layout that
| | 00:20 | has the context of the
child table in a relationship.
| | 00:24 | If we look at the relationships that we
have in our database by going under the
| | 00:29 | File > Manage > Database and choosing
the Relationships tab, we will see that
| | 00:33 | here's the Orders Table.
| | 00:34 | That's the table that this layout is
based off of. And we've placed some related
| | 00:39 | fields that we are borrowing from the
parent table of Customers, but we also see
| | 00:44 | that Orders has a
relationship down to OrderLineItems.
| | 00:47 | So what we want to do now is
put some related fields from the
| | 00:50 | OrderLineItems table on here.
| | 00:51 | You notice that we've already got
a section for this in our layout.
| | 00:54 | We've got some text headers for the
different columns: Product ID, Product Name,
| | 00:58 | Part Number, and so on.
| | 01:00 | So why don't we place some related fields from
OrderLineItems below each one of those columns?
| | 01:05 | I will go under Insert > Field, and I'll
want to make sure that I am choosing from
| | 01:11 | the dropdown list here OrderLineItem.
| | 01:13 | You see that that's selected, and so
now what I am doing is I am choosing from
| | 01:17 | fields that are inside the child table.
| | 01:20 | So Orders is the parent to OrderLineItems,
so I am picking a field--let's say the
| | 01:25 | ID field. And we don't have to choose
the label, make that a little bit smaller.
| | 01:33 | And let's do one more underneath Product Name,
| | 01:35 | Insert > Field and we will go to Product Name.
| | 01:42 | So now just like we have got related
fields from the parent table to Orders,
| | 01:47 | which is Customers, we have now got
related fields from the child table to
| | 01:52 | Orders which is OrderLineItems.
| | 01:53 | So let's go into Browse
mode, and see what we get.
| | 01:56 | Well, we have got some data
showing up, but it's only one product.
| | 02:01 | So what happens if we have more
than one line item on this order?
| | 02:04 | Should we add some new fields?
| | 02:06 | Copy the same fields?
| | 02:08 | Actually no, neither of those will work.
| | 02:10 | The problem here is, what we are seeing
is that the Order Table is the one, or
| | 02:14 | the parent in the relationship
between Orders and OrderLineItems,
| | 02:17 | so that means that there can potentially be
many related line item records to this order.
| | 02:22 | Since a FileMaker field can only
display one value at a time, we won't be able
| | 02:27 | to use a related field. It would always appear
that we only have one related record, and that's just
| | 02:32 | simply not reliable.
| | 02:33 | Instead, what we could really use
is some type of a field or layout
| | 02:37 | object that would display a
dynamic list of related child records,
| | 02:41 | regardless of how many there were.
| | 02:42 | Well, we actually do have a special
kind of layout object in FileMaker and
| | 02:46 | it's called a Portal.
| | 02:47 | Let's go into Layout mode, and I
will show you how we use a portal.
| | 02:52 | First, let's get rid of our first attempt.
| | 02:56 | Now, look at our tools in the
status toolbar on the top of the page.
| | 02:59 | You will notice that there is one right
here that's called the Portal tool and
| | 03:04 | when you click on this option, like in many
other layout objects, you will see that
| | 03:08 | our cursor turns to a crosshair.
| | 03:09 | So what I want to do is draw a box about
this size to make sure that it fills up
| | 03:16 | all the area below those
different column headers.
| | 03:18 | So when I release it, we see a window
pop up that's called the Portal Setup.
| | 03:22 | We also see that in the layout we've
got what appear to be multiple rows.
| | 03:26 | So really, this is kind of like having
one related field, but the field will
| | 03:30 | then repeat itself for every
related record that it has.
| | 03:33 | This is really what we are looking for.
| | 03:35 | Instead of using related field, we are
going to use this Portal Layout object.
| | 03:38 | So that way, if we have ten different
related line items on one order, this will
| | 03:42 | show ten different records;
| | 03:43 | but if we only have two on another order,
it will only show two different records.
| | 03:46 | This is exactly what we are looking for.
| | 03:47 | Now inside the Portal Setup,
we do have some decisions to make.
| | 03:51 | You will notice the dropdown that's
very similar to the one that you see when
| | 03:54 | you're inserting a field.
| | 03:55 | This shows you the related tables
that you can choose from, because a portal
| | 03:59 | is only used in related tables, and I
should clarify at this point, you will
| | 04:03 | only use a portal on a layout
that has a context of the parent in a
| | 04:07 | relationship, because what it's used
to do is show child records inside the
| | 04:11 | context of that parent layout.
| | 04:13 | Since we are in a layout that's based
on the Order table, we know that the
| | 04:17 | OrderLineItem is a child to the Order
table, so we will select that option.
| | 04:21 | We also have a couple of other Portal
Setup options that we will get to in
| | 04:25 | later movies, but I'm going to show one here
that's called the Show vertical scrollbar option.
| | 04:29 | What that will do is show a vertical
scrollbar on the right-hand side of our portal.
| | 04:34 | So even though we've only got four
different rows here, if we end up with 30,
| | 04:38 | the user can just scroll through.
| | 04:39 | That way we don't have to take
up all that space on the layout.
| | 04:42 | So let's hit OK. And now the next
window is asking us what related fields we
| | 04:46 | want to put on there.
| | 04:47 | Since each of these rows in the portal
is going to be like a related record,
| | 04:52 | what we're really doing is choosing the
fields that are going to show up on each record.
| | 04:56 | We're going to pick fields that are
going to match the column headers that are
| | 04:58 | already on the layout.
| | 04:59 | So first, we've got Product ID and Product Name.
| | 05:02 | Now, Product ID and Product Name are
actually fields that come from the Product table.
| | 05:07 | This portal row will have the context
of OrderLineItem, which is related to
| | 05:12 | products, so therefore we can borrow
from the parent in that case as well.
| | 05:16 | You'll see by simply clicking down to
the Products option allows us to choose
| | 05:20 | from all the related product fields.
| | 05:23 | So let's choose the Product ID first
and click Move and move that over--
| | 05:28 | these are all the fields that are going
to appear on this Portal row--and let's
| | 05:31 | also choose Product Name.
| | 05:35 | So the reason this will work,
the Portal row has the context of
| | 05:37 | OrderLineItem, but any one
OrderLineItem record only has one related product
| | 05:42 | record as a parent.
| | 05:43 | So, that way we will only get one
piece of data in each of these fields, and
| | 05:46 | that's exactly what we are looking for.
| | 05:48 | So we've got Part Number, moving
Part Number over, and a couple of more,
| | 05:53 | Taxable, same thing here.
| | 05:55 | But now, Quantity is specific
to the OrderLineItem record.
| | 05:59 | So let's go back to
OrderLineItem and choose Quantity.
| | 06:05 | You can see by the prefixes that
we've got Products, Products, Products,
| | 06:09 | Products, and then OrderLineItem.
| | 06:10 | These are the tables where we've
defined those different fields. And we
| | 06:14 | will finish up by adding price and extended
price, which both come from the OrderLineItem.
| | 06:20 | So we have got Products
fields and OrderLineItem fields.
| | 06:23 | The Products fields are being borrowed
from the parent of the OrderLineItem, and
| | 06:27 | the OrderLineItem values are coming from
the actual OrderLineItem record that we
| | 06:30 | are seeing inside the portal.
| | 06:32 | So now when we hit OK, we notice that
we have got a vertical scrollbar and we
| | 06:36 | notice that only the first row, which
was the only white row--the rest of them
| | 06:39 | are gray in color when we are looking at them--
| | 06:42 | we notice that that row is
populated with all the related fields.
| | 06:45 | You only have to fit the related
fields into the first row of a portal for
| | 06:49 | them to show up, and then when you
look at it in Browse mode it will repeat
| | 06:52 | itself for all the different related values.
| | 06:54 | You also need to make sure to keep
your fields within that first row, because
| | 06:57 | they if bleed over into another row or
outside of the portal, they are no longer
| | 07:01 | considered part of the portal
and that could mess up your layout.
| | 07:04 | If you need more space, you can just
grab one of the handles and make the portal
| | 07:07 | larger, but we seem to have the right size here.
| | 07:10 | Let's go into Browse mode, saving our
changes, and take a look at what we've got.
| | 07:15 | Now, it's no magic here.
| | 07:16 | The exercise file that I am using
happens to already have related data showing
| | 07:21 | up in it so that we can
display the related values properly.
| | 07:24 | So what we see here as we go
through each order is that the amount of
| | 07:28 | related records changes.
| | 07:30 | Some orders don't have any related
records, some only have four or five, and
| | 07:35 | then some have many of them, which we
can see by scrolling through the portal
| | 07:39 | using the vertical scrollbar.
| | 07:41 | So as you go from record to record,
all related child records will
| | 07:45 | appear dynamically.
| | 07:47 | Portals will help you most when you're
designing layouts that are based on a
| | 07:50 | parent table in a relationship.
| | 07:52 | It provides extremely useful visibility
of child records from the context of a
| | 07:56 | parent, and it's going to be something
that you are going to use on almost every
| | 07:59 | database solution that you create.
| | Collapse this transcript |
| Using relationship options to create related fields| 00:00 | Up until this point, we've been looking
at how to use the FileMaker relationship
| | 00:03 | graph to set up your database's
basic one-to-many relationships.
| | 00:07 | That relationship graph can be found
under File > Manage > Database, and it's
| | 00:12 | the last tab on the right.
| | 00:14 | Relationships can have different
options set up for each, besides just which key
| | 00:18 | fields are used in the match tables.
| | 00:20 | So you see now we've just simply
selected what the primary key and the foreign
| | 00:23 | key are going to be in each relationship.
| | 00:25 | But if you'll notice, there is a
little equal sign in the middle of line in
| | 00:28 | between each tables in a relationship
and if you click on that equals sign,
| | 00:32 | you'll see the Edit Relationship dialog appear.
| | 00:35 | This dialog lets us control all
of the options for a relationship.
| | 00:38 | You'll notice that each table in
the relationship is represented here:
| | 00:42 | one table on the left, in this case the
parent, and then another table on the right.
| | 00:47 | So this is where you can select all
the different fields that you would use
| | 00:50 | as your match fields.
| | 00:51 | We've already got those selected
because we've dragged and dropped between the
| | 00:55 | primary and the foreign keys when we
first set these up. This shows what our
| | 00:58 | selections were at that time.
| | 01:00 | Below the match fields are a series of
options for each side of the relationship.
| | 01:04 | There are options for records,
created, or deleted or sorted on the parent
| | 01:09 | side, and then there are options for
records that are created, deleted, or
| | 01:13 | sorted on the child side.
| | 01:15 | So in this example what we're going to
do is select one of these options on the
| | 01:19 | child side, or the OrderLineItem
side of the relationship, specifically
| | 01:23 | selecting Allow creation of
records in this table via relationship.
| | 01:28 | This means that we can now use objects
that refer to this relationship to create
| | 01:32 | related records in the OrderLineItem table.
| | 01:35 | Some of these objects could be a
related field or more commonly, it would be a
| | 01:39 | portal layout object.
| | 01:40 | So when I close this window and go back
to the Order layout, you'll see that in
| | 01:47 | a portal that's based on the
OrderLineItems layout, you'll see that when I
| | 01:51 | scroll down to the last row, I have
what appears to be a blank record.
| | 01:55 | Well, what this is is a data entry
record allowing me to create related
| | 01:59 | OrderLineItem records through
the context of this order layout.
| | 02:04 | So this is just there for the user
to be able to enter in their own data.
| | 02:07 | If we go back into Layout mode,
| | 02:09 | you see that I've also assigned a
dropdown menu to the Product ID field.
| | 02:15 | You can see over here underneath the
Data tab we've got the Product ID field
| | 02:19 | from the OrderLineItem and I've got a
Pop-up menu selected, and here I've created
| | 02:24 | another one of these value lists--
we can see it by clicking the Edit button.
| | 02:28 | This value list is called ProductIDs,
and what it's showing me is a list of all
| | 02:33 | of the valid Product ID primary key
values, and I've chosen the Product Name to
| | 02:37 | show up next to it to help
the user select the correct one.
| | 02:41 | So in order to create a related value,
users will populate the Product ID
| | 02:45 | foreign key field with a valid primary
key value, and then they can enter in
| | 02:50 | the rest of the values.
| | 02:50 | Let's look at that in action.
| | 02:52 | So I scroll down to the very last row,
click into the field, pick one of the
| | 02:58 | products, and now a new
related record has been created.
| | 03:03 | I'll scroll all the way down to the bottom,
and we see that I can enter in a quantity
| | 03:08 | for my new value, or I can
change an existing quantity.
| | 03:12 | So by simply going into the Options in
our Edit Relationship dialog, I've now
| | 03:17 | added this ability to create
related records through a portal.
| | 03:20 | This is a great way to allow your
users to do data entry for related records
| | 03:23 | without having to bounce around to
various different layouts in your database.
| | 03:27 | If we go back under the File menu,
to Manage > Database and into our
| | 03:31 | Relationship Graph, double-clicking on
the equals sign again, we notice that
| | 03:36 | we've got a couple of other options.
| | 03:38 | We've got Delete related records in this
table when a record is deleted in the other table.
| | 03:42 | What that means in English is that
this will delete any related child records
| | 03:47 | in the OrderLineItem table whenever their
related parent order record is also deleted.
| | 03:52 | This is intended to help you keep your
data clean, but it should also be used
| | 03:56 | with extreme caution, because it will
delete all these child records in every
| | 03:59 | case where the parent is deleted.
| | 04:01 | So if you are cleaning out a test
version of a database, and you delete all of
| | 04:05 | the order records, all of your
OrderLineItem records will be deleted
| | 04:08 | automatically as well.
So use that with caution.
| | 04:11 | Finally though, you notice that
we've got the option to sort the values.
| | 04:14 | So not only will it sort records that
we see inside of a portal, but anywhere
| | 04:18 | else that an array or a list of
related records are showing up.
| | 04:21 | We'll look at this a bit
further in a later movie.
| | 04:24 | The FileMaker relationship graph allows
you to not only create your one-to-many
| | 04:28 | relationships, but it also gives you
various different options for those
| | 04:30 | relationships once they're already in place.
| | Collapse this transcript |
| Using multiple match fields in one relationship| 00:00 | Once you have your main FileMaker table
relationships in place, you'll have the
| | 00:03 | architecture you need to
build upon for your database.
| | 00:06 | Now I'd like to discuss with you how you
can also use the FileMaker relationship
| | 00:10 | graph to create other query types
of relationships that can be used for
| | 00:13 | specialized purposes.
| | 00:14 | Let's take a look at the relationship
graph under File > Manage > Database, go
| | 00:20 | into the third tab for Relationships.
| | 00:22 | The first thing I want to point out is
that the boxes that you place on the
| | 00:25 | relationship graph are not actually
tables, although they do look like them;
| | 00:29 | rather they're known as table occurrences,
| | 00:32 | sometimes also referred to as TOs.
| | 00:34 | A table occurrence is exactly that,
| | 00:36 | it's an occurrence, or an instance, of a
database table in a relationship graph.
| | 00:40 | Each table occurrence has a window, or a
point of view, into the underlying source
| | 00:44 | table, and therefore refers to only
one single underlying source table.
| | 00:49 | So each box that you see on screen
references one of the tables in your
| | 00:53 | database, and only one.
| | 00:55 | However, one of the more powerful
features of the relationship graph is the
| | 00:59 | ability to create more than one table
occurrence per table in your database.
| | 01:03 | This way each new table occurrence,
which actually represents a new relationship,
| | 01:07 | in your database can have its own
properties, separate from the original
| | 01:11 | one-to-many relationships
that you initially set up.
| | 01:14 | I like to refer to these types of
relationships as query relationships.
| | 01:18 | Let's look at a couple specific examples.
| | 01:20 | Back in Browse mode, you'll see on the
Customer layout that I've got a tab here
| | 01:25 | for Orders and I've already got a
portal in place, which is going to display all
| | 01:30 | of the orders related to this customer.
| | 01:33 | And you'll notice that I've put some
headers here for other related values.
| | 01:37 | Here we'd like to see all shipped orders,
and then here we'd like to see all future orders.
| | 01:42 | So if I want to see portals under
each of those, I could certainly take the
| | 01:46 | portal that we have here and go change
the criteria under the Edit Relationship
| | 01:49 | dialog, but we want to preserve
the original ALL Orders portal,
| | 01:54 | so in that case what we're going to
want to do is create new relationships.
| | 01:58 | More specifically, we're going
to create two new relationships.
| | 02:01 | One relationship is going to show all
orders related to a customer but only the
| | 02:05 | ones that have a status of shipped,
| | 02:08 | and the other one is going to show all
orders related to a customer but only
| | 02:12 | with a sale date that is in the future.
| | 02:15 | So let's create those relationships now.
| | 02:17 | I'll go into the File > Manage > Database.
| | 02:21 | Before we go to the Relationships tab to
set up this new table occurrence, let's
| | 02:24 | look at the Fields tab, because I want
to show you two fields that I created in
| | 02:28 | the Customers table that we're going
to use for these new relationships.
| | 02:31 | Notice on the very bottom I've got two fields--
they're both calculations--
| | 02:35 | one that's called Shipped.c and
the other one that's called Today.c.
| | 02:40 | They're calculations because I want
one single value to appear in every
| | 02:44 | one of the customer records.
| | 02:45 | So in the Shipped.c field, I'm going to
place the value Shipped in every single
| | 02:51 | customer record in the database.
| | 02:53 | I'm doing the same thing here in the
Today.c Calculation, but instead, I'm using
| | 02:57 | a calculation function to display the
current date in every customer record.
| | 03:01 | And I'll show you why I've
done this in just a second.
| | 03:04 | Let's move over to the Relationships tab,
and now we see our four base tables, or
| | 03:08 | table occurrences, that are based on
our original one-to-many relationships.
| | 03:12 | Now we can create a new table occurrence,
and this one will be just for setting
| | 03:16 | up the specialized portal.
| | 03:18 | The way you add a new table
occurrence to a relationship graph is by going
| | 03:21 | to the far bottom-left corner and choosing
the little table with the plus sign icon.
| | 03:27 | Once you select that, you'll see
the Specify Table dialog appear.
| | 03:30 | So the first choice we need to
make is, what table will this new table
| | 03:35 | occurrence be based on?
| | 03:36 | So in our exercise, we want to show all
related orders as they're related to the
| | 03:40 | active customer record, so we're
going to choose the Orders table.
| | 03:44 | After I select the Orders table, you see that
a name appears below for a table occurrence.
| | 03:50 | Automatically, FileMaker has given it
the name Orders 2, and the reason for the
| | 03:54 | number 2 there is because there
already is a table occurrence that's called
| | 03:58 | Orders, and you cannot have two
table occurrences with the same name.
| | 04:02 | I like to use my own naming
convention when I'm naming table occurrences,
| | 04:05 | because eventually, you're going
to have a ton of these inside your
| | 04:07 | relationship graph.
| | 04:09 | And when you're looking at different
places in FileMaker that reference these
| | 04:11 | relationships that you've set up, for
example, like dropdown menus and insert
| | 04:15 | fields or dropdown menus when you're
setting up a portal, it'll then be easier
| | 04:19 | for you to pick the right relationship
because of the name that you've given to
| | 04:22 | the table occurrence.
| | 04:24 | Since each table occurrence must
bear a unique name to avoid FileMaker
| | 04:27 | getting confused when you refer to
a relationship, I like to use the
| | 04:31 | following naming convention.
| | 04:33 | I like to show the context for where we
are starting, or the context of the table
| | 04:37 | where we're going to be using this relationship.
| | 04:39 | And in this case, it's customers,
so I'll put in the word customers
| | 04:43 | deliberately in lowercase.
| | 04:45 | I'll follow that by an underscore,
where I will deliberately put in uppercase the
| | 04:51 | name of the table that this
table occurrence is based on.
| | 04:54 | And then finally, because this
relationship will have a specialized purpose, I'm
| | 04:57 | going to put an underscore here and
put the word shipped underneath it.
| | 05:01 | So this tells me just by looking at it
that I'm going to use this in the context
| | 05:04 | of Customers, but it's going to be based
on related records from Orders, but I'm
| | 05:09 | only going to be looking at
related orders that have shipped.
| | 05:11 | So now I hit OK, and you see it's
created a new table occurrence.
| | 05:15 | Both of these happen to be based on Orders.
| | 05:18 | What I like to do in my relationship
graph to avoid confusion is to select any
| | 05:23 | new table occurrences that aren't
part of the original one-to-many
| | 05:26 | relationships and I like to
assign them a different color,
| | 05:29 | by clicking down on the Color Wheel in
the bottom portion of the layout.
| | 05:33 | I'll pick a nice red color.
| | 05:35 | And that way I know that this is a
specialized table occurrence, or a
| | 05:37 | query relationship.
| | 05:39 | So now let's create our link
between Customers and Orders.
| | 05:42 | And since we want to see only orders
related to a customer, we'll start with the
| | 05:46 | same primary key to foreign key match
fields that we have in this relationship.
| | 05:50 | We'll do customerIDpk to customerIDfk.
| | 05:54 | Currently, these two relationships
have the exact same properties, but we're
| | 05:57 | going to add some additional
options to this relationship.
| | 06:00 | And the way that we do that is by
double-clicking on the equal sign to invoke
| | 06:04 | the Edit Relationship dialog window.
| | 06:06 | So we've already established in this
relationship that we want to see any order
| | 06:10 | records that are related to the active customer.
| | 06:12 | But in this activity, we want to see
only those related order records that have
| | 06:16 | a shipping status of Shipped.
| | 06:19 | So there's a field over in the Orders
table that's called Shipping Status, and
| | 06:25 | the only ones that we want to show up in
this relationship, or to be related via
| | 06:29 | this relationship, are ones
that have the value of Shipped.
| | 06:33 | So I'll select this field as one of the
match fields on the right-hand side, or
| | 06:37 | in the Order side on the child table.
| | 06:39 | But what I need is a field on the
Customer table that is always going to have
| | 06:44 | the value of Shipped in every record,
| | 06:45 | and that's this Calculation field
that we set up before this exercise.
| | 06:49 | So now I've got Shipped.c to Shipment
Status, and you'll notice that I have
| | 06:54 | these buttons Add and Change.
| | 06:55 | If I hit the Add button, you'll notice
something pretty interesting is happening here.
| | 06:59 | Now I've got two sets of match fields,
| | 07:01 | two different pairs of match fields.
| | 07:03 | To tell you what's happening here is
this is going to show us any customer
| | 07:07 | record where the value inside the
foreign key field for CustomerID matches the
| | 07:12 | active CustomerIDpk value--
| | 07:14 | so basically, any order records related
to a customer--but only the order records
| | 07:19 | where the Shipment Status matches
the value inside the Shipped.c field.
| | 07:23 | And since every record in the Customer
table is going to say the word Shipped
| | 07:27 | then what we have here is show me all
of my related records that only have the
| | 07:31 | Shipment Status of Shipped,
which is just what we need.
| | 07:33 | So let's put this to work.
| | 07:35 | We'll hit OK and hit OK again.
| | 07:37 | Let's go into Layout mode to add a new portal.
| | 07:40 | I'll make this easy, and we're going to
select the existing portal and copy it
| | 07:49 | and hit Paste, and this will
allow us to have the same format.
| | 07:54 | But now we've got two portals with the
same relationship, so what we want to do
| | 07:58 | is double-click on this portal, and this
brings up the Portal Setup dialog, which
| | 08:01 | will allow us to switch from the
original Orders relationship that you see
| | 08:05 | listed here, to the new one we just
created, which is customer_ORDERS_shipped.
| | 08:10 | So now in the Related Tables list, we
see not only the actual related tables,
| | 08:14 | but any table occurrences that
are based on those related tables.
| | 08:17 | So now we say customers_ORDERS_shipped,
and these fields, if you look over here
| | 08:22 | in the Inspector, they are all
still using related values from Orders,
| | 08:27 | so we want to make sure that the portal and
the related fields in the portal all match.
| | 08:33 | I'll simply double-click these and switch
them quickly over to the new relationship
| | 08:41 | that we just created.
| | 08:42 | And let's see what this
looks like in Browse mode.
| | 08:45 | Saving our changes.
| | 08:46 | Now we'll notice that here are all
the different orders related to this
| | 08:51 | customer, but here are all the
orders related to this customer that have
| | 08:54 | the status of Shipped.
| | 08:55 | Let's try this one more time to
fulfill the all future orders.
| | 09:00 | This time we'll create another
table occurrence, base it on Orders.
| | 09:05 | We use the similar naming
convention, but this time we'll say
| | 09:08 | customers_ORDERS_future and hit OK.
| | 09:13 | Here's our new table occurrence.
| | 09:15 | I'm going to make this a different
color, so I know that it's not one of our
| | 09:18 | original base tables, and we can drag it
to make it a little bit larger to see this.
| | 09:23 | Now, let's create a
relationship between customers.
| | 09:25 | We do want to see any order records
related to a customer, so we'll pick
| | 09:29 | customerIDpk, customerIDfk, and
now let's add some more criteria by
| | 09:34 | double-clicking on the equal sign.
| | 09:36 | And what we want to do is only
show records that are in the future.
| | 09:40 | So what I've got is a field on the
left-hand side or the parent side
| | 09:44 | that is a calculated field called Today.
| | 09:47 | And inside that is going to be the
current calendar date being pulled from the
| | 09:50 | computer's operating system.
| | 09:52 | We want that to match up with the Sale
Date field in the Order record, so here's
| | 09:55 | Sale Date, and we're going
to add this new criteria.
| | 09:57 | So basically what this says is, give
me all related order records to the
| | 10:01 | active customer record, but only
show me the ones where the value in Sale
| | 10:05 | Date equals today's date.
| | 10:06 | But that's not exactly what we want to do.
| | 10:08 | This of course would only show
order dates for the current day.
| | 10:12 | What we intended to do in this
exercise is to show any sale dates that were
| | 10:15 | greater than today's date.
| | 10:17 | So you'll notice for the first time
we're going to use a different operator.
| | 10:20 | We've got the Customer table and the
Orders table, and so far we've only created
| | 10:24 | relationships that have an
equal sign in the middle.
| | 10:26 | But what you may not know is that
you've got all these different operators
| | 10:29 | that you can use, and we've got one
operator in particular that would be very
| | 10:32 | useful for us here, because we want to
see only related records where the sale
| | 10:36 | date is greater than today,
| | 10:38 | so let's choose the greater than
sign and hit the Change button.
| | 10:41 | So now you see we've got
two sets of match fields--
| | 10:43 | the primary key and the foreign key--
but then also a value that shows the
| | 10:47 | current date in every
record in the Customer table.
| | 10:50 | So no matter which record you're on in the
Customer table, it'll always show today's date.
| | 10:53 | And a portal based on this
relationship would only show us records where the
| | 10:58 | value in Sale Date is greater than today's date.
| | 11:01 | So let's take a look at this
new relationship in action.
| | 11:04 | I'll hit OK and OK and quickly go
into Layout mode, select this portal
| | 11:10 | again, paste it right here, and all we
have to do in this case is change the
| | 11:18 | relationship again.
| | 11:19 | So we'll double-click on portal and pick future--
| | 11:22 | you see that new one I added.
| | 11:23 | And we'll do the same thing for the
fields in the portal, all three of them.
| | 11:35 | And now we'll go into Browse mode
and look at the data that we've got for
| | 11:39 | our finished product.
| | 11:40 | So we've got this customer.
| | 11:42 | Here's all of their orders, so we only
have one match field criteria for that
| | 11:46 | relationship that this portal is based off of.
| | 11:48 | But now we have a specialized query
relationship that we set up that shows not
| | 11:52 | only their related orders but only
related orders with the value of Shipped.
| | 11:55 | And then we have a third one that is
all the related orders with a Sale Date
| | 12:00 | greater than today's current date.
| | 12:02 | So FileMaker, being a relational
database application, allows us to set up
| | 12:07 | architecture for relating those
tables together within our database.
| | 12:10 | However, the true power of
FileMaker comes when you start to use the
| | 12:13 | relationship graph to create these
customized query relationships for viewing
| | 12:17 | and working with
specialized sets of related data.
| | Collapse this transcript |
|
|
6. Real-World Data Modeling ExamplesDonations example| 00:00 | So far we've learned about the basic
relationship types and how to model
| | 00:03 | relationships between tables.
[00:00:5.16]
However, we've only looked at one use case.
| | 00:08 | But in this movie, we're going to use a
real-world example to apply what we've learned.
| | 00:13 | In review, data modeling
breaks into three phases:
| | 00:17 | First, you develop a list of entities
or tables that belong in your data model.
| | 00:21 | Next, determine and diagram the
relationships between those tables.
| | 00:26 | And third, review the diagram for
correctness and consistency and possibly
| | 00:30 | revisit steps one and two if necessary. Okay.
| | 00:34 | Here's a process description for this example.
| | 00:38 | Take a minute to read this through,
making special note where you think you'll
| | 00:41 | see potential entities.
| | 00:43 | And when you're looking for entities
in your description look for nouns.
| | 00:47 | Go ahead and pause this
video and finish reading.
| | 00:50 | Using the nouns technique, we can identify
our entities as events, members, and donations.
| | 00:58 | Each of these entities are in fact
different and discrete types of data, and
| | 01:02 | each can be described
differently from the others.
| | 01:05 | Let's also make note of the phrase
"donations that are made by more than one
| | 01:09 | member at a time" for later reference.
| | 01:11 | Identifying these entities tells us what
tables we'll need to define in our database.
| | 01:15 | These will then become our entity list.
| | 01:19 | Identifying the tables is only half the task;
| | 01:22 | we must then determine how
they are related to each other.
| | 01:24 | And this is where you want to
create an Entity Relationship Diagram,
| | 01:28 | otherwise known as an ERD.
| | 01:31 | In ERDs, it's common to represent your
entities as a box with an entity name inside of it.
| | 01:37 | Next, we'll move on to exploring
relationships between the entities, starting
| | 01:41 | first with members and donations.
| | 01:44 | We will questions formatted within the
one-to-many logic that we've been using
| | 01:48 | throughout this title.
| | 01:50 | In this example, here are
the questions we should ask.
| | 01:54 | Can each member potentially
have many donations? Yes.
| | 01:58 | We actually see this in the description
where there's a reference to donations
| | 02:01 | made by their members.
| | 02:03 | Now, can donations be made from
one and only one member? No.
| | 02:08 | In our process description, it also
states "donations that are made by more than
| | 02:12 | one member at a time."
| | 02:13 | So what we have here is what we
call a many-to-many relationship.
| | 02:17 | And if you recall from previous
movies, the way we resolve a many-to-many
| | 02:22 | relationship is by adding a
third join table to our diagram.
| | 02:26 | We'll add a new join table to
this diagram, and we'll call this
| | 02:30 | one members/donations.
| | 02:32 | When naming a join table, I like to
combine the names of the two parents so that
| | 02:36 | we know what type of table it
is when we see it in our diagram.
| | 02:39 | So we'll visually represent the
relationship between each parent and child table
| | 02:43 | with a line connecting the two boxes.
| | 02:46 | These lines will have a double hash on
the members/donations side because that
| | 02:49 | serves as the child in
both of these relationships.
| | 02:52 | Now let's look at the rest of the
entities, and let's evaluate whether there's a
| | 02:56 | relationship between donations and events.
| | 03:00 | Now here the question we'll ask will
be, is each donation associated with one
| | 03:06 | and only one event?
| | 03:07 | In this example, yes, donations can
only be associated with a single event.
| | 03:12 | Now, can each event potentially
have many donations in the system?
| | 03:16 | Again, our description mentions
"a record of all donations generated by
| | 03:20 | each event," so yes.
| | 03:22 | This tells us that there is a
one-to-many relationship between the two entities,
| | 03:26 | with donations as the many.
| | 03:28 | We'll want to represent
that on our diagram as well.
| | 03:33 | Now let's take a look at the entire
diagram with all the relationships in place.
| | 03:38 | The one thing to note here is that
although the relationships are not drawn
| | 03:41 | between members and events directly,
these tables are still related through a
| | 03:46 | chain of relationships.
| | 03:48 | For example, a member could submit a
donation when they're attending an event,
| | 03:52 | and therefore that member is
indirectly related to the event through this
| | 03:56 | chain of relationships.
| | 03:59 | Once our relationships are in place,
we can now determine which tables need
| | 04:02 | primary keys and which need foreign keys.
| | 04:05 | We'll begin first with the primary keys.
| | 04:07 | In review, we see the following
tables acting as a one, or a parent, in
| | 04:11 | their relationships.
| | 04:13 | Members are a parent to
members/donations and will require their own primary key.
| | 04:18 | And donations are also a parent to
members/donations and therefore need their primary key.
| | 04:24 | And events are a parent to donations and
thus will require a primary key as well.
| | 04:29 | Now finally, the foreign keys.
| | 04:31 | In review, we see that the following
tables will be acting as a child or many
| | 04:35 | in their relationship.
| | 04:37 | Donations are a child to events, so
they'll need an events foreign key, and
| | 04:42 | member/donations are child
to both members and donations,
| | 04:46 | so we actually need two
foreign keys in this table:
| | 04:49 | one foreign key for
donations and one for members.
| | 04:52 | Going through all these steps in your
planning stage will help you make sure
| | 04:54 | that all of your relationships
are one-to-many relationships.
| | 04:57 | And it will also help you determine which
keys you're going to need inside of each table.
| | 05:02 | So that's a quick rundown of
applying a relationship design to a
| | 05:05 | nonprofit donations example.
| | 05:07 | Feel free to use this
structure if it maps to your situation.
| | Collapse this transcript |
| Class enrollment example| 00:00 | In this movie we are going to use a
real-world use case to apply what we've
| | 00:04 | already learned about modeling
relationships, but this time we're going to look
| | 00:07 | at one-to-one relationships and instances.
| | 00:11 | Remember, data modeling
breaks down into three phases.
| | 00:15 | First you develop a list of entities
or tables that belong in the data model.
| | 00:20 | Next, you determine and diagram the
relationships between those entities, and
| | 00:24 | finally, review the diagram for
correctness and consistency--and sometimes
| | 00:28 | you'll revisit steps 1 and 2 when necessary.
| | 00:31 | Here's another process description.
| | 00:34 | Remember, when looking for entities, we
want to look for nouns in our description.
| | 00:37 | So why don't you just go ahead and pause this
video and take a minute to read this through?
| | 00:43 | Using the nouns technique, we can
identify entities as classes, teachers,
| | 00:49 | students, and advisors.
| | 00:52 | Each of these appeared to be different
discrete types of data, and each can
| | 00:56 | be described uniquely.
| | 00:58 | Let's also make note of this section,
"a teacher can also be an advisor"
| | 01:02 | for later reference.
| | 01:04 | Now we have our entity candidate lists,
and these entities will eventually
| | 01:09 | become tables defined in our database.
| | 01:11 | But before they become tables in our
database, we must first determine how the
| | 01:14 | entities are related to each other.
| | 01:17 | This is where we'll want to
create an entity relationship diagram,
| | 01:20 | otherwise known as an ERD.
| | 01:23 | In any ERD, it's common to
represent your entities as a box, with the
| | 01:26 | entity name inside of it.
| | 01:29 | Let's move on to exploring the
relationships between these entities, starting
| | 01:33 | first with students and classes.
| | 01:35 | We are going to use questions format
within the one-to-many logic that we've
| | 01:38 | been discussing throughout this title.
| | 01:40 | For example, here the questions would be,
can each student potentially have many classes?
| | 01:46 | Yes, we remember in our description,
students can enroll in as many classes as they like.
| | 01:52 | Now, can each class be taken
by more than one student? Yes.
| | 01:56 | We also learned that classes are intended to
have more than one student in them at a time.
| | 02:01 | So what we have here is a
many-to-many relationship.
| | 02:04 | And if you recall from previous
movies, the way we resolve a many-to-many
| | 02:08 | relationship is by adding a third join table.
| | 02:11 | We will add a new table
and call it Students/Classes.
| | 02:16 | Now when I am naming a join table I
like to combine the names of the two
| | 02:19 | parents, but if you'd like, you
can name them something different;
| | 02:22 | in this case you can call it Enrollment as well.
| | 02:25 | We'll then visually represent the
relationships between each parent and child
| | 02:29 | table with a line connecting each box.
| | 02:32 | The double hash lines will be on the
side of students and classes because that
| | 02:36 | table serves as the many, or the
child, in both of these relationships.
| | 02:41 | Now let's evaluate whether or not there's a
relationship between teachers and classes.
| | 02:46 | Here are the questions
we would ask in this case.
| | 02:48 | Can each teacher
potentially teach many classes? Yes.
| | 02:52 | If you see in our description, it
mentions teachers can teach many classes.
| | 02:55 | Can a class be taught by
more than one teacher? No.
| | 02:59 | Class only requires one teacher.
| | 03:02 | So this tells us that there is a
one-to-many relationship between these two
| | 03:05 | entities, with classes as the many.
| | 03:09 | We'll want to represent this on our diagram.
| | 03:12 | Now in this example system we have got
something of the bit different happening.
| | 03:16 | Let's look at the relationship
between teachers and advisors.
| | 03:20 | Now let's ask, can one
teacher have many advisors?
| | 03:24 | Well, this doesn't fit, because in
this process description we saw a teacher
| | 03:28 | can also be an advisor, so what we
really have here is what's called a
| | 03:32 | one-to-one relationship.
| | 03:34 | What that means is that a teacher and an
advisor are technically the same table;
| | 03:38 | therefore, we'll want to eliminate
advisors as a table on a diagram.
| | 03:43 | But there is a relationship
between students and advisors.
| | 03:46 | We saw in our description that a
teacher can be an advisor to many students.
| | 03:51 | So what this means is that we have to
add another instance of the teachers
| | 03:55 | table, but not a separate advisors table.
| | 03:58 | That instance will act
as the parent to students.
| | 04:01 | We will discuss how to handle
instances in an upcoming movie, but first let's
| | 04:05 | look at our entire diagram with
all the relationships in place.
| | 04:10 | Now although relationships are not
drawn directly between the students and
| | 04:14 | teachers tables, these tables
are still related through a chain.
| | 04:17 | For example, a student could enroll
in a class that's taught by teacher;
| | 04:23 | therefore that student is indirectly
related to the teacher through this chain.
| | 04:26 | Once our relationships are in place,
we can now determine which tables need
| | 04:31 | primary keys and then foreign keys.
| | 04:33 | But let's start first with the primary keys.
| | 04:36 | We see the following tables acting as
the one, or the parent, in our diagram.
| | 04:41 | Students are a parent to student
classes, so they will need a primary key.
| | 04:45 | And classes are also a parent to student
classes, so they will also need a primary key.
| | 04:50 | And teachers are a parent to classes, so
teachers will also need a primary key field.
| | 04:58 | Now, the advisor table is
not actually its own table;
| | 05:01 | it's an instance of the teacher's table.
| | 05:04 | For now, since we do not need an
additional advisors table, we also don't need a
| | 05:08 | separate advisor primary key field.
| | 05:10 | Instead, we will reuse the primary key field
and the teacher table for that relationship.
| | 05:16 | And now finally, the foreign keys.
| | 05:19 | We apply our rule and see double hashes
touching the following tables in our diagram.
| | 05:24 | Classes are a child of teachers,
so they need a teacher foreign key.
| | 05:28 | Student classes are child to both
students and classes, so we need two
| | 05:32 | foreign keys there:
| | 05:33 | one for students and one for classes.
| | 05:36 | But let's not forget our instance of advisors.
| | 05:40 | Even though it's not actually a new
table, the students table is still going
| | 05:44 | to be a child to advisors, so let's
make sure to add a foreign key for
| | 05:48 | advisors to the student table.
| | 05:51 | Going through these steps for each
database you create will help you verify that
| | 05:54 | each relationship that you
have is going to be a one to many.
| | 05:57 | It will also tell you what keys
you are going to need for each table.
| | 06:01 | So there's a quick rundown of
applying relationship design to an
| | 06:04 | academic situation.
| | 06:06 | Go ahead and use this example
if it works for your project.
| | Collapse this transcript |
| Personnel management example| 00:00 | In this movie, we're going to use
another real-world example to apply what we've
| | 00:04 | already learned about modeling relationships,
| | 00:06 | but this time we're going to examine
one-to-one relationships and the concept of
| | 00:10 | a self-join table using an instance.
| | 00:13 | Now remember, relationship modeling
breaks down into three different phases.
| | 00:18 | First, you develop a list of entities,
or tables, that belong in the data model.
| | 00:23 | And then next, you determine and diagram
those relationships between the entities,
| | 00:27 | and then finally, review the diagram for
correctness and consistency, and revisit
| | 00:32 | steps 1 and 2 if necessary.
| | 00:35 | Here's another process description.
| | 00:37 | And remember, when looking for entities,
you want look for nouns in your description.
| | 00:41 | So go ahead and pause this movie and
take a minute to read this through.
| | 00:45 | Using the nouns technique, we can
identify the entities here as employees,
| | 00:51 | benefits, and managers.
| | 00:54 | Each of these appear to be different,
discrete types of data, and each can be
| | 00:58 | described differently from the others.
| | 01:01 | Let's also make a note of the phrase
"employees that are managers" for later reference.
| | 01:07 | Now we have our entity candidate list,
where entities will eventually become the
| | 01:12 | tables that were defined in the database.
| | 01:15 | Identifying the entities is only half the task;
| | 01:18 | we must now evaluate how the entities
are related to each other before they
| | 01:22 | become tables in our database.
| | 01:24 | This is where we'll create an
Entity Relationship diagram, otherwise known as an ERD.
| | 01:29 | In ERDs, it's common to represent your
entities as a box with the entity name inside of it.
| | 01:35 | So now let's move on to exploring
relationships between the entities, starting
| | 01:39 | first with employees and benefits.
| | 01:41 | We're going to use the questions
consistent with the one-to-many logic that
| | 01:46 | we've been discussing throughout this title.
| | 01:47 | For example, here the questions would
be, can each employee have many benefits?
| | 01:54 | Yes, we actually saw a reference to
benefits in our process description.
| | 01:58 | Now can a benefit in our system
belong to more than one employee? No.
| | 02:03 | If you remember, there's a phrase
in our description that says "benefits
| | 02:06 | each employee receives."
| | 02:07 | So what we have is a one-to-many relationship.
| | 02:10 | We will visually represent the
relationship between employees and benefit tables
| | 02:14 | with a line connecting the two boxes.
| | 02:17 | The double hash is on the side of the
benefits table because benefits table
| | 02:21 | serves as the many, or
child, in this relationship.
| | 02:25 | Now in this example system, we
have something interesting going on.
| | 02:29 | Let's take a look at the
relationship between employees and managers.
| | 02:33 | If we ask a similar question here,
can one employee have many managers
| | 02:38 | the answer would be yes, but employees and
managers are not different groups of data;
| | 02:43 | they're both people with the same
data being recorded in each case.
| | 02:47 | And also, our process description
indicates that managers are employees.
| | 02:51 | So what we have here is a one-to-one
relationship, meaning that a manager and
| | 02:56 | employee are in fact the same entity.
| | 03:00 | In this case, since they will be the same
table, we'll eliminate the managers as a table.
| | 03:05 | But there is a relationship
between managers and employees.
| | 03:09 | We saw in our description that an
employee can manage other employees.
| | 03:13 | What this actually means is that we need
another instance of employees, not another table.
| | 03:19 | This instance we will call managers
will then act as the parent to employees.
| | 03:25 | This is what's called a self-join
relationship when one table is the parent to itself.
| | 03:30 | We'll discuss how to handle instances and
how to create self-joins in a later movie.
| | 03:35 | But for now, we know that we don't need an
additional managers table, just an instance.
| | 03:41 | Let's look at our entire diagram
with all the relationships in place.
| | 03:45 | We can now determine
which tables and keys we need.
| | 03:48 | Let's start first with the primary keys.
| | 03:51 | To review, we see the following
table acts as a one, or a parent, in our
| | 03:55 | relationship diagram.
| | 03:57 | Employees are a parent to benefits,
| | 04:00 | so we know we need a
primary key in the employee table.
| | 04:03 | However, let's not forget our instance
of employees that we're calling managers.
| | 04:07 | Even though it's still based on the
employees table, it does act as the parent
| | 04:11 | to employees in that relationship,
| | 04:14 | yet it does not require its own
primary key, because we're going to reuse the
| | 04:18 | employee ID for the purpose of the
manager's primary key in order to
| | 04:22 | accommodate that relationship. Finally,
| | 04:25 | the foreign keys.
| | 04:26 | We see that benefits are a child of employees--
| | 04:29 | note the double hash--so we'll
need a foreign key inside of benefits.
| | 04:33 | But let's not forget our instance of managers.
| | 04:37 | Even though it's not actually a new
table, employees is still going to be
| | 04:40 | the child of managers,
| | 04:42 | so let's make sure to add a manager
foreign key to the employees table.
| | 04:49 | After you go through all the
data-modeling steps, you'll then know which tables,
| | 04:53 | relationships, and keys you'll need.
| | 04:55 | So there's a quick rundown of
applying a relationship design to a
| | 04:59 | human resources scenario.
| | 05:01 | Go ahead and use this example
if it applies to your situation.
| | Collapse this transcript |
|
|
7. Advanced Relationship TechniquesUsing global fields to filter portals| 00:00 | In FileMaker, a portal can be thought of
as a layout object that simply provides
| | 00:04 | perspectives into other related tables.
| | 00:07 | For example, the ones that we've got
here are based in a layout that's based on
| | 00:11 | the Customer table, but we're actually looking
at data that is stored inside the Orders table.
| | 00:16 | The portal shows rows of related data
from another table, like the Orders table
| | 00:20 | in this case, and as information in
that distant table is added or changed, the
| | 00:24 | portal will display these changes.
| | 00:26 | So, all the data that you're
seeing is live and dynamic.
| | 00:28 | However, this is not the only way that
displayed information in a portal might change.
| | 00:33 | There's a technique that's called using
a portal filter to change the portal's
| | 00:37 | perspective, or match criteria, and
thereby presenting the user with a different
| | 00:41 | set of records based on
information changing in the current table.
| | 00:45 | That is a very powerful tool when
you're developing FileMaker databases.
| | 00:49 | This is the concept of creating a
Portal Filter field that allows the user to
| | 00:54 | change the related data that they're seeing.
| | 00:56 | In this technique, you will create a
field that won't act as an attribute, like
| | 01:00 | all the other fields in your layout
like First Name, Last Name, or Title, for
| | 01:03 | example, but rather, this field will
act as an input field to use as the
| | 01:07 | control or the filter for the related values
that they're going to see in the portal below.
| | 01:11 | So let's put this technique
to use in our exercise file.
| | 01:14 | You see that in the file we're in
the Customers layout, which is a layout
| | 01:18 | based on the context of the Customer
table, and we've got these three portals
| | 01:21 | here all showing related
orders with different criteria.
| | 01:25 | But what our goal is going to be for
this exercise is that we're going to
| | 01:27 | combine these first two portals together,
so we're going to show all the orders,
| | 01:33 | but we're going to allow the user to
pick which status that they want to see.
| | 01:36 | So that way they don't have to just see Shipped;
| | 01:37 | they could choose from Pending or New
or whichever status that they like, and
| | 01:41 | those will be the related order
records that show up inside the portal.
| | 01:44 | But first we're going to need to add a
field for users to use to select their
| | 01:48 | filter values, and we add fields by
going into the File > Manage > Database
| | 01:54 | option, and we will select Tables.
And since the layout is called Customers and
| | 02:00 | that's the layout where
we're going to add our filter,
| | 02:02 | that's where we need to define the field.
| | 02:04 | So we will double-click on the Customers
table, and I am going to add a new field.
| | 02:08 | In this case, we want a field for the
users to interact in that will not
| | 02:12 | affect attribute values stored on each record.
| | 02:14 | So that's why we create a new field.
And FileMaker has the perfect field option
| | 02:19 | for this activity, and they
are called global fields.
| | 02:21 | This might be something that
you're already familiar with,
| | 02:23 | but if not, global fields are not
actually a type of a field, but rather a field
| | 02:27 | that can have an option to be globally stored.
| | 02:30 | By choosing a field with global storage,
the field value will be shared by every
| | 02:33 | record in the table, or globally.
| | 02:35 | It's the same concept we used when
we created these calculation fields
| | 02:38 | for another exercise.
| | 02:39 | But what's nice about globally
stored fields is that they're going to be
| | 02:42 | specific to each user's session.
| | 02:45 | So, as you have a database that's
shared by multiple users at the same time,
| | 02:50 | the value that one user selects in
their field will be different and
| | 02:53 | session-specific based on the
value that another user might select.
| | 02:57 | So the calculations won't work in that
case, but instead, we're going to create
| | 03:00 | a new field which will be a text field
that is just simply globally stored, and
| | 03:05 | this globally stored field will allow
users to pick a filter value that will
| | 03:09 | affect every portal in the entire
customer record, by just selecting it on one of
| | 03:13 | the customer records at a time.
| | 03:15 | So let's go ahead and add this new
field, and I use a naming convention here
| | 03:18 | too for globally stored field where I
give it a prefix of a lowercase g, again,
| | 03:23 | so when I see it listed elsewhere, I know
that this happens to be a globally stored field.
| | 03:28 | I am going to call it OrderFilter,
and we will make it a type of text, and when
| | 03:33 | we hit Create, you see that
it's added to our Customers table.
| | 03:37 | But in order to make it globally stored,
we're going to have to hit the Options
| | 03:40 | button and click on Storage, and all we
have to do is just simply click Global
| | 03:44 | Storage. And you'll see the description
under Global Storage says, "A field using
| | 03:48 | global storage contains only one value
shared across all the records," and it can
| | 03:52 | also be used as temporary storage
location, temporary meaning that it's
| | 03:55 | specific to just this session.
| | 03:57 | So let's hit OK on this window, and we'll
hit OK again, and let's go into Layout mode.
| | 04:04 | So first let's make a little bit of room.
| | 04:06 | What we're going to do is get rid of
this portal, because it will be obsolete
| | 04:12 | now that we put the new
filter field on our initial portal.
| | 04:15 | So let's add a field to our layout.
| | 04:17 | I am going to go into Insert > Field
and under the Customer Table, select the
| | 04:24 | last one, gOrderFilter, and I'll
put that just above the portal.
| | 04:31 | And now any value that the users type
into this field will act as their filter,
| | 04:36 | but a really good technique is to
apply a dropdown menu or a pop-up menu to a
| | 04:41 | Filter field, and here this global field
you'll notice is currently an edit box,
| | 04:46 | but I can choose Pop-up menu. And I am
going to choose a Value List that I had
| | 04:53 | currently set up that points to the
values inside of the order status.
| | 04:58 | So you'll see here, in the Orders
table it points to Shipment Status, which is
| | 05:03 | the same field here.
| | 05:04 | So what that does is this is going to
give us a complete set of order statuses.
| | 05:09 | So no matter if someone adds one on the
fly, that means you don't have to go in
| | 05:13 | and update your Value List.
| | 05:14 | So this is really perfect for this filter.
| | 05:16 | So we will hit OK and OK again, and now we
have a dropdown menu assigned to that filter.
| | 05:21 | If we go into Save,
| | 05:23 | we see now that it is automatically
showing us an indexed and alphabetized list
| | 05:28 | of all of the different order statuses.
| | 05:30 | But choosing a new value currently
won't affect the different values that show
| | 05:36 | up in a portal, because we don't have a
relationship that uses that new global
| | 05:40 | field as one of the match fields.
| | 05:41 | That's really how this technique is working.
| | 05:44 | So what we need is a new relationship
that has this new global field as one of
| | 05:48 | its match fields, so that the user can
get the opportunity to dynamically affect
| | 05:51 | the matched values, and therefore the
related records that are displayed.
| | 05:55 | So let's go into the File > Manage
Database and then select the Relationships tab.
| | 06:00 | We see that we've got all the
different relationships that we've been working
| | 06:03 | with thus far in the title already set up here.
| | 06:06 | But we want to create a new table
occurrence, or a relationship, that is going to
| | 06:09 | be specific just for this filtering purpose.
| | 06:11 | So to create a new table occurrence,
we hit the button in the bottom left-hand
| | 06:14 | corner of the screen and we will
choose the table for which the data that's
| | 06:19 | going to be displayed in
the portal will come from.
| | 06:22 | That's going to be Orders.
| | 06:23 | I'm going to apply a naming convention
here again. Lowercase is the context of
| | 06:30 | the layout we will be using this, all
caps is the context of the table that's
| | 06:35 | going to be displaying the data, and
then another underscore, which then I am
| | 06:41 | going to put the word filtered here so I know
| | 06:43 | that this is the Customer and Orders
relationship that we've set up with the filter field.
| | 06:47 | So I hit OK, and we see we get the
table occurrence showing up on the graph.
| | 06:55 | Let's make this one a different color,
again so we know that it's a specialized
| | 06:59 | relationship. And what we're going
to do now is we're going to pick the
| | 07:04 | CustomerIDpk and related to the CustomerIDfk.
| | 07:08 | We seem to be starting there for all
these different new types of relationships,
| | 07:11 | and the reason that we're doing that
is because we always want to see order
| | 07:14 | records that are related to the customer
record we're on, not all the different order
| | 07:18 | records in the database.
| | 07:20 | So now in order to add or change match
fields, we'll move this over so that we
| | 07:23 | can clearly see the equal sign.
| | 07:25 | Double-clicking on the equal sign
brings up the Edit Relationship dialog for
| | 07:29 | this new relationship we've created.
| | 07:30 | We'll notice that right now it's just
showing us Related Orders, but we want to
| | 07:35 | see related orders where
the filter value is applied.
| | 07:38 | So if we scroll down on the Customer
side, we're going to use the new global
| | 07:42 | field as one of our match
fields in the relationship.
| | 07:44 | But what we want to do is have the
user pick one of the status values and
| | 07:50 | populate the global field, and then only
the related records that have the same
| | 07:56 | matching value for Shipment status are
going to be the ones that show up in the portal.
| | 08:00 | So we've already got the CustomerIDpk
= CustomerIDfk, showing us only related
| | 08:04 | order records, but also only related
order records where the value that the user
| | 08:09 | selects in the global OrderFilter
field will match the value that's in the
| | 08:14 | Shipment Status for the order.
| | 08:15 | So in order to do that, we're going to
hit Add, and so now we see both of those
| | 08:19 | values stacked in our Edit Relationship dialog.
| | 08:23 | Now hit OK and OK again to close out
these windows, and let's go into Layout
| | 08:27 | mode, and let's make a change in this portal,
| | 08:31 | so it will now use our
new filtered relationship.
| | 08:36 | You'll see that that updates along
the bottom of the portal. And let's also
| | 08:39 | change the field references real
quickly for these three. Now we hit OK.
| | 08:53 | Let's take a look at this in Browse mode,
| | 08:54 | so we go into Browse mode.
| | 08:57 | So now we've got a relationship that
shows its match field just above the
| | 09:01 | portal, so now let's pick one
of the items from the portal.
| | 09:04 | You see now what we've done is populate
one of the match fields, so now it shows
| | 09:07 | us all the orders that are New, and now
all the related order records that are
| | 09:12 | Pending or Shipped, and so on.
| | 09:15 | It's important to understand that these
values that the user is using inside the
| | 09:19 | global field are session specific,
| | 09:21 | so that way you don't have to worry
about one user filtering the portal for
| | 09:25 | Shipped and having another user see
only shipped records in there. Each user
| | 09:28 | gets their own filter selection, and
that's one of the real nice things about
| | 09:31 | using a global field in a case like this.
| | 09:34 | Using these globally-stored filters as
a portal filter can change the portal's
| | 09:38 | perspective within their session,
and thereby present a user with a set of
| | 09:42 | related records of their choice.
| | 09:43 | It's just another powerful use of
portals and a great way to make the user
| | 09:46 | experience in your FileMaker
databases even more dynamic.
| | Collapse this transcript |
| Using global fields to create a date range filter| 00:00 | Using a globally stored field as a
portal filter for one of your relationships
| | 00:04 | can allow users to change the
portal's perspective within their session and
| | 00:08 | thereby present users with the set
of related records of their choice.
| | 00:11 | Adding just one of those global fields
as a filter can control the value seen in
| | 00:15 | the portal, but adding two globally
stored fields as a pair of filters can
| | 00:20 | present users with a range of related values.
| | 00:23 | So, for example, in this file that
we've got here you'll see that we have got
| | 00:26 | this portal for all future orders.
| | 00:28 | Right now, all it does is use a
relationship that's already been set up in the
| | 00:32 | file, which is called customer_ORDERS_future,
and it shows all order records
| | 00:39 | related to the customer, but where the
Sale Date value is greater than today's
| | 00:46 | date--and today's data stored
inside each customer record using the Get
| | 00:50 | function for the current date as a
calculation, so that every customer record
| | 00:54 | has the same value.
| | 00:55 | But let's say that we'd like to allow
the user to pick a start date and an
| | 00:59 | end date for the range of related order
records that they see inside of a portal,
| | 01:04 | and this would require that we
actually set up two different filter fields.
| | 01:08 | We are going to stick with globally
stored fields as our filter, so users have
| | 01:11 | a session-specific field that will
apply filters across all of the records in
| | 01:14 | the customer found set.
| | 01:16 | And in order to do that, let's go into
Tables and Customer tables first, and
| | 01:21 | let's add one of those global filters.
And since it's global, we will call it g,
| | 01:26 | and we will do StartDate.
| | 01:28 | What we are going to allow the users to do
is pick a start date for their range for
| | 01:31 | their filters and then an end date,
and so we will need two different fields.
| | 01:34 | And in this case these
are going to be date field.
| | 01:36 | So I am going to select Date and I will
hit the Create button and after you hit
| | 01:41 | Create, you have the Options available to
you. And here I am going to choose, under
| | 01:45 | the Storage tab, the Use global storage
option, which works for date fields as
| | 01:50 | well as text fields.
| | 01:51 | And now I am going to create an gEndDate
field to pair with the StartDate fields,
| | 01:59 | and that's also going to be a date field.
| | 02:01 | I am going to hit Create, go into my
Options, go to Storage, hit Global Storage.
| | 02:06 | And again, just to iterate, the
global storage is session specific and
| | 02:10 | it applies to all records in the found set,
so they are really perfect for filtering.
| | 02:14 | So while we are in here, I am going to
click on the Relationships tab and we are
| | 02:17 | going to add a new relationship by
clicking on the Add New Table Occurrence, or
| | 02:22 | Specify Table window.
| | 02:24 | And this is also going to be a
relationship that shows us related order records,
| | 02:30 | so we will call this
customers_ORDERS_range, and we will hit OK.
| | 02:37 | Now this new table occurrence has been added
to our graph, and we want to pick the criteria.
| | 02:41 | So let's go up here, and we're going to
start, just like we did with these other ones,
| | 02:45 | we are going to start with the
CustomerIDpk primary key going to the
| | 02:48 | CustomerIDfk foreign key.
| | 02:50 | That means that no matter
what filter we are applying,
| | 02:52 | we still want to only see the
records that are related to the customer
| | 02:55 | record that were on.
| | 02:56 | So, that's why that's the baseline
for all these different relationships.
| | 02:59 | Double-clicking the equal sign will
bring up the Edit Relationship dialog,
| | 03:02 | where we see that we already have
our match fields of CustomerIDpk and
| | 03:06 | CustomerIDfk already selected.
| | 03:09 | So now what we want to do is pick a
StartDate filter, and we are going to match
| | 03:14 | that up with the Sale Date, because
that's the field, you can see it here
| | 03:19 | behind our windows.
| | 03:20 | The Sale Date is the date
field that we are showing.
| | 03:22 | So I pick Sale Date and StartDate and
I hit Add. And of course, by default we
| | 03:27 | see the equal sign there, but
we will fix that in a second.
| | 03:30 | And then I am going to pick EndDate
and also pair that up with Sale Date.
| | 03:34 | So right now we would really get no
records that match this, but what we really
| | 03:37 | want to do is show all the related
customer order records where the gStartDate
| | 03:43 | is actually less than the Sales Date,
and so that's where we would choose one
| | 03:47 | of these operators.
| | 03:49 | We have to select this option first,
so we can change just this operator--
| | 03:52 | we don't want to change all of them.
So we select gStartDate=Sale Date, and I
| | 03:57 | hit the greater than sign, hit
Change, and so now that's updated.
| | 04:01 | And now the EndDate is actually going
to be opposite, so when we select this
| | 04:03 | row, we want to make sure that the
value inside the EndDate field is greater
| | 04:09 | than any Sale Date that
shows up in our related set.
| | 04:13 | So now when we are done, this is what we've got.
| | 04:15 | We've actually had three different
operators in three pairs of match fields.
| | 04:18 | It just goes to show that when you're
setting up a relationship, or one of these
| | 04:22 | query relationships as I like to call them,
you can have as many match fields as you want.
| | 04:26 | You can get specific as
you want with the operators.
| | 04:28 | So it just goes to show you that you
can really create relationships just for
| | 04:32 | using on one layout, if need be, and
it's actually quite common technique in
| | 04:35 | FileMaker development.
| | 04:37 | Once your relationship is in place,
we will hit OK, and let's give this one
| | 04:42 | a little different color as well, just so we
can identify it from some of the other ones.
| | 04:48 | We will hit OK, and let's
go into Layout mode now.
| | 04:52 | In here we show the all future orders.
| | 04:55 | What we really want to do is show a date
range, so we are going to start with one field.
| | 04:59 | Let's use our Field Insertion tool to
drag the StartDate onto our layout. And we
| | 05:10 | will drag another one, which will be
the EndDate. And really we can get rid of
| | 05:19 | these if we want. We can just choose range.
| | 05:25 | Okay, so these are both global, and
since they are going to be dates, why don't
| | 05:29 | we add something by selecting the field?
| | 05:33 | We will add a control style of
dropdown calendar, including the icon, so that
| | 05:38 | users know that they're picking from
dates, make it a little bit easier for them.
| | 05:42 | Drop-down calendar > Include icon,
make that a little larger. All right!
| | 05:48 | So now we have got a date field
that's got global storage for StartDate and
| | 05:51 | EndDate, and we have set up a
relationship to show us a range of related order
| | 05:55 | records in between those two dates.
| | 05:57 | And so now we want to make sure that
our related fields inside the portal and
| | 06:01 | the portal itself are pointing to
the relationship that we set up.
| | 06:05 | See, right now they're still pointing to the
| | 06:06 | customers_ORDERS_future relationship.
| | 06:08 | We'll double-click on this and drag down to range.
| | 06:13 | Hit OK and we will do the same thing,
and notice that the correct field stays
| | 06:18 | selected if we change the relationship.
| | 06:21 | ORDERS_future, set range and one more.
| | 06:26 | So what we have done here is made all
three fields in the portal based on the
| | 06:31 | new relationship that we just set up,
and the new relationship uses these two
| | 06:35 | global fields as match fields.
| | 06:37 | So now in Browse mode, after we Save
our values, we see that we don't have any
| | 06:41 | values, because we haven't set
up our match field range yet.
| | 06:44 | So let's test this out.
| | 06:46 | I'll go in and add a date for
StartDate, and let's say the EndDate of
| | 06:58 | couple months later.
| | 06:59 | And now we see that the records are
only showing me values that fall within
| | 07:04 | this date range, so between July 2nd and
September 21st, and if I want to change that, I could.
| | 07:13 | Those all stay the same.
| | 07:14 | I could say July 2nd and July 11th.
| | 07:18 | You can pick whatever range you want.
| | 07:20 | So this will maintain the same global
filter values for every customer record, as
| | 07:25 | you see I moved through all these
different records, and it will be session
| | 07:29 | specific, so my selections in the date
range the high and low end of the scale
| | 07:33 | won't affect anyone else's.
| | 07:35 | I use dates, but you can use the same
technique with numbers to determine a
| | 07:38 | range, or even time fields to determine a range.
| | 07:41 | Using these pairs of globally stored
fields as portal filters in combination
| | 07:45 | with multiple match fields and
different operators inside our Edit Relationship
| | 07:49 | dialog can change the portal's
perspective within a user session and thereby
| | 07:53 | present the user with a set of
related records of their choice.
| | 07:56 | It's just another powerful use of
portals and a great way to make the user
| | 07:59 | experience in your FileMaker
database even more dynamic.
| | Collapse this transcript |
| Using relationships in a script to navigate| 00:00 | Once you've established some table
relationships inside your FileMaker file,
| | 00:03 | you can start benefiting from some of the
relationship-based functionality inside FileMaker.
| | 00:08 | We've already discussed in previous
movies in this chapter the ability to use
| | 00:11 | related fields in your layouts, which
are basically borrowing a field value
| | 00:14 | from a parent table.
| | 00:15 | And you see here in this layout we've
already discussed how we can add portals,
| | 00:20 | a layout object that allows you to see
related child records from the context
| | 00:23 | of your parent record.
| | 00:25 | And now I'm going to show you how
relationships can benefit you outside of just
| | 00:28 | using layout objects, but
instead, inside of your scripts.
| | 00:31 | So first a couple of quick items.
If you're not already familiar, a script is
| | 00:34 | something in FileMaker that allows you
to save stored actions in a sequence and
| | 00:38 | allow them to be triggered by some user action.
| | 00:41 | Scripts can be set up under
the Manage Scripts dialog.
| | 00:46 | You create a script and choose from
the series of different script steps.
| | 00:49 | What we're going to do here is create a
script and attach it to this button here
| | 00:56 | on our Order layout,
| | 00:57 | this button for the printer icon.
This will allow users to select the print
| | 01:02 | icon, which will then allow them to go
to a print-ready layout that shows all of
| | 01:07 | their related OrderLineItem records formatted in a
way they can print that doesn't have a portal.
| | 01:13 | And the trick there is that a layout
for print purposes that doesn't have a
| | 01:18 | portal on it that shows OrderLineItem
records will have to be based on the
| | 01:22 | OrderLineItem table, thus a different context.
| | 01:25 | And we already have the order print
layout built, if you look in Layout mode and
| | 01:30 | go to Print Layouts > Order_Print, and
we'll see that the context of this layout
| | 01:35 | by selecting the Layout
Setup icon, is the OrderLineItem.
| | 01:44 | So we're not just navigating to a
different layout, but we're navigating to an
| | 01:48 | entirely different context. And just
to review this idea of context, let's go
| | 01:52 | under Manage > Database and
look at our Relationships tab.
| | 01:55 | So here's the table that this layout is
based on, and here's the table that the
| | 02:00 | print layout is based on.
| | 02:02 | You see that we have a relationship
between these two, and it happens to be one
| | 02:05 | order has many related OrderLineItem records.
| | 02:09 | That's why this one order record has
many related portal rows, and this portal is
| | 02:16 | directed towards the OrderLineItem relationship.
| | 02:20 | So given all of that information,
what we want to do is create a script that has
| | 02:24 | the following tasks assigned to it.
| | 02:27 | We need to navigate over to the
OrderLineItem table context and go to the
| | 02:31 | layout that we've set up for printing
an order, and then we want to isolate a
| | 02:35 | found set of only those OrderLineItem
records that are child records to the
| | 02:39 | order that we're on--
| | 02:40 | so basically, all the ones that we've
seen in this portal and only the ones we've
| | 02:43 | seen in this portal.
| | 02:45 | And then finally we'd like to present this
new layout and new found set in a new window.
| | 02:49 | So really if you're a little bit
familiar with scripts, you know that scripts
| | 02:52 | have different steps, and what I just
rambled off is about three may be four
| | 02:56 | different script steps, which would normally
require that we go and generate a script for this.
| | 03:01 | But instead I'm going to go into
Layout mode and I'm going to show you one
| | 03:04 | simple script step that we can assign
to this button that would allow us to do
| | 03:08 | all of those things in one shot.
| | 03:10 | So let's select the button and go under
Format to Button Setup, and here we can
| | 03:16 | just assign a single script step to a
button or layout object. And we only have
| | 03:21 | one that we need, so we're going to go
to Go to Related Record, and now I'll hit
| | 03:24 | the Specify button, and we've got a
couple of decisions to make here.
| | 03:28 | First, we want to see related records from.
| | 03:31 | It's important to understand what that means.
| | 03:32 | So when we navigate over to a
different context, what is that different
| | 03:37 | context going to be? So in this case
we're going to be in the Orders table
| | 03:41 | and we want to see related records from
OrderLineItem. So parent Order, Child OrderLineItems.
| | 03:49 | Now we get to choose which layout we
see, and we're going to see a list of
| | 03:52 | layouts listed here that are all
based on the OrderLineItem table, and
| | 03:56 | there's only two of them, and we're
going to pick the one that we set up to
| | 03:59 | print out our order details.
| | 04:01 | And the reason that we've got it based
on OrderLineItem is so that we can have
| | 04:04 | one row for each OrderLineItem, and then
we borrow related values from the order
| | 04:10 | record, or the parent order record, to
display in the header and footer of that
| | 04:14 | layout, a pretty common
technique for printing related records.
| | 04:18 | We'll hit OK, and now we will also say, yes,
we'd like to show this in a new window,
| | 04:23 | and we'll call this Order Items.
And the window will be let's say 700 pixels
| | 04:32 | high, 500 wide, so it's smaller than
the one behind it, so we can see that
| | 04:37 | it's a pop-up window.
| | 04:38 | And this is a really important option
here, Show only related records; otherwise
| | 04:41 | it will just navigate us to the
OrderLineItem context and not show just the
| | 04:46 | records in the found set.
| | 04:47 | This is creating records in a found
set that are only going to be related to
| | 04:51 | this order that we're on.
| | 04:52 | So we hit OK there, and we hit OK again.
| | 04:56 | So in summary, this one script step
will open up a new window, navigate to the
| | 05:00 | order print layout, which is based on
OrderLineItem, and then show us related
| | 05:04 | records inside of the OrderLineItem
table, but only the related records related
| | 05:08 | to the order record that we're currently on.
| | 05:11 | It happens to be just showing us the same
data that we're seeing in the portal, and
| | 05:14 | this works inside of the script step
because we already have a relationship
| | 05:17 | between the Order Table and
OrderLineItems that's set up in our database; if not,
| | 05:21 | we could have just created one.
| | 05:23 | Let's go into Browse mode and take a look at
this one in action, and let's see what happens.
| | 05:28 | So here I am. On the first order I
see I have a series of related order
| | 05:32 | records, and I'll hit OK.
| | 05:35 | And so now it opens up the new window,
and we see that it contains the same
| | 05:40 | records - Snow Tire, Accessory,
Bike Helmet, Snow Tire, Accessory, Bike
| | 05:44 | Helmet, gel-settle, and it isolates
all of those for us, puts them in a nice
| | 05:48 | layout so that we can print these,
and you'll notice that if I go to a
| | 05:51 | different record--let's say this one--
| | 05:57 | it shows all those related
records instead, so it's dynamic.
| | 06:00 | And what's nice about it is that you can
have as many of these as you'd like.
| | 06:03 | No matter how many related records that
you've got, it'll expand--since we're in
| | 06:08 | a List view here, we'll look at Layout mode.
| | 06:11 | This is actually the context of
OrderLineItem and everything in the header are
| | 06:18 | related fields that are coming from customer.
| | 06:21 | So we're borrowing from the parent--
| | 06:23 | we're actually borrowing from our
grandparent in this case because we're saying
| | 06:27 | we're in OrderLineItems and we're going
up to the order and finding the customer
| | 06:31 | related to that order, so you see that
you can not only borrow from the parent
| | 06:34 | tables but also the grandparent tables.
| | 06:36 | Then we simply set up a body that shows
OrderLineItem fields peppered in with
| | 06:42 | some product fields.
| | 06:44 | Here's our OrderLineItem fields right
there, and then we put in a related record
| | 06:48 | that we're borrowing from the parent.
| | 06:49 | We borrow from the parent here,
grandparent here, parent here as well.
| | 06:53 | You can take a closer look at the
Order Print layout if you have access to
| | 06:57 | the exercise files.
| | 06:59 | So again there is a bunch of
interesting things that you can do with navigating
| | 07:02 | users to other layouts once you've
established relationships between tables;
| | 07:06 | allowing your users to print the
contents of a portal without having to
| | 07:09 | print the portal itself is simply one of them.
| | Collapse this transcript |
| Using portals to connect to data more than one table away| 00:00 | Thus far in our examples, we've worked
with showing child records in a portal in
| | 00:03 | the context of a parent.
| | 00:05 | For example, if you look on screen, we
are looking at a layout that's based on
| | 00:09 | the Customer table, and what we have got
here in the bottom are two portals that
| | 00:13 | are showing related Order records.
| | 00:15 | So the Customer table is the parent
table in this relationship, and the Order
| | 00:19 | table is the child table.
| | 00:20 | A lot of people like to call this
"looking at related records from one table away."
| | 00:24 | But when we were discussing the
concept of diagramming your relationships, I
| | 00:28 | mentioned how parent tables are not
only related to the child tables but are
| | 00:33 | also related to all linked tables
through a chain of relationships.
| | 00:37 | And if we go under the File > Manage >
Database, in the Relationships tab I can
| | 00:40 | show you visually how this works.
| | 00:42 | So we already know that the Customers table
and the Orders table are related to each other.
| | 00:47 | So of course, I can view all my
related order records in a portal.
| | 00:50 | But let's say in the context of a
Customer table I decide that I am going to put
| | 00:55 | a portal based on the
OrderLineItem record instead.
| | 00:58 | Well, what I'm actually going to see in
that case, are all OrderLineItem records
| | 01:03 | that appeared on an order that
was related to this customer.
| | 01:06 | So we can see related data that is more
than one table away, and you can go as
| | 01:11 | far down your chain of relationships as
you have relationships in your database.
| | 01:15 | So, for example, I could go all the way
to the end, where Products and Customers
| | 01:20 | are actually related to each other
through this chain, and I could say, instead
| | 01:24 | of showing me all products in a portal,
show me all products that appeared on an
| | 01:29 | OrderLineItem on an order related to a customer--
| | 01:33 | so essentially, all the different
products that they have ordered. And what
| | 01:35 | else is nice about this is that it will give me
a list of products without any duplicates in it.
| | 01:40 | As complicated as this concept might be,
the overhead is almost minimal because
| | 01:44 | we're going to use existing
relationships, and it's really easy to create a
| | 01:49 | portal in your layout.
| | 01:50 | You go into Layout mode and you
click on your Portal Drawing tool, so
| | 01:57 | really not having to create any
relationships, and simply choosing Products in
| | 02:02 | this example, hit OK, and let's simply just
hit Product Name, and we will move that over.
| | 02:10 | And just to reiterate the point here, we
are looking at a table that is actually
| | 02:14 | four tables away, but it's
still related to the Customer table.
| | 02:19 | And when we go into Browse mode, we would be
able to see that little bit more dramatically.
| | 02:22 | So now we are on customer number one,
and here are all the different products
| | 02:26 | that this customer has ordered.
And there are new duplicates, even they have
| | 02:29 | ordered 50 Bike Helmets and 20 SpeedGel Saddles.
| | 02:32 | It tells us that they've
ordered at least one of these things.
| | 02:35 | So that the fact that there's
actually values showing up in the Products
| | 02:38 | portal means that a product has
appeared as an OrderLineItem on an order
| | 02:43 | related to this customer.
| | 02:44 | If the customer has no
orders, nothing will show up.
| | 02:46 | If the customer has Orders but don't have
any OrderLineItems, nothing will show up there.
| | 02:51 | So the entire chain has to have related
records all the way through it in order
| | 02:54 | for this data to appear, but that
will be logical based on the data that's
| | 02:58 | entered into the system as well.
| | 02:59 | So going back to our original customer
record, this basically means that we can
| | 03:03 | view related data in our portals and
related fields can give us an entirely
| | 03:07 | different way to visualize the related
data in our database, but with absolutely
| | 03:11 | no programming overhead.
| | 03:13 | These are the types of value-added
possibilities that are built right inside
| | 03:16 | your database just because you
decided to use related tables.
| | 03:19 | And this can be rather useful, for
example, if you wanted to go into Find mode
| | 03:26 | and do a search in this related portal.
| | 03:28 | So let's say I want to search for
a product and I hit Perform Find.
| | 03:34 | So what this has actually done is
created a found set of records, so it's
| | 03:37 | telling me that 21 out of 22 customers
have ordered the Bonsai toolset before.
| | 03:42 | So if I want to do an incentive program,
or we are doing a sale on these things,
| | 03:46 | I can use that portal that's more than
one table away to help me do searches or
| | 03:50 | just display data inside of the
context of our customer layout
| | 03:54 | that can help me find out who has
ordered what and see if any of these products,
| | 03:57 | let's say aren't that popular or some
of them are very popular, I can find out
| | 04:01 | which customers are buying them.
| | 04:03 | So setting up a proper relationship
structure in your files can help you view
| | 04:06 | related child records, but it also
gives you visibility into any related table
| | 04:11 | inside your database, all of this
without having to make any special
| | 04:14 | relationships and almost no overhead.
| | Collapse this transcript |
| Creating self-relationships| 00:00 | We've been looking at various ways to
visualize and work with data from other
| | 00:03 | related tables in a properly
architected relational FileMaker database,
| | 00:07 | and in this movie, we're going to
discuss how you can actually view related
| | 00:11 | data, but from the same
table that you are currently in.
| | 00:13 | So unlike other relationships where we focused
on putting a portal, or a related field,
| | 00:18 | on a layout and having to pay special
attention to the context of the layout as
| | 00:22 | well as the context of the data in the
portal-related field, here we're going to
| | 00:26 | put a related field and a portal on a
layout where a layout and the related field
| | 00:31 | have the same context.
| | 00:33 | Let's first create a story behind this exercise.
| | 00:35 | In this case, let's say we're going to
put a field on each customer record right
| | 00:39 | over here where it says Referred By,
and in that field we're going to store the
| | 00:43 | ID of the customer who referred
the customer record that we're on.
| | 00:46 | So if such and such organization
refers another customer over to our company
| | 00:52 | then we're going to put the ID of
the referring account into that field.
| | 00:56 | So something like this can allow
you to do referral programs and pay out
| | 00:59 | commissions and do different functions
without having to add any new tables.
| | 01:03 | So first we're going to need a field
where we would store that ID, and this
| | 01:07 | field will actually act as a foreign key in
this new relationship we're going to create.
| | 01:12 | So let's go into the File > Manage >
Database > Tables tab and inside Customers
| | 01:21 | what we're going to do is create a
foreign key for relationships from customer
| | 01:26 | ID back to the customer table itself.
| | 01:29 | In this case, we're going to call it
the Referral ID and as with any other
| | 01:33 | foreign keys, we will do these single underscore.
| | 01:39 | So we've created a field, and it's
going to be a text field. We simply hit the
| | 01:42 | Create button, and now we've added a
foreign key field to the customer table.
| | 01:47 | Ignore for a moment that this is the
same table that's going to contain the
| | 01:50 | primary key field in this relationship.
| | 01:52 | We'll get to that in a second.
| | 01:54 | So now we'll hit OK, and in Layout
mode let's add a field, and we'll
| | 02:06 | choose _ReferralID.
| | 02:08 | Line it up a little.
| | 02:10 | So now what we're going to do is allow
users to populate this field with the ID
| | 02:15 | of the customer record who referred them.
| | 02:17 | To make it a little bit easier, let's
go over to our Inspector and instead of
| | 02:21 | having an Edit box, let's call this a
Pop-up menu, and we'll choose a value list that's
| | 02:26 | already been set up in our system that
shows all of the primary key values from
| | 02:33 | the Customer table, along with the
Company name, so that users know which primary
| | 02:38 | keys go with which customers.
| | 02:41 | So, simply adding that pop-up menu
control style to the existing referral field
| | 02:47 | will then allow us to go through,
record by record, and assign referral values.
| | 02:54 | So let's say we pick a few different
records in here, and they're all referred to
| | 03:02 | us by the BooJax Corporation, just so
we have some related data to work with.
| | 03:08 | And you could pick a series of
different ones if you'd like to.
| | 03:12 | So now we have a field that
allows us to store who referred whom.
| | 03:16 | And in this story we're also going
with the assumption that only one other
| | 03:20 | customer can refer a customer,
| | 03:22 | so that's why we're using a field
where only one value can be stored.
| | 03:25 | This makes the customers the parent
and in this concept of referrals that
| | 03:30 | becomes our child, even though they are
still going to be based on the same table.
| | 03:34 | This means that the customers must have
both a primary key to uniquely identify
| | 03:39 | each record, but must also have a
foreign key added that will be populated only
| | 03:43 | when they've been referred.
| | 03:45 | Let's go into the Manage Database window and
take a look at this a little bit more visually.
| | 03:51 | Go into the Relationships tab,
and what we want to do is create a new
| | 03:54 | relationship and like any other of these
query type of relationships that we create,
| | 03:59 | we're going to go down to the bottom
left-hand corner and create a new table
| | 04:03 | occurrence, but this time if you think
about what we want to see inside of a
| | 04:06 | portal, for example, it's
going to be Customer records,
| | 04:09 | so we're going to base this
relationship on the Customer table. And we'll still
| | 04:14 | use our same naming convention,
customers_REFERRALS, and we'll place that over
| | 04:25 | here, give it a little bit different color.
| | 04:30 | And now what we're going to do is, if
we think this through is in a portal
| | 04:34 | that we would place on the customer table,
it's what data do we want to see in the portal?
| | 04:38 | It's always a good way to think about
what your match fields are going to be.
| | 04:41 | So we'll say that we want to see
all of the customer records that were
| | 04:44 | records that are the referrals for the active
customer record that we're on at the moment.
| | 04:50 | So in order to do that, what we're
going to do is use the Customer ID as the
| | 04:55 | primary key, but instead we're
going to use a different field.
| | 05:00 | Sometimes it's tricky to
scroll through all these.
| | 05:02 | I'll show you a little trick.
| | 05:04 | If you just pick any field and then
double-click on the equal sign, now you can
| | 05:08 | use a little bit easier interface
to pick the one that you'd like.
| | 05:10 | So we'll go CustomerIDpk in the
customer table to the _ReferralIDfk also in
| | 05:17 | the customer table.
| | 05:18 | It doesn't matter that they're in the
same table; we're still able to create a
| | 05:20 | relationship between these two.
| | 05:21 | I'm going to hit Change,
and this is all we need.
| | 05:24 | We need the CustomerID=ReferralID,
and now when we hit OK, close that window out,
| | 05:31 | and we see we've got a tab here for Referrals.
| | 05:34 | I'm going to go into Layout mode, click in the
Referrals tab, and I'm going to add a Portal.
| | 05:40 | What I want to see here, all the different
customers that I've referred if I'm this customer.
| | 05:45 | So I'm going to choose the new
relationship we just set up called
| | 05:49 | customers_REFERRALS,
| | 05:50 | do a little vertical scrollbar, hit OK,
and I'm just going to pick the Company
| | 05:56 | name, and now we hit OK.
| | 05:58 | Let's see what we get
when we go into Browse mode.
| | 06:02 | So now if I go find the record that
we used for all the referrals and click
| | 06:10 | over into the Referrals tab, now you see
that it gives me a list of 1, 2, 3, 4,
| | 06:14 | 5 different records.
| | 06:16 | And so now we can do some searches
inside our database if we want to.
| | 06:20 | Go in here and use the Search Operator,
like the Asterisk tool, to tell me if I
| | 06:24 | have any values in any fields.
| | 06:27 | Do a search there and it shows me that
only one record out of all of these 22
| | 06:31 | has even done any referrals, and it
shows me which referrals those are.
| | 06:34 | Now then also by having this foreign
key value on each record we can then
| | 06:40 | navigate one at a time to see who referred
each customer that's inside of our database.
| | 06:45 | So relationships can give us the
opportunity to view data in other tables
| | 06:49 | through the base relationships,
but they can also give us some compelling ways
| | 06:52 | to link and view records that
are stored within the same table.
| | Collapse this transcript |
|
|
8. Relationships in CalculationCreating aggregate functions using relationships| 00:00 | So far in this title we've looked at
how to create relationships to establish
| | 00:03 | the architecture of our database and
then how to use those relationships, or
| | 00:07 | even create specialized ones, to use
with layout objects like related fields and
| | 00:11 | portals in our layouts. We've also looked at
incorporating these relationships into our scripts.
| | 00:16 | In FileMaker our architecture combined
with our scripts are a large part of the
| | 00:20 | schema of our database,
| | 00:21 | but one important area of the schema that
we have not yet looked at are calculations.
| | 00:26 | Calculation fields and calculation
dialogs are found throughout the FileMaker
| | 00:30 | interface, and they are there to add
another dimension to your FileMaker solution--
| | 00:34 | and related fields can be used
within those calculation dialogs.
| | 00:37 | This can be a very powerful way to
leverage relationships to aggregate related
| | 00:41 | data throughout your database.
| | 00:43 | Let me show you a couple of examples.
| | 00:44 | These are easy to do and they can give you a
lot of value back with very little overhead.
| | 00:49 | In the first example what I am going to
do is create a calculation for subtotal
| | 00:53 | that dynamically totals up all of the
different related extended price values.
| | 00:58 | So no matter how many different
related records there are, it will go and
| | 01:01 | add all those up, and then that can
be displayed as calculation result
| | 01:05 | inside the Subtotal field.
| | 01:07 | So let's go into File > Manage >
Database, and let's find the Subtotal field.
| | 01:15 | You see we've got Subtotal. It's set
up as a calculation, but it doesn't have
| | 01:18 | a formula in it yet.
| | 01:19 | So let's just go ahead and hit Options,
and we'll get rid of the two quotes and
| | 01:23 | this field is intended to give us
the total of the extended price.
| | 01:27 | Now, assuming that you're familiar
with calculations already, you know that
| | 01:30 | calculations are fields that you don't
enter data into, but instead provide a
| | 01:34 | formula to FileMaker, so that
FileMaker can go gather the appropriate
| | 01:38 | information and then display the
result based on your instruction.
| | 01:42 | Any field that you place in a
calculation formula acts as a placeholder, meaning
| | 01:47 | that FileMaker will process these
instructions in the formula for every record
| | 01:51 | in your table but will use the data in
the fields referenced as it processes.
| | 01:55 | So for example, if I were to just put
the Order ID into my calculation, it's
| | 02:00 | not going to actually
result in the word Order ID;
| | 02:03 | it's going to result in whatever the
value is that's stored inside the Order ID
| | 02:06 | field. And a calculation gets processed
for every record in your table, so really
| | 02:11 | these fields are just placeholders for
the data that's stored within a field.
| | 02:14 | Just like you can use related fields
inside of a layout, you can also use them
| | 02:18 | in a calculation dialog.
| | 02:20 | But just like a layout, the context
of the calculation will depend on what
| | 02:24 | values you get inside of those fields.
| | 02:25 | So you may not have noticed, but up on
the very top of this field we've got a
| | 02:29 | dropdown menu that lets us choose
different contexts to use for a relationship.
| | 02:33 | So the idea of context when we're
looking at a layout and determining what
| | 02:37 | related portal we want to put on it,
for example, tells us from what context
| | 02:41 | we're looking and what data we're going to see.
| | 02:44 | So this is where we choose from what
context we're looking at, and you see here,
| | 02:48 | here's all the relationships that are
based on the Order table, and we're going
| | 02:51 | to stick with the Orders one as the default.
| | 02:54 | So that tells us that we're
defining this field inside of Orders.
| | 02:59 | That's going to be our context and
any related field we put in here--
| | 03:02 | if we look at our Manage
Relationships, we can see this visually--
| | 03:07 | if we base it on orders, that means that
if we put any related fields from order
| | 03:11 | line items, those are going to be the
child, and if we put any fields from the
| | 03:16 | products table, those will be
the grandchild in this case.
| | 03:19 | So we'll have many related records if we
use related values from either of these
| | 03:23 | tables. But if we put the customer
field inside of a calculation that has an
| | 03:28 | Orders context, it's only going to
have one related value. So anytime we go
| | 03:32 | upstream, so to speak, there will only
be one related value; and anytime we go
| | 03:36 | downstream in the relationship,
there will be multiple related values.
| | 03:39 | So back in this context what we want to
do here is we want to simply summarize
| | 03:43 | all of the extended price
fields from one of the child tables.
| | 03:47 | So as you see, we can click on the
current table where it says Orders and we can
| | 03:53 | choose a different related table
and fields from that related table.
| | 03:57 | So here you see OrderLineItem,
and we've got the Extended Price field.
| | 04:00 | But what I'd like to do is
total up all those values,
| | 04:03 | so I am going to go under the function
listing here on the right-hand side and I
| | 04:07 | am going to choose Aggregate functions
and I am going to double-click on Sum.
| | 04:12 | So in the case of the Sum function
it gives some parameters inside the
| | 04:15 | parentheses here that are supposed to
act as sort of helpers so that you know
| | 04:18 | what kind of fields to put here.
| | 04:20 | And what this one is asking us to do
is pick a semicolon-delimited list of
| | 04:25 | different fields so that it can
summarize all the values within those fields.
| | 04:29 | Now, you could put in a bunch of
different fields that are in the Order table
| | 04:32 | and it would just go and add all those up,
| | 04:34 | but nine times out of ten you'll be
using these aggregate functions to
| | 04:38 | aggregate related data.
| | 04:40 | So by simply putting a related field,
let's say Extended Price, into the Sum
| | 04:44 | function as the single parameter,
instead of this field just being a
| | 04:48 | placeholder for one field value,
it's actually a placeholder for every related
| | 04:53 | record that you may have.
| | 04:54 | So if this order has ten different
order line items on it, this is actually
| | 04:58 | going to summarize an array of ten
extended price values, and that's going to
| | 05:02 | change for every record.
| | 05:04 | So this really is a placeholder for
an array of related values. And this is
| | 05:08 | a very popular trick that FileMaker
developers use, simply putting one
| | 05:12 | single related field in there in
between the parentheses instead of having
| | 05:15 | several individual ones.
| | 05:17 | Now keep in mind that this technique
will only work when you're defining a
| | 05:20 | calculation in the parent table.
| | 05:22 | If you try this on the child table
with a related parent record, you'll only
| | 05:25 | ever have one related record anyway,
so the sum doesn't even make any sense.
| | 05:29 | Now if you remember, Order is the
parent in this relationship with
| | 05:32 | OrderLineItem, so this will make sense.
| | 05:35 | This is all you need to do inside your
Calculation window, and you've got to make
| | 05:38 | sure that your calculation result is a
number. And we'll hit OK and hit OK again,
| | 05:44 | and now you see our value is
adding up all of the different related
| | 05:48 | OrderLineItem records and presenting
that value to us inside the Subtotal field,
| | 05:53 | and that changes from
record to record within our customer database.
| | 05:58 | Let's just do one more really quick
example in another table, just to make
| | 06:02 | sure this makes sense.
| | 06:03 | Let's go under the Customer table,
and remember, we have all the
| | 06:06 | different referrals here.
| | 06:08 | Now personally, when I am developing
databases I like to create aggregate
| | 06:11 | calculations with related field
references for nearly every portal that I
| | 06:14 | display on a layout.
| | 06:15 | I happen to find that they
make sense in a lot of cases.
| | 06:17 | It's not a rule by any means, but
I just think it's a good practice.
| | 06:21 | It's another one of those areas
where since you already have the
| | 06:24 | relationships in place,
| | 06:25 | why not use them to provide you data
that you won't normally have if you were
| | 06:28 | just simply using a flat file?
| | 06:30 | So let's say, for example, that we
wanted to know the total number of referrals
| | 06:33 | for each customer record.
| | 06:34 | This could, let's say, help us with an
incentive program that we're running.
| | 06:37 | So if we go into the File > Manage >
Database, we see in the Relationships we
| | 06:43 | already have a customer
referral relationship set up.
| | 06:47 | It's a self-join table with the
relationships between customers and customers,
| | 06:53 | which is currently displaying all
the referrals for any given customer.
| | 06:58 | So these are all the different
customer records that were referred to the
| | 07:02 | company using the database
by the BooJax Corporation.
| | 07:06 | But let's say we have an incentive
program that says that anybody who has
| | 07:09 | more than three referrals gets a
certain discount on their next order,
| | 07:12 | something along those lines.
| | 07:14 | Then we would certainly want to have a
field inside the customer table that told
| | 07:18 | us the total number of referrals.
| | 07:20 | So let's create one of those.
| | 07:21 | We go File > Manage > Database, go
into Fields, and we're going to find that
| | 07:26 | field inside Customers, and we're
going to call it TotalReferrals.
| | 07:33 | Let's make that a calculation, hit
Create, and we're going to keep it the context
| | 07:40 | of Customers, and let's go down to the
Aggregate functions. And instead of Sum,
| | 07:44 | let's just use Count, and this
function is similar to the Sum function, but
| | 07:47 | instead of totaling up the numeric
value in each related field within your
| | 07:51 | array, it's actually going to count all
non-empty related fields and give us the
| | 07:56 | total number of those non-empty related fields.
| | 07:58 | So let's first put a related
field inside of this as a parameter.
| | 08:02 | Let's choose customer_REFERRALS.
| | 08:05 | Now what we want to do when we're using
the Count function is we want to pick a
| | 08:08 | field to draw our array where that
field is always going to be populated if
| | 08:14 | it's a related record.
| | 08:15 | So what field do we know will always
have a value in it in all related records
| | 08:20 | for customer_REFERRALS?
| | 08:21 | Well, we know that's the foreign key field.
| | 08:23 | So another thing I like to do when I
am developing databases is always choose
| | 08:27 | the foreign key fields, the related
foreign key fields I should say, inside of my
| | 08:32 | Count functions for aggregating related records.
| | 08:35 | So in this case it should look like
Count (customers_REFERRALS::_ReferralIDfk).
| | 08:40 | We see that the table name indicates
that this is a related value, and so now we
| | 08:45 | have this as a number.
| | 08:47 | I hit OK, I say OK again, and you go
into Layout mode and simply drag a field
| | 08:55 | below your portal, scroll down to
TotalReferrals, and now with very little
| | 09:02 | programming you've got an aggregate
function that will go through each record in
| | 09:08 | your database and give you a total
count of all the different related referral
| | 09:13 | records. And this is something
that you can search inside of,
| | 09:16 | so let's say I want to do everybody
greater than 3 referrals qualifies for our
| | 09:22 | referral discount, and I hit Perform
Find and I find out that I have one record
| | 09:27 | in my database that matches that.
| | 09:28 | Using related fields in your
calculations, especially if you aggregate these
| | 09:32 | calculations in the context of a parent,
can provide some very useful and dynamic
| | 09:37 | values, again, with very little overhead.
| | 09:39 | These fields can then be placed in your
layouts as summaries or related values in portals.
| | 09:44 | They can even be used in fine
criteria just like any other field.
| | Collapse this transcript |
| Using the List function| 00:00 | The List functions is one of my
favorite functions in FileMaker.
| | 00:03 | It's a simple function that
does just what its name indicates,
| | 00:05 | it produces a list.
| | 00:07 | But when you combine that with other
functions in FileMaker calculation dialogs,
| | 00:10 | it can become very powerful.
| | 00:12 | Let we show you a list function and
take a look at the value it produces, so we
| | 00:16 | can kind of start there with your
understanding of how this can help enhance
| | 00:20 | your FileMaker database.
| | 00:22 | We will go under the Manage Database
dialog, under Fields, and under Customers,
| | 00:27 | we are to create a New Field called OrderList.
| | 00:30 | Let's make this one a calculation so that we
can use Calculation function, and hit Create.
| | 00:36 | The list function is an aggregate
function found in the Aggregate function
| | 00:40 | group, and much like the other
aggregate functions, the parameters that it has
| | 00:45 | are just a series of different fields
inside the parentheses. And since all it's
| | 00:49 | doing is creating a return-delimited
list of values, it doesn't really make much
| | 00:54 | sense to just put a bunch of local
fields in here, since really you could just
| | 00:59 | create a calculation that lists all
these different fields concatenated
| | 01:03 | together with return characters.
| | 01:05 | So the true power of the list function
comes when you put a related value in
| | 01:10 | between the parentheses, in order for
it to process, and of course only a
| | 01:14 | related value that comes from the
child table in a parent-child relationship.
| | 01:19 | So the child table to the customer
table is the Orders table. And let's go down
| | 01:26 | here and pick Sale Date.
| | 01:29 | So now what we've instructed it to do
is find all of the related order records
| | 01:34 | and extract only the sale date from
them, and display those in a returned-
| | 01:38 | delimited array of related information.
| | 01:41 | Now you think that we would put dates
because of the date field, but instead I
| | 01:44 | am going to suggest that you choose
Text, because I want to show you another
| | 01:48 | function that needs it to be text later
in this exercise. So we'll hit OK, hit OK
| | 01:53 | again, and now let's go to Layout mode
and just add this list function to our
| | 01:58 | layout so we can see what it does,
before we move forward with our exercise.
| | 02:03 | So here's the Order list. And since
it's going to be a returned-delimited list,
| | 02:11 | we will make it little bit larger,
and we will get rid of that bold and the
| | 02:15 | underline, and we'll save our changes.
| | 02:20 | And in Browse mode now we see,
looking at a List Function right next to a
| | 02:25 | portal with the same related value,
| | 02:26 | you see that they are listed in the same
order and have the same number of values.
| | 02:31 | So really it's almost like a text
version of a portal showing one of the values
| | 02:36 | from within that portal.
| | 02:37 | It is like extracting a column, and
these values change as we go from customer
| | 02:40 | record to customer record.
| | 02:42 | Nothing too dramatic here, but this
could be helpful to you in its existing
| | 02:46 | form. For example, if you want to print
all of the related values in the context
| | 02:49 | of a parent, without having to print a
portal you can combine that with Sliding
| | 02:54 | and Printing Layout options, and this
could allow you to print related values
| | 02:56 | without the hassle of a portal.
| | 02:58 | But the real values are when we
combine this list, or this array of related
| | 03:02 | values, with other functions inside of FileMaker.
| | 03:05 | So before we proceed with this exercise,
I want to have you pay special attention
| | 03:09 | to the order in which these dates are listed.
| | 03:13 | You see that they are the same order
that the portal uses, and the portal
| | 03:17 | doesn't have a Sort Order specified in it,
as we can see by going under Manage >
| | 03:23 | Database > Relationships,. If we
double-click on the equal sign between the two
| | 03:27 | related tables, that brings us to our
Edit Relationship dialog, and we see that
| | 03:32 | we don't have the Sort Records option
selected, so then what FileMaker does is
| | 03:38 | it chooses by default the order in
which the records were created. So both in
| | 03:42 | the portal that we're looking at and in
the list array that we've created with
| | 03:46 | the related values, they are
listed in the order they were created.
| | 03:50 | But let's mix this up a little bit.
| | 03:51 | Let's say that we want to sort the
related records by the sale dates in
| | 03:55 | descending order. We will choose
Descending, and now this is going to show
| | 04:02 | us the most current date on top, and on the
very bottom of the list, show you the latest date.
| | 04:07 | So what this means is that the most
current order will show up at the top of our
| | 04:10 | portal and on our list view, and the
reason for that is the relationship that
| | 04:14 | both of those objects are
based on has this Sort value in it.
| | 04:19 | So anything that references this
relationship will now have a new sort value,
| | 04:22 | but we see that the most recent
versions are on top, and this is good
| | 04:25 | because what if we could just skim off
the top of our list the very top date?
| | 04:30 | That would mean that we could have a field
that would tell us what the last order date is.
| | 04:34 | So let's go into our Manage Database
again, into our Tables and Customer
| | 04:40 | tables, and let's create a
Field that's called LastOrderDate.
| | 04:46 | And we will make that Calculation.
| | 04:48 | Now I am going to show you a different
Calculation function that extracts values
| | 04:54 | from an array of different values,
and this one is called the GetValues
| | 04:59 | function. And if you scroll down into
your normal list, you'll see that you've
| | 05:03 | got several different functions that
starts off with Get, but we'll take
| | 05:06 | GetValue, and it has two parameters.
| | 05:09 | It says give me a listOfValues and then
tell me which Value you want out of that list.
| | 05:14 | And what's interesting about having our
OrderList field already defined is that
| | 05:20 | this is in fact a list of Values.
| | 05:23 | So we put the Order List in there and
it acts as a placeholder for our list,
| | 05:27 | then we can say which
value out of those we want.
| | 05:29 | And if we say number one, it takes the
top; if we say number two it takes second
| | 05:33 | one down; and so on; and so on.
| | 05:35 | So I am going to say number 1, I am
going to make this a Date, and if we hit OK,
| | 05:40 | and Ok again, go into Layout mode, and
instead of having all of the orders on
| | 05:46 | there, let's just say that we pick our
new field, add it to our Layout, and now
| | 05:56 | let's look at this in Browse mode.
And what it does is it goes through and
| | 06:02 | creates a list based on the Sort Order
set up in the Relationship, and it skims
| | 06:06 | the top Date value off of the top of that list.
| | 06:09 | And since we purposely made the Sort
value have the most recent order on top,
| | 06:14 | we know that we can faithfully extract
the value off of the top of our Order
| | 06:17 | List field, and that
gives us the last order date.
| | 06:21 | And in addition, this field will
automatically update every time a new order is
| | 06:24 | created and will display the
most recent order at any given time.
| | 06:28 | We can search in this field by simply
entering a year, for example, let's say
| | 06:34 | last year, and what that
can do is give me a found set.
| | 06:39 | Let's see, I have got 1 record in my
Customer table, and basically this tells me
| | 06:43 | that this is a customer that hasn't
ordered anything from us in the last year.
| | 06:47 | So the list function provides a great
way to isolate a list of related values in
| | 06:51 | the form of an array, and that array,
or list, can be used for printing in the
| | 06:55 | parent context, but when it's combined
with other functions inside FileMaker, it
| | 06:59 | can give you some really useful ways
to display and work with related data.
| | Collapse this transcript |
|
|
ConclusionNext steps| 00:00 | I hope you enjoyed Relational
Database Design with FileMaker Pro.
| | 00:03 | Understanding relationships in
FileMaker is really the key to taking your
| | 00:06 | programming skills to the next level.
| | 00:08 | There is a lot of important concepts in
this title, so if you are not sure about
| | 00:11 | anything that I've covered in this course
then feel free to come back and check
| | 00:15 | out these chapters for a refresher.
| | 00:17 | If you're looking to brush up on
your FileMaker skills, I'd encourage you
| | 00:20 | to check out the FileMaker Pro
Essential Training title on the lynda.com
| | 00:23 | Online Training Library.
| | 00:24 | So, good luck architecting your
databases, and we'll see you next time.
| | Collapse this transcript |
|
|
