10 Replies Latest reply on Feb 14, 2013 3:49 PM by STLABORDE

    Know when record is opened

      I have several users that will be updating records using Apex. It is my understanding and through testing that there is not easy way to LOCK a record. So I thought the easiest solution would be to let the user know when the record has been opened by someone. This would tell them to pick another record to update.

      I created a column in the table called LOCK_DATE. I created a Before Header Process that places the SYSDATE into this column. This works. I needed to clear out the date when the user exits the record.

      I created a Process - After Submit and Validations to set this column to null. This seems to work when the user hits Apply. But when the user hits Cancel the Process is not kick off (because the user did not submit anything).

      So, I can not figure out how to set this column to null when the user presses Cancel.

      First question is am I on the right track? If so, how can I get this to work.

      Second question, am I totally doing this wrong and there is a simplier way to do this.

      Thanks in advance.
        • 1. Re: Know when record is opened
          You're aiming for a pie in the sky - APEX is a stateless environment with pessimistic locking. What you're trying to achieve is simply not viable.

          If you're ever getting ORA--00054 or the browser "hangs" because of a lock - look into creating application item called apex_dml_lock_wait_time

          • 2. Re: Know when record is opened
            Jorge Rimblas
            Scott's right.
            You're going at this the wrong way. APEX (when using the built in processes) implements optimistic locking (not pessimistic) which means it's going to assume the update will be successful. If the update (or delete) is attempted on a record that has changed since it was rendered on the screen the user will get an error. The error is along the lines of checksum has changed..etc..etc..
            It's easy to reproduce. Use two different browsers. Load the same record on both. Change one. Then change the other; you'll see the error.

            There's no need to lock records at all.

            Hope this helps.
            • 3. Re: Know when record is opened
              Darn it - I had two screens open, one with forms and one with apex - trust me to use the wrong word!

              Optimistic locking - APEX - sure, of course I'm going to be able to lock the record & make changes when I submit the page
              Pessimistic locking - Forms - ooh, just in case someone else wants to change this, I'll lock the record while I make my changes - stopping anyone else from doing so in the meantime.
              • 4. Re: Know when record is opened
                I am only trying to show a user that someone else is in the record. I know that I can not prevent this from happening.

                I have a Report that leads to a form. On the report, it will display the work "Locked" or "Opened". This will tell that user not to open that record and choose another. It is just a display.

                When the user enters the form, I can fill out a column with the SYSDATE. My problem is that I need to change the value in this column to NULL when they exit. I can get this to work when they hit apply, but not when they hit Cancel.

                How do I get a process to fire when the user hits Cancel?
                • 5. Re: Know when record is opened
                  And what do you do when someone crashes in the application? Quits the application in the middle of your report? I am sorry, what you are trying to do via a stateless environment like APEX is VERY CONFUSED.. This would be doable if you were using Forms or something that, as was noted earlier.. There you could put a lock on a row while working on it and having the app notify the user the row was locked..

                  Can you explain WHY you need to do this? Is there a business requirement to this problem?

                  Thank you,

                  Tony Miller
                  Ruckersville, VA
                  • 6. Re: Know when record is opened
                    Jorge Rimblas
                    We do understand what you're trying to do. We just advise you not to. It's not realistic on a web application.

                    BUT, to answer your question, easy, change the cancel button to submit the page instead of doing a redirect (change the Action). I would set "Execute Validations" to no also so that it's truly a cancel.
                    Then make sure your process that clears the lock date fires when the cancel button is pressed.
                    You may or may not need a specific branch back to the report.

                    Here's the one of the big problems with this. If that lock_date field is part of that table you just modified the record. This will give you some unexpected results due to the optimistic locking.
                    Big problem #2... what if the user just closes the browser? or leaves it open and their session times out? or the power goes out while they are looking at the record? etc... etc.. etc...
                    Now the record will show as locked forever (or until someone hits the cancel button again I suppose) unless you clean those up regularly.

                    This is just a bad plan for a web application.

                    Hope this makes sense.

                    • 7. Re: Know when record is opened
                      Tom Petrus
                      <li>Clicks a tab
                      <li>Clicks a link
                      <li>Per accident clicks the link to the application, which will create a new session
                      <li>Hits back on the browser
                      <li>Closes browser tab
                      <li>Closes browser
                      <li>PC crashes
                      <li>Opens a record, starts making some edits, but then walks away for an amount of time
                      <li>... And upon coming back the session has expired.

                      My advice: just don't. You're not forcibly disallowing them to go to the record, but you want to use it as a deterrent. But what use if information if it is not reliable? Do you mean to say "Don't open the record unless it's over 15 minutes ago"? Maybe they can give eachother a call to check whether it's ok to update?
                      • 8. Re: Know when record is opened
                        Jorge Rimblas
                        TexasApexDeveloper wrote:
                        Can you explain WHY you need to do this? Is there a business requirement to this problem?
                        I think the users just love their forms too much and rather not see change. :)

                        Seriously though, I would demo to them how they will not have problems editing records. Demo how optimistic locking works.
                        I don't know your situation or data, but I would challenge you: Are your records really being updated that much by multiple users at the same time?

                        Ok, here's a cumbersome alternative that can be implemented with DA, AJAX and PL/SQL
                        Have an AJAX call that fires whenever any field is updated ont he form and check to see if the record has been changed already by a user (by maybe looking at a LAST_UPDATED_DATE column). If the record has changed then display a message to warn them that their record is now stale.

                        • 9. Re: Know when record is opened
                          Thanks to everyone and being patient. I understand the issue. Just as an FYI, I have about 6K records that are being "worked" by about 4 people. They are worried about people opening and updating the same record.

                          I will work on a way to "split" the records into group so a user only sees "his/her" records, thus preventing an update problem.
                          • 10. Re: Know when record is opened
                            Great job everyone!