"No Holding Back FileMaker Blogging"


Navigation:


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



Create a Password Management solution!



Become a patron of this FREE web site!


Recent Blogs:

Currency Formatting
Currency Formatting

Gathering Portals
Gathering Portals

Multiple Choice Picker
Multiple Choice Picker

Popups and Pickers
Popups and Pickers

Window Locking
Window Locking


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.


$10.00 Beginner Video Training


Quick Tip:

Switching Layouts
When switching layouts via a script, it's a good idea to switch to find mode first. This will prevent the loading of any records on the new layout. This could be a serious speed issue in a remote connection scenario. If you are performing a find on the new layout then you're already in find mode (i.e. locating a specific record to change). If you need to add a record then perform a search on a field for something that will find no records so when you enter browse mode you have a found set of zero.



The Philosophy of FileMaker recommends PCI!


Fun Stuff:

Linux
FileMaker Server briefly ran on the Linux platform but was abandoned when FileMaker 7 was released. People still request Linux support to this day. Claris finally brought Linux back in FileMaker 20!



Create a Password Management solution!







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!