Edit records

[AlwaysWilling]

Hi people, I need some assistance here. We are coding a new CMS system and would like to know how to deal with updates. My scenario is this: suppose Bill logs in and clicks on the link for Product1 to edit/modify. Now, suppose Jane logs in 5 minutes later and she also clicks on Product1 to edit/modify. Now we have both Bill and Jane trying to modify Product1 info, how can I prevent Jane from modifying that record until Bill is finished? I would like (if its possible) to allow Jane to see the existing info but not make any changes, possibly show a alert on top of page saying that 'this is record is open for editing by another user'.
If that's not possible, gray out the link itself so she can't click on it, until Bill finishes his editing.
Any ideas?
Thank you very much.

 

[AlwaysWilling]

Any ideas? Suggestions?

 

[AlwaysWilling]

No one has any ideas?

 

[daNewfie]

Create a new field in the product info table call it "Indicator".
store a one or a zero in that field (default it to zero).

When Bill initally goes and retrieves that record. only allow it to go to edit screen if "Indicator" = 0 otherwise
bring him to view screen.

ok, we will assume that indicator is 0 and bill can edit the record, first thing we have to do is update the indicator so jane cant get in.
Bill makes his updates and clicks save, you write your changes to the table and reset your indicator back to 0


I dont have the code for this, its just off the top of my head...

 

[imstillatwork]

I had to deal with this on an intranet helpdesk system with multiple techs updating the same ticket at the same time. ended up using a user_id field, timestamp and locked fields along with a cookie that expired on browser close or logout...

instead of completely locking out a user, say, this is in use, are you sure you want to edit it? that way if the locks ever fail for any reason, you can still get in.

Kevin

http://www.lot-o-nothin.com/cfml/

 

[AlwaysWilling]

Hi, sorry for the delay in responding back. imstillatwork how can I implement your idea? I want something to that effect.

 

[scon44]

Perhaps use CFLOCK? Lock the record so that the second user can only read it, and not modify...

 

[imstillatwork]

I always wanted to experiment with named locks and throwontimeout with cftry / catch. Should do that someday.

Kevin

http://www.lot-o-nothin.com/cfml/

 

[AlwaysWilling]

Hmmm, <cflock>? I guess I can try that. Anyone have a good example other then the one from macromedia?

 

[NorthStarDA]

i would simply add a locked and a lockedTime columns to that table, and upon pulling up the edit page set the locked flag and mark the time it was done. (as in imstillatworks solution)

then on the save button, you have that script unlock the record and clear the time.

this presents the obvious issue of someone navigating away from your site in the middle of an edit and leaving it in a locked state where others can only view.

i use an hourly scheduled task to look for records with locks and if the difference between now and locked time is greater than my application timeout, i unlock the record. this way, if someone quits the browser before unlocking a record, it will only stay locked for as long as the application timeout is.


=========================================
I have not failed. I've just found 10,000 ways that won't work.
Thomas A. Edison

 

[AlwaysWilling]

NorthStarDA, thats seems like a good idea. Do you use this setup also?

But does it matter that I need to run an UPDATE statement everytime the user tries to edit the record the first time? This is what I was thinking on implementing it, let me know if you see any flaws:

1. User1 clicks on the link to edit record1
2. record1's edit page, I run a select query to retrieve all the info, and then an update query to set the isEditing to 1 (true).
3. User2 clicks the same record 5 minutes later
4. record1 page loads, I run a query to check and see if the record's isEditing field is true or false. If true, I load all the info but make the fields 'readonly' with a note alerting the user someone else is editing this record.
5. User1 clicks on the 'save' button, and I run the update query where all fields are updated, and the isEditing field is set back to 0 (false).

Does running so many update queries an issue? Just so we're clear we do not have hundreds of people vying to make updates. At most we have 5 people now, and maybe total of 10 in the far future. Only admins have that right, no one else.

Thanks.

 

[imstillatwork]

That's pretty much it. I like having a warning page that says something like "this record is in use by #username# since #lockdate# Are you sure you want to edit this record? yes | no

This way you can get around any false lock without waiting for the update.

Kevin

http://www.lot-o-nothin.com/cfml/

 

[AlwaysWilling]

But then how do I handle users that simply close their browsers or naviagate to another section without clicking the 'submit' or 'cancel' button?

 

[imstillatwork]

You would allow another user to edit the record if they said YES to what I said above. Then, the scheduled task would remove all expired locks at the scheduled time. So a closed browser, etc, would not be an issue.

Kevin

http://www.lot-o-nothin.com/cfml/

 

[AlwaysWilling]

Ho do I check if the application timeout is greater then the lockedtime? Also, what happens when the user just clicks on another link instead of continuing with the edit record? I mean their still logged into the system and doing work.

This is the scenario I was thinking of:
1. Bill logs in and click record1 to edit
2. Five minutes later Jane logs in and clicks the same record
3. After 10 minutes Bill decides he needs to do something else first, so instead of clicking the 'cancel' button, Bill clicks on price1.

In the above case, Bill is still logged in and he still has record1 locked but now he also has price1 locked. How can I then unlock record1?

I hope this makes sense.