Multi-table form needs to append to only one table

[opticalman]

I am using VFP 8.0 as a front end
I am using SQL 2005 Express as a backend
I am using remote views.

My question is : What is the best way to display info from multiple tables, but only add info to one table?

I would like to display information from multiple tables on my form. An example is insurance claim info on service, but not every service has an insurance claim. I can easily make a remote view and form to display the information, but my problem comes when it is time to add a new record. Appending a record to the multi-table remote view and issuing a TABLEUDATE() also tries to add a record to the second table, even if SEND UPDATES is unchecked for the fields of the second table in the view designer.

Do I need to open another remote view with only one table for appending records? Do I need to use multiple remote views to gather info for my forms? I would appreciate some suggestions and guidance.

Jim Rumbaugh

 

[alvechurchdata]

I find this difficult too. The only reliable way I've found is to have a specific SaveRecord method which accepts the primary key of the the table being changed, opens that table again in a new work area and then operates on that one record of that table. I then have to refresh the main multi-table view so I know it's not the most efficient way of working.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[opticalman]

Thanks Geoff

I may try your method. I feel better knowing I have not overlooked something obvious.

Jim Rumbaugh

 

[opticalman]

Mike

Thanks for the education. Your right, I’m not going to go out and buy a framework at this time. I’m still getting down how to write classes. I’m stuck in the past, writing user defined functions for everything, but I’m changing my ways slowly I think I can work around using multiple views, such as one for displaying and another for new entries, but I guess what is holding me back is how to link the form’s text boxes from one view for display to a second view for new entries. These forms have over 40 to 60 fields. I’m reluctant to create a separate form for new entries and displaying info, or is that what I should do??.

Jim

 

[bborissov]

Jim,
another opinion. DON'T use Remote Views, start learning CursorAdapters. They are more powerfull and you will have less headache to maintain. I use them all the time and have no problems with multitable queries and one table updates

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[opticalman]

Thank you all for your suggestions.

I now have an answer that works for my application.

1) Use 2 views for the fields in the form. One view for updateable data and the other for read only.
2) Index the read only data on a common key field.
3) Set a relation between the two views.

I can now scroll through the main view while retrieving info from the second ‘read only’ view. When it is time to edit and save the main view, the ‘display only’ view is not affected. Though this method will work with large data sets , the time to build an index may slow performance. In my application, there are usually less than 20 records per view and the speed is excellent. I consider this problem solved.

Jim Rumbaugh