How to swap data in two records simultaneously

[jjjjp]

To simplify my situation: I have two records in Table B, with parent A. I want to swap the contents of a field in the two records, but if the operation is interrupted for some reason (e.g., a network interruption), I want to be 100% sure that the records will revert to their state before the swap, i.e., that they won't be committed half way through the swap.

 

My question is whether the only way to do this properly is with a portal: Create a portal for record A, then go to the two rows in question. So in effect the two records are open, as is their mutual parent. Perform the swap and then commit all records while in the portal. If the operation is interrupted, then all records go back to their original state.

 

I have found that almost everything I can do with portals can be done some other way, so I'm wondering if this is an exception. Also, I'm wondering whether there is some other way to do what I'm asking without having to lock record A.

 

My question is somewhat theoretical, I recognize. What are the chances, after all, of an interruption occurring in the micro- second between the steps in a swap? But I feel more comfortable making my scripts airtight.

[Wim Decorte]

 

My question is whether the only way to do this properly is with a portal: Create a portal for record A, then go to the two rows in question. So in effect the two records are open, as is their mutual parent. Perform the swap and then commit all records while in the portal. If the operation is interrupted, then all records go back to their original state.

 

Yes, that is the way to go.  It is the only way in FM that you can have multiple open records at once, which is what you want in your scenario so that you can commit them as a batch or revert as a batch.  Or as a transaction:

http://www.modularfilemaker.org/module/transactions/

[jjjjp]

Thank you, Wim.

[jjjjp]

Actually, I closed this post prematurely. I'm hoping I can pursue this a bit further.

Let's say B, with parent A, has its own children in table C. I want to let users select a record in B and edit the children, and I want to offer the option of cancelling. So what I have done till now is duplicate the record in B and all its children in C, but for all these new children in C, I reset the referent to B, BID, to the new record in B. Till now, I have done all of this in table views. I have a field in B that I call Hidden. I set the Hidden field of the new record in B to 1 so that no other users can see it or operate on it. Basically, a record in B counts and is visible to users only if it is not hidden. If the user clicks Cancel, the temporary record in B and its children get deleted. If the user clicks Accept, the Hidden field gets swapped in the new and old record in B. Then I delete the old record and its children. When I switch to using portals, as per your advice, I will ensure that the swap of the Hidden filed is simultaneous, and so there will be no possibility of corruption of my data.

The only loose end is garbage collection. If a user's session is aborted before the process ends, there will now be unrelated records in C. They can be tidied up later and in principle shouldn't be a problem. (I have discovered recently, though, that garbage college is essential after every import; otherwise surprising things can very occasionally happen.)

What I'm wondering is if there is a way to extend committing and reverting in batches to three rather than just two levels, so that garbage collection becomes unnecessary, and everything happens cleanly at once. Perhaps by having two portals on the same record for A, one showing related records on B and the other showing related records on C? Or is this too complicated? Or maybe it's actually simple. Ideally, I'd like to not have to duplicate anything but just to have the user alter the original records via a single portal (though the portal I set up wouldn't quite be the same one I would want the user to see, so I'm hoping they don't actually have to be the same). The changes world revert or they would be accepted.

This must be a fairly common situation. Are there best practices?

Thanks in advice for any insight.

[Wim Decorte]

For a transaction, everything has to be a child of the transaction parent.  In order to avoid garbage collection, the best option is to create a transaction table and use a layout with multiple portals.  The transaction table (T) in your scenario would have portals to A, B and C, each one triggered by their own relationships.  The whole idea of the transactional model is that you never do anything that would cause the transaction record (in T) to commit until you are ready.

 

In your scenario you may need to show all related records in B and C at the start of the transaction, you can't reset any of the relationships once you start the transaction.

[Lee Smith]

Hi jjjip,

 

Actually, I closed this post prematurely. I'm hoping I can pursue this a bit further.

Let's say B, with parent A, has its own children in table C. I want to let users select a record in B and edit the children, and I want to offer the ....

Please don’t ask your questions in the abstract, use real names for your fields, layouts and files. Not only will it make it easier to follow your questions, we can respond with better clarity.

 

Even better, post a demo or copy of your file.

 

Lee

[jjjjp]

Thank you, again, Wim. I realize now that since I won't need the temporary records, I can go with a more straightforward layout (not two portals but one) and dispense with the swap. I'll see how it all works out in the implementation and re-open this post if I run into snags, but I think it should be fine.

 

