Wiki Home


Namespace: VB
Here is an example of how you can set up views to manage keys in a Parent Child relation. It relieves the app from having to move the keys around.

This setup is meant for VFE, but works well in any framework.
I did not link in a primary key generator routine, that would just distract from this example.

create database pc
create table pnt ( ;
        pkPnt i primary key, ;
        cPntID c(10) unique, ;
        cPntName c(40) )

create table cld ( ;
        pkCld i primary key, ;
        fkPnt i references pnt, ;
        cCldId c(10) unique, ;
        cCldName c(40) )
create sql view v_Pnt as ;
	select pnt.* ;
		from pnt ;
		where cPntId = ?vp_cPntId ;
			and cPntName = ?vp_cPntName 

* The above parameters are for searching.  
* only one will be used, the other will be set to an empty string, 
* so it will "drop out" of the experssion.
* Note: this only works this way for dbf tables.  To get it to work for
* most back end servers (like MsSql) is a bit of a problem, but it can be done.

* Make the whole view updateable
dbsetprop( 'v_Pnt', 'view', 'SendUpdates', .t. )
* Define which table gets updated
dbsetprop( 'v_Pnt', 'view', 'Tables', 'Pnt' )
* Define the view parameters so that VFE knows what to put on the "Selection Criteria" tab
dbsetprop( 'v_Pnt', 'view', 'ParameterList', "vp_cPntId, 'C'; vp_cPntNam, 'C' " )
* Set the key field to Updatable too - if you are using VFE)
dbsetprop( 'v_Pnt.pkPnt', 'Field', 'KeyField', .t. )
dbsetprop( 'v_Pnt.pkPnt', 'Field', 'Updatable', .t. )

create sql view lv_Cld as ;
	select cld.* ;
		from cld ;
			where cld.fkPnt = ?v_Pnt.pkPnt

* The above parameter is what 'relates' v_Cld to v_Pnt
* when v_Cld is requeried, it will use the current pkPnt
* and only return child records of the current parent

* Make the view update the Cld table (same as for Pnt)
dbsetprop( 'v_Cld', 'view', 'SendUpdates', .t. )
dbsetprop( 'v_Cld', 'view', 'Tables', 'Cld' )

* No need to define the parameters, 
* VFE dosn't need to know about the link to the parent

* Set the key field to Updatable too  (same as for Pnt)
dbsetprop( 'v_Cld.pkCld', 'Field', 'KeyField', .t. )
dbsetprop( 'v_Cld.pkCld', 'Field', 'Updatable', .t. )

* Set the foriegn key to use the Parents primary key for new records
* When a new child is added, it will be a child of the current parent.
dbsetprop( 'v_Cld.fkPnt', 'field', 'Default', 'v_Pnt.pkPnt' )

See also View Parameters
Contributors Carl Karsten
Category Code Samples Category Data
( Topic last updated: 2001.04.12 11:18:51 AM )