"No Holding Back FileMaker Blogging"


Navigation:


Support this site by clicking on a sponsor below or becoming a patron!



Create a FileMaker Calendar



Become a patron of this FREE web site!


Recent Blogs:

Conditional Tab Control
Conditional Tab Control

Server Optimization
Server Optimization

Designing for Efficiency
Designing for Efficiency

Hey Nineteen
Hey Nineteen

Why FileMaker
Why FileMaker


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.


Create a FileMaker Calendar


Quick Tip:

Switch Layouts Quickly
FileMaker Pro 19 offers a slick method for switching layouts that's similar to the Spotlight feature in OSX. While in layout mode, type Option-Command-K (Mac) or Ctrl+Alt+K (Win) and you can start typing the name of the layout. So much faster the the Manage Layouts dialog!



Document Management Videos


Fun Stuff:

FileMaker, Inc.
FileMaker, Inc. or FMI is a wholly owned subsidiary of Apple, Inc. It was once named Claris when it also sold MacWrite, MacPaint, Claris Emailer, ClarisWorks, etc. but changed it's name in 1998 when it focused on FileMaker. FileMaker started out as a DOS application called Nutshell but was shaped into the powerhouse it is today by Claris and FMI.



Create a Complete Contact Manager







RSS Feed
Transactional Processing
Level: Advanced
Version: FileMaker 19
Category: Scripting
Tuesday, September 8, 2020
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.

Transactional Processing

Background
I've discussed record locking in many articles but the article that focuses on the essentials is found below:

Reconciling Record Locking

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:

Form Letters in Browse

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.

Transactional Processing

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.

Transactional Processing

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:

Sum(Inventory::Received) - Sum(Line_Items::Quantity)

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.

Transactional Processing

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:

Transactional Processing

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.

Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com

Example File (available to patrons at $5.00 per month): Download here if you are a patron

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!

Add Comment:

First Name: *
Last Name:
Email: *
Web Site:
Comment: *
 Email Addresses will not be shared on the web site!