Join Cris Ippolite for an in-depth discussion in this video Non-equijoin relationships, part of FileMaker Pro 9 Beyond the Basics.
- [Narrator] So far in this title, we've been talking exclusively about what are called equi joins, or relationships that create matches based on the equality of two key fields. As a review, if you open up the 02_Relationships and 02_03 Orders, we'll look under File, Manage, Database, and our Relationships, and we'll see, for example, that the relationship that we created between customers and Order, if we double-click on the equal sign, is based on these single fields matching.
If the CustomerID value and Customer equals the CustomerID value order, for example if they both have a number one in them, then the relationship is resolved and these records will match. Now, what you may not know is that FileMaker, in more recent versions, meaning any version after FileMaker 7, supports relationship criteria other than just simple equality. You'll see that here based on the different operators that you can choose: equals, does not equal, greater than, less than, greater than or equal to, less than or equal to and so on. FileMaker Pro allows the construction of relationships that examine more than just one field in a table as well, and we're going to look at an example of both of those.
First, in this relationship, suppose we want to see all of the orders related to a customer. Well, we already know that we have that relationship set up. If we go into Layout mode, grab our Portal Drawing tool and pick Order, OrderDate, OrderTotal, and we hit OK. If we go into Browse mode, we'll see that we've got all the different orders.
You see here, there's some orders from this year. Some customers don't have any orders, and so on. That's based on having CustomerID = CustomerID. That's what we're looking for there. However, let you suppose, for example, that we wanted to create a portal that showed orders for a particular customer as we've chosen here, but not only for that customer, but for the current year. Well, what we'll need to do, we'll need to do two things there.
We'll have to extend the relationship criteria to consider not only the customer, which we have here, but to also add another set of criteria that includes the order date, and it will be necessary to use a condition, rather than strict equality instead of just an equal sign, in this case of course, greater than or equal to, since we'll want all the orders on a date that's greater than or equal to the start of the current year. Two things will need to happen. First, we want to restore this relationship, so we're going to have to create a new one. We do that by selecting the button down here, you'll notice that with the green plus sign, and choosing, we're going to say "Order," and you see it already has an Order table occurring, so it renames it, but we're going to call this one "Order." This way, when we're ever looking at a list of relationships, or trying to determine which field set we're going to choose, that we have a plausible name for this.
Let's first drag CustomerID to CustomerID, because we know it's going to be based on the customer-related orders. Let's double-click on that equal sign to bring up the Edit Relationship dialog. Now we want to say, we know that OrderDate is another criteria. We want any OrderDate that's greater than or equal to the first of the current year. Well, do we have a field for the first date of the current year? No, we don't. Let's go back and define that quickly. Inside "Customer," let's call it "currentYearStart," and you can play along with me by creating a calculation.
We'll use a couple intermediate techniques here. Let's build the Date field by clicking on Date, and since we want the start of the year, the date of the start of the year, because that's what we want our OrderDate to be greater than, to determine whether or not it is included in this year. We'll hit the number one, which means January, and we'll hit the number one, meaning the first date. We want to dynamically pull in the year. There's also a function, if you double-click on year, it will extract the year from a certain date field, and we can make it completely dynamic by choosing what's called a Get function.
A Get function pulls in things from your computer or your operating system. In this case, we're going to have it pull in the current date. What this does here, is it says pull the current date from the computer and only give me the year. If we string that together with a one and a one, the value inside "currentYearStart" will always say, one, one, of whatever the current year is. If the year happens to be 2010, it will say one, one, 2010. If the computer date happens to be 2009, it will automatically say one, one, 2009, and so on.
Now we've got that field defined. If we go back into relationships, double-click on the equal sign here, we're going to use that field. If this is one, one of the first day of the year, and this is the OrderDate. what's the relationship between these two? We want the OrderDate to always be greater than or equal to the first day of the current year. Let's choose. Now we've got, if the OrderDate is greater than or equal to the currentYearStart or the first date of the current year, then we hit Add, and you see by hitting the Add button, it doesn't replace our first criteria, but it adds more.
It says, in addition to just having the CustomerIDs match, so we say, CustomerID = CustomerID, AND the currentYearStart value must be less than or equal to the OrderDate, or conversely, OrderDate must be greater than or equal to the currentYearStart date, which we of course, dynamically created a calculation field that will tell us what the first day of this year is. Now, when we hit OK, we see a couple things, first, that the equal sign is replaced in this non equi join relationship with an X, and we see that the double criteria are isolated with the pronged relationship line.
If we hit OK, now we can create a relationship based on that value. Let's go into Layout mode, and we'll select the Portal drawing tool, and for now, under Filtered Orders we see now that Order Current Year shows up under Related Tables, even though it's not really a related table, it's just a different relationship to the same table, and that's why we want to name these differently. Anything that's based off of Order should always just lead with Order, for example. In this case, we're just going to say the current year.
Now, quickly we can do OrderDate, OrderTotal. Now, let's preview this in Browse mode quickly, but we're going to notice that there's one more thing we need to change. We go into Browse mode, we see wait, here's the current year, as the current year happens to be 2007, but they're still not showing up. Well, if we go back into Layout mode, and that's because in our database, in our "currentYearStart," if we click in here, we see that since we're comparing two dates, and this is important to note, that the result of this calculation should make it a date.
That way it's actually the date of one, one, in this case 2007, so January 1, 2007, rather than just a string of numbers that way. Now, we hit OK and OK, go back into Browse mode, and we can see now that we've got a Portal here, that these two show what appear to be the same, because we've got all of the orders related to this customer, so it shows three orders in the portal, and the filtered orders for the current year show three orders in the portal as well, but you see as we rifle through these that it's only showing us current year.
A relationship from the same table, but with a multi-level or non equi join relationship, helping us filter out those values. This relationship matches on two criteria, and in order to appear in the portal of related orders, an order must have the same CustomerID as the current record, as you see here, and the OrderDate must be greater than or equal to the start of the current year. This example that I showed you here shows two different types of advanced FileMaker Pro relationships. They're non equi joins, meaning relationships based on something other than strict equality or an equal sign, and also a multi-predicate relationship, which means a relationship that considers more than one pair of matched fields.
You see both of those exist in this example. We've got something that's not equal, non equi join, meaning that we're using greater than or less than in this case, and instead of just one criteria, we're using two criteria. There's a couple things to remember here when you're working with these types of relationships. First of all, a multi-predicate relationship is always going to be And, an And relationship. When several match criteria are specified, all the criteria must be true. In order for a related value to match, it should have the same CustomerID and fit within the beginning of the current year into the OrderDate.
FileMaker Pro can not directly define a relationship where A, or B, or C is true. It's really only when A, and, B, and C are true. That's the only control that you've got. Really, that's giving you a very powerful tool anyways. Match fields in two files may be compared based on the number of logical operators. Equality is more common, as we've seen on all the other relationships, but fields can also be compared in equal, not equal to, greater than, greater than or equal to, less than, less than or equal to, or what's called the Cartesian X. This unique cross join X operator will always match all records in a related table.
It will give me all the records in another table, or a global relationship is what the X stands for. This has been a review of working with not only multiple criteria, or non equi join criteria, but multiple predicates within your relationships as well, and using those combined together with each other throughout your relationships in addition to your functional base table, equi join relations from your entity relationship diagram can really give you a lot of power in not only creating layouts, relationships, portals, related fields, and then using those related values and filters all throughout your FileMaker database.
- 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