>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?


NOt sure if why this might manifest itself as an update conflict, but I think I see at least one problem with your view definition- the field iStudentRatingID is pulled from the base table field StudentRating.iID, but it's UpdateName is set as StudentRating.iStudentRatingID. Does correcting this make the problem go away?