Basic Append Question

[martinwcole]

Re: Basic Append Question

since the record is not deleted yet, you would need to do it after it was deleted

[MoGrace]

Re: Basic Append Question

This is what I have working so far:

FUNCTION Auto_Lup AS L (fname as C,tname = "" as C)
'DESCRIPTION: Used in FRUL CanWrite field event to get next field value for primary key
'and in CanDeleteRecord record event to test if deletion is allowed.
'
'Use this function instead of FRUL calc Auto Increment so if needed in the future
'the series for a primary key can be changed using the Counter table, eg. for Invoices
'that use a new prefix to reflect the year - allows user to reset the series.
'
'Ex1: CanWrite event in DIR_ID field - Auto_Lup("DIRCODE")
'-----User enters a new Directory->DIR_ID value and function gets the next DIRCODE value
'
'Ex2: CanDeleteRecord 
'flag = Auto_Lup("DIRCODE")
'if .not. flag then
'	cancel()
'end if
'function allows deletion of last record entered only in case of user error and restores
'the next number to its previous value so series will not be interrupted.

	dim lv as p = local_variables()
	dim commit_flag as L = .f.
	dim fld as p
	dim tc as p
	dim tp as p
	dim tpname as c = "counter"
	dim msg as c
	dim tries as n = 0
	dim vmax as c
	
	tc = table.current()
	if tname = "" then
		tname = tc.name_get()		
	end if
	fld = tc.field_get(fname)	
	vmax = alltrim(tablemax(tname,".t.",fname))
				
	If tc.mode_get() = 2 Then	'current table is in ENTER mode	
		TRYAGAIN:
		If tries < 4 Then			
			Dim newval as C	
			commit_flag = .t.					
			On Error goto ERRLOOP 
			tp = table.open(tpname,FILE_RW_EXCLUSIVE)
			On Error goto 0
			tp.index_primary_put("tblname")									
			rec = tp.fetch_find(tname)
			if rec > 0 then
				newval = ut(tp.nextval)
			else
				msg="COUNTER record not found for table: "+ut(tname)
				Tln("Auto_Lup",msg)
				'trace.WriteLn("Auto_Lup: "+msg)
				msg=crlf(2)+msg+"Please report error to Admin and cancel new record"+crlf()				
				msgbox("Auto_Lup() Script Error",msg,16)
				msg = ""
			end if
			
			If fld.type_get() = "N" Then	'current field type should match tp.type, can use either here
				fld.value_put(val(newval))
			Else
				fld.value_put(newval)
			End If		
										
			on error goto CLOSETP
			tp.change_begin()
				If tp.type = "N"
					tp.lastval = val(newval)
					tp.preval = val(vmax)
				Else
					tp.lastval = newval
					tp.preval = vmax
				End If
			CLOSETP:
			tp.change_end(.t.)				
			tp.close()			
		End If
	Else If A_DELETING_RECORD = .T. Then
		'Allow or cancel deletion of the current record in CanDeleteRecord FRUL event
		Auto_Lup = Del_Rec(tname,lv)				
		EXIT FUNCTION	
	End If
	
	Auto_Lup=commit_flag
	Tln(ut(tname)+"-New Record Added",ut(fname)+": "+newval)
	'trace.WriteLn(ut(tname)+"-New Record Added -"fname+": "+newval)
	end 
	
	ERRLOOP: 
	tries = tries + 1
	if tries < 4 then		
		RESUME 0
	else
		msg = "You do not have exclusive access to the "+ut(tname)+" table."
		Tln("Auto_Lup",msg)
		'trace.WriteLn("Auto_Lup: "+msg)
		msg = crlf()+msg+"Please CANCEL the new record else check that no other users"+crlf()
		msg = msg+"are using the table and try again."+crlf(2)
		msg = msg+"If this error continues, then try one or all of the following:"+crlf()
		msg = msg+"1. Close all open forms"+crlf()
		msg = msg+"2. Exit then reopen the application"+crlf()
		msg = msg+"3. Contact Admin and report this error for additional options to try."+crlf(2)
		msg = msg+"Select OK to retry or CANCEL to cancel your new record"
		
		response = msgbox("New Record Locked: Table in Use",msg,49)
		msg = ""
		if response = 2 then
			'user cancelled
			cancel()
		else
			tries = 1			
			goto TRYAGAIN		
		end if
	end if	
END FUNCTION

function Del_rec AS L (tname as c,LocVars as p)		
	WITH LocVars	
	dim curval as c
	dim oldval as c
	commit_flag = .f.
	curval = fld.value_get()	
	on error goto ERRMSG	
	tp = table.open(tpname,FILE_RW_EXCLUSIVE)
	On Error goto 0	
	tp.index_primary_put("tblname")	
	tp.fetch_find(tname)
	oldval = tp.preval
	if tp.lastval = curval then				
		commit_flag = .t.
		on error goto CLOSETP2			
		tp.change_begin()
			tp.lastval = oldval
			tp.preval = Get_Prev(oldval)
		CLOSETP2:
		tp.change_end(commit_flag)				
		tp.close()
	end if		
	
	if commit_flag = .f. then					
		msg = "Auto Increment rule is in force"+crlf()
		msg = msg+"You may only delete the last record entered"+crlf(2)
		msg = msg+"If this is in error, please see Admin to correct" 
		msgbox("Field Rule Violation",msg,16)
		msg = ""			
	end if	
	
	Tln(ut(tname)+"-Record Deleted-"+ut(fname)+": "+curval,commit_flag)
	'Trace.WriteLn(ut(tname)+"-Record Deleted-"+ut(fname)+": "+commit_flag)
	
	'sets the result of the calling script to test in CanDeleteRecord FRUL event	
	Del_rec = commit_flag
	goto ENDIT
		
	end
	ERRMSG: 
	commit_flag = .f.
	script_play("ERRMSG")
	on error goto 0
	RESUME NEXT
	
	ENDIT:
	END WITH
