Wiki Home

Vfe Mak Lups


Namespace: SQL
This program will use the relations in the dbc to set up lookups in the dbcx.



* program MakLups.prg

lMakLups( ;
"test", ;
"C:\vfe6\vfeframe\libs\", ;
"C:\vfe6\dev\test\metadata\" )

return

function lMakLups( tcDbc, tcPthDbcx, tcPthMta )

* For each table,
*  for each relation,
*   look for forgin keys that match field names
*   set the lookup properties of the foriegn key field based on the relation
*   if they have not been set
* Parameters:
* tcPnmDbc - Path and Name of dbc
* tcPthDbcx - Path to DbcxMgr.vcx
* tcPthMta - Path to Metadata

local ;
	lcPnmDbcx, ;
	loMeta, ;
	lnTbls, ;
	laTbls(1), ;
	lcTbl, ;
	lnRels, ;
	laRels(1), ;
	lcRel, ;
	lcRelPntTbl, ;
	lcRelPntFld, ;
	lcRelCldTbl, ;
	lcRelCldFld, ;
	lnTags, ;
	laTags, ;
	lcTags

lcPnmDbcx = addbs( tcPthDbcx ) + "DbcxMgr.vcx"
loMeta = NewObject( "DbcxMgr", lcPnmDbcx, "", .t., tcPthMta, .f. )

* Validate the dbc. This will add in any new tables.
loMeta.Validate( tcDbc, "database" )

* Get a list of tables in the database
lnTbls = loMeta.dbcxGetAllObjects( tcDbc + "!tables", @laTbls )

for each lcTbl in laTbls

	* Get a list of relations for the current table
	lnRels = loMeta.dbcxGetAllObjects( tcDbc + "!relations " + lcTbl , @laRels )

	for each lcRel in laRels

		* Break the relation into (parent and child) (table and field).
		* Example of a relation: "client.cclkey,joborder.cjoclkey"
		* If we had a string function sf( string, start, end ) things would be nicer...
		lcRelPntTbl = substr( lcRel, 1, at( ".", lcRel )-1 )
		lcRelPntTag = substr( lcRel, at( ".", lcRel )+1, at( ",", lcRel )-at( ".", lcRel ) - 1 )
		lcRelCldTbl = substr( lcRel, at( ",", lcRel )+1, at( ".", lcRel, 2 ) - at( ",", lcRel )-1 )
		lcRelCldTag = substr( lcRel, at( ".", lcRel, 2 )+1 ) 

		* If the Child Table of the Relation is the current Table 
		if upper( lcRelCldTbl ) == upper( lcTbl )

			* If the current Look Up Type is 0 (none)
			lnCurLut = loMeta.dbcxGetProp( ;
				tcDbc + "!" + lcTbl + "." + lcRelCldTag, ;
				"field", "vfenLuType" )
				
			if lnCurLut = 0

				* Get the Id and Name fields from the parent table
				* This is where my nameing convention kicks in:
				* Keys, Ids, and Descriptions all have the same prefix				
				lcPfx = strtran( lcRelPntTag, "key" )
				lnFldId = lcPfx + "Id"
				lnFldDsc = lcPfx + "Dsc"

				loMeta.dbcxSetProp( ;
					tcDbc + "!" + lcTbl + "." + lcRelCldTag, ;
					"field", "vfenLuType", 2 )

				loMeta.dbcxSetProp( ;
					tcDbc + "!" + lcTbl + "." + lcRelCldTag, ;
					"field", "vfecLuTable", tcDbc + "!" + lcRelPntTbl )
			
				loMeta.dbcxSetProp( ;
					tcDbc + "!" + lcTbl + "." + lcRelCldTag, ;
					"field", "vfecLuTag", lcRelPntTag )
			
				loMeta.dbcxSetProp( ;
					tcDbc + "!" + lcTbl + "." + lcRelCldTag, ;
					"field", "vfemLuFields", lnFldId )
			
				loMeta.dbcxSetProp( ;
					tcDbc + "!" + lcTbl + "." + lcRelCldTag, ;
					"field", "vfemLuUpdtExp", lcRelPntTag )
			
				loMeta.dbcxSetProp( ;
					tcDbc + "!" + lcTbl + "." + lcRelCldTag, ;
					"field", 'vfecObjType', "iComboBox" )

			endif
			
		endif
		
	endfor
	
endfor




Contributors Carl Karsten
Category Visual FoxExpress Category Visual FoxExpress Tips And Tricks
( Topic last updated: 1999.08.03 01:37:21 AM )