>>- (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.


hi Nadya,

The first part does not loop through all the fields. It loops through getfldstate(-1). If a byte indicates a change, then it finds out whether the field really has changed, excluding the user and time fields (you may want to add the insert fields there).

It only loops until it finds a field that has changed.

A recap,

RecordValidation is only called when there is a change or it thinks so

We may get a 'false' change, ie repl a field with a value and repl it back with the oldval(). Hence the loop

We do not include the User and date fields of both the insert and update

If you a shorter way I have overlooked, plse let me know


cheers