Hi Trey,

I am only using option #1 - key fields only so by rights the generated code should only be searching on the Primary Key. In this app, these are files that are linked back to a parent record where the record is a Plaintiff (this is a legal type app) so the primary key is File_ID N(5) and the foreign key back to the parent is Plaintf_ID N(5). The generated SQL code should therefore be just:

WHERE File_ID = xxxx

The wierd behaviour is this: since opening a file is *so* important to this client, they also want to know when someone has opened and abandoned a file before saving. And so they want the abandoned records inserted into the table even though abandoned (they then run a printout the end of the month to see which of their staff are doing the abandons and they go over this with them). Regular records look like this:

File_ID Plaintf_ID DateOpened WhoOpened DateClosed Notes
2345 1789 12/12/1999 LMJ / /
2346 1845 12/15/1999 AGS 12/20/2000

For an abandoned file, what I have done is to drop the link to the Plaintiff
table so that these files are never related back when joining; so the record
looks something like this:

2347 0 12/16/1999 LMJ / / Abandoned by Laura M...

This was the way it ran under their FPW2.6 app and it worked okay. They continue to have their old app "hit" against these tables as we are starting to switch to their new VFP app. The new VFP app does not (yet) include code to handle an abandon. But when it does a regular INSERT of a new record, it finds all the abandoned records in the table and fills them in with the Plaintf_ID of the correctly inserted record. That is, if I inserted a record above where File_ID was 2348 and Plaintf_ID was 6677, the new record is inserted correctly but along with this, VFP does an update of all rows where Plaintf_ID is equal to zero. Funny thing though, it only updates the foreign key field and does not touch the file number or any other fields.

For now, the way around it was to change the old app so that it inserts a bogus Plaintf_ID into the record so that it is non-zero (since it is a numeric column, I just stick in a -9999 as there are no negative ID's to join to in the Plaintf_ID column. This will work (somewhat) but it would have been nice to understand what VFP was doing. Maybe somehow it's INSERT does something like APPEND a blank and then it does an UPDATE for all records where the foreign field equals zero (this doesn't really make sense though - I could see it doing this for the primary field [File_ID] but not for a foreign key field.

Any thoughts.

Albert



>>Hi Y'all,

>>I have a local view that when it is updated, it does the odd "wierd" thing to the table - much as if I had hand-written an "update" statement that had some logic error in it.
>>
>>Is there any way I can grab the actual SQL statement that VFP is using to do the table update? This is a local view using VFP as the data engine so it's not like I am using ODBC or SQL server where I can capture what is being sent to the back end. I looked around for a SYS() command that might give this to me but I could not find anything.
>>
>>Albert Gostick
>
>You don't say what "weird" stuff is happening, but here's a start.
>
>The WhereType tells you what would be added:
>
>1 - Key fields only: e.g., if keyfield = "iid" and current row iid = 3 then tags this to the update: "WHERE iid = 3"
>
>2 - Key and Updatable fields - in addition to the key field, adds to the where down the list of updatable fields, e.g.: "WHERE iid = 3 AND field1= old_field1_value AND field2 = ...."
>
>3 - Key and Modified fields - same as 2, but for modified fields instead of updatable, e.g. if only field2 was modified: "WHERE iid = 3 AND field2 = old_field2_value"
>
>4 - Key and Timestamp - more applicable to SQL Server remote views - same as 1, but compares timestamps as well, e.g., "WHERE iid = 3 AND timestamp=old_timestamp_value"
>
>My guess is you have a couple of different records that match the WHERE that is added.