Sorry, Lee, about the abstract framing of my question. I think you asked me once before (a while ago). I actually thought that in this case, abstracting away the inessential details would make the question easier to follow, and there were at least two concrete instances in my database to which the question applied. But I will redouble my efforts going forward to adhere to the preferred practices of this forum community.

 

You also raise the possibility of sending a copy. That's always an issue because I manage accounts and passwords myself, something I never wanted to do. When I share the database, I need to launder all of the passwords (inaccessible though I have tried to make them). My IT person at the time I created the database said that authentication at the service level was a non-starter because he would have to create accounts beforehand on our college's server, and most of our users are outside the college.

 

Do you or does anybody else know whether what my IT person said is true? I would very much like to try again now that we have a new IT person, but he knows very little himself about Filemaker.

 

Let me know if I should start a new post.

[comment]

I actually thought that in this case, abstracting away the inessential details would make the question easier to follow

Abstract names like A, B and C are never easy to follow. If you want to generalize your example, use generic names like Parent and Child.

 

When I share the database, I need to launder all of the passwords

On those occasions that you want to demonstrate the issue using a file, it's best to construct a new file from scratch, containing nothing that's not essential to reproducing the problem. That not only makes it easier for others to follow; in my experience, more often than not an attempt to construct such file will lead to discovering the solution by yourself.

[Lee Smith]

Abstract names like A, B and C are never easy to follow. If you want to generalize your example, use generic names like Parent and Child.

 

On those occasions that you want to demonstrate the issue using a file, it's best to construct a new file from scratch, containing nothing that's not essential to reproducing the problem. That not only makes it easier for others to follow; in my experience, more often than not an attempt to construct such file will lead to discovering the solution by yourself.

[jjjjp]

I have run into one challenge in moving to the transaction model for committing a parent and children records all at once using a portal. If I have a record from the table Workshops open with children from the related table TeamMembers displayed in a portal, and a user changes an entry for a team member, I want to check that there are no duplicate presenters. Presenters is a child of the TeamMembers table. I have set up a field PresenterUnique in TeamMembers that will cause validation to fail if I try to commit a team member but there are two team members with the same workshop id and presenter id. My question is whether it is possible to commit a row in the portal (a team member) without committing the parent record (a workshop). Otherwise, I assume I would need to run a script that tests for duplication the longer way (through a Find) without being able to take advantage of FileMaker's native verification.

[comment]

My question is whether it is possible to commit a row in the portal (a team member) without committing the parent record (a workshop).

 

No. You either commit all open records or none.

 

 

 

I want to check that there are no duplicate presenters.

 

If there should be only one team member that is the presenter for a workshop, then the presenter's identity is an attribute of the workshop and should be recorded in a PresenterID field in the Workshop table. Thus changing the presenter requires nothing more than modification of this field - and no complex swapping/validation/scripts are necessary.

[jjjjp]

There can be several team members for a workshop. A team member is constituted principally by a presenter and a unit (institutional affiliation). The only restriction is that all of the presenters (grandchildren) of any workshop contain no duplicates. So I'm wondering whether I can take advantage of the native validity checking in Filemaker to keep users from changing a presenter field in the portal to a duplicate of a presenter already present in the portal. I can just write a script that goes to a layout for team members, does a Find for the current workshop id and the new presenter id, and checks whether there is more than one record in the Find.

Since I have a tendency to reinvent the wheel and create scripts for things that can be done much more easily using the native features of Filemaker, I want to determine whether or not that is the case here. I know that I can use validity checking to check for duplicates when a user creates a new team member, but modifying an existing team member in the portal seems to me an entirely different matter.

Given this clarification of the issue, does the answer remain the same?

[Wim Decorte]

So I'm wondering whether I can take advantage of the native validity checking in Filemaker to keep users from changing a presenter field in the portal...

 

The whole point of the transaction model is that YOU take full control over the transaction.  You don't want the transaction to fail because of some validation that happens at the schema level instead of in your scripts.  Validations in FM only happen just prior to the commit, your logic should have already fired and vetted everything by then.

 

The user also should not be working in your transaction layouts.  You should collect all the user's input, vet it and then start a transaction.

[comment]

A team member is constituted principally by a presenter and a unit (institutional affiliation). The only restriction is that all of the presenters (grandchildren) of any workshop contain no duplicates.

 

I am afraid you lost me at this point.

[jjjjp]

Thanks all for your comments and suggestions. I have a clear sense now (thanks Wim) of what I can and can't do and will rely on my logic to vet everything. Sorry for any confusion.