Append Most recent Date

[Gustavo1478]

Hello world, I need help writing a filter for an append operation. I have a table that holds class schedules for students, this table has many entries for the same student. What I would like to do is append from this table to an empty table with the exact same structure, the most recent class date for each ssn in the table (assuring that only one record per ssn is appended) the two fields that I am using is for the filter is START_DATE and SSN.

The past year I've been working strictly on the web side of alpha using a sql back end so im a bit of at a loss when trying to write this filter, I know how to write this in sql but I guess I've forgotten how to handle this in dbf for alpha. Can anyone give me hand, it would be very much appreciated.

[Al Buchholz]

Re: Append Most recent Date

What I find handy is to open a browse and write a query by expression to get what I want.

I then save the query and use it (or the code from it) in the operation.

[Gustavo1478]

Re: Append Most recent Date

I made an attempt at it using the default browse for my table hoping that the Genie would help to create a filter but I'm still not sure how to write it.

[Mike Wilson]

Re: Append Most recent Date

The following should work. I personally would make it into a function (UDF). This only works because you stated that the source and target table are identical - ie. same fields in the same order. An append would be faster but maybe that isn't as important.

'set the table names
	dim vsource_tbl as C="schedules"
	dim vtarget_tbl as C="target"
	
'acquire a list of students social security number
	dim vLssn as C
	vLssn=word_unique(table.external_record_content_get(vsource_tbl,"ssn"),crlf())

'get the record count for the wait dialog
	dim vnrecs as N
	vnrecs=line_count(vLssn)

'get pointer to tables and open the tables
	dim ts as p
	dim tt as p
	
	ts=table.open(vsource_tbl)
	tt=table.open(vtarget_tbl)
	
	dim cycle as N=0
	DIM GLOBAL pDlg1 as {waitdialog}
	pDlg1.Set_Title("Please Wait")
	pDlg1.Create(1,"Percent")
	
'loop through the ssn list, filter the source table to the ssn in date order, 
'get the last date record and write that record to the target table
	dim vdata as B				'record data variable
	dim vssn_now as C			'loop iteration ssn value
	
	for each foo in vLssn
		vssn_now=alltrim(foo)
		
		'wait dialog report
		cycle=cycle+1
		pDlg1.Set_Message(vssn_now)
		pDlg1.Set_Bottom_Message(str(cycle)+"  of  "+str(vnrecs))
		pDlg1.Set_Percent(Var->cycle,Var->vnrecs)		
		
		'filter the source table to the now ssn in start_date order and get the last date record into variable
		xs=ts.order("start_date","ssn="+quote(vssn_now))
		if xs.records_get()>0
			ts.fetch_last()
			vdata=ts.record_data_get()
			
			'write to the target table
			tt.enter_begin()
			    tt.record_data_set(vdata)
			tt.enter_end(.t.)
		end if
		
	'get next ssn
	next
	
'close the tables and the wait dialog at the end of the loop
	ts.close()
	tt.close()
	pDlg1.close()
	
'report complete
	msgbox("The last dates for each student have been written to the "+vtarget_tbl+" table.")

[Al Buchholz]

Re: Append Most recent Date

Or create a summary operation that groups on Ssn and max on start_date.

[Gustavo1478]

Re: Append Most recent Date

Thanks for the reply, this code looks loaded but thank you for adding the comments, its very easy to read.

[Gustavo1478]

Re: Append Most recent Date

Or create a summary operation that groups on Ssn and max on start_date.

This was much easier to accomplish, thank you for the push in the right direction. It worked perfectly.

[Al Buchholz]

Re: Append Most recent Date

Now create a set with the summary table as the parent that links to the original table with the ssn and max date.

Then you have all the records to append (or copy) to the new table.