Progressive Lookup Function

[Ted Giles]

Did you get anywhere with this Charlie?
Reason I ask is that I use a simple Quick Filter button on a Form.
Example:
Focus on account
Click QF
Shows those accounts
Focus on date
Click QF
Shows the selected accounts AND the date you picked.
Not found a limit to this form of drill down.
Used on Local Gvmnt app with many budget headings, suppliers and months/accounting governance values.

Just sayin'

[Charles Hoens]

I just revisited this great piece of code in order to help me on an application I am building. Problem is when I try to save the code I am getting an error message: "Error: Bad expression character." This is the line that it references:

vDlg=vWidth],[varN->vNumberOfLines]vSelect^#vList!vList_*]{vscroll=%0,[varN->vNumberOfLines+1]%3,[varN->vNumberOfLines]pos!pos_Changed};
%dlg%

Is there something I am missing here??

In fact, as I go on I am getting many other errors, such as a right parens, in an expression, etc. Is this the final code?

[jacobr]

Re: Progressive Lookup Function

Yes Keith,
is there any possibility to print that or export to excel?

Renny

[Keith Hubert]

Re: Progressive Lookup Function

Hi Renny,

Meanwhile I'm looking for an option to print the grid of progressive search result. Any advice on this?

Do you mean to print a report with the same data as the lookup?

[nehru]

Re: Progressive Lookup Function

+ 2 john

[Al Buchholz]

Re: Progressive Lookup Function

John,
Don't bother about the code too much. There is an xdialogu option (in v10) which will do the progressive search easily. If you are still struggling let me know, i will upload a sample project.

Group members,
Meanwhile I'm looking for an option to print the grid of progressive search result. Any advice on this?
Thanx

Depending on the data volume, you may notice a large performance issue between Jim's routine and other routines....

Jim's is extremely fast even with large tables - ie more than hundreds of thousands of records.

Usually the difference is noticed in the 2,000 or more records in a table...

[jacobr]

Re: Progressive Lookup Function

John,
Don't bother about the code too much. There is an xdialogu option (in v10) which will do the progressive search easily. If you are still struggling let me know, i will upload a sample project.

Group members,
Meanwhile I'm looking for an option to print the grid of progressive search result. Any advice on this?
Thanx

[Stan Mathews]

Re: Progressive Lookup Function

Agreed.

You pass the source table, source index, return field, rows and columns.

John,

The last function posted is database and table independent.
You tell the funtion what table to use.
You tell the function the index name (on that field, of course) to use.
You tell the function what field to return.
Etc.

[forskare]

Re: Progressive Lookup Function

John,

I don't think posting a DB would help you any more than the code posted above because you will still need to change parts of the code to reflect your database. Tell us what your issue is.

[johngtatp]

Re: Progressive Lookup Function

Would someone be so kind as to post a small database with this function working within it, thank you.

[jkletrovets]

Re: Progressive Lookup Function

Hi All,

Thought I would post this here in case it might help someone else. I made a quick and dirty little function that calls Jim's Progressive Lookup function. But, this function will use the recno() returned by Jim's function and return whatever field you want for that record number. I wanted an easy way to have a one line function call to give me the data from the field I wanted. Instead of modifying Jim's function I decided to use it as is and just call it with this little function.

You pass the source table, source index, return field, rows and columns.

'Date Created: 12-Mar-2009 05:00:37 PM
'Last Updated: 12-Mar-2009 08:03:23 PM
'Created By  : Jeff
'Updated By  : Jeff
FUNCTION PrgLkupRetVal AS C (srctable AS C, srcindex AS C, srcretfield AS C,lkuprows as N, lkupcolumns as N)
'Call Jim's function
	vrecnum=prglkup(srctable,srcindex,lkuprows,lkupcolumns)
	
	if vrecnum>0 ' a valid record was returned
		dim tblsrc as p
		tblsrc=table.open(srctable,FILE_RO_SHARED)
		tblsrc.fetch_goto(vrecnum)
		PrgLkupRetVal=alltrim(eval("tblsrc."+srcretfield))
	             tblsrc.close()
	else
	PrgLkupRetVal=""
	ui_msg_box("No Record Chosen","No Record was chosen - Process Terminating!")
	end
	end if
END FUNCTION

BTW, Thanks to Jim and all that have contributed to this thread. This is a great function to have.

I have used a fixed width font (QuickType Mono) that I did put into Jim's dialog code to make columns line up perfectly when I use more than one field in my index for the lookup (actually they all have more than one field in them) - makes for a nice looking presentation to the user.

Regards,

Jeff

[dik_coleman]

Re: Progressive Lookup Function

This is my 'latest' version. It works!

these are the variables passed along to the function.

the index 'lkup4' is an index on the table that organizes last and first names of husband and wife into the following: Coleman, Richard or Ann.
It also allows for only one spouse and formats the index accordingly.
The record chosen is displayed on a form. Data is transferred from the lookup
to shared and global variables that facilitate data input to a form.

vtblname="c_custbl"
vindxname="lkup4"
vnumlines=25
vWidth=45

vrecnum=PrgLkup(vtblname, vindxname, vnumlines, vWidth)

if vRecNum > 0 then
	:fc_cusok.Recno_goto(vRecNum)
	 ' original line in script
end if

this is the function PrgLkup:

'Date Created: 22-Sep-2005 08:51:46 PM
'Last Updated: 14-Dec-2008 08:43:13 AM
'Created By  : Jim Chapman
'Updated By  : Dick Coleman

FUNCTION PrgLkup AS N (vtblname AS C, vindxname AS C, vnumlines AS N, vWidth AS N )

dim shared pos as N  'The position of the scroll bar
dim shared vList as C 'The list variable that is displayed in the control
dim shared vSelect as C 'The 'record' selected by the user
dim shared vTypeIn as C  'The variable that contains the 'search for' string the user types in
dim shared vfstrecnum as N 'The variable that contains the first record number
dim shared getoutnow as L = .F.

pos = 0
vSelect = ""
vTypeIn = ""
t = table.open(vtblname,FILE_RO_SHARED)
vIndex = t.index_primary_put(vindxname)
t.fetch_first()
vfstrecnum = t.recno()

vList = ""
vList = BuildList(t,vIndex,vnumlines)
dim shared vpgdn as n

