compare two fox tables

Dear All,

I wanted to findout whether user made any changes in a fox
table.
Is there any function to do this ?

Thanks in advance

Jijo David

Wolfgang
Tue Dec 30 05:17:09 CST 2003

Hi Jijo!

Take a look at:
GETFLDSTATE( ) Function
See Also
CURVAL( ) | DELETED( ) | FIELD( ) | OLDVAL( ) | CURSORSETPROP( ) |
SETFLDSTATE( )
Returns a numeric value indicating if a field in a table or cursor has been
modified or had a record appended, or if the deleted status of the current
record has been changed.
GETFLDSTATE(cFieldName | nFieldNumber [, cTableAlias | nWorkArea])


--
_________________

MFG
Wolfgang Schmale

MS Visual FoxPro MVP

John
Tue Dec 30 07:51:13 CST 2003

Hi,

If you're looking at comparing a table with another old version of a table,
I don't know of a function but here's an idea that might give you more...

SELECT * FROM MyTable1 ;
INTO CURSOR MyCursor ;
UNION ;
SELECT * FROM MyTable2

SELECT IDField FROM MyCursor GROUP BY 1 HAVING COUNT(*) > 1

This assumes that you have a primary/candidate key field that would not have
been available to the user for changing. The first query would implicitly
remove all duplicated rows. The second would identify any keys that are
duplicated, which would indicate one table's version was different from the
other's.

For newly added or deleted records you'd want to check for non-existence in
other table, such as:

SELECT * FROM ThisTable WHERE IDField NOT IN (SELECT IDField FROM ThatTable)

HTH,

John

"reachme_fast@yahoo.com" <anonymous@discussions.microsoft.com> wrote in
message news:074001c3cea7$a3dcc240$a301280a@phx.gbl...
> Dear All,
>
> I wanted to findout whether user made any changes in a fox
> table.
> Is there any function to do this ?
>
> Thanks in advance
>
> Jijo David
>

Chaim
Tue Dec 30 08:02:58 CST 2003

There is no function to detect this. You could write a program to do a
comparison; this approach requires a "before" image to compare to the
current data.

Igor
Tue Dec 30 11:17:04 CST 2003

Hi, reachme_fast@yahoo.com!
You wrote on Mon, 29 Dec 2003 23:36:30 -0800:

rf> I wanted to findout whether user made any changes in a fox
rf> table.
rf> Is there any function to do this ?

No, there can't be such function.
BUT as you know the structures of those tables, you can easily write a
little program that will process both tables and find out the differences.
Just some pieces of code:

SELECT nPK ;
FROM table1, table2 ;
WHERE Table1.nPK = Table2.nPK AND ;
(Table1.nOtherField # Table2.nOtherField OR ;
Table1.cOneMore # Table2.cOneMore OR ...) ;
INTO CURSOR DifferentRecords
SELECT nPK ;
FROM table1 ;
WHERE Table1.nPK NOT IN ;
(SELECT Table2.nPK FROM Table2) ;
INTO CURSOR MissedIn2Records
SELECT nPK ;
FROM table2 ;
WHERE Table2.nPK NOT IN ;
(SELECT Table1.nPK FROM Table1) ;
INTO CURSOR MissedIn1Records

Then you may do all you want with those cursors (they will contain primary
keys of records that are different in both tables, or missed from one of
them) it will also work if you have complex (non single-field Primary Key).
If you don't have PK - then you HAVE to have one - otherwise what are you
doing with relational database :)

P.S. Happy New Year

--
WBR, Igor