Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss. print.
HOW TO HANDLE UPDATE CONFLICTS AND REFRESH DATA IN FORMS WITHIN A NETWORK MULTI-USER APPLICATION

So many times I have seen this question posted in the forum. You know the question, I am sure and it goes something like this:

"I have a network application and one user is viewing a record and a second user edits and saves the same record. How to I update the screen for user number 1 so he/she sees the changed data?"

Then the other question that comes up time after time:

"If two user’s edit the same record at the same time and one saves the record, how do you let the other user know that someone has in fact made a change and saved it?"

I have taken a bit of code out of my projects and put together a very simply example to demonstrate the answer to both of the above questions. Download my netrefresh.zip

Lets look at the Second Question First
In order to simulate how this works in a network environment, load up Visual FoxPro and set your default to the folder you have my project in. Make sure the project is not loaded and closed. Then type in DO FORM Form1 and load form1. Now load up a second instance of Visual FoxPro and set the default to the same folder and run form2 via DO FORM Form2. Next move back to the FoxPro instance running form1 and click the Edit button. Now move to the second instance of FoxPro with form2 loaded and edit my address record and save it. Now back again to the first instance of FoxPro running form1 and click the save button. You will see that you are notified, another user has changed the record. You now can decide to overwrite the record, on cancel out and loose your changes and see the record that was changed.

Now, how do I accomplish this task? When you look at the code you will see how easy this is to accomplish with Visual FoxPro. The entire control of this is handled by a function that returns .T. if the record was changed by someone else and .F. if the record has not been changed. Lets look at this very simple code example.

FUNCTION ResolveRec
    LPARAMETERS cTblName
    * --- This function returns .T. if someone else has changed 
    * --- and saved this record, and .F. if no changes are
    * --- sensed.
    * --- This function assumes that buffering is enabled.
    * --- The table, and buffering is required for OLDVAL() and
    * --- CURVAL() to work.
    * --- Check to see that buffering is on.
    lnView = CURSORGETPROP("Buffering", cTblName)
    IF lnView = 1
        =MESSAGEBOX("Buffering on this table is set off. You ; cannot use this function unless you have table buffering on!",0+16,"Set Buffering On")
        RETURN
    ENDIF
    * --- Select the table passed to this function.
    SELECT (cTblName)
    * --- Build an array of field names.
    lnRecords = AFIELDS(laFields)
    * --- Local memory variables required for this function.
    nCnt = 1
    cOldVal = ""
    cCurVal = ""
    cFldName = ""
    * --- Now process the field list in the array.
    * --- The field name is always (x,1)
    DO WHILE nCnt <= lnRecords
        * --- Get field name into the memory variable cFldName
        cFldName = laFields(nCnt,1)
        cOldVal = OLDVAL(cFldName)
        cCurVal = CURVAL(cFldName)
        * --- Now verify if this field has been changed.
        IF cOldVal <> cCurVal
            RETURN .T.
        ENDIF
        * --- Increment the nCnt memvar to process the next
        * --- field name in the array.
        nCnt = nCnt + 1
    ENDDO
    * --- If no field changes were detected, return .F.
    RETURN .F.
ENDFUNC

Now you can see that the logic in the above function is very simple and uses the OLDVAL() and CURVAL() functions to test if any data for the record has been changed. Table buffering must be turned on for this to work. SET MULTILOCKS ON is also set in the load event of both forms, and both forms have a private data session setup. Notice the AFIELDS(laFields) command and read up on this in the VFP help. This function allows me to extract all the field names in the table into a simply array and then use this in my code. The function is called in the click event of the save command button and in my example the code goes something like this:

* --- Function to determine if someone else changed this record
* --- while I am editing it called ResolveRec().
lWasChanged = .F.
lWasChanged=ResolveRec("Address")
* --- Advise this user someone else has edited and save 
* --- this record.
IF lWasChanged = .T.
    retval=MESSAGEBOX('This record was changed by another ;
            user. You can select Yes to overwrite the other ;
            person(s) entries, or No to see the changed ;
            record. Please make a choice!',4+32,'Decision Required?')
    IF retval = 6
        =TABLEUPDATE(.T.,.T.)
    ELSE
        THISFORM.REFRESH
    ENDIF
ELSE
    =TABLEUPDATE(.T.,.T.)
ENDIF
* --- Disable the input boxes and restart the timer to the
* --- screen refreshes are updating again.
THISFORM.SetAll("Enabled",.F.,"Textbox")
THISFORM.TIMER1.Enabled = .T.

Now this example is a very stripped down version of what I really use. I as well have a free table that if a change has been made the code adds the field name the version of the field data that the user is working on, and the data for the same field that has been saved. So now the user can make an easy decision to overwrite the data, or cancel his/her entries. This requires another form with a grid object on it to show all the changed data associated with the record. I have not added this into my example, but I am sure you get the drift of how easy this feature would be to add.

Question # 1
Now to the first question raised at the beginning of this article. "I have a network application and one user is viewing a record and a second user edits and saves the same record. How to I update the screen for user number 1 so he/she sees the changed data?"
This is a very simple thing to accomplish. If you look at both "form1" and "form2" you will see a timer on each form. The timer is set to fire off every 3 seconds. When the timer fire all it does is a Thisform.Refresh. So if I am looking at a record and someone changes and saves the same record I am looking at, I will every 3 seconds get an update of the most current data contained within the record. The only trick is to stop the timer from firing when you add, or edit a record. This is accomplished by disabling the timer when you click add, or edit buttons, and enabling it when you click the save button. Review both my add and edit buttons on the form’s and the save button to see how simple this really is.

If you do not disable the timer event when you attempt to add, or edit a record; when the time event fires the refresh will bump your cursor to the left hand position in the textbox that has the focus. This is very confusing for anyone trying to add, or edit data… so kill the timer when performing these operations.

I hope that this very, very simple example provides you the basics of how to handle update conflicts and screen refreshes across a network with a multi-user application. You will have to agree after looking at this stripped down example this is a no-brainer… ay?

All one has to do is to fine tune the example code to your liking, drop the function in your project, past the finalized code in your form, or button class, then sub-class what you need. These two commonly asked questions are resolved forever.

Note, as mentioned at the beginning this example will not run if both forms are loaded up in the same VFP instance. You have to double load VFP and load form1 in one instance, and form2 in the second VFP instance to simulate what you would see on a network. The main difference on a network would be the database and tables would be on a shared network drive and the various VFP exe’s would have their set default to this network folder.

ABOUT THE AUTHOR: PETE SASS

Pete Sass Pete has worked in the computer world since the late 70's. He loves the outdoors and lives in a small town called Marathon, in the north of Canada.

FEEDBACK

fallouja fallouja @ 6/18/2007 9:07:32 AM
thnk's form tunisia !!

this what i need

Amol Deole @ 5/7/2010 11:19:09 AM
Is the deletion of a row in a grid (instance 1) and updated grid (instance 2) in terminal server env works in the same way as mentioned by you?

T.S.DUBEY @ 11/13/2013 10:57:36 AM
IT IS HELP FULL FOR ME.



Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: