Hi everybody,

I'm trying to massage one table's info into another table and I could not find a good algorithm, so I need some help.

Here is source table info:

rcode c(2)
date  D
doctype C(6) && DEED/MTG/FDAFT
deedtype C(2) && FD
docketref C(11)
book N(5,0)
page N(4,0)
price I
BuyerName C(25)
BuyerRel C(1)
SellerName C(25)
SellerRel C(1)
NameRel C(1)
Mortgage I
LenderName C(45)
City C(25)
Address C(42)
LotUnit C(25)
RecNum I && New field, I added, to keep track of new records created.

Here is the info table I need to create:

RecNum I && Unique ID of the record
Source C(1) && S-sale, M - mtg
ccode C(2) && county code
town C(4) && town code
FileName C(8)
Buyer1 C(22)
Buyer2 C(22)
Seller1 C(22)
Seller2 C(22)
Price I
Mortgage I
Lender C(6) && Lender code
LName && Lender Name, if not coded
StNum I
StNumExt C(5)
Street C(21)
LotCode C(1)
Unit C(6)
Date D
Book I
Page I
DocketRef C(10)
...

I added two new indexes to the source table:

1  ADDRESS    UPPER(CITY+ADDRESS+LOTUNIT)

2  GEO   UPPER(CITY+ADDRESS+LOTUNIT+DOCTYPE+ ;
         STR(MORTGAGE,10)+DOCKETREF+STR(BOOK,5)+STR(PAGE,4))

The data could in the source table could be this way:

DEED - several records one per each buyer and one per each seller and one additional record

MTG several records: one per each buyer and another one for Lender Name.

I need to create one record in output table: if it's a sale, I need only two buyers, two sellers and one mortgage info in the same record. -Source "S"

All additional mortgages ("piggy back" mortgages should produce a separate record).

If it's a mortgage only, each mortgage (indicated with a DocketRef/Book/Page should produce one record - 2 buyers combined). Additional records (buyers from 3 to 5) are not used.

I need to provide back link, so while I'm inserting records into OutTable, I need to set RecNum in the Source table.

Hopefully my explanation is clear enough.

