Join Cris Ippolite for an in-depth discussion in this video Understanding multi-step relationships, part of FileMaker Pro 9 Beyond the Basics.
- [Narrator] Now that we're shifting out of data modeling and into working with relationships inside of FileMaker, our mindset's going to have to change just a tad. The reason for that is, in the context of data modeling, we think of relationships as being binary or only involving two different elements. For example, A relates to B in some way, and then B relates back to A in some reciprocal way. But when we're inside the FileMaker relationship graph, we can create relationships, as we saw on the data modeling section, not only between two tables, but we can create relationship chains, or relationships that join instances of several tables in a deep or multi-level relationship sequence, and that's what we're going to talk about in this movie.
So let's consider again the example. If we look into the exercise files, under 02_Relationships, and select the 02_01_Orders file. And note here, you see the FileMaker cannot share a file because FileMaker network sharing is off message that onscreen. Don't worry about this one, you're going to see this a lot throughout the exercises. What this means is that we've configured this file to be shared on FileMaker server, but we're currently opening up locally within FileMaker, and that the FileMaker application is not configured to share. So you can just simply hit OK in order to proceed and open the file so that you can use them within the exercises.
We will notice inside the File, Manage, Database, and if we click on the Relationships tab, we'll notice that we have some of these relationships. In our ERD, as represented in our FileMaker relationship graph, we have a relationship between Order and Order Line Item, and between Product and Order Line Item, and so on, and Customer to Order. These are what are called one hop away. One customer can order have one order, and one order can belong to any customer at any given time. Now, in FileMaker, we can actually pull data that's coming in from this entire chain of relationships, and this is a little bit different than how it used to work.
If you've worked with FileMaker Pro in a version prior to 7, then you didn't see this type of ability to be able to pull in data from more than one hop away. But now, just to give you an idea, we consider this, again, the example of the customers and the orders. Customers, of course, can be related to many orders, and, in turn, they can be related to many individual line items. Because if you think about it, any line item that appeared on an order for a customer means that it is related to the customer. Any order line that's related to an order, that's related to customer, means that this order line is essentially related to the customer itself, and therefore, a product can be related to customer also.
So, for example, we've got all these products and then some of these products appeared as order line items and some of them appeared as line items on these orders and some of these orders belong to customers, so technically, pulling data all the way through, we've got all products, all the ones that ever appeared in an order, then all the ones that ever appeared in an order for a customer. So that's what all these relationships look like in the FileMaker relationship graph. Let's bring this one little bit closer to home here. Let's take a look at a order detail. If you look at the order detail layout, you see that we've got a portal, that shows all the different order line items.
It's represented in the relationship between orders and order line. But if we go into layout mode, we'll notice something interesting about this portal. All the different fields, we see are from the order line relationship, order line quantity. Double-clicking on these. Order line unit price and extended price. All of those come from the child file, in this case. But one of them does not, it comes from product. This comes all the way from, if we look back into Manage, Database, Relationships.
We've got orders to order line and we were seeing all these different fields in that relationship, but also, in the same portal, we can pull in the product name. So this is something that you weren't able to do in versions of FileMaker prior to FileMaker 7 and it's very useful in the program environment now. It's something that it's good to get used to moving forward. So achieving this in FileMaker is pretty straightforward. In the current example that were looking at it's only necessary to add the description field from the product table onto the portal row. And this field will simply display the data from whichever product record is associated with the specific order line.
So this is showing us order line item data, but just as if I put the related product name field on an order line item record, it's going to show me whatever the related product is, because there's only one of those products. So this field is going to display the data from whichever product record is associated with a specific order line item record, and the usual considerations for related fields apply. The data being viewed ties directly to the product record, so it's unlikely that you want a permit users to either enter or edit data in this field, because if they do, they're going to change the name of the product record in the product table and that's not necessarily always something that we want to do.
Now, in addition to being able to use fields from a couple hops away, if we go back into our customer detail, let's say that we want to create a portal down here for all of the products that this customer has ever ordered. Now I don't mean all the different individual line items for aproduct, which means if they ordered pickles or something like that, that'd the pickles is going to show up multiple times. But just give me a portal that gives me a view into the products table, but will only show me the products that are related to this customer.
Now we know that those relationships are more than one hop away, let's take a look at how would do that. And now we select the portal drawing tool and we'll draw a portal on screen and instead of choosing the order, now remember, we're in the customer, and we'll notice that we have options of being able to base our portal on all the different related tables. So in this case, we're going to choose product, and that's more than one hop away. So let's hit OK. It asks us which fields we'd like to bring in and we'll move each one of these, products with the unit price, and we'll hit OK.
Now let's go into Browse mode, save this, and let's look at what our portal tells us. Now we see as we go from customer to customer, the different products change and so what this tells me is that Janet Swanson ordered however many blueberry items, blueberry items, some sharp cheddar and some tangy clam juice. These are the different products that she ordered and what it's doing is showing us was called an indexed list, because, if you think about it, we have a portal that's looking into the product table, where a product only exists one time.
There's one record per product inside that table. So, if you want to see an indexed list of all the related values from any one of the related tables you just have define your portal on your layout based on those relationships. So just wanted to point out some of the differences between pre-FileMaker 7 relationship logic and the most most current version, FileMaker 9. So given the above relationship graph in this demonstration that we just did. A portal on a customer layout, showing records from a product. We'll show one record for every product the customer has ever ordered.
- Introducing data modeling
- Understanding multi-step relationships
- Reviewing number and text functions
- Working with logical function calculations
- Working with text formatting function calculations
- Working with aggregate function calculations
- Understanding objects
- Scripting for developers
- Publishing a database on a network or on the web
- Setting up external SQL sources
- Reviewing the new Server 9
- Extending the Web Viewer
- XSLT and PHP Site Assistants