vDlg =<<%dlg%
{position=remember=cuslkup1}
{on_key=PGUP!pgup}
{on_key=PGDN!pgdn}
{on_key=Down!Down}
{on_key=Up!Up}
{on_key=ENTER!EnterKey}
Type in Query Description:;
[.[varN->vWidth]vTypeIn!changed];;
[%O={@@}%.[varN->vWidth],[varN->vnumlines]vSelect^#vList!vList_*]{vscroll=%0,[varN->vnumlines+1]%3,[varN->vnumlines]pos!pos_Changed};
<10OK> <10Cancel>
%dlg%

vDlg = replace_parameters(vDlg,local_variables())

vDlgReturn = ui_dlg_box("Input Search String",vDlg,<<%code%

if a_dlg_button = "Cancel"
	getoutnow=.T.
	goto inside_end
end if

if a_dlg_button = "vList_dblclick"
	a_dlg_button = "OK"
end if 
if a_dlg_button = "EnterKey" .or. a_dlg_button = "OK"
	select
	   case vSelect = ""
	   	a_dlg_button = ""
	   	ui_msg_box("Oops","A lookup selection must be made or the Cancel button pushed!",UI_STOP_SYMBOL)
	   case vSelect <> ""
	   	a_dlg_button = "OK"
	end select
end if 
if a_dlg_button = "changed"
	a_dlg_button = ""
	t.fetch_find(vTypeIn)
	vList = BuildList(t,vIndex,vnumlines)
	GetCurrentValues("TypeInChanged",1)
end if 
if a_dlg_button ="pos_changed" .or. a_dlg_button = "Up" .or. a_dlg_button = "Down"
	select
	   case a_dlg_button = "Down"
	   pos = pos + 1
	   case a_dlg_button = "Up"
	   pos = pos - 1
	end select
	a_dlg_button = ""
	select
	   case pos > vnumlines
	   	vList = BuildList(t,vIndex,vnumlines)
	   	GetCurrentValues("Scroll_EOL",1)
	   case pos = 0 .and. vfstrecnum = val(vSelect)
	   	GetCurrentValues("At First Record",0)
	   case pos = 0
	   for i = 1 to vnumlines * 2
	   		if t.bof(vindxname)
				exit for
			end if 
	   	t.fetch_prev()
	   	next 
	   	vList = BuildList(t,vIndex,vnumlines)
	   	GetCurrentValues("Scroll_BOL",vnumlines)
	end select
	vCurrentString = word(vList,pos,crlf())
	vReturnString = GetCurrentValues("POS_Changed",pos)
end if 
if a_dlg_button = "pgdn"
	a_dlg_button = ""
	select
	   case pos < vnumlines 
	   if vnumlines > line_count(vList)
	   	GetCurrentValues("PageDown",line_count(vList))
	   	else
	   	GetCurrentValues("PageDown",vnumlines)
	   end if 
	   case else
	   	vList = BuildList(t,vIndex,vnumlines)
	   	GetCurrentValues("PageDown",1)
	end select
end if 

if a_dlg_button = "pgup"
	a_dlg_button = ""
	select
	   case pos > 1
	   	GetCurrentValues("PageUp",1)
	   case else
	   	for i = 1 to (vnumlines + line_count(vList))
	   		if t.bof(vindxname)
				exit for
			end if 
	   	t.fetch_prev()
	   	next 
	   	vList = BuildList(t,vIndex,vnumlines)
	   	GetCurrentValues("PageUp",1)
	end select
end if 

'_____________________________________________________________________
'Code created by Selwyn Rabins due to bug report submitted.
'this has fixed the loss of clicking on a name to pick it and have it displayed in pick box.
	if atc("vlist_",a_dlg_button) = 1 then
	
	if a_dlg_button = "vList_Change"
	dim pos as n
	pos = atc("{data=" + alltrim(vselect),vlist)
	vCurrentLine = word_from_pos(vlist,pos,crlf())
	
	vReturnString = GetCurrentValues("vList_Changed",vCurrentLine)
	a_dlg_button = ""
	else
	a_dlg_button = ""
	end if
	end if 
'_____________________________________________________________________
'Original code below and commented out

'if a_dlg_button = "vList_Change"
'	a_dlg_button = ""
'	vCurrentLine = word_number_get(vList,vSelect,crlf())
'	vReturnString = GetCurrentValues("vList_Changed",vCurrentLine)
'end if 
'if left(a_dlg_button,6) = "vList_"
'	a_dlg_button = ""
'end if 
'______________________________________________________________________

inside_end:

%code%)
t.close()
vrecnum = val(vSelect)
select
   case vDlgReturn <> "OK"
   	PrgLkup = -1
   case vrecnum > 0
   	PrgLkup = vrecnum
   case else
   	PrgLkup = -1
end select

END FUNCTION


function GetCurrentValues as C (vWhatFired as c,vPositionNum as n)
	   	vCurrentString = word(vList,vPositionNum,crlf())
	   	'*****************************************************************************
	   	'*** Uncommenting the following lines will cause the type in control to reflect
	   	'*** highlighted selection when using page and arrow keys, mouse and scroll bar
	   	'*****************************************************************************
	   	if vWhatFired <> "TypeInChanged"
	   		vTypeIn = alltrim(substr(vCurrentString,at("}",vCurrentString)+1))
	   	end if 
	   	vTempLen = at("}",vCurrentString) - 7
	   	vSelect = substr(vCurrentString,7,vTempLen)
	   	pos = vPositionNum
	GetCurrentValues = "T"
end function 

function BuildList as c (vTable as p,vIndex as p,vCount as n)
	BuildList = ""
	for i = 1 to vCount
		vKey = vIndex.key_get()
		BuildList = BuildList + "{data="+padl(alltrim(str(vKey.record)),10,"0")+"}"+ vKey.key + crlf() 		
		if vTable.eof(vIndex.name_get())
			exit for
		end if
		vTable.fetch_next() 
	next
end function

I don't know if there are any 'other' versions.
Hope it helps.
D

[michaelwpayton]

Re: Progressive Lookup Function

I'm a little lost here... with all the mods, or not, mods, or not... that have gone on with this function. Anyone have any idea where the "latest/greatest" code for this can be found... if anywhere... out here :-)

Thanks,

[Tom Cone Jr]

Re: Progressive Lookup Function

Recommended changes (for Alpha Five vers 8) to Jim's script are discussed in the following thread:

http://msgboard.alphasoftware.com/al...ad.php?t=71983

[dik_coleman]

Re: Progressive Lookup Function

Thanx Tom...
That is exactly what I found out over the past hour.
by creating a variable to carry out a value after getting the minus one, I was able to stop all further action and return to the form that started the script...

Sorry to be so dense sometimes..... Me like turtle -- I get there eventually

Thanx again
D