My vfp6 Table1 is related to table2 one-to-one, that is, table2.foreignkey has a candidate index. They are often LEFT JOINed because many records in table1 have no corresponding record in table2, but NOT vice versa. I have a table3 related many-to-many with table1 using a join table table4. A form for maintaining table3 has a grid whose .RecordSource is a view joining table4 and table1. These relationships used to be simpler, but didn't accurately reflect the real data, I have now learned.

The problem is that when a certain change is made to table3, it generates a new record in table2 for each related record in table4. If the record in table2 already exists, it is not generated.

I thought I would add table2 to the view that joins tables 4 and 1 using the LEFT JOIN. If the table2 record doesn't exist, its fields would be .NULL. in that view. Without knowing any better, I might try to add the record to table2 by REPLACEing those nulls with values and trying to update. I have no idea if you can actually add a record that way. There is a fair amount of code to write in order to carry out all of this. I thought I should ask before trying something screwy.

I have other reasons to want table2 in this form's data environment, but my question is complicated enough already. I suppose that if I use table2 in more than one way in the form, it could be part of more than one view in the data environment.

The VFP6 help says "You can update multiple base tables from a view. When your view combines two or more tables, you set properties to ensure that only the many side of the view query is updatable." I don't know how that applies to my one-to-one-related table2.