hi
i new to scriptcase
is it possible i can flag the record somehow so if some other user is using the same record it will show it somewhere in the form? what I can think of is to add new field in the database make it e.g. accessed (yes,no) then i don’t know how to fill it with data so if next user is accessing same record can show him that somebody is on this record right now
is it possible this way or you have better idea?
Unfortunately not automatically, as this behaviour depends on your database. Some databases allow row level locking other databases check the state of the original record to see if someone has already changed it. There is no default behaviour. Even the sql might differ as setting up transactions will differ too technically spoken. If you run a desktop application you could try to lock a record ‘for update’ since the lock will be released if you quit the program. But anyone can quit the browser and you are depending on timeouts or won’t be notified at all. That’s why this mechanism is very unreliable if you do that by hand. So in general yes, you cannot. In scriptcase you first select the record and then apply updates. While selecting and you have the intention to update it and your database allows you to lock the record for update you must apply the lock in the onrecord event. But I doubt what will happen if you break the form and start the application again. Is the record released? If your database supports sql syntax for row level locking then you can simply try.
I would not mark every record with fields for ‘inuse’ as they will not be released if the user quits the browser. Also it requires a lot of updates to your tables with live data. Instead I would build a transaction table and put the userid, datetime, tableid, key into that. Then in the onload I would test if the record exists in the table. If it does I would look at the date and decide if the transaction could be valid or not (how much time does a user have to fulfil his transaction). Other option is that in the onload you can delete all transactions older than the allowed time. In the onbeforeupdate or delete I would test in the database if the key is still in the table and if the current user is owner. That way you can test if the user took too long to make his update and reject it with an appropiate message.
Thank you very much aducom for your comperhansive response. I do use mysql with phpmyadmin and I have no idea if it is allowed or not, anyways it is interesting to search and try, I will try and post what I can come to but I was thinking that may be somebody did it before and can advise an easy way.
And by the way, I agree with you it is not reliable, I don’t want to lock the row, I just want to inform the user if someone else is working on the same record (at least).
Thank you again.
In that case creating a transaction table would be the way I would go for.
Thank you Mr Drent. I will try to do it while I’m new to scriptcase might take a long while until works as you described, if you could have time to explain the idea in more details will be great.
I can’t give you a fully working app but in principle: create a transactiontable with the fields which contain the primary key of the record you want to detect and apply a datetime timestamp when the request was done.
Decide how long a user is allowed to do his update, i.e. 15 minutes.
If a user is opening a form with a certain record: (You can do this in the onrecord event)
delete all transactions older than the 15 minutes
look into the transactiontable if the record is marked.
If so then apply a warning to the new user or deny.
If not, insert the primary key with the timestamp into the transaction table
decide what you do if the warning is applied, ie. the old user was 14 minutes active and the new user gets a warning. Is the new transactiontime now valid or not? Depends on your need.
Afaik, that’s it.
Thank you again dear, that is more than enough, will try it out for sure and see what happens.