How to reliably keep tabs on the state of other sessions

[jjjjp]

In my database, every user, at login, chooses an organization and a program within it. The data the user sees is filtered by the program id, which is kept in a global variable. The user can switch programs in the middle of a session, but a number of housekeeping operations must occur when that happens.

Users can do pretty much whatever they want while in a program; conflicts are nicely managed by Filemaker's record-locking mechanisms. However, there are a few operations, essential but rarely performed, that require that nobody else be in the program at the time.

I have tried to manage this by having the OnOpen script create a record in a table called OpenPrograms, which contains essentially two fields: a unique session ID and the program id. When a user wants to perform one of these rare operations on a program, s/he does a search of the open programs to see if there is more than one match for the program id. If so, the process does not go ahead. On closing, the database deletes the OpenPrograms record matching that session's ID.

All fine in theory and for a while in practice. I realized in time, however, that the OpenPrograms weren't being reliably deleted in client sessions. If the client gets disconnected or force quits, the OnClose procedure never gets invoked. I am testing the database off the server, so I find that sessions on other computers on my LAN will get disconnected if the host computer goes into sleep mode. With these orphaned OpenPrograms records remaining in the table, the operations in question can never be performed again on some programs, even though the user trying to invoke the operation is the only one at the time in the program.

If anyone has any insight into how to achieve what I am trying to, I would be very grateful.

[jjjjp]

My apologies to anyone who has already tried to get their heads around this one. As has happened to me before with the Forum, I don't get the insight until I have formulated my thoughts in a post, pressed the Add Reply key, and thought about the problem a little more. I will try to let a problem sit for longer in the future before reaching for the Forum.

I believe the solution (or a solution) will be to create the OpenPrograms record during the OnOpen script and to keep it open for the entirety of the session. Then I can check for all OpenPrograms record with the same program id that are also open. If any client gets disconnected, at least the OpenPrograms record created in the session will no longer be open. When I am looking for OpenPrograms records, I should also be able to perform garbage collection, deleting all orphaned records.

I haven't fully implemented this solution yet so may still hit some unexpected snags, but I want to make sure that no one wastes their time trying to come up with their own solution now that I am reasonably confident one exists.

[comment]

keep it open for the entirety of the session

I am not sure that's possible. Well, perhaps if you keep it open in another window... but then how do you keep users out of that window? And how will you be able to tell the "garbage" from the real stuff?

there are a few operations, essential but rarely performed, that require that nobody else be in the program at the time.

What exactly are those?

[jjjjp]

I am not sure that's possible. Well, perhaps if you keep it open in another window... but then how do you keep users out of that window? And how will you be able to tell the "garbage" from the real stuff?

I'll open it in a new window and hide the window. What users can do is tightly controlled, and there is only one programmer (me). Outside of development, even what someone with Full Access can do is tightly controlled. Assuming no bugs, nobody will know the window exists.

When I want to see if there are any open programs that aren't garbage, I would search for the ones that match the current program id and then go through the found records, trying temporarily to open each one in turn. When I hit an already open one (i.e., get an error on trying to open the record), I'll know that another program with the same ID is truly in operation.

Going through the found records and getting rid of the orphans is, I think, the trickiest part, since there is no guarantee that the found records will sit still for me. Found records might in theory get deleted as I proceed through the list. My plan is to temporarily lock (by opening) the parent record of the program (the "organization") just before I go through the found records. If the organization is already blocked, then the user has to try again later. This is no problem. The operations in question (see below) can be postponed. To make this work, I will also, then, need temporarily to block the organization record in my OnClose script when I delete the OpenProgram record as the end of the session. If that record is already open, then the OpenProgram record will have to remain as an orphan till someone comes along and collects the garbage. The important thing is that the record will cease to be open as soon as the file is closed.

This is admittedly much more complicated than I would like it be, but I can't think of a simpler way of going about things. (Any ideas?--assuming you will grant that what I am trying to do is necessary. See below.)

What exactly are those?

(1) Delete a program. I want it to be possible to delete a program if there is no essential data associated with it (in particular, workshops). The process of deleting a program involves getting rid of the inessential data that supports the program (defaults, locations, presenters, etc). But there is nothing to stop a user from going into an undeleted but deletable program. If the user is in the middle of a session that gets deleted, it will be as confusing as all hell for the person. So in my DeleteProgram script, I block logins by setting a BlockLogin field in the program record, and then I check whether there are any open programs with the relevant program id (not the same as the id of the program I am in). In the OnOpen program, I create and open an OpenPrograms record and then check whether logins are blocked for the program the user wants to log into.

