Meta-Consulting
Meta-consulting is a service Database Pros offers to help other developers
over programming hurdles via GoToMeeting. Email John Mark Osborne or call John Mark at (909) 393-4664 to find out more about this service.
Merge Field Madness I'm sure most of you have run into this problem at one point or another. You wish to place a merge field on the layout, but the name of the field extends beyond your available layout space. While the data still displays correctly in browse mode, the length of the merge field covers up nearby objects, making it difficult to work in layout mode. One solution is to shorten the name of your field. If you can't or don't want to do that, try changing the font size of your merge field so the text block fits in your available space. Keep the first less-than sign in your chevron (e.g. <) the font size you want to display the merge data. Even though most of the text in your merge field is a tiny font size, the first merge field character will set the size for the data. Pretty cool. This can also be done with symbols that are surrounded by curly brackets (e.g. {).
Hall of Fame I started working with FileMaker 25 years ago and a lot has changed. Here are the folks I think need to be recognized for their effort in shaping the early FileMaker (in no particular order):
Record locking in a multi-user scenario is easy to control on a single record. Just use the Open Record/Request script step and test for an error 301. If the error occurs, stop the script and present a dialog telling the user they can't run that script right now. If no error occurs, the current user now has control of the record and it can be freely modified by a script. However, if your script loops through records or uses the Replace Field Contents script step, the difficulty controlling record locking is on a whole 'nother level. In this article, I'll discuss how to get around record locking when multiple records need to be modified with a script, using an example of inventory leveling.
Background I've discussed record locking in many articles but the article that focuses on the essentials is found below:
I highly recommend reading this article first if you are new to record locking. You should understand exactly what causes record locking and the basic methods for controlling the modification of a record before reading about this more complex technique.
More than One Way to Skin a Cat There are only two ways to cause record locking with a script. A script either has to attempt to edit or delete a locked record. On the other hand, there are as many ways to avoid record locking as there are script steps in FileMaker. Rather than becoming a One-Trick Pony, I recommend looking at a variety of different solutions to record locking. In my opinion, solutions to record locking issues are unique to almost every scenario. Here's one example I wrote about:
I like this example because it makes you think about the different ways calculations can be formulated in regards to record locking. But, my point is that record locking solutions can be solved in variety of ways, each with it's distinct advantages and disadvantages. Don't just apply the same solution to every record locking situation. By giving you multiple examples, I'm hoping you'll come up with your unique solution when the time arises to battle record locking.
With that said, the technique being discussed in this article is a very broad tool that can be applied to just about any multi-record locking scenario. I recommend not using it unless it is absolutely the best way to solve the problem at hand.
The Example In this article, I'm going to discuss an invoicing solution with inventory leveling. Therefore, it's important to understand the basic structure of an invoicing solution. Let's start with the ERD (Entity-Relationship Diagram). There are four tables in a basic invoicing solution: Invoices, Products, Customers and Line Items.
The Line Items tables is required to resolve the many-to-many relationship between Invoices and Products. If you want to know more about join tables, please watch a video I recorded on the subject:
Otherwise, an invoicing solution is pretty straightforward. If you need more information, please download the example file located at the bottom of this article.
Calculating Inventory Tracking inventory is fairly easy. All you need is a number field in the Products table to hold the current inventory level. The hard part is how to subtract the quantity of a product sold when an invoice is completed. Wait... I got this one! I'll just use a live calculation with an aggregate function to sum the products sold. Unfortunately, this is slow! Maybe not at first. But, as you add records, an aggregate formula that sums all the products ever sold and subtracts them from all the inventory you ever added can take a long time to calculate a value, especially in a multi-user scenario.
Here's a simple example of the formula which would be placed in the Products table:
The calculation above assumes a fifth table with a record for each quantity of products received. While using a live calculation might be easier, you are better off spreading out the calculation requirements as each invoice is completed or at least once a day. The live calculation references related tables so it can't be stored and therefore calculates each time it displays. That means each record in the Products will require a recalculation as it is navigated. In other words, you are calculating all the related record every time the calculation displays and that's gonna be slooooooooow.
If I have sold you on the idea of scripting inventory subtraction then let's start with the wrong way to script it. Yes, you learn more when you understand why one method is better than another. In the following example, the script levels the inventory after each invoice is printed.
The script above uses the Go to Related Record script step (GTRR) to locate all the line items from the current invoice and display them on a layout showing records from the Line Items table. It then loops through each line item record and levels the inventory in the Products table using Set Field. Simple and straightforward, right? Yes, but record locking could occur on any one of the line item records or the related products table. It's very unlikely to get record locking on the line items since there is no interface for the Line Items table but what if someone is editing a product description. Do you go back and remove the values from the previously leveled product records? Do you come back later and hope the record is unlocked? There's really no good way to back out or move forward when a locked record is encountered.
A better method is to use some knowledge about portals. When a record is committed, it commits the fields from the local table but also all the related records. That also means you can revert a parent record and the various changes in a portal will also be reverted. It's kinda like the local and related records are merged into a single record when they are contained within the same layout. With that knowledge, you now have a way out of your record locking predicament. Let's take a look at the script:
Instead of using GTRR to create a found set in a layout showing records from Line items and looping through the records, a portal loop is used to walk through the line items in the portal on the current invoicing layout. The same Set Field is used but now we can detect the record locking on a different level as if the Line Item records were all one record. If a 301 error is encountered on any of the line item records, the entire invoice record along with the portal of line items related records is reverted. Now this invoice can be leveled later or marked for inventory leveling in the middle of the night (this option is not covered here).
BTW: I threw in a check for no related records in the portal, just in case.
Downsides of Transactional Processing In general, transactional processing is a solid solution to record locking but there's almost always a downside. In this case, the script is married to a particular layout and portal. If a developer is not careful, they could break the inventory leveling functionality by modifying the invoicing layout.
What Should I Do? First and foremost, don't use transactional processing as your only method for controlling record locking across multiple records. It's a great solution but not the only solution. Think of it as simply one tool on your tool belt. Use it when it makes sense and is the best solution for your record locking issue.
This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!
