>- (1) a general update_user_and_time record validation (which you may have to change if you recycle deleted records)
>- (2) a program that will update all the tables in the database
>
>You can have it up and running in a couple of minutes
>
>Make sure you test on a copy of the database, just in case, I only tested it briefly
>
>(1)
>I do not know whether the fields of the tables are prefixed. All mine are, eg emp_id, emp_name, emp_email, ...
>So you have to set PREFIXED to TRUE if the field names are prefixed, and set PREFIXED to FALSE if you want the same fieldname in all tables
>

>#ifndef TRUE
>	#define TRUE			.t.
>	#define FALSE			.f.
>#endif
>#define PREXIXED		TRUE	&& if fieldnames like emp_cLast_Updated_User
>#define	USERFIELDNAME	'cLast_Updated_User'
>#define DATETIMEFIELD	'tLast_Updated_Date'
>
>function RecordValidationRuleUpdateUserAndTime()
>
>	local UpdateFieldUser, UpdateFieldDateTime, fs, i
>	
>#if PREXIXED
>	UpdateFieldUser		= left(Field(1), at('_', Field(1))) + upper(USERFIELDNAME)
>	UpdateFieldDateTime	= left(Field(1), at('_', Field(1))) + upper(DATETIMEFIELD)
>#else
>	UpdateFieldUser		= upper(USERFIELDNAME)
>	UpdateFieldDateTime	= upper(DATETIMEFIELD)
>#endif
>
>	do case
>	case isnull(oldval(m.UpdateFieldUser))
>		&& insert
>		repl (m.UpdateFieldUser) with sys(0), (m.UpdateFieldDateTime) with datetime()
>	
>	otherwise
>		&& update
>		fs = getfldstate(-1)
>		
>		for i = 2 to len(m.fs)
>		
>			do case
>			case inlist(substr(m.fs, m.i, 1), '1', '3')
>				&& not changed
>			
>			case !FieldChanged(Field(m.i-1))
>				&& not changed
>			
>			case inlist(Field(m.i-1), m.UpdateFieldUser, m.UpdateFieldDateTime)
>				&& ignore
>			
>			otherwise
>				repl (m.UpdateFieldUser) with sys(0), (m.UpdateFieldDateTime) with datetime()
>				exit
>			endcase
>		endfor
>	endcase
>endfunc
>*--------------------------------------------------------------------------
>function FieldChanged(FieldName)
>
>	do case
>	case isnull(oldval(m.FieldName)) or Isnull(eval(m.FieldName))
>		return (isnull(oldval(m.FieldName)) <> Isnull(eval(m.FieldName)) )
>	otherwise
>		return (oldval(m.FieldName) <> eval(m.FieldName))
>	endcase
>endfunc
>*-------------------------------------------------------------------------
>

>
>(2) You can launch this program to (a) add the fields to all your existing tables and (b) to set the table validation rule.
>This program can be launched as many times as you like, it will only alter the tables if necessary
>
>

>function LastUserAlterTable()
>
>	local i, aa[1], TableName
>	
>	local UpdateFieldUser, UpdateFieldDateTime
>	
>	local TableValidationRule, TableValidationText
>	
>	local RuleName
>	Rulename = 'RecordValidationRuleUpdateUserAndTime()'
>	
>	for i = 1 to adbobjects(aa, 'Table')
>	
>		TableName = aa[m.i]
>		use (m.TableName) again in 0 excl
>		
>		select (m.TableName)
>#if PREXIXED
>		UpdateFieldUser		= left(Field(1), at('_', Field(1))) + upper(USERFIELDNAME)
>		UpdateFieldDateTime	= left(Field(1), at('_', Field(1))) + upper(DATETIMEFIELD)
>#else
>		UpdateFieldUser		= upper(USERFIELDNAME)
>		UpdateFieldDateTime	= upper(DATETIMEFIELD)
>#endif
>		do case
>		case type(m.UpdateFieldUser)<> T_UNDEFINED
>		
>		otherwise
>			alter table (m.TableName) ;
>				add	(m.UpdateFieldUser) 	c(20)	not null default '' ;
>				add (m.UpdateFieldDateTime) T		not null default { :} ;
>				NOVALIDATE
>			
>		endcase
>		
>		TableValidationRule = dbgetprop(m.TableName, 'Table', 'RuleExpression')
>		TableValidationText = dbgetprop(m.TableName, 'Table', 'RuleText')
>		
>		do case
>		case !empty(atc(m.RuleName, m.TableValidationText))
>			&& already in
>		
>		otherwise
>		
>			if( empty(m.TableValidationRule) )
>				TableValidationRule = m.RuleName
>			else
>				TableValidationRule = m.TableValidationRule + ' and ' + m.RuleName
>			
>			endif
>			
>			if( !empty(m.TableValidationText) )
>				TableValidationText = 'error ' + m.TableValidationText
>			endif
>			
>			alter table (m.TableName) ;
>				set check &TableValidationRule &TableValidationText ;
>				NOVALIDATE
>			
>		endcase
>		
>		use in (m.TableName)
>	
>	endfor
>
>endfunc
>*--------------------------------------------------------------------------
>

Hi Gregory,

Thanks a lot for the code. I'm going to change the first procedure a little bit. We don't need to loop through all the fields. We just need to find changed fields, right? I also want to give you a proper credit for the code.