(2) Change the setting which indicates whether workshops are to exist in groups (called a series) or not. I allow users to change their mind about this after their program has already been operating and collecting data. A number of things have to happen to make the conversion from "include series" to "don't include series" (or vice versa) work, and it would be impossible to proceed safely with anyone else in the same program at the same time. You just helped me with this setting change today: I was having trouble being sure that the setting had indeed been modified. All other setting changes are innocuous and don't require that the program not be open elsewhere.

[comment]

If the user is in the middle of a session that gets deleted, it will be as confusing as all hell for the person.

As with any multi-user application, users should be aware that other users may do stuff that affects them. I don't see much difference between this and another user deleting a record that I happen to be browsing (of course they cannot delete a record that I am editing).

I didn't get the second point.

[jjjjp]

As with any multi-user application, users should be aware that other users may do stuff that affects them. I don't see much difference between this and another user deleting a record that I happen to be browsing (of course they cannot delete a record that I am editing).

I take your point and will think concretely some more about the consequences of leaving things more unrestricted. I'd be happy to uncomplicate my database as much as I can, and I appreciate the ways in which the Forum has in the past served this function by convincing me of the adequacy of the built-in tools native to Filemaker. In this case, however, I am still concerned that a program will get deleted, another user will create workshops and other data after the program has been deleted, and then will start recreating data that will stay orphaned in the system--attached to a program that no longer exists--in perpetuity. Maybe this is less of a problem than I think it is. There may be other problems, but I think that the structure of global fields created for the session might allow the user whose program has been deleted to get through the session unscathed. Again, I'll need to think some more. My main concern is that the user doesn't get hit with all kinds of errors (e.g., finds and, worse, references that don't work).

I didn't get the second point.

That's fine; you would probably need to understand the functionality of the database much more than you now do to get it. In this case, however, there is definitely potential for invalid references, but there might also be ways of preventing them.

[comment]

I am still concerned that a program will get deleted, another user will create workshops and other data after the program has been deleted

That can be prevented by validating the ProgramID field in the Workshops table. Besides, your solution doesn't prevent the same thing happening a month after a program has been deleted.

I think this is more about business rules than about Filemaker. As you say, I don't have the necessary overview, but I would tend towards a process of [a] sending a memo to users that Program X is to be discontinued; marking the program as "being deleted" (this should trigger a warning when accessing it); and a few days later [c] marking the program as discontinued (this should deny access).

[jjjjp]

That can be prevented by validating the ProgramID field in the Workshops table. Besides, your solution doesn't prevent the same thing happening a month after a program has been deleted.

Well, once a program is deleted, a user can't log into it and create new records that match the program id. But your tip is valuable and, assuming I get rid of the baroque machinery I was putting in place, as I am inclined to do, I will follow up on it. The one remaining sticking point will be a handful of finds that were set up on the assumption that as long as a user is in the program, the program exists and certain records therefore have to exist. I'll need to zero in on these finds and add error testing that doesn't now exist.

[comment]

baroque machinery

:)

[jjjjp]

Thanks so much for the invaluable help over the last day.

[bcooney]

I was thinking that you may be interested in Transactional Scripting as described by Todd Geist. That is, if multiple records need to be edited, they are all edited or the entire process fails completely and all records are back in their original state. This is especially useful in multi-user setups where there may be a record lock, and for session interrupts (such as on FMGo). See Todd's blog.

[jjjjp]

Very sorry to re-open what was looking like a closed case, but I am coming up against a problem that, it seems to me, error testing can't prevent.

All of the settings for a "program" are stored as fields in the program record. As part of the OnOpen script, all of the fields for the program record are transferred one-by-one to a table of globals that reproduces the program settings. These globals control much of what will happen during a session. If another user deletes the program after the user selects his program but before the settings are transferred, no problem: the OnOpen script can check to see that the program still exists before transferring settings. But if the other user deletes the program right in the middle of the transfer, then the record will change under the user's nose, and wrong data from another program will be transferred to the table of globals. Or if this was the only remaining program in the organization (its parent), then Filemaker will generate dozens of error messages as it tries to transfer data from an invalid (non-existent) record.

Similar problems will occur if, after successfully logging in, the user clicks on a button to modify the program settings. S/He then begins editing a record that could disappear or turn into another record right in the middle of the process. Again, I don't think that careful error checking will protect me here.

