Subtitles of the Movie

You may have noticed already that when we modify a field inside the portal, or even add an item into the portal, that this time stamp does not update and from a developer point of view, you probably understand this. This is in a completely different table than this field. This is tracking modifications to a status field or this foreign key or any field that's in invoices but not to customers and not to this portal. So what you can do is explain this to your users or you can actually make it to work the way that they imagine it to work. They see this as being part of the invoice, therefore this should update. So here's how we can work through this. Let's go to layout mode and you notice we've changed this already to time stamp mod all and account mod all. Let's go look at those fields and let's start with time stamp mod all. It's the most straightforward one and it looks pretty complicated, but it's really not that difficult and just to make sure you can see the end here, there's two closed parentheses at the end here. So let's start at the top here. We start with the let function and we declare two variables. And anytime you declare more than one variable, you need a bracket, a square bracket at the beginning and one at the end and then the final semicolon follows that bracket. And what we declared here is time stamp invoices, which is the time stamp mod field from invoices, that's why there's not table in front of that. So it's this one right here. And then time stamp lines equals max lines time stamp mod. That means it's looking through the lines relationship and it's an aggregate function, so it says look at all the records, or all the related records to this invoice in lines, and find which one has the maximum modification time stamp and we'll put that into that variable. Then we go into a case statement and we say does the invoice time stamp, is it greater than the lines time stamp, or the maximum lines time stamp? If it's greater, then we result in that time stamp, which is right up here, or we result in the maximum time stamp. So that works great. Nothing wrong with that. Now we got a calculation on the layout and it updates depending on which ones change. It determines which one's largest and displays that information. Well, it's a little more complicated with account mod all. So you'll see that the beginning is the same. We'll scroll down just to make sure you see the two closed parentheses there. We have the exact same declarations here; time stamp invoices, time stamp mod, time stamp lines, max time stamp mod. Now, why are we doing that? Why don't we do max account mod? Well, you can't really compare text the same way as you compare time stamps. We need to find out what the maximum time stamp is and then give the corresponding account. So what we do is we compare it the same way, invoices to lines. If invoices is greater we give account mod from here, otherwise we use this new relationship here, lines underscore mod account mod. We're not using just the regular lines relationship. Why? Because there's no way to say give me the maximum and then give me the corresponding account to that. So we have to figure out a different way to do that. So let's hit cancel here, go to relationships and you'll see there's lines mod. It's based on the same relationship we have from invoices to lines, which is KP invoices ID to KF invoices ID. It's exactly the same relationship. The difference is when you click on it, on the equals sign, you'll see that it sorts through the records by time stamp mod, in descending order. So you need to understand how FileMaker displays records when you ask for multiple related records. Well, if you display them in a portal, they're displayed in creation order. So that makes sense when you go ahead and add products to your invoice in that portal. They stay in the order that you entered them because that's the creation order. If you want them in a different order, you have to sort them and what that essentially does is changes the creation order to whatever you sorted by. So what would happen in this case is that the record with the largest time stamp would float up to the top and so when you reference across a relationship, right, and there's more than one related record and you're not using a portal, it has to choose which record it's going to display, which related record, it's going to choose the first one according to that creation order, or if you sorted, according to that sort order. So that explains why we've done this. We'll go back to it real quick. This relationship grabs only one value and it's sorted by time stamp so it grabs the right account for you. Now, you're thinking, well, aren't aggregate functions kind of slow? Any aggregate function that you have is kind of slow. It has to look at all the related records and slow is relative to how many related records you have, but the more you get, the slower it gets. So why don't we just use this where we have max everywhere. The problem is if we put this everywhere we see max, then our calculation doesn't update properly and we have a problem. In other words, you'll make a change and it won't automatically change. And the idea behind this is that the max function has some type of trigger inside a calculation that makes it update, whereas this one doesn't. FileMaker wants these calculations to run as fast as possible and, in this case, they found that max is important when to update the calculation every time something changes in the related table. But this isn't enough and so they had to make a choice and that's just the way. It's a refresh issue. So if we put this up here instead of max, what would happen is we'd make this change, let's say we change this to three. Notice we have user one as the one right there. And you'll see it changes to admin. Well, if we didn't have max in there, it would change but it wouldn't reflect here. We'd have to somehow get the screen to refresh, possibly like this. If we somehow hide that and then show it again, that would be enough to refresh it.