Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Using related fields

From: FileMaker Pro 11 Essential Training

Video: Using related fields

In an earlier chapter we discussed how to set up relationships in your FileMaker database. It's actually one of the most important components to setting up a relational database system. So if you haven't watched those videos yet, I strongly recommend you go back and watch them before you proceed with this chapter. Just a little note. Once you have established a relationship inside your FileMaker file, then you have the benefit of being able to start working with some of the relationship based functions in FileMaker, and there are a lot of them. We are going to cover each one of them in this chapter. The first one of these functions that we are going to look at is the concept of using related fields.

Using related fields

In an earlier chapter we discussed how to set up relationships in your FileMaker database. It's actually one of the most important components to setting up a relational database system. So if you haven't watched those videos yet, I strongly recommend you go back and watch them before you proceed with this chapter. Just a little note. Once you have established a relationship inside your FileMaker file, then you have the benefit of being able to start working with some of the relationship based functions in FileMaker, and there are a lot of them. We are going to cover each one of them in this chapter. The first one of these functions that we are going to look at is the concept of using related fields.

If you recall from our discussion on relationships, each relationship between two tables in your database must have one parent and one child. Every relationship is going to be one-to -many relationship, so the child record is on the many side of the one-to-many, and the parent is on the one side. So in our example, as you see onscreen, I have got an Invoice detail record and the relationship between customers and invoice is one customer can have many invoices so that customer is the parent, which is on the one side, and the invoice is the child, which is on the many side.

So right now our context is in the child in the relationship. So once we have a relationship like this in place, which we do here in our example file, it's possible to use a relationship to look at data from one table from the perspective of another. So, for example, if I go into the Invoice Detail layout, you will notice that each of these records represents an invoice or an order. So we have got some information that lives in the order level, like the status of the order, the invoice date, the sales person who generated this, their commission, and some other information about subtotals and rates and that type of thing.

We also notice that we have got a field here that's called Customer ID and inside this field, contains the ID of the parent customer. So what that means is that our tables have a relationship set up, but this record actually has a related parent because its foreign key field is populated. And now you think yourself, well this is an order record. So it would kind of make sense that we had some fields that pertain to the customer somewhere on this layout. So, for example, you might want to go to an invoice record and see what company it's for, what their address is, shipping information, that type of thing.

Well, we could easily just go into the Invoice table and define a bunch of field to that are called company name, company address, company city, company state. However, that process wouldn't be efficient because we'd go in and create these fields that already exit in our solution somewhere else, but the real problem is we would create a risk of redundant data. So what if somebody who is writing up an invoice saw that the customer's address had updated. So they will go and update the fields that are in your Invoice table, the ones I would propose that we could have created.

But that's not going to go link back to the parent table. They're just going to be the same address fields. So it would make sense to do that, and that's just a small example of some really big damage and confusion that you can cause if you don't just leave the fields defined within their appropriate table. So the solution to all this is to store one type of information in only one place. In this case, store customer data in the Customer table. This is one of the primary reasons that you would work with the relational database system like FileMaker instead of something like a spreadsheet, for example. So how do we get around this issue if we actually want to have customer information on a layout that's based on Invoice? Well, this is one of the great benefits of having tables related to each other and of course we know that the Customer is related to Invoice.

Since the Invoice is the child in this table, it can pull data from any of the fields in the parent record. So, let me repeat. Because this is the child in a parent-child relationship or one-to-many relationship that means that we can place fields from the parent on this layout. And the reason we can do that is because there is only one parent. So we know exactly what data would show up in that field. I like to refer to this concept as borrowing from a parent. So, if we go back into Layout mode, let me show you how easy it is to do this.

So, let's grab from our toolbar here the Field tool, and we will drag that down to the Email and release, and now instead of choosing a field from the current table, which is the default, notice what happens when I click that dropdown. You see I have got all the other related tables and then the unrelated tables. Now, I can choose any one of these related tables and borrow fields from them, and then the fields that I place on here will display the data that's actually stored back in that parent record. I can't do that for anything that's listed under Unrelated, but we don't have any of those in the solution.

So let's pick Customers, and I will pick AddressEmail. So, now we see the AddressEmail field entered and what's interesting if you look real closely is that we have got two colons that precede the name of the field. That way visually you can see the difference between a native field for this table and a related field. Now, I am going to hold down my Option key in Mac and drag in the field, which is going to allow me create a new field. And you see it's already going to the Customer context. I am going to pick CompanyName, and I am just going to do a couple of more of these again, holding down the Option and dragging, Street, City, State, almost done, and Zip.

So, now you see they all are indicated as related fields and I didn't have to define any of these inside the Invoice table. So, let's go in the Browse mode and take a look at what happens. Save our changes. And now you see what we have got here is data that's coming in, but where is that data coming from? Well, let's go into Customer detail, we can find all Radley & Friends contacts, and we see we have got Boo Radley from Montverde, Arkansas, and if you go into the Invoice detail, you see, yeah Montverde, Arkansas ordered by Boo Radley.

So, now what happens if we change the parent? So if we go in and pick really any one of these, let's say Pernell Inc., now all this information changes. That's because I just changed the foreign key field again. This is all relationship stuff, but if I changed the foreign key field value to another parent ID. That means now these fields are going to be related to another parent record. So now you see them all update. So this is a great way to populate field or display field or even print values in fields without having to define them inside of your table.

So having a relationship between two tables means that you can share data both upstream from the parent, because there will just be the one related parent record and also downstream to the many related records in a child table. This allows you to have one location to store a piece of data and that one piece of data can then be shared throughout the database via these relationships that you are going to set up.

Show transcript

This video is part of

