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.
Cascading Delete Self-join or Same-Table relationships are very useful (two table occurrences from the source table related to each other), but be careful! Don't ever turn on the option to allow deletion of related records! If you do, whenever you delete a parent record you may delete more than you want. What happens is that since your related records are also your parent records, deleting records can cascade out of control. I have seen the deletion of a single record cause the deletion of all records in all tables.
Claris Commercial Few people know that Claris produced a commercial and it aired on national television. It was one of those favor things so it only played once. I'm not saying it's anywhere close to the impact of the Apple Super Bowl commercial but it's fun to have a look.
Download the Claris commercial
According to the FileMaker online help, the Snapshot Link feature saves a found set of records. But, it saves so much more including the current record, layout, sort order and the mode. The basic idea is to save a Snapshot Link and send it to a colleague via email so they can see what you are viewing. So much more efficient than trying to tell someone in another building or state how to recreate a found set. In this article, we're going to use Snapshot Links to save and restore multiple found sets for future retrieval.
My Bad I just realized I forgot to finish my article series on found sets. I'm such a forgetful old man, lol. And, there's actually two more articles in the series. The other article deals with restoring the actual find criteria and will be published soon. It's the most complex in the series so I'll save it for last. If you've found this article before the previous three, here are links to them. If you don't read them first, you may get lost by some of the references I make.
The First Step Make sure you understand how Snapshot Links work. They're a pretty easy nut to crack but let's make sure. Just open a database from a server and choose Save/Send Record As from the File menu. Under the sub-menu, you'll see three choices. Choose the one that says Snapshot Link... FileMaker will open a save dialog and ask you to name the file. Provide a name that reflects the found set and FileMaker will create a .fmpsl file.
FYI: Saving a .fmpsl file from a locally launched database doesn't make sense since the remote recipient won't be able to access the FileMaker file anyhow.
A .fmpsl file is an XML document that describes the status of the database at the time the Snapshot Link was created. As mentioned before, it contains the sort order, the layout, the mode, current record and found set. It also stores the Status Toolbar state and the view (form, list or table). If you want to see the XML code, just open an .fmpsl in a text editor. It's really not too complex to read.
If you open a Snapshot Link, you'll also notice it stores a path to your file. What's really important to notice is that it doesn't store credentials so the recipient will still need to have a valid account and password to use the .fmpsl file. That means the file is secure and doesn't reveal anything about your database that will compromise it.
The Basic Idea The basic idea behind this technique is to save a Snapshot Link via a script and insert it into a container field in a table. This preserves the found set so it can be restored at a later date with another script that simply exports the file and opens it. Easy peasy, right? In reality, there's a little bit of work but I would rate this technique as fairly straightforward, especially when compared to restoring find criteria with a script (which will be covered in a future article).
The Table You should already have a table of data for which you want to save found sets. In this article, we'll use a simple CONTACTS table. What you'll need to add to it is a SNAPSHOTS table. You only really need two fields:
1) snapshot (container) 2) name (text)
I always add a primary key to every table, just in case. It's also not a bad idea to add housekeeping fields to track account creation and modification as well as timestamp creation and modification, in case you need to track down who or when a snapshot record was created. In the example file for download, I only include the two basic fields, just to keep us focused on the technique.
Saving Snapshots FileMaker has a corresponding script step to automate the saving of Snapshot Links called Save Records as Snapshot Link. With a few other complimentary script steps, it's easy to design a basic script to save a Snapshot Link and store it in a table.
The first step initializes the global "xname" field specified in the Show Custom Dialog script step following the Set Field step. No need to see the previous value saved or even the value last used by the developer when it was in single-user mode. If the user chooses to save the current found set, the first step inside the If construct is Set Variable. Here, the location of the soon to be saved Snapshot Link file is specified with the Get(TemporaryPath) function. This places the file in a hidden folder that gets deleted each time FileMaker is closed. It's a great place to throw files that don't need to be seen by the user so as to hard drive prevent clutter.
FYI: The initial value of a global field when someone opens a hosted solution in a multi-user scenario is the last value the developer left in the global field. That's why it's a good idea to initialize all globals in an open script or just before they are utilized.
Once the location is specified, the Save Records as Snapshot Link is employed to output the .fmpsl XML file. Make sure to specify "records being browsed" rather than "current record" or your results won't be a found set. Also, specify the $Path variable in the "specify output file" area so the Snapshot Link ends up in the temporary path.
The next step is to insert the Snapshot Link file into the SNAPSHOTS table. This process starts by changing context to a layout showing records from the SNAPSHOTS table. There are many ways to accomplish this task but I've chosen a new window displaying as a Window Card. I used to use a standard document style window but it tends to flash the status toolbar as it appears and disappears. As long as you turn off the "dim parent window" option in the New Window script step, you are unlikely to even notice the new window opening with a Window Card. The sleight of hand is further enhanced by spawning the new window off screen with top and left positions of -10,000. Also, don't forget to specify a layout based on the SNAPSHOT table (I often do in this busy dialog).
BTW: It's also possible to change context in the current window so you don't have to open a new window but this will lose focus for your cursor, tab pane and portal row. It's easier to open a new window to avoid the loss of focus.
Once the context has been changed, the script creates a new record, sets the name from the global field and inserts the Snapshot Link file. Not much explanation needed here as these script steps are straightforward and should already be on your tool belt if you are reading this article. Finally, the window is closed to return the user back to the context where they clicked the button to save a found set and a message is displayed letting them know the the process was successful.
Preventing Duplicate Snapshots As is, the script works great but what happens if someone tries to enter a name for a found set that already exists? The script will add the duplicate found set to the SNAPSHOTS table but this will cause trouble. First, which found set is which? There's no way to tell without restoring them and comparing the found sets they restore. Secondly, depending on how you plan to select found sets for restoration, there will be varying degrees of problems. While we haven't discussed how to restore a found set yet, it's worth mentioning issues at this point because you have to think ahead. If you use a popup menu based on the contents of the SNAPSHOTS table, the two found sets with the same name will only display once. If a picker is used, they will both display but then it won't be possible to ascertain which found set you want.
Clearly, this needs to be resolved so no duplicate names can be entered. You might think about using the validation option in Manage Database to only allow unique values. It's a straightforward check box implementation allowing for easy toggling of the feature on and off. Definitely an entry level solution than can work great in many situations. Unfortunately, this solution also involves a script that adds the possible duplicate record, making it difficult to connect the script and the validation. You might be thinking you can try capturing validation errors inside the script.
504 - Value in field is not unique, as required in validation entry options
The problem with this approach is you have to add a record to the SNAPSHOTS table in order to determine if there's a duplicate. That means you also have to remove that record if there's a duplicate. I prefer a looping construct instead. In the screen shot below, I've marked the script steps that were added in order to allow for duplicate checking. This includes steps 3, 4, 7, 8, 17, 18, 19, 20 and 22.
The first step is just a comment so need to cover it. Step 3 and 22 add the looping construct to keep the script looping until a unique value is entered. Step 6 tests across a relationship using the xname global field from the CONTACTS table and name field from the SNAPSHOTS table to determine uniqueness. Normally, I would try to avoid cluttering up the Relationship graph with a single purpose relationship but it will get used later for another reason.
BTW: You could use ExecuteSQL to check for existing values in the SNAPSHOTS table and avoid adding a table occurrence and a relationship but ExecuteSQL won't be able to do what we will do later with the relationship.
The basic idea behind this looping script is to keep looping until the script validates the name and allows it to exit. The method for exiting the loop is the Exit Loop If script step. You'll notice the formula in the Exit Loop If step is simply a value of one (1). If you look closely at the bottom of the Exit Loop If script step calculation dialog, it requires a boolean result. I'm simply giving it a true result so it will exit any time it reaches the Exit Loop If script step. It's the If script step with the test across the relationship that determines if the Exit Loop If script step is reached.
Restoring a Found Set I've decided to use a Popover to house the popup menu containing the stored found sets but there are many ways to interact with the saved found sets. You might try a layout that shows records from the SNAPSHOTS table and allows people to search. You could also go the route of a picker using a relationship and a portal to display the available choices. Each has it's merits but I'm going with the method that takes the least amount of work. Easiest is not always the best choice, but in this case I think it is.
BTW: If you have a large number of found sets to save or need to conditionally display them by user then a find or filtered portal is a better interface choice.
For starters, a global field is needed. Luckily, we can just reuse the existing "xname" field. Just place it in a Popover along with a button that will eventually run the script discussed below. As a user convenience, the global field should be initialized on opening of the Popover. This can be done using the OnObjectEnter Script Trigger attached to the Popover pane (not the button) and Set Field. Otherwise, the previous choice will show or the value the developer left in the global field before uploading it to the FileMaker Server.
The restoration script itself is fairly simple when compared to the script for saving Snapshot Links. All you really need to do is find the desired record in the SNAPSHOTS table and then perform a Go to Related Record step (GTRR). There's some other housekeeping steps but that's about it, as you can see below.
The first step ensures a choice has been made from the popup menu used to display the list of found sets that can be restored. If a value has been selected, the Popover is closed and the record committed. It's always a good idea to commit a record before a GTRR so the relationship cache is flushed. The next step is the GTRR which displays the record from SNAPSHOTS corresponding to the choice from the popup menu interface. The record/layout is opened in a new Card Window to avoid screen flash so make sure to turn off the option to diim the parent window. The window is also opened off screen to avoid any screen residue.
Finally, a variable is set to the temporary path, much like in the script that saved the found set in the first place. The path is then passed to the Export Field Contents with the option to Automatically Open. The last step is to close the Card Window to clean up the context change. Finally, the found set is restored in a new window, per the Snapshot Link standard functionality.
The Saved Window Size The window size and location produced by the Snapshot Link is based on the last closed window that was modified. Most of the time, this isn't a problem. I noticed it when I was creating the example file for this article. The window produced by the Snapshot Link restoration was tiny. I realized it was the same size as the splash screen I create on open. Not only that, it was a combination of how I performed a script on close of the file to frame the splash screen so the window resize wouldn't occur on open.
Unfortunately, it had the side effect of resizing the default window size but only when a change was made to the database prior to closing it. It had me confused for a bit but I finally deciphered the behavior after three or four attempts to script away the issue. I've disabled the steps in the script (in the example file) that resize the window so as to present the pure version of the solution. The steps aren't that radical so it's not really a big deal if you turn them on. I just wanted a clean solution and it was an unlikely scenario anyhow.
Steps 6, 7, 8 and 9 simply open a new window, adjust the window to frame the layout, modify a field to the same value it already contains and then close the window. The series of four steps sets the default window size so there's no doubt how the Snapshot Link restoration will behave. There's only one drawback I can see and that's window locking or record locking. I'm not going to cover techniques to get around these two issues but I have referred you to examples that will assist you if you want to go down this rabbit hole. Personally, I don't think it's a tragedy to have the window open in a strange size from time-to-time. Think of it as the 80/20 rule. Don't waste 80% of your programming effort for 20% of the features (or, in this case, it's more like the 99/1 rule).
Multiple Tables If you want to support multiple tables, you'll need to add a field to store the table name along with the existing found set name and Snapshot Link. There's two programming changes you'll need to add that aren't represented in the example file. First, you'll need to program conditions to determine which table is being used so you can branch to the correct GTRR script step. This includes creating a table occurrence and a relationship for every supported table. So, if you are supporting multiple tables, reconsider the ExecuteSQL method for determining duplication of found set names and a find based solution for locating the correct SNAPSHOT record. I usually find people just want to save and restore found sets for a single table so there shouldn't be a need to complicate this solution.
Secondly, you'll need a conditional value list menu that only shows the appropriate found sets for the current table. This technique can also be used to show different found sets for different users. This is a more likely scenario IMHO but isn't very difficult to solve using a relationship based on a primary key field defined as an unstored constant calculation field containing the Get(AccountName) function.
Your Tool Belt This technique is one of the preferred tools on my belt. The reasoning is simple. It's easier and faster when compared to collecting serial numbers. In the next article, we'll discuss saving find criteria but I have never implemented this in a real world solution. I mostly present it as a brain teaser that will absolutely help you somewhere down the line when you are trying to program an entirely different solution.