Should I tableupdate() the view or the base table????

Regards
Geoff Scott



>Make sure that IIF returns a string of the same lenght regardless of condition.

CREATE SQL VIEW vWoItems AS ;
>	SELECT Woitems.*, ;
>		Contsub.prsched, Contsub.name, ;
>		Prodserv.desc, Prodserv.unit, Prodserv.type, ;
>		IIF(ISNULL(vlocations.locdesc), ;
>                  PADR('**Unknown**', LEN(vlocations.locdesc)), ;
>                      vlocations.locdesc) as locdesc, ;
>		IIF(ISNULL(vwo_istatus.statusdesc), ;
>                  PADR('**Unknown**', LEN(vwo_istatus.statusdesc));
>                    vwo_istatus.statusdesc)  as statusdesc ;
>	FROM wots!woitems ;
>...
>

BTW, ISNULL() alreday returns logical value. There's no need to compare it to .T.
>
>>Hi Sergey
>>
>>here it is
>>
>>

>>OPEN DATABASE wots
>>SELECT 0
>>USE wots!wohead
>>
>>CREATE SQL VIEW vWoItems AS ;
>>	SELECT Woitems.*, ;
>>		Contsub.prsched, Contsub.name, ;
>>		Prodserv.desc, Prodserv.unit, Prodserv.type, ;
>>		IIF(ISNULL(vlocations.locdesc)=.T.,'**Unknown**',vlocations.locdesc) as locdesc, ;
>>		IIF(ISNULL(vwo_istatus.statusdesc)=.T.,'**Unknown**',vwo_istatus.statusdesc) as statusdesc ;
>>	FROM wots!woitems ;
>>			LEFT JOIN wots!prodserv;
>>				ON  Woitems.prodserv = Prodserv.code ;
>>			LEFT JOIN wots!contsub;
>>				ON  Woitems.contractor+ Woitems.contsub = Contsub.contractor+Contsub.subcode ;
>>			LEFT JOIN wots!vlocations;
>>				ON  Woitems.location = vlocations.loccode ;
>>			LEFT JOIN wots!vwo_istatus ;
>>				ON  Woitems.status = vwo_istatus.code ;
>>		ORDER BY woitems.wo_id, woitems.wo_itemno ;
>>		WHERE woitems.wo_id=wohead.wo_id
>>
>>SELECT 0
>>USE wots!vWoItems
>>
>>DBSETPROP('vWoItems','VIEW','SENDUPDATES',.T.)
>>DBSETPROP('vWoItems','VIEW','TABLES','WOTS!WOITEMS')
>>DBSETPROP('vWoItems','VIEW','WHERETYPE',1)
>>
>>DBSETPROP('vWoItems.wo_id','FIELD','UPDATABLE',.T.)
>>DBSETPROP('vWoItems.wo_id','FIELD','KEYFIELD',.T.)
>>DBSETPROP('vWoItems.wo_id','FIELD','DEFAULTVALUE','wohead.wo_id')
>>
>>DBSETPROP('vWoItems.wo_itemno','FIELD','UPDATABLE',.T.)
>>DBSETPROP('vWoItems.wo_itemno','FIELD','KEYFIELD',.T.)
>>DBSETPROP('vWoItems.wo_itemno','FIELD','DEFAULTVALUE','Next_Wo_ItemNo()')
>>
>>DBSETPROP('vWoItems.ex_ref','FIELD','UPDATABLE',.T.)
>>DBSETPROP('vWoItems.prodserv','FIELD','UPDATABLE',.T.)
>>DBSETPROP('vWoItems.location','FIELD','UPDATABLE',.T.)
>>DBSETPROP('vWoItems.contractor','FIELD','UPDATABLE',.T.)
>>DBSETPROP('vWoItems.contsub','FIELD','UPDATABLE',.T.)
>>DBSETPROP('vWoItems.baserate','FIELD','UPDATABLE',.T.)
>>DBSETPROP('vWoItems.contmargin','FIELD','UPDATABLE',.T.)
>>DBSETPROP('vWoItems.contmtype','FIELD','UPDATABLE',.T.)
>>DBSETPROP('vWoItems.custmargin','FIELD','UPDATABLE',.T.)
>>DBSETPROP('vWoItems.custmtype','FIELD','UPDATABLE',.T.)
>>DBSETPROP('vWoItems.status','FIELD','UPDATABLE',.T.)
>>DBSETPROP('vWoItems.qty','FIELD','UPDATABLE',.T.)
>>DBSETPROP('vWoItems.notes','FIELD','UPDATABLE',.T.)
>>DBSETPROP('vWoItems.price','FIELD','UPDATABLE',.T.)
>>DBSETPROP('vWoItems.origin','FIELD','UPDATABLE',.T.)
>>
>>DBSETPROP('vWoItems.wo_id','FIELD','UPDATENAME','Wots!Woitems.wo_id')
>>DBSETPROP('vWoItems.wo_itemno','FIELD','UPDATENAME','Wots!Woitems.wo_itemno')
>>DBSETPROP('vWoItems.ex_ref','FIELD','UPDATENAME','Wots!Woitems.ex_ref')
>>DBSETPROP('vWoItems.prodserv','FIELD','UPDATENAME','Wots!Woitems.prodserv')
>>DBSETPROP('vWoItems.location','FIELD','UPDATENAME','Wots!Woitems.location')
>>DBSETPROP('vWoItems.contractor','FIELD','UPDATENAME','Wots!Woitems.contractor')
>>DBSETPROP('vWoItems.contsub','FIELD','UPDATENAME','Wots!Woitems.contsub')
>>DBSETPROP('vWoItems.baserate','FIELD','UPDATENAME','Wots!Woitems.baserate')
>>DBSETPROP('vWoItems.contmargin','FIELD','UPDATENAME','Wots!Woitems.contmargin')
>>DBSETPROP('vWoItems.contmtype','FIELD','UPDATENAME','Wots!Woitems.contmtype')
>>DBSETPROP('vWoItems.custmargin','FIELD','UPDATENAME','Wots!Woitems.custmargin')
>>DBSETPROP('vWoItems.custmtype','FIELD','UPDATENAME','Wots!Woitems.custmtype')
>>DBSETPROP('vWoItems.status','FIELD','UPDATENAME','Wots!Woitems.status')
>>DBSETPROP('vWoItems.qty','FIELD','UPDATENAME','Wots!Woitems.qty')
>>DBSETPROP('vWoItems.notes','FIELD','UPDATENAME','Wots!Woitems.notes')
>>DBSETPROP('vWoItems.price','FIELD','UPDATENAME','Wots!Woitems.price')
>>DBSETPROP('vWoItems.origin','FIELD','UPDATENAME','Wots!Woitems.origin')
>>
>>USE vWoItems
>>BROWSE
>>CLOSE TABLES
>>

>>
>>thanks in advance
>>Geoff Scott
>>
>>>Can you post the view definition (CREATE VIEW ... ).
>>>
>>>>Hi
>>>>
>>>>I have created my view and it updates the underlying table (for that datasession). I
>>>>
>>>>

TABLEUPDATE(1,.T.,'myview') or
>>>>TABLEUPDATE(2,.T.,'myview')

>>>>
>>>>I skip to the next record and the data in the underlying table (for that datasession) remains changed I
>>>>
>>>>

Requery().

>>>>
>>>>I skip back to my record and Requery() again.
>>>>
>>>>My data is now the same as it was before I modified it even though the table (in that datasession) still has the modified values and keeps them until the table is closed. I reopen the base table and it is still in the original state
>>>>
>>>>What gives? How do I pick up the modified?
>>>>
>>>>If I
>>>>
>>>>

TABLEUPDATE(1,.T.,'basetable')

>>>>
>>>>I run into a
>>>>
>>>>

View definition has been changed (Error 1494)

>>>>
>>>>everytime I try to acces that record and the only way I can get rid of it is to delete the record from the base table(s).
>>>>
>>>>Please help
>>>>
>>>>regards
>>>>Geoff Scott