Folks,

I'm having trouble making a correct updatable view using a LEFT JOIN. I keep getting update conflict errors if the outer join column contains a NULL. What can be done to correct this?

--

Here is the view:

CREATE SQL VIEW "LV_CLIENTSERVICE" AS ;
SELECT ClientService.iClientID, Service.cService,;
ClientService.lAlreadyReceiving, ClientService.lNeeded, ;
ClientService.lUnavailable, ClientService.lIneligible, ;
ClientService.lCALSafeFunded, ClientService.dReferral, ;
ClientService.dFollowUp, ClientService.tAddStamp, ;
ClientService.tModStamp, TTOD(ClientService.tModStamp) AS tModDateStamp, ;
ClientService.iUserID, ClientService.iID, FollowUpStatus.iID AS iFollowUpStatusID, ;
StudentRating.iID AS iStudentRatingID, ;
NVL(FollowUpStatus.cStatusNumber, [ ]) AS cStatusNumber, ;
NVL(StudentRating.cRatingNumber, [ ]) AS cRatingNumber ;
FROM ClientService ;
LEFT JOIN FollowUpStatus ;
ON ClientService.iFollowUpStatusID = FollowUpStatus.iID ;
LEFT JOIN StudentRating ;
ON ClientService.iStudentRatingID = StudentRating.iID ;
INNER JOIN Service ON ClientService.iServiceID = Service.iID ;
WHERE ClientService.iClientID = ?vp_iClientID

--

I've been working with eView for hours and I'll spare you the reams of properties it generates. Here's some highlights just to show I haven't missed the obvious:

DBSetProp('LV_CLIENTSERVICE', 'View', 'UpdateType', 1)
DBSetProp('LV_CLIENTSERVICE', 'View', 'WhereType', 2) && for debugging
DBSetProp('LV_CLIENTSERVICE', 'View', 'Tables', 'ClientService')
DBSetProp('LV_CLIENTSERVICE', 'View', 'ParameterList', "vp_iClientID,'I'")
DBSetProp('LV_CLIENTSERVICE.iid', 'Field', 'KeyField', .T.)
DBSetProp('LV_CLIENTSERVICE.iid', 'Field', 'UpdateName', 'ClientService.iid')
DBSetProp('LV_CLIENTSERVICE.istudentratingid', 'Field', 'Updatable', .T.)
DBSetProp('LV_CLIENTSERVICE.istudentratingid', 'Field', 'UpdateName', 'ClientService.istudentratingid')

--

I'll use StudentRating as an example, but the following also applies to the FollowUpStatus table.

Assuming I have the view running with several records in record buffering mode, here's what I've been able to figure out:

If the iStudentRatingID contains a valid pointer to the StudentRating table and I change a field, the record saves. I can verify this by looking at the base table.
If the iStudentRatingID originally does not contain a valid pointer (i.e., it's NULL), I get an update conflict error when I attempt to save the record. This is true even if a valid pointer value is placed in iStudentRatingID.
If this is corrected with a TableRevert(), the original record that saved correctly a moment ago now generates an Update Conflict error!

I'm baffled. What am I missing?