Adding new record: Base table vs. View?

[foxrainer]

I have three tables:
Patients - KeyIndex 'Code' - no buffer
Visits - KeyIndex 'Auto_ID' - no buffer
Codes - KeyIndex 'c_Code'

The three tables are combined via 2 views:
1) 'View_Patient_Visits' (Left INNER join)tables 'Patients' & 'Visits' (Buffer = 1)
2) 'View_Patient_Visists_Codes' (Left Inner Join) combining:
'view_Patient_Visits' and Table 'Codes'

My current routine adds a new record via: INSERT SQL, giving a new (higher) Key Value to each the 'Patients' Key fields 'Code'.
I then issue a REQUERY("view_patient_visits_code).

Here is the problem: The view is not updated, unless I leave Foxpro and re-enter.

Any ideas where the problem lies?

Thank you in advance

Rainer

 

[fluteplr]

look up tableupdate in the help file.

 

[BPeisch]

Are you adding the new record to the source table or to the view? I'm guessing you're adding to the table because otherwise trying to REQUERY the view withing doing a Tableupdate first would give you an error that the buffer is dirty.

There's no point in using views if you're going to be making changes directly to the source tables. And if you are going to use views for your changes, you don't want any buffering on the source tables. So, the bigger question here is, what are you using the views for?


-BP (Barbara Peisch)

http://www.peisch.com

 

[foxrainer]

BP,thank you for the questions:
In general, all data changes are made to the view. My question above was to determine if 'Adding' a record should be done to the view also, or directly to the table.

I resulted to tables ONLY because adding a record to the view and then trying to update it gave the error: 'Can not update base table with an empty record'.
I did, of course, add key field data to the newly added view record prior to giving a 'tableupdate() command.

Does that help at all?

Thanks again for trying,

Rainer

 

[BPeisch]

You should be able to add records via the view. The tricky part is getting the primary key. If you're used to letting a DBC trigger assign your PK, you won't have that with a view, although you can manually call the same method. If you do that, you have to make sure you have the view update the PK as well as the other fields, which is not the default.

As far as the error you're getting, it may be because you haven't changed the view's Buffering to 5 (table buffering) and you're moving the record pointer before you fill the record. With row buffering, any change to the record pointer does an implicit tableupdate.


-BP (Barbara Peisch)

http://www.peisch.com

 

[foxrainer]

Barabara,

you got the answer! I buffered '3', not '5'!

Thanks tons,

Rainer