Join Cris Ippolite for an in-depth discussion in this video Using global fields in relationships, part of FileMaker Pro 9 Beyond the Basics.
- So we've talked in earlier chapters how relationships can be used to create the fundamental structural lengths between tables, the same kind of relationships that you're mapping out during the data modeling process. But as you venture into more esoteric relationship types, the relationships come to have more and more to do with a query-like behavior or a filtering-like behavior. So for an example, a portal showing all the customer's invoices, like the one we've got in 02-04 Orders, that would be this portal, shows all of the different related orders.
So that one's really just a fundamental or a linking relationship. But here we used one that is all of the orders for the current year. Now this becomes more of a query or a filtering mechanism. But what if we wanted to have act more like a mini report? So if we wanted to allow the user to interface with some components of the portal to, therefore, determine what values will appear inside the portal itself. Well it is possible to do that. So let's do the example here of saying a portal showing all of the customer invoices for the current quarter or something more interactive than that.
Let's say we wanted to say for the date range that a user supplies. Well let's do that. Let's change this portal to allow users to enter in a Start Date and an End Date and then, therefore, dynamically see in the same relationship in the same portal, different values based on whatever criteria they enter. So it's possible to extend this concept a little bit further and create relationships that change their behavior based on user input and that's what we're going to do here. So suppose in this portal, again, we're going to create a portal that rather than simply showing the orders from the current year or quarter, which we'd have to hard code in, we want to show orders from a user-defined date range.
It's actually pretty straight forward to do, but first, in order to create our multipredicate relationship, we're going to need a couple more fields here. See, here's a multipredicate relationship we created earlier. First, we're going to define two fields and these are going to be the fields that the user is going to add date into or use to interface with our portal or mini report. So to accomplish this, we are going to define two new fields. And we'll go into Customer. If we think about what we're trying to do, we're going to want to show all the orders that are both greater than or equal to date X or less than or equal to a date Y which is going to have to be later than X so that's our date range.
Our beginning of the date range and our end of the date range. So in order for the user to be able to enter dates into these fields, we're going to need to define something to capture and hold that data. And I'm going to suggest that we create global fields which are best to find although you could certainly make the case if your requirement dictated that each user record should be able to show a different date range. Well, that's fine, but in this case, we're just going to say for every record in the Found set that the users are looking at, whatever values are entered into these fields, are going to dictate how that portal behaves. So in that case, we're going to create global fields. So let's create a couple global fields.
We'll call one of them, you know this is some criteria that I happened to use for identifying a global field or a field that will be used as a match field also. First, let's create (typing) the start date so we'll say "z" underscore "g" order filter start and I'll make that a date and I'm going to hit Create and there's one more step to make it global. We click into the options, go to Storage and say Global, which means every portal in the whole file is going to have the same start date and then now, the smart thing to do would be to duplicate start and then change "start" to say "end" and then just hit Select, Change.
So now we've got two fields, one that's going to store the start date and one that's going to start the end date. And if we select OK and go into layout mode, you see that we've got field areas already for those. So we'll say start date. And we'll say end date. So now, looking back in Browse mode and saving our layout, we've got an area where a user can enter in a date and we'll go one step further. And I'm going to right click on this field, just another way that I can access the Field/Control Setup, Drop-Down Calendar, I'll do the same by right-clicking on this field, Setup Drop-Down Calendar, OK.
Save. Now it allows me to choose a start date and allows me to choose an end date. But still we haven't created the relationship that's going to be based on these. So lets go under Manage, Database. And we're creating a relationship similar to this one but let's create another occurrence just for this relationship. And again it'll be Order and we'll say (typing) Order User Filter. So already you see that we are way beyond our base table relationships that we drew up in our ERD.
We've got another relationship here for filtering a portal and now we've got another one, an interactive report to look at related values. They're all basically stemmed off the same relationship and that is, customer ID to customer ID, but we're going to add another criteria, by double-clicking on the equal sign. We'll move this into the middle area. Instead of having a hard-coded value equal the dynamic order date value, we're going to have a user-driven value determine what the range is.
So the way that we're going to do this is adding other criteria. So let's first find our start date field. So of course what we're going to want to do here is say that the matching records are going to have to be greater than or equal to the start date. So what we will do here is that we will pick our greater than or equal to Order Date and hit Add. So any customer records that have matching Ids and the date value inside the Order Filter Start is less than or equal to Order Date value and in addition, we're going to close out this range by flipping this around and saying greater than the end date needs to be greater than or equal to the Order Date.
So now we've got three predicates in our relationship and we see those represented again by the X on screen. If we hit OK and go into Layout Mode and double-click on the Filter Orders portal to open up the portal setup dialogue box. And I'm going to change to Order User Filter. When we go into Browse, we'll first initially see that there are no related values. But let's say we want to click in and find any values from June 1st to (clicking) the end of the year, let's say the 31st.
And see if any of those exist. Well we don't have any of those, but let's try 2006. (clicking) And we notice now just by changing these values (clicking) that we're getting not all the orders but any order that falls within the criteria. So we see anything from June of 2006 through 12/31/2007 and if we go all the way to the end here we'll see all of our 2007 records of course match and anything in 2006 that's after June will fall into that criteria also.
So you see, now the user can dictate instead of June let's say that we want to look at anything from January 1st, 2007 to (clicking) October 8th, 2007 (clicking) See if any of these orders match our criteria. So now you notice that we can use this not only visually as a report, but if you think about how many times you use related fields and the combination of related fields and layouts and the context you are providing and allowing the user to filter these or all sorts of functions that use related data, you'll see that this can become a very powerful tool.
So if you go beyond equijoin relationships to add multipredicates and allow users to input values that will change those predicate values, you can see that just by using one portal, you can have all sorts of really cool dynamic, not only visual representations of related data, but also things that you can do internally within layouts, scripts and calculations using these layouts also.
- 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