Here is my attempt to create such program (but obviously it doesn't work correctly):

********************************************************************
*  Description.......: PopulateBldMstr - this program is supposed to create
*                    : one BldMstr record from multiple records in the source file
*  Calling Samples...: PopulateBldMstr('RegMstr','RegOut')
*  Parameter List....: tcSourceTable, tcOutTable
*  Created by........: Nadya Nosonovsky 07/12/2002 12:49:16 PM
*  Modified by.......:
********************************************************************
lparameter tcSourceTable, tcOutTable
local lcDataDir, lnStartTime
lnStartTime = seconds()				&& # seconds since midnight
lcDataDir = curdir()+'Data\'

if vartype(m.tcSourceTable)<>"C" or not file(forceext(m.lcDataDir+m.tcSourceTable,'DBF'))
	tcSourceTable = getfile('DBF', '', '', 0, 'Select Source Table')
endif

if vartype(m.tcOutTable)<>"C"
	tcOutTable = InputBox('Enter output table name','Choose name for output file')
endif

if empty(m.tcOutTable)
   return .f.
endif
   
if file(forceext(m.lcDataDir+m.tcOutTable,'DBF')) and messagebox('File '+ m.tcOutTable + ;
		' already exists.' + chr(13) + 'Do you want to re-create it?',36,'File already exists')=7
	return && User doesn't want to produce a new file
endif

if not UseTble(m.lcDataDir+'BldMstr','Template')
	return .f.
endif

if not UseTble(iif(empty(justpath(m.tcSourceTable)),m.lcDataDir,"")+ m.tcSourceTable, ;
               'Srce','exclusive','Geo') && Assume, it already has the correct index
	return .f.
endif

if not UseTble('Lookups!Towns','Towns','shared noupdate','FullName')
	return .f.
endif

if not UseTble('Lookups!Lender','Lender','shared noupdate','FullName')
	return .f.
endif

select Template
use in select('OutTble')
copy to (m.lcDataDir+m.tcOutTable) with cdx && Create a copy of the template
use in Template && Close

if not UseTble(m.lcDataDir+m.tcOutTable, 'OutTble')
	return .f.
endif

local lcAddress, lcDeed, Source, Buyer1, ;
	Buyer2, BuyerRel, Seller1, Seller2, SellerRel, ;
	Lender, town, ccode, lcDocketRef, lnBook, lnPage, lName, ;
	Pstreet, DocketRef, RecNum, lnBuyerCount, ;
	lnSellerCount, lnPrice, lnMortgage, lnRecNum

select OutTble
scatter memvar memo

select Srce
go top
lcAddress = upper(Srce.City+Srce.Address+Srce.LotUnit)
lcDeed = Srce.DeedType

private plHalt
local lcPrevOnEsc, lcPrevEscape, lnCount, lnReccount, lnUpdateNumber, ;
	lcMsgTail, lcStatusBar, loTherm, lnElapsed
* support user Escapes for interrupting the main loop
lcPrevOnEsc = on('escape')	&& save previous Escape handler
lcPrevEscape = set('escape')	&& previous Escape enablement state
set escape on			&& enable escape handling
plHalt = .f.		&& allow loop to run until this flag is toggled
on escape plHalt = .t.	&& force immediate termination if user escapes
lnReccount=reccount()
set message to 'Processing '+transform(m.lnReccount)+' records'
do case
case m.lnReccount<100 && Very rare case
	lnUpdateNumber=1
case between(m.lnReccount,100,10000)
	lnUpdateNumber=100
case m.lnReccount>10000
	lnUpdateNumber=val('1'+replicate('0',len(transform(m.lnReccount))-3))
endcase
* assemble fixed portion of status bar message outside of loop, for speed
lcMsgTail = "/" + transform(m.lnReccount) + ".  Wait or press Esc to cancel ..."

*--- instantiate thermometer bar class....
wait window nowait "Processing " + transform(m.lnReccount) + " records from " + ;
	m.tcSourceTable + chr(13)+ " to " + m.tcOutTable

loTherm = newobject("thermometer", "wg.vcx","", ;
	"Processing "+transform(m.lnReccount)+" records from "+ m.tcSourceTable,m.lnReccount)

loTherm.show()
lcStatusBar=set('status bar')
set status bar on

store 0 to lnCount, lnBuyerCount, lnSellerCount, lnRecNum

local llSale
lSale = .f.

do while not eof()

	lcAddress = upper(Srce.City+Srce.Address+Srce.LotUnit)

	if m.plHalt
		if messagebox('Do you want to stop the process?',36,'Stop Process')=6
			exit
		else
			plHalt = .f.
		endif
	endif

** Update status message
	if mod(m.lnCount,100) = 0
		set message to 'Record # '+transform(m.lnCount)+m.lcMsgTail
	endif
** Update thermometer
	if mod(m.lnCount,m.lnUpdateNumber) = 0
		loTherm.update(m.lnCount)
	endif

	select towns
	locate for State='MA' and fullname = proper(Srce.City)
	if found()
		town = towns.town
		ccode = towns.ccode
	else && Should not happen
		town = space(4)
		ccode = space(2)
	endif
    select Srce
	Pstreet = alltrim(Srce.Address)+ ;
		iif(not alltrim(Srce.LotUnit) $ Srce.Address,alltrim(Srce.LotUnit),'')

	lnRecNum = m.lnRecNum+1
	
** Inner loop within the same address
	do while upper(Srce.City+Srce.Address+Srce.LotUnit)= m.lcAddress
		lnCount=m.lnCount+1
		if m.plHalt
			if messagebox('Do you want to stop the process?',36,'Stop Process')=6
				exit
			else
				plHalt = .f.
			endif
		endif

** Update status message
		if mod(m.lnCount,100) = 0
			set message to 'Record # '+transform(m.lnCount)+m.lcMsgTail
		endif
		
** Update thermometer
		if mod(m.lnCount,m.lnUpdateNumber) = 0
			loTherm.update(m.lnCount)
		endif

		scatter memvar
		lnPrice = Srce.Price
		lnMortgage = Srce.Mortgage
		lcDocketRef = Srce.DocketRef
		lnBook = Srce.Book
		lnPage = Srce.page

		if inlist(Srce.DocType,'DEED','FDAFT') and empty(m.Source)
			Source = 'S'
			llSale = .t.
			select Srce
		*	gather memvar fields RecNum
		    replace RecNum with m.lnRecNum
		endif

		if Srce.DocType = 'MTG' and empty(m.Source)
			source = 'M'
			llSale = .f.
			lcDocketRef = Srce.DocketRef
			lnBook = Srce.Book
			lnPage = Srce.page
		endif

		lName =  substr(Srce.LenderName,1,40)
		if seek(m.lName,'Lender')
			Lender = Lender.code
			lName = ""
		endif

&& Buyer List
		if not empty(Srce.BuyerName) and m.lnBuyerCount = 0
			Buyer1 = Srce.BuyerName
			lnBuyerCount = 1
		endif

		if not empty(Srce.BuyerName) and m.lnBuyerCount = 1
			Buyer2 = Srce.BuyerName
			lnBuyerCount = 2
		endif

&& Seller List
		if not empty(Srce.SellerName) and m.lnSellerCount = 0
			Seller1 = Srce.SellerName
			lnSellerCount = 1
		endif

		if not empty(Srce.SellerName) and m.lnSellerCount = 1
			Seller2 = Srce.SellerName
			lnSellerCount = 2
		endif
		if eof()
		  exit
		endif  
		skip

		if Srce.DocType = 'MTG'
			if (not empty(m.lcDocketRef) or not empty(m.lnBook) or not empty(m.lnPage)) ;
					and (Srce.DocketRef<>m.lcDocketRef or Srce.Book<>m.lnBook or Srce.page<>m.lnPage)
** New "piggy back" mortgage
				Price = m.lnPrice
				Mortgage = m.lnMortgage
				select OutTble
				insert into OutTble from memvar
			    replace RecNum with m.lnRecNum
				lnRecNum = m.lnRecNum + 1
				store 0 to lnBook, lnPage, lnPrice, lnMortgage, lnBuyerCount, lnSellerCount
				scatter memvar blank
				select Srce
			else
				select Srce
*				gather memvar fields RecNum
			    replace RecNum with m.lnRecNum		
			endif
		endif
	enddo
	
	if (OutTble.DocketRef <> m.lcDocketRef or OutTble.Book<>m.lnBook or OutTble.page<>m.lnPage) ;
	    and (not empty(m.lcDocketRef) or not empty(m.lnBook) or not empty(m.lnPage))
		Price = m.lnPrice
		Mortgage = m.lnMortgage
		insert into OutTble from memvar
	    replace RecNum with m.lnRecNum
	endif
	
	store 0 to lnBook, lnPage, lnPrice, lnMortgage, lnBuyerCount, lnSellerCount
	select OutTble
	scatter memvar blank
	select Srce
	if eof()
	  exit
	endif  
	skip && In the outer loop
enddo

Thanks in advance for your help.