Reviewing changes to data

[johnhodgson]

I need to find a way to review and authorise changes to data. New data is submitted by a large group of users, and the database controller needs to see what changes have been made and authorise them. The only, somewhat inelegant, solution I can think of is to have two tables 'original' and 'newupdates', users have access to 'newupdates' and make changes there. When the changes are submitted I take each field in turn, write each 'original' and 'newupdate' value to an 'original' text file and a 'newupdate' text file, create a Word object, open the 'original' text file in Word, do a document compare with the 'newupdate' text file in the Word object, save the 'compared' document as an RTF file, load the RTF file into a Rich Text box, where the database controller can see and edit the changes and then save the authorised text back to the both databases. This seems a very cumbersome way of acheiving the result. Can anyone suggest a more straightforward method of doing this?

 

[Mike Gagnon]

johnhodgson

Without going into too many details, does it have do be done visually? Can you not just loop through the two tables and use a SCATTER MEMVAR and compare the diferences between the two records and skip to the next record if the record are the same. And if they a not the same create a third table with the differences, at least it would avoid look at data that is unchanged.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Mike Lewis]

johnhodgson,

Couldn't you just create a new form for use by the controller? Copy the updated fields there, and, once they have been authorised, copy them to final table? Or am I missing something?

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[johnhodgson]

Mike and Mike

Thanks. The point I omitted from the original explanation is that the data includes memo fields which can be quite lengthy, with perhaps only one or two words changed. The supervisor needs to be able to spot the changes quickly without have to do a comparative read between the two versions.

John

 

[Mike Lewis]

John,

The supervisor needs to be able to spot the changes quickly without have to do a comparative read between the two versions.

Maybe you should write some code that programmatically compares the two memos, and displays the differences in a form. Off-hand, I've no idea how to do that, but it shouldn't be that difficult to figure out.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[johnhodgson]

Mike

but it shouldn't be that difficult to figure out.

Tried doing that, but couldn't get a good result. I can spot the first change by working forward through the field, and the end of the last change by working backwards, but figuring out anything in the middle is mindboggling eg

abc def ghi jkl mno pqr stu vwxyz

is edited to

abc edf ghi abc def pqr mno abc stu vwxyz

should (where italic is new text and bold is deleted text) be

abc edfdef ghi abc def jkl mno pqr mno abc stu vwxyz

(I think!!) It's the looking ahead to see where the original sequence resumes that is puzzling me. It seems easy(ish) where it's restricted to three letter 'words', but real English text, especially if it's lengthy with complicated rewrites that's the real problem.

I was hoping someone knows of a good tool which would do the trick without resorting to the clunky Word solution or having to write some really complex code (esp as the end result would have to be RTF encoded to go into a Rich Text box - if it were displayed in a plain edit box then the only solution would be coding like TGML to show the changes, and that's even less supervisor-friendly than having to do a comparative read)

John

 

[Mike Gagnon]

johnhodgson

Wow!! This is really a difficult one (although feasible most likely, maybe not within the confines of this forum. But if you look at a portion of faq184-3184 (code listed below), which automates the Word Spell check, there is a way to determine if what is seen in a RTF control, does not match the "corrected" version, you could use something like that to at least determine if there was an actual change in the first place, thus eliminating some of the tedious work.

With oWord
            .Documents.Add()
            .Documents(1).Content = This.Value
            .Documents(1).CheckSpelling()
            .Selection.WholeStory()
            If .Selection.Text # this.value
                This.Value = .Selection.text
                Thisform.Refresh()
            Else
                Messagebox("No errors ...Done.")
            Endif
            .Documents(1).Close(.F.)
            .Quit()
        Endwith

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[johnhodgson]

Mike

I've done some experimenting, and

oword=CREATEOBJECT('word.application')
oword.Documents.Open('c:\temp\text1.txt')
**The original text field copied to a text file
oword.Documents(1).Compare('c:\temp\text2.txt')
** the changed text field copied to a text file
oword.documents(2).Close
oword.Documents(1).SaveAs('c:\temp\text3.rtf',6)
oword.Documents(1).Close(0)

gives a file that can be loaded into the RTF box. But it seems a roundabout method.

John

 

[Mike Gagnon]

johnhodgson

Honestly, since VFP is good with Data and Word is good with text, I cannot see anyway around it. It might have been different if is with only character fields, but memo fields are more treated as a whole in VFP. I was thinking maybe with ASCAN(), but maybe the process with word is less complicated.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[johnhodgson]

Mike

I think I'd come to the same conclusion. An ancillary problem is that not really understanding how to handle Word. When trying to shutdown (the computer) after doing this I got loads of 'Template open do you want to save' messages (though Task Manager didn't show any running Word tasks). Time to climb that learning curve I think.

Thanks for the comments

John

 

[Mike Gagnon]

johnhodgson


oword.documents(2).Close
oword.Documents(1).SaveAs('c:\temp\text3.rtf',6)
oword.Documents(1).Close(0)

You might consider adding a few lines to this, to prevent alerts and close word.
oword.displayalerts = .f.
oword.documents(2).Close
oword.Documents(1).SaveAs('c:\temp\text3.rtf',6)
oword.Documents(1).Close(0)
oword.quit()
owrod=.null.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[johnhodgson]

Mike

Thanks that's really useful - and I see you learned your keyboard skills at the same place as me - I'm sick of typing owrod for oword, perhaps I ought just to use owrod as the object name

John