end function

function Get_Prev AS C(vc as C)
'get the Counter->preval if deleting the current record by testing for the prefix in use
'then decrements the value portion and puts the prefix back	
'my 14 tables that use this UDF use 1 of 3 different prefix styles for their primary keys
	dim va as c
	dim vn as n
	dim vs as n
	'In counter table all values are type 'C'
	if .not. isdigit(left(vc,1)) then		'D0115 DIRCODE; also KO_NO,STYLE_KEY,YARN_ID,HELPID,MSG_ID.
		vs = len(alltrim(vc))				'5
		va = left(vc,1)					'D
		vc = alltrim(substr(vc,2))			'0115
		vs = vs-1						'4
		if .not. isdigit(left(vc,2)) then	        'YR-100007 REC_HDR (only exception to prefix)
			va = substr(vc,1,3)			'YR-
			vc = alltrim(substr(vc,4))		'100007
			vs = vs-3					'9-3=6
		end if					
		vn = val(vc)-1					'115-1; 100007-1		
		vc = va+padl(alltrim(str(vn)),vs,"0")'D+0+114; YR-+100006 (no pad needed)
	else
		vn = val(alltrim(vc))-1		        '165323-1 PS_NO; also SCH_NO,MACHNO,IMGNO,INV_NO,PMT_ID
		vc = str(vn)				        '165322
	end if
	Get_Prev=vc
end function

My counter table follows this format: (Nextval is a calc field)
counter_tbl.jpg

[martinwcole]

Re: Basic Append Question

Have you thoroughly tested it, yet? Please let us know.

[MoGrace]

Re: Basic Append Question

An anomaly with Tablemax() ? lvlcls is a character field with numeric data - the first example is wrong but the 2nd is correct - why doesn't the first work right?

?tablemax("menux_img",".t.","lvlcls")
="9 "

?alltrim(str(tablemax("menux_img",".t.","val(lvlcls)")))
= "59"

Edit: because this is the order when I sort the browse on that field
1
10
11
12

more work to do...

[Stan Mathews]

Re: Basic Append Question

tablemax() with a numeric field or tablemax() with a character field?

[Stan Mathews]

Re: Basic Append Question

Character fields evaluate left to right. "9 " is greater than "59".

? lst
= 12
99
36100
105
1000
100000

? sortsubstr(lst,crlf())
= 1000
100000
105
12
36100
99

[Stan Mathews]

Re: Basic Append Question

You need to pad the return value of the tablemax().

tablemax("menux_img",".t.","padl(alltrim(str(val(lvlcls),19,0)),19,\"0\")")

so the evaluated values are of the same length.

Then "unpad it".

alltrim(str(val(tablemax("menux_img",".t.","padl(alltrim(str(val(lvlcls),19,0)),19,\"0\")"))))

If you padded the lvlcls in the table itself you wouldn't have the problem.

[martinwcole]

Re: Basic Append Question

my main question is whether your use of canwrite in the field events in field rules will work the way you think it will. In that event I think you are referencing a value in the buffer, not the field, usually referred to as a_field_value.value (which I think is always a character value)

[MoGrace]

Re: Basic Append Question

Stan - my 2nd result in the IW (post #19) did return the data I wanted ("59").
I can't use a numeric field for LVLCLS because I have to format another field used for a Tree as "LVLCLS+LVLOPN" ("2+22")
My purpose in getting the last LVLCLS is for when an image is added to the table. Whereas the IMGNO field which is padded with '0' can be changed for sorting or grouping the list without changing the unique primary key. Thus the user selects IMGNO from a list and gets the correct LVLCLS record. This way an image can be changed, the sort order can be reorganized and the Tree will display the change correctly.

Martin - Since this function is most often used for adding new records, I don't actually need the new DIR_ID value the user is entering in order to populate the DIRCODE field. I only need him to be in that field, which is why I am using the CanWrite event for the DIR_ID field and testing for ENTER mode on the table in the function. Once the new record is saved, the DIR_ID field cannot normally be edited by the user. This is however the lookup field used by other tables when selecting a customer. If for some reason he does want to change the DIR_ID field, my sets will not be affected since they use the DIRCODE field.

These primary keys are being populated 'behind the scene' when a user creates a new record. The user doesn't normally see these values - exceptions being Inv_no, Ps_no and Yarn_id which have no natural keys.

[MoGrace]

Re: Basic Append Question

I have my two functions Auto_Lup and Reset_Counter working with one small problem - if the user enters a value and then cancels the new record the counter table has already been updated. I would need to use the Reset_Counter function somewhere - but there is no event in field rules that tests for if a record entry is cancelled.

Any suggestions?

Edit: If the user cancels before leaving the target field, no update occurs to the counter table. Perfect.
Otherwise if I use the OnWrote field event to save the record, then he must delete the record and my script is invoked in the CanDeleteRecord record event. Whew... thanks for reading.