FileMaker Pro 11: Advanced Tutorials
Miscellaneous Techniques / Transaction Processing
Visitors to VTC.com will be able to view all introductory videos for each training course.
Free Trial Members will gain access to first three chapters for each training course.
Full Access Members have full access to VTC.com�s entire library of video tutorials.
Learn More
Subtitles of the Movie (FileMaker Pro 11: Advanced / Miscellaneous Techniques / Transaction Processing)
Recently I had a discussion with Todd Geist, another FileMaker developer I know pretty well. We had a discussion regarding the benefits of Transactional Processing and my immediate reaction is I understand the benefits but it's hard to implement. Well, Todd has really changed my mind about Transactional Processing. A basic definition of Transactional Processing is either all your edits occur or none of them occur. So let's show you what Todd means with our Inventory Leveling Script. If you remember that every time we print an invoice we set up a script and I'll show you this just to give a quick review. You'll see Print Invoice and down here we say Remove Inventory. We've disabled it because - we'll come to that in a second - but if we look at the Inventory Remove we'll see that while we're in LINES we go to each record and subtract what's in LINES from PRODUCTS, so we remove the inventory that we've just sold so it loops through all the actual records in our PRODUCTS Table. So the issue that we came across is what happens if somebody happens to be editing one of those Products when we try to level the inventory? Well, it's not going to level that particular product or even maybe multiple products. So what did we decide on? We decided to go with our other Print Script which was printing our invoices, our daily invoices and if you see in here, if you go down, you'll see that we have the same code in here that we saw in our Inventory Leveling that does the same thing but it does it at night, essentially. You're doing it when nobody's on the system so you don't have to worry about record logging. Well, that's a possibility but here's what we did in the example of Todd with Transactional Processing and let me first run this script so you can see what happens. I'm going to go ahead and click this button. I've really removed this from the Printing Script and made it separate just so you could have both techniques. You could do it either way you want. You know, you have to make a decision for yourself what's the best approach but here's how it works. When we decide we want to level inventory and remember all of our products have been set to a level of 100, so Mountain Mist, Guatemala and Amaretto, 3, 3 and 2. So when we click this it goes really quickly, we can go over to our Products here and we can walk through these and see how they've leveled the inventory - 98, 97 and then also 97. So it works very fast. I mean it happened so quickly I was wondering if I even clicked on the button. That's not the concern. In fact, Todd says even looping through a Portal like this might even be faster than actually going through over to the other Table, so there's lots of things to check. It all depends on, so few records won't really make a difference. But what's really the benefit here? Why would we want to go ahead and run this other type of script and what is that script? Let's take a look at it. Well, let's first talk about the reason why we want to run it. Let's go back to the definition: either all your edits or none of them occur. Either all of them occur or none of them. What if one of these records is actually being edited, the Product record? Well, let's say it was Amaretto. If it goes through these two and takes out those values it should go ahead and when it gets to Amaretto go, oh, well, I can't, got to go ahead and reverse these and I told you how can we go back and reverse these back to the way they were? It would be a very difficult process. Well, not with Transactional Processing so let's take a look at the script. So we'll go in here and type in Transaction, there's the script, and this just handles leveling the inventory, that's it. We start by freezing the window and the first thing we do is commit the records. Now the reason we do that is because we might have some type of Validation error on that record. We want to make sure it's committed before we try to do this because we're going to use a Revert Record to, you know, reverse all the changes we made so this is to make sure, let's say, we accidentally didn't fill in the first name field or who knows what other field, it had nothing to do with our Line Items. Then we go to the Object Name Portal which is that portal and make sure we have the right portal even though there's only one. We go to the first row, then we enter a loop and we're looping through the Portal rather than the actual PRODUCT Table and we're doing essentially the same thing. We're saying take the inventory from PRODUCTS and subtract the quantity from LINES. If there's an error we set it to the Get LastError, the dollar sign Error and then what we do is we say if the Get LastError equals 0 or 1, we could check dollar sign Error here wouldn't really make a difference, then we've got to go and exit, and we don't continue on. So, that's how it exits out of that loop and it stops at that point where it finds 1 record that's locked. Then we come down here after the loop and we say, if the error was 0 or 1 we had to do it with a Variable because it's, you know, there's steps in between this and it would have been lost, we say show inventory can't be updated and then we revert the record. And this is the interesting thing about Reverting records - it reverts all of the related data and all of the Line Items in that Portal, not just the fields on the invoice record, but all of its related items. All those things go back to the way they were when you revert it. So, everything's set back to the way it was before you tried running the script so all of your transactions that you tried to process are all set back to the originals and then you can show, of course, you know, then that message makes sense up there, otherwise it commits the record and those transactions are committed because it went through and didn't have any errors. Pretty simple to do. In fact, we'll come back into this. Todd actually says he usually checks for an error greater than 0 in both these places because any kind of error can occur and if any error occurs it probably means that what you meant to do didn't happen and you don't want to put forth some of your transactions, you want to put forth all of them, so he usually checks for an error greater than zero and that way if any errors occur then he goes in and says you can't do this and so that's a possibility here if you want to check for other errors other than record locking, but record locking is the most common error. So, that's a great little technique and hopefully you understand how that works. As long as you have a Portal on your layout and even if you don't, you can go ahead and put a Portal on some layout that's hidden and use this technique to do Transactional Processing.
Tutorial Information
| Course: | FileMaker Pro 11: Advanced |
| Author: | John Mark Osborne |
| SKU: | 34140 |
| ISBN: | 1-936334-36-4 |
| Release Date: | 2010-07-09 |
| Duration: | 14.5 hrs / 159 lessons |
| Work Files: |
Yes |
| Captions: | No |
| Compatibility: |
Vista/XP/2000, OS X, Linux QuickTime 7, Flash 8 |
VTC Sign up & Benefits
- Unlimited Access
- 81,350 Video Tutorials (14,200 free)
- Video Available as Flash or QuickTime
- Over 715 Courses
- $30 for One Month Access
- Multi-User Discounts Available
