Dealing with incompletely created records

[jjjjp]

A while ago I had to deal with a problem of frequent interruption (see http://fmforums.com/forum/topic/79247-unresolved-interruptions/page__fromsearch__1), which my IT person finally solved. (I still don't have a description from him of his solution that I can share on the Forum, though I gather it was fairly server-specific.)

I have discovered that interruptions can still happen at inopportune times for unavoidable reasons--for example, the user's computer goes to sleep while a script is paused and a record is not completely entered and validated.

It appears that the record remains in the database in incomplete form. This can cause problems going forward.

Are there best practices for dealing with this issue? Does one inevitably have to perform routine garbage collection to spot and delete such problem records?

[comment]

I am not sure what you mean by "incompletely created record". Has the record been committed? If not, then it doesn't exist. Use validation to prevent committing records with incomplete data.

[bcooney]

The best way to ensure that a record is not left in an undesirable state is to create/edit records transactionally. That is, the record is either completely entered/edited or all changes are abandoned.

[jjjjp]

I am not sure what you mean by "incompletely created record". Has the record been committed? If not, then it doesn't exist. Use validation to prevent committing records with incomplete data.

I have been able to recreate something similar to an actual situation: I am in the middle of a dialogue to create a new record on a client computer, and I put the client to sleep. If I wait long enough, I lose the connection. The record appears to have committed rather than deleted. I see it in the table after the fact, but it is incomplete in the sense that, for example, the Active field which my script would usually set to 1 after the end of the process is left blank.

Your proposed solution sounds promising, but I can't quite get it to work. I thought that since I generally set the Active field to 1 at the very end of the process of creating a new record, I would validate the record by requiring that Active be non-empty, and by specifying that the field should occur always (see attachment titled "validation"). But I get the attached error message, which expects Active to have a value as the user starts creating the record.

How should I be going about this?

The best way to ensure that a record is not left in an undesirable state is to create/edit records transactionally. That is, the record is either completely entered/edited or all changes are abandoned.

Sorry, I'm not clear how to abandon changes (except somehow after the fact), since the process gets interrupted on the client computer midstream. The script controlling the entry of the new record apparently doesn't have a chance to complete.

validation.png.zip

error message.png.zip

[comment]

Well, you could set the Active field to auto-enter "1". However, this:

I get the attached error message ... as the user starts creating the record.

lights up a warning. The error message is the result of an attempt to commit the record - so it looks like your script does something it should not be doing.

---

P.S. Please do not zip attached images.

[jjjjp]

The error message is the result of an attempt to commit the record - so it looks like your script does something it should not be doing.

Yes, you're right. There is a problem with how my scripts go about creating new records.

I do most of my validation inside my own scripts--checking for empty fields and for duplicates of combinations of fields. To do this validation myself, I must execute a find, and while I'm not sure that this fully commits the record (the new record remains invisible to other users, whereas a Commit Records step would make it visible), it certainly does trigger the error message. I imagine there's no way around this?

I did my own validation when I first started creating scripts before I realized the full potential of FM's built-in validation. I tried switching to FM's validation at some point but I found the messages to users to be somewhat complex, if not confusing, particularly its handling of multiple records in portals, so I chose to stick with what I had. If using FM's validation is the only elegant way to ensure that I don't end up with unwanted records after interrupts, I'm certainly willing to give it another go.

[comment]

First, you should use Filemaker's native validation, as this the only way to ensure enforcement at the data level.

A scripted "pre-validation" is a fine way to prevent validation error messages - but it needs to be planned carefully, otherwise the record will be committed or an error message will be thrown. For example, performing a find in the same window will definitely commit.

And no, you don't need to perform a find to indicate duplicates - you should have remembered this from:

http://fmforums.com/forum/topic/72979-how-to-navigate-layouts-using-find-when-there-is-no-serial-id-on-the-layout/page__p__345272#entry345272

[jjjjp]

And no, you don't need to perform a find to indicate duplicates - you should have remembered this from:

http://fmforums.com/forum/topic/72979-how-to-navigate-layouts-using-find-when-there-is-no-serial-id-on-the-layout/page__p__345272#entry345272

You have an excellent memory. BTW, I do remember that there is a way to check for duplicates without a find. That earlier post, way back, helped me, among other things, to avoid unnecessary self-joins, but I stuck with the finds while they worked, knowing that at some point I would probably have to make the switch over to FM's native validation capability. That time has clearly come. Thanks for the help.

[bcooney]

Transactional scripting is best explained by Todd Geist, the recognized advocate in the FM community. I'd read his entire site, if I were you (actually, that goes for everyone). He also just posted a wonderful video explaining record commits.

[jjjjp]

Transactional scripting is best explained by Todd Geist, the recognized advocate in the FM community. I'd read his entire site, if I were you (actually, that goes for everyone). He also just posted a wonderful video explaining record commits.

Thanks. I think I'm following the basic principles of transactional scripting but will read up (and listen) more to try to identify unforeseen vulnerabilities.

[jjjjp]

First, you should use Filemaker's native validation, as this the only way to ensure enforcement at the data level.

A scripted "pre-validation" is a fine way to prevent validation error messages - but it needs to be planned carefully, otherwise the record will be committed or an error message will be thrown. For example, performing a find in the same window will definitely commit.

I have attempted to change my data definitions and scripts so that I am relying on FM's validation and see myself as committed now to this way of doing things. However, as I rediscover the complication and inelegance that seem to go hand in hand with FM validation, I remember why I shied away from it in the first place.

2 situations that present problems:

(1) Creating a new record on a layout in Form View. FM's validation error messages are complicated enough for most basic users (my users don't have to think about the concepts of records or reversion or layouts), but I think because I am validating for uniqueness using text fields that don't appear on the layout, there are two layers of dialogue for the user, one of which will appear wholly superfluous to users. I have managed to avoid FM's validation error messages by trapping error messages and providing my own based on the error code--as long, that is, as the user clicks on the buttons on my layout. But when the user clicks anywhere else on the layout, then the system validation messages appear instead of mine. Is there anyway to neutralize the act of clicking outside of fields? Or to activate my own error message when that happens?

(2) Modifying an already existing record on a portal. When I was performing my own validation, I used script triggers to carry out the process of remembering what was originally in the field and checking for non-emptiness and uniqueness upon exiting. But I cannot even begin to think about how to use script triggers to override FM's error messages. Part of the problem is that if clicking outside a field tries to commit a record, how do you turn Error Capture on if you're not yet in a script and at the same time record the last error code? But reconciling myself to FM's error messages here is less than half the problem. When the changes I make to a field are invalid, the focus jumps to a different portal row entirely. Totally disorienting for a typical user.

I'm sure that many other FM programmers have struggled with these complexities before. You mentioned that it is possible but tricky to avoid FM's validation error messages. Can you suggest how I might successfully do so, and also avoid the jumping of rows in situation 2?

[jjjjp]

I think I figured it out, using script triggers.

In situation 1 (Creating a new record on a layout in Form View): I want to commit and validate only if I clicked a button on the layout. When I click the button, a global field is set to "ok." I created a script triggered in the layout on record commit that returns 1 if the global field is set to "ok" and 0 otherwise. That way no attempt is made to commit if I click outside a field.

In situation 2 (Modifying an already existing record on a portal): I created a script triggered on object validation that captures error messages and generates the error messages I want. The trick was to remember to have the script return 0.