Image for FileMaker Pro 11 Essential Training
FileMaker Pro 11 Essential Training

94 video lessons · 14980 viewers

Cris Ippolite
Author

 
Expand all | Collapse all
  1. 7m 22s
    1. Welcome
      1m 8s
    2. Understanding the FileMaker family
      2m 15s
    3. Using the FileMaker Quick Start screen
      2m 52s
    4. Using the exercise files
      1m 7s
  2. 21m 9s
    1. What is a database?
      2m 26s
    2. Flat vs. relational databases
      2m 29s
    3. How FileMaker works
      4m 48s
    4. Understanding the essential preferences
      3m 13s
    5. Touring the interface
      8m 13s
  3. 11m 44s
    1. Creating databases from templates
      2m 29s
    2. Creating new databases in the spreadsheet-like format
      5m 35s
    3. Importing tables
      3m 40s
  4. 9m 9s
    1. Determining which tables you will need
      5m 10s
    2. Creating tables in the Managing Tables window
      3m 59s
  5. 34m 47s
    1. Understanding relationship types
      6m 58s
    2. Diagramming relationships (with ER diagrams)
      8m 50s
    3. Determining which key fields you need
      5m 18s
    4. Defining primary and foreign keys
      7m 56s
    5. Creating relationships using the relationships graph and table occurrences
      5m 45s
  6. 50m 34s
    1. Deciding what fields you will need
      5m 34s
    2. Understanding field types
      7m 54s
    3. Defining fields
      9m 56s
    4. Using Auto Enter options in fields
      9m 33s
    5. Reviewing field validation options
      8m 16s
    6. Building with container fields
      9m 21s
  7. 14m 8s
    1. Creating and duplicating records
      5m 40s
    2. Editing and locking records
      4m 42s
    3. Deleting records and backing up files
      3m 46s
  8. 21m 6s
    1. Importing data
      9m 57s
    2. Importing records to refresh data
      7m 2s
    3. Exporting data
      4m 7s
  9. 18m 30s
    1. Understanding layouts
      4m 15s
    2. Using the Layout Assistant to create List views
      7m 59s
    3. Using the Layout Assistant to create labels and envelopes
      6m 16s
  10. 27m 11s
    1. Using the Layout Setup dialog box
      4m 54s
    2. Understanding layout parts
      4m 40s
    3. Understanding the new Inspector
      2m 26s
    4. Exploring the Status Area in Layout mode
      6m 46s
    5. Managing layouts and layout folders
      8m 25s
  11. 41m 9s
    1. Arranging, aligning, grouping, and locking layout objects
      11m 5s
    2. Placing and formatting objects, parts, and graphics
      4m 10s
    3. Formatting fields and applying field attributes
      8m 26s
    4. Setting field behaviors
      4m 4s
    5. Using the Tab Control feature
      9m 8s
    6. Setting tab order
      4m 16s
  12. 36m 48s
    1. Using the basic find functions
      7m 31s
    2. Reviewing new requests in the Find mode
      5m 54s
    3. Establishing search operators
      6m 43s
    4. Constraining or extending found sets
      3m 24s
    5. Finding records using date, time, or timestamp criteria
      5m 18s
    6. Using Fast Match and Quick Find
      4m 41s
    7. Working with saved finds
      3m 17s
  13. 17m 28s
    1. Sorting with one criterion
      6m 4s
    2. Sorting with related fields
      2m 18s
    3. Sorting with multiple criteria
      1m 36s
    4. Sorting using custom values
      3m 14s
    5. Sorting using buttons
      4m 16s
  14. 17m 14s
    1. Reviewing Field/Control styles
      5m 43s
    2. Creating and applying static value lists
      5m 20s
    3. Creating and applying dynamic value lists
      6m 11s
  15. 23m 52s
    1. Previewing pages and print options
      6m 20s
    2. Printing in different views
      2m 54s
    3. Sliding objects
      3m 26s
    4. Printing merge letters
      4m 53s
    5. Saving as a PDF or Excel file
      6m 19s
  16. 15m 1s
    1. Building simple reports with summary fields
      4m 36s
    2. Creating subsummary reports
      6m 51s
    3. Creating subsummary reports in Table view
      3m 34s
  17. 52m 19s
    1. Defining calculations
      2m 31s
    2. Exploring the Calculation dialog box
      5m 8s
    3. Using number functions
      12m 41s
    4. Using date and time functions
      4m 58s
    5. Using text functions
      11m 43s
    6. Using get functions
      4m 0s
    7. Using logic functions
      11m 18s
  18. 46m 56s
    1. Understanding scripts and script steps
      2m 23s
    2. Assigning script steps to buttons
      3m 54s
    3. Understanding the ScriptMaker dialog box
      8m 28s
    4. Creating multi-line scripts
      6m 44s
    5. Adding find criteria to a script
      4m 58s
    6. Understanding the If script step
      8m 36s
    7. Using script parameters
      4m 42s
    8. Reviewing the Send Mail option
      7m 11s
  19. 28m 0s
    1. Understanding script triggers
      2m 41s
    2. Using object-based triggers
      11m 58s
    3. Using layout-based triggers
      7m 51s
    4. Using file-based triggers (Open and Close scripts)
      5m 30s
  20. 56m 42s
    1. Using related fields
      7m 18s
    2. Creating portals and using portal filtering
      10m 38s
    3. Using related fields in calculations
      7m 6s
    4. Understanding multi-predicate relationships
      11m 11s
    5. Using the Go to Related Record script step
      7m 26s
    6. Creating a chart
      13m 3s
  21. 30s
    1. Goodbye
      30s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Join now "Already a member? Log in

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed FileMaker Pro 11 Essential Training.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.