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.
Date Search Techniques Some date search features in FileMaker may not be obvious. For example, you can type in a range of "Monday...Friday" or "Mon...Fri" to locate all weekday dates. Or, you can use wildcards to locates all dates on January 1st, regardless of the year, by typing "1/1/*". You can use curly brackets to specify a range of months, such as "{1...6}/*/* to locate all the birthdays in the first 6 months of the year. Try these same wildcard techniques with time fields too. Play around with these tools, and I'm sure you'll be impressed with the flexibility. And let me know if you figure out some combination of these new features that let's you accomplish some really cool technique.
DevCon History Here's a list of the FileMaker Developer Conferences* through 2020:
#
Year
Location
23
2020
Nashville, Tennessee**
23
2019
Orlando, Florida
23
2018
Grapevine, Texas
22
2017
Phoenix, Arizona
21
2016
Las Vegas, Nevada
20
2015
Las Vegas, Nevada
19
2014
San Antonio, Texas
18
2013
San Diego, California
17
2012
Miami Beach, Florida
16
2011
San Diego, California
15
2010
San Diego, California
14
2009
San Francisco, California
13
2008
Phoenix, Arizona
12
2007
Orlando, Florida
11
2006
Orlando, Florida
10
2005
Phoenix, Arizona
9
2004
Phoenix, Arizona
8
2003
Phoenix, Arizona
7
2002
Palm Desert, California
6
2001
Orlando, Florida
5
2000
Palm Desert, California
4
1999
San Diego, California
3
1998
Monterey, California
2
1997
San Jose, California
1
1996
Santa Clara, California
* Name changed to Claris Engage in 2020 to match renaming of FileMaker, Inc. to Claris.
**Due to the COVID-19 virus, this conference was changed to a virtual conference.
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!
