LOCAL loCombo AS ComboBox CREATE CURSOR cTest (OrderNo C(10), CustomerNo C(10)) INSERT INTO cTest VALUES ('5', '861') INSERT INTO cTest VALUES ('9', '23') INSERT INTO cTest VALUES ('23', '66') INSERT INTO cTest VALUES ('123', '34') INSERT INTO cTest VALUES ('767', '7878') INSERT INTO cTest VALUES ('1223', '4543') loCombo = CREATEOBJECT('ComboBox') SELECT cTest SCAN loCombo.AddListItem(OrderNo, RECNO(), 1) loCombo.AddListItem(CustomerNo, RECNO(), 2) ENDSCAN *-- Add more values to test that you can handle more than 65k SELECT cTest FOR lnI = 1 TO 100000 loCombo.AddListItem(ALLTRIM(STR(lnI)), RECNO(), 1) loCombo.AddListItem(ALLTRIM(STR(lnI + 100)), RECNO(), 2) NEXT *-- Sort the list on OrderNo (First Column) loCombo.Sorted = .T. *-- Lookup Values lcOrderNo2Find = '767' FOR lnI = 1 TO loCombo.ListCount IF loCombo.ListItem(lnI, 1) = lcOrderNo2Find *-- If found, print CustomerNo ?'Found :', loCombo.ListItem(lnI, 2) EXIT ENDIF NEXT>Jayesh,
>>>
>>>set procedure to bobaseclasses additive
>>>set proced to fingerprintclass additive
>>>public loFingerPrint
>>>loFingerPrint=createobject("fingerprint")
>>>*-- populate the fingerprint object's Afingerprint array with the orders, and their fingerprints
>>>
>>>
>>>
>>>loFingerPrint.lcHeaderInputTable='ORDERS'
>>>loFingerPrint.lcDetailInputTable='ORDITEMS'
>>>*loFingerPrint.lcIntermediateTablePhysicalFileName='' && PROTECTED will get set and ultimately erased by the class methods
>>>loFingerPrint.lcIntermediateTableAlias='Curorders' && ultimately gets closed and not used but available in case it is needed
>>>
>>>loFingerPrint.lcKeyFieldName='order_num'
>>>*loFingerPrint.lcKeyValue='' && protected set in classmethods
>>>loFingerPrint.lcFingerField='item_code' && set in classmethods
>>>
>>>
>>>*-- for test sort is on fingernum, but will be on more complex expression later
>>>loFingerPrint.lcFingerSortExpression='nfingernum'
>>>
>>>loFingerPrint.lcFinalFingerDataPhysicalFileName='' && set in classmethods
>>>wait wind 'Processing fingerprints ' nowait
>>>loFingerPrint.ProcessFingerPrintsAll()
>>>*-- after processing all fingerprints - data resides in a uniquely generated filename held in the property
>>>*-- loFingerPrint.lcFinalFingerDataPhysicalFileName
>>>lcFinalFile=loFingerPrint.lcFinalFingerDataPhysicalFileName
>>>
>>>
>>>loFingerPrint=NUll
>>>RELEASE loFingerPrint
>>>select 0
>>>use (lcFinalFile) alias FingerPrintedOrders
>>>browse nomod
>>>*use in fingerPrintedOrders
>>>*erase (lcFinalFile)
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>Define class Fingerprint as bobase
>>> *-- dss created on 6/10/03
>>> *-- everything is done in a private datasession as bobase is based on session class
>>>
>>> lcHeaderInputTable=''
>>> lcDetailInputTable=''
>>> protected lcInterMediateTablePhysicalFilename
>>> lcInterMediateTablePhysicalFilename=''
>>> lcInterMediateTableAlias=''
>>> lcKeyFieldName=''
>>> protected lcKeyValue
>>> lcKeyValue=''
>>> lcFingerField=''
>>> lcFingerSortExpression=''
>>> lcFinalFingerDataPhysicalFileName=''
>>>
>>>
>>>
>>> *-- first column is order number
>>> *-- second column is fingerprint
>>> Dimension AFingerPrint[1,2]
>>>
>>> nNumberOfLinesInArray = 0
>>>
>>> Procedure init
>>>
>>> Set delete on
>>> Set safe off
>>> Set talk off
>>> Set echo off
>>> Set exclu off
>>> This.AFingerPrint='NOTINITIALIZED'
>>> Endproc
>>>
>>> Procedure CreateFingerPrintInterMediateTable
>>> *-- this will create the fingerprint intermediate table - a temp table with a unique name
>>> *-- that has the columns nfingernum, and mfingerdat in it
>>> *-- and open it in the private data session with alias of Tcfingerprintsourcetablealias
>>> *-- note that fingerprint source table is not 'original ' source, but
>>> *-- and pass back the physical name of the file -
>>> Local lcSqlCmd
>>> this.lcIntermediateTablePhysicalFileName=sys(2015) +'.dbf'
>>> Create cursor FingerMemo (mfingerdat m)
>>>
>>> lcSqlCmd="select *, 000000000000 as nfingernum from " + this.lcHeaderInputTable + ;
>>> " left outer join FingerMemo on .t. into table " + this.lcIntermediateTablePhysicalFileName
>>> &lcSqlCmd
>>> Use
>>> Use in FingerMemo
>>>
>>> Select 0
>>> Use (this.lcIntermediateTablePhysicalFileName) alias (this.lcInterMediateTableAlias) shared
>>>
>>>
>>> Endproc
>>>
>>>
>>> Procedure AddAllRowsToFingerPrintArray
>>>
>>> Local lcAliasPlusFieldName
>>> Select (this.lcIntermediateTableAlias)
>>> Go top in (this.lcIntermediateTableAlias)
>>> Do while not eof(this.lcIntermediateTableAlias)
>>> lcAliasPlusFieldName = alltrim(this.lcIntermediateTableAlias) + "." + alltrim(this.lcKeyFieldName)
>>> *lcKeyValue= "'" + &lcAliasPlusFieldName + "'"
>>> this.lcKeyValue= &lcAliasPlusFieldName
>>> This.AddRowToFingerPrintArray()
>>> Skip in (this.lcIntermediateTableAlias)
>>> Enddo
>>>
>>> Procedure EraseIntermediateTable
>>> *-- must close this.lcIntermediateTableAlias first before erasing
>>> Erase (this.lcIntermediateTablePhysicalFileName)
>>>
>>> Endproc
>>>
>>> Procedure CloseIntermediateTable
>>> *-- must close this.lcIntermediateTableAlias first before erasing
>>> if used(this.lcIntermediateTableAlias)
>>> use in (this.lcIntermediateTableAlias)
>>> endif
>>>
>>> Endproc
>>>
>>>
>>>
>>> Procedure AddRowToFingerPrintArray
>>> ** select from any table or cursor values where column: tckeyfieldname ='s tckeyvalue
>>> ** and order by by tcFingerField - and loop through cursor lines, building up a string
>>> *-- based upon fingerfield and add to the object's aFingerSort array,
>>> *-- the keyvalue for tckeyfieldname in array column 1 , and the built up string (fingerprint)
>>> *-- in array column 2
>>> Local lcFingerPrint, lcsqlString
>>>
>>> If used('CurSortedLines')
>>> Use in CurSortedLines
>>> Endif
>>> lcWrappedKeyValue="'"+this.lcKeyValue+"'"
>>> lcsqlString = 'select * from ' + this.lcDetailInputTable + ' where ' + this.lcKeyFieldName + ' = ' + ;
>>> lcWrappedKeyValue + ' order by ' + this.lcFingerField + ' into Cursor CurSortedLines'
>>>
>>> &lcsqlString
>>> Select CurSortedLines
>>> Go top in CurSortedLines
>>> lcFingerPrint=''
>>> Do while not eof('CursortedLines')
>>> lcFingerField=this.lcFingerField
>>> lcFingerPrint=lcFingerPrint+ &lcFingerField + '|'
>>> Select CurSortedLines
>>> Skip in CurSortedLines
>>> Enddo
>>>
>>> This.nNumberOfLinesInArray = this.nNumberOfLinesInArray +1
>>> *-- redimension the array - only adding a line at a time - only make as big as needed
>>> Dimension this.AFingerPrint[this.nNumberOfLinesInArray,2]
>>>
>>> This.AFingerPrint[this.nNumberOfLinesInArray,1]=this.lcKeyValue
>>>
>>> *-- put the fingerprint string into the array
>>> This.AFingerPrint[this.nNumberOfLinesInArray,2]=lcFingerPrint
>>> Use in CurSortedLines
>>>
>>> Endproc
>>>
>>> Procedure SortArrayOfFingerPrints
>>> *-- sort the FingerPrint Array by column 2 - which has the fingerprint data
>>> lnretVal=asort(this.AFingerPrint,aelement(this.AFingerPrint,1,2))
>>> Endproc
>>>
>>> Procedure PopulateIntermediateTableWithFingerPrintData
>>> *-- populates a table with the values from the FingerPrint Array
>>> *-- assumes the cursor has a numeric field named nfingernum, and a memo field called mfingerdat
>>> *-- we don't really need the fingerprint data - itself - but I'm putting it in here - just in case
>>> *-- in the cursor
>>>
>>>
>>> *assert .f.
>>> Local lnFingerRow, lnFingerColumn, lnElementNumber, lmFingerData
>>> Local lcKeyFieldName
>>> Select (this.lcIntermediateTableAlias)
>>> Go top in (this.lcIntermediateTableAlias)
>>> Do while not eof(this.lcIntermediateTableAlias)
>>> *-- search the first column of the array for the order number
>>> *lnElementNumber = ;
>>> *Ascan(this.AFingerPrint,order_num, ;
>>> *aelement(this.AFingerPrint,1,1), ;
>>> *aelement(this.AFingerPrint,alen(this.AFingerPrint,1),1))
>>> lnFingerRow=0
>>> For i = 1 to alen(this.AFingerPrint,1)
>>> *-- order_num should not be hardcodedd
>>>
>>> lcKeyFieldName = this.lcKeyFieldName
>>> If alltrim(this.AFingerPrint[i,1]) == alltrim(&lcKeyFieldName)
>>> lnFingerRow=i
>>> Exit
>>> Endif
>>>
>>> Endfor
>>>
>>> If lnFingerRow <> 0
>>> *if lnElementNumber <> 0
>>> *-- the row number is the sort number - after the sort function has been applied
>>> *lnFingerRow = Asubscript(this.AFingerPrint,lnElementNumber,1) && return row subscript of element found
>>> *lnFingerColumn = Asubscript(this.AFingerPrint,lnElementNumber,2) && return Column subscript of element found
>>>
>>> *-- Fingerdata is in same row as ordernumber but in column 2
>>> lmFingerData= this.AFingerPrint[lnFingerRow,2]
>>>
>>>
>>> **- the above returns row and column for the ordernumber
>>> *replace nfingernum with lnFingerRow in (tcTableName)
>>> Replace nfingernum with lnFingerRow in (this.lcIntermediateTableAlias)
>>> Replace mfingerdat with lmFingerData in (this.lcIntermediateTableAlias)
>>>
>>> Else
>>>
>>> *MessageBox('Error while sorting Array','Pickpack Sytsem')
>>>
>>> Endif
>>> Skip in (this.lcIntermediateTableAlias)
>>> Enddo
>>> Endproc
>>>
>>> Procedure SortIntermediateTableWithFingerPrintData
>>> *-- findoutputtablename will be passed by reference
>>> Local lcSqlCmd, lcIntermediate
>>> *-- selects all rows from tcInputtable that have been populated with the field nfingernum
>>> *-- and orders them by tcsortexpression where the assumption is that tcsortexpression
>>> *-- includes nfingernum as the primary or secondary sort
>>> *-- outputs the rows to a cursor named CurFingerData
>>> this.lcFinalFingerDataPhysicalFileName=sys(2015) +".dbf"
>>>
>>> lcSqlCmd = "select * from " + this.lcInterMediateTableAlias + " order by " + this.lcFingerSortExpression + ;
>>> " into table " + this.lcFinalFingerDataPhysicalFileName
>>> &lcSqlCmd
>>> use
>>> this.CloseIntermediateTable()
>>> this.EraseIntermediateTable()
>>>
>>> Endproc
>>>
>>>
>>>
>>>
>>> Procedure ProcessFingerPrintsAll
>>>
>>> this.CreateFingerPrintIntermediateTable()
>>> this.AddAllRowsToFingerPrintArray()
>>>
>>>
>>> *-- now sort the array by the fingerprint column: loFingerPrint.afingerprint[nrow,2]
>>> this.SortArrayOfFingerPrints()
>>>
>>> *-- now populate the Intermediate Tempt table with the fingeprint data
>>> * the nfingernum field will have the number of the row of the sorted array
>>> *-- note must be a temp table - because we can't update a cursor in vfp 6
>>> *-- table must have fields nfingernum and mfingerdat
>>> this.PopulateIntermediateTableWithFingerPrintData()
>>>
>>> *-- temp input table Name is first parameter, sort expression is 2nd param, output CursorName is third parameter
>>> this.SortIntermediateTableWithFingerPrintData()
>>>
>>> Endproc
>>>
>>>Enddefine
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>- Jayesh