Maybe I'm missing something, but I think I am may need to go back to thinking of a way to ensure that I never delete a program when someone is logged in under that program, which means going back to the complex machinery of keeping tabs on "open programs."

As for the last suggestion, if I understand the concept of transactional scripting correctly, I think it could provide a fix, but it would mean disallowing some logins, possibly a fair number. I could block the program record while a program is being deleted, which would trigger an error during login, but program records get blocked for other reasons as well, and each one, it seems to me, would lead to an unsuccessful login if the two operations overlapped. The only good reason to block a login would be that the program is in the process of being deleted.

An alternative may be to disallow deletions of programs, except by the system administrator. A condition could be added to delete only if the number of users is <= 2 (the server and the system administrator). Other users could still make a program inactive, which is one step short of deleting it. (Inactive programs don't appear as choices on most menus.) This is starting to look like the most attractive option.

Though, when this thread started, I was about 85% of the way towards a solution that tracked "open programs," I do agree that when something gets too elaborate and complex, it is time to rethink.

[comment]

As part of the OnOpen script, all of the fields for the program record are transferred one-by-one to a table of globals that reproduces the program settings.

I am not sure why this is necessary. Especially if - as you say - the program record can be modified by another user while this session is still in progress.

S/He then begins editing a record that could disappear or turn into another record right in the middle of the process.

No, that cannot happen.

I am still not getting a clear picture of what are you trying to achieve. In particular, I don't see enough of a distinction between viewing records and modifying them.

[jjjjp]

I am not sure why this is necessary. Especially if - as you say - the program record can be modified by another user while this session is still in progress.

Having the program settings available as globals is a tremendous programming convenience with no downside that I'm aware of. Various defaults are available for conditional formatting of layouts, and there is no need to switch layouts in scripts to have access to these defaults. These settings will change rarely, and there is no problem if another user changes any in the middle of one's session. The user can proceed using the slightly out-of-date defaults.

But some users (not all) have privileges set to modify the program settings. In that case they need to go into the actual record and change fields. The corresponding globals for the user's session are updated at the end of the modification process.

No, that cannot happen.

Why not? When an unblocked record is deleted another one comes up in it's place.

I am still not getting a clear picture of what are you trying to achieve. In particular, I don't see enough of a distinction between viewing records and modifying them.

A bit more of an overview. A program (name not to be confused with computer program) is in an organization. Each program divides its programming up between (its children) years. Each year's set of offerings consist of series, which in turn consist of workshops. So when users log in they see a schedule for the current year of their program's workshops. (Used to be a portal based on records for year but now a form in list view based on workshops and filtered via a find that can be modified throughout the session). This schedule forms the centre of activity for the database. Users can add or delete series and workshops as well as add or delete related data such as presenters, topics, locations, supporting presenters. They can send out email reminders and batch emails to presenters about their commitments. The program comes into the picture when the user adds related data to a workshop. These types of data are filtered using a program id match field. Most of the time the program records aren't being directly modified or even viewed, though if the user decides to switch to a different program mid-session, all of the workshops showing on the schedule will change completely to reflect the years, series, and workshops that exist for that program. You were right that if another user deletes the program and thus all of the supporting data for a program (all of the presenters, topics, etc.) that won't be an issue. I have made the absence of workshops a precondition for deleting a program, so the workshops will already have disappeared. The supporting data will just disappear from the portals through which that data is managed, with no errors caused. But deleting the program becomes a problem when the user asks to do anything that directly relies on the program record--such as asking to modify program settings--or when the user is in the middle of creating a program record during login. No problem when the user tries to view (or use without modifying) the global copies of the program settings, which happens fairly often. Big problem when the user tries to change the program record, which happens rarely.

I find it very frustrating sometimes that the things that can take the most effort are the things that rarely occur but that still need to be accounted for if you are to create a watertight (i.e., error-free) experience for the user. I don't know whether this is a common feeling, or whether I simply have a knack for creating complicated boundary conditions.

[comment]

S/He then begins editing a record that could disappear or turn into another record right in the middle of the process.

No, that cannot happen.

Why not? When an unblocked record is deleted another one comes up in it's place.

It cannot happen because when User A begins editing a record, it becomes locked - and User B cannot delete it until User A is done and commits the record. I think this should also answer your concern re:

But deleting the program becomes a problem when the user asks to do anything that directly relies on the program record--such as asking to modify program settings--or when the user is in the middle of creating a program record during login.

This part is not quite clear:

The program comes into the picture when the user adds related data to a workshop. These types of data are filtered using a program id match field.

Do you mean a workshop's child record contains the ID of the grandparent program? It shouldn't.

[comment]

they need to go into the actual record and change fields. The corresponding globals for the user's session are updated at the end of the modification process.

This update affects only the user that had modified the record. Other users, who have loaded their globals before the modifications, are unaffected.

[jjjjp]

It cannot happen because when User A begins editing a record, it becomes locked - and User B cannot delete it until User A is done and commits the record. I think this should also answer your concern re:

Understood, but the user can go into the modify program settings script, change a field, click outside the field, and the program becomes open again until he goes back into a field. And in the OnOpen script, nothing is being modified; data is being copied. So there has never been a reason to open the program record. But if another user deletes the record, the wrong data will be copied. I could of course change tack and open the record prior to copying, but then that means that logins should fail if the program record is being modified by somebody else. This is undesirable and generally unnecessary. Sometimes the program record is open when a transaction involving its children is occurring (changing value lists used in batch emails) that can't be protected simply by opening the children records. Whenever this is happening, someone won't be able to log in. You can I hope begin to see that the solution to my complicated procedure of keeping records of open programs creates a host of other complications.

This part is not quite clear

Sorry. I think, though, that what I was talking about there is pretty much what we're talking about now, so you can probably forget about that unclear phrasing.

Do you mean a workshop's child record contains the ID of the grandparent program? It shouldn't.

A workshop doesn't contain a field for the program id. Only the related data in the workshop do. A presenter, for example, is created by the program user, and the program id ensures that the presenter can be attached to a program workshop at any time.

This update affects only the user that had modified the record. Other users, who have loaded their globals before the modifications, are unaffected.

Exactly. But the nature of the settings is such that this is not a problem. These settings are mainly preferences that the user can modify at some later point. For example, the default for the length of a workshop may be 60 minutes. Someone may change that default to 90 minutes, but another user already logged in will have 60 minutes as the length of the next workshop he sets up. Nevertheless, the 60 minutes can still be set to something else before the workshop is committed.

[comment]

the user can go into the modify program settings script, change a field, click outside the field, and the program becomes open again until he goes back into a field.

Well, it's possible to keep the record locked until the user clicks "Post Changes" or "Cancel" - see:

http://fmforums.com/forum/index.php?showtopic=66874&view=findpost&p=317187

if another user deletes the record, the wrong data will be copied

If you are truly worried about the record being deleted in the split second it takes to load the variables, you could have your script check the program ID before and after.

BTW, I have already hinted at this: I am not sure you should be deleting records for real. Don't you need to keep history of past programs?

[jjjjp]

Well, it's possible to keep the record locked until the user clicks "Post Changes" or "Cancel" - see:

http://fmforums.com/forum/index.php?showtopic=66874&view=findpost&p=317187

Thanks. Excellent to know about this. I never really understood, nor paid much attention to, the "automatically save" checkbox.

If you are truly worried about the record being deleted in the split second it takes to load the variables, you could have your script check the program ID before and after.

Yes, that would certainly plug up the loophole in the modify settings script, but the login procedure would remain an issue because I still don't want to open the program record on logging in just to copy data, since that would necessitate aborting some logins.

BTW, I have already hinted at this: I am not sure you should be deleting records for real. Don't you need to keep history of past programs?

In many cases, deletion is not an option in my database, or I make it very hard to do a deletion (e.g., of a past workshop). But I have made a practice of allowing deletion of data when no substantial records have been created, just in case the user starts something new and then decides early on to abandon it. So I wanted to allow deletion of programs up until the point where a workshop was created, no matter if presenters, locations, etc, had been.

Given all of the complications, however, it is tempting, as I suggested earlier, to raise the bar on deletions by requiring that there are no other users. I could even, to make things even easier on the programming end, allow only the system administrator to do the deletion upon request when the database is offline. Allowing no deletion of programs is also something I have been considering. I have certainly discovered the drawbacks of trying to do all of the things that seem possible in theory. In any case, I have learned a great deal during this exchange, and you have done me a real service by moving me in the direction of simpler and more expedient solutions.

[comment]

you could have your script check the program ID before and after.

I still don't want to open the program record on logging in just to copy data

No opening of the program record is necessary.

[jjjjp]

No opening of the program record is necessary.

I may be wrong about this, but if the very last program in the organization is deleted in the middle of copying, the references on the right side of the set field script step will no longer be to a wrong record--which could be remedied afterwards with an id check, a program-generated error message, and a close file command--but to a non-existent one, which would produce a string of FM-generated error messages.

[comment]

I am not following you. How did the user select the program?

[jjjjp]

I am not following you. How did the user select the program?

The user selected the program via a portal offering a selection of all the programs in the organization. But that program could disappear while the data is being copied. It's that split-second thing you raised a couple of posts back, which you successfully closed up in one of the two cases. No doubt, I'm being way too fastidious, but I've convinced myself a while back always to close up the gaps in my code. Am I being unrealistic? I'd be much happier with no deletions of programs than with something that could fail in theory.

[comment]

So they clicked on a portal row - and what happened as a result?

[jjjjp]

A button-generated script places the program id in a global field (not in the same table as the program globals). The portal layout is closed. A find produces the correct program record in the program table and the copying (to a virtually identical layout for the global layouts) begins. The transfer relies on the identical field names in the two layouts.

[comment]

Why don't you simply set the globals directly from the portal row - e.g.:

Set Field [ AnyTable::gWorkshopLength ; Programs::WorkshopLength ]

Set Field [ AnyTable::gAnotherSetting ; Programs::AnotherSetting ]

...

Now, my guess (which I am not going to test, but you could) is that if another user deletes the record in Programs during the runtime of the script, the script will still complete successfully. But if I am wrong, you can always do what I suggested earlier, i.e.:

Set Variable [ $programID ; Programs::ProgramID ]

Set Field [ AnyTable::gWorkshopLength ; Programs::WorkshopLength ]

Set Field [ AnyTable::gAnotherSetting ; Programs::AnotherSetting ]

...

If [ $programID ≠ Programs::ProgramID ]

# SCRIPT FAILED

End If

---

BTW: I suspect that if you calculate the chances of such coincidence, and assess its possible damages, compared to the efforts you are expending to prevent it...

[jjjjp]

Hmmm... Well, I just created a portal, deleted the corresponding record, and the portal row disappeared, but I would trust your judgement and your experience over mine on what happens in such unusual circumstances.

Over the last two or three hours I made the decision to go with the relatively simple and safe allow-deletes-if-there-only-two-users option, and have implemented and tested it. I have promised that this database would be up and running in its multi-program version by August 1, and there is still too much other error-testing I would have to add in parts of the code I haven't bothered to discuss (because they are in the end less problematic) to make this feature worth the extra effort at this point. There are still a few more features I need to add to the database and a good deal of testing to do. I am happy that I got rid of the complicated machinery you convinced me to let go of, and happy enough with my compromise.

I will plan to try out your suggestion, if only to satisfy my own curiosity, as soon as I have enough space to do so, and if anything interesting emerges, I'll post it on this thread.

BTW: I suspect that if you calculate the chances of such coincidence, and assess its possible damages, compared to the efforts you are expending to prevent it...

No question that on a cost-benefit analysis I fail worse than miserably. Which is why I'm moving on to the next thing ...

Thanks again for your help.

[comment]

I would trust your judgement and your experience over mine on what happens in such unusual circumstances.

Not good. This is a guess, not a judgment. And the question is not whether the row disappears, but what happens if the record is deleted while the script executes. You can test this by looping a Set Variable[] step enough times to give you the time to delete the record. And the record should be deleted from another account, not another window.

[jjjjp]

Not good. This is a guess, not a judgment. And the question is not whether the row disappears, but what happens if the record is deleted while the script executes. You can test this by looping a Set Variable[] step enough times to give you the time to delete the record. And the record should be deleted from another account, not another window.

Ok, understood. I'll try to get back to this (but unfortunately can't right away).

[Vaughan]

Given all of the complications, however, it is tempting, as I suggested earlier, to raise the bar on deletions by requiring that there are no other users. I could even, to make things even easier on the programming end, allow only the system administrator to do the deletion upon request when the database is offline. Allowing no deletion of programs is also something I have been considering. I have certainly discovered the drawbacks of trying to do all of the things that seem possible in theory.

Use record level access privileges to restrict program record deletion is a program has been used (ie, has related records). That way an unused program can be deleted, while those that are used cannot.

[jjjjp]

Use record level access privileges to restrict program record deletion is a program has been used (ie, has related records). That way an unused program can be deleted, while those that are used cannot.

Thanks, Vaughan. Another feature of FM that I wasn't aware of--thought it would be in Manage Databases and assumed it didn't exist because it wasn't there. I'm not sure this will take care of the boundary case that this thread has most recently been about, but it is good to know about in any case.