What is it called a search like this

[peterth]

I usually select name of member from member_id using table lookup.
Is it possible or what is it call a search like this.
If I key in a part of name of member name and it can show all member name that similar the part of name I key in.
For exsample I key in Pet and it will automatically show all name that included Pet for me to select the name from list
--------------
I key in Pet in a search field and it can show all from list like this
005 Pet
006 Peter
009 Petee
012 JuPeter

and I select Peter from a list

Thanks
Peter-th

[Davidk]

Re: What is it called a search like this

Hi Peter,

I have a search just like this in my app. I have a search field bound to a variable. I have OnDepart code as follows:

Dim nbrwords as N
Dim srchstr as C
Dim fname as C
Dim lname as C

varPaySched = ""	
srchstr = :frmMembershipEntry:membersearch.text
notfoundflag = 0
If Isnull(srchstr) = .t. then
	end
end if
Old_filter = topparent.filter_get()

select

	case isnumber(srchstr) .and. len(srchstr) = 10
		'this looks for phone numbers
		srchstr = Mask(srchstr, "(   )|   -    ")
		dim filter as c
		
		filter = "Homephone=" + Quote(srchstr) + " .or. Workphone=" + Quote(srchstr) + " .or. Cellphone=" + Quote(srchstr)
		result = :frmMembershipEntry.QueryRun(filter)	
		If (result = 0) then
			ui_msg_box("Member Search", "No Member Found")
			'To "undo" the last query:
			Topparent.QueryRun(old_filter)
			notfoundflag = 1
 		end if

	
    case isnumber(srchstr)
    'this looks for a membership #
		dim filter as C    
		filter = "Member_Id=" + srchstr
		result = :frmMembershipEntry.QueryRun(filter)
		If (result = 0) then
			ui_msg_box("Member Search", "No Member Found")
			'To "undo" the last query:
			Topparent.QueryRun(old_filter)
			notfoundflag = 1
 		end if

	case *any(srchstr, ",")
		'this looks for last name, first name
		fname = ltrim(trim(word(srchstr, 2, ",")))
		lname = ltrim(trim(word(srchstr, 1, ",")))
		dim filter as c
		filter = "left(First_name,len("+Quote(fname)+")) = "+Quote(fname) + " .and. left(Last_name,len("+Quote(lname)+")) = "+Quote(lname)
		result = :frmMembershipEntry.QueryRun(filter)	
		If (result = 0) then
			ui_msg_box("Member Search", "No Member Found")
			'To "undo" the last query:
			Topparent.QueryRun(old_filter)
			notfoundflag = 1
 		end if
		
	case *any(srchstr, "(") .or. len(srchstr) = 10
		'this looks for phone numbers
		dim filter as c
'		ui_msg_box("phone", quote(srchstr))
		
		filter = "Homephone=" + Quote(srchstr) + " .or. Workphone=" + Quote(srchstr) + " .or. Cellphone=" + Quote(srchstr)
		result = :frmMembershipEntry.QueryRun(filter)	
		If (result = 0) then
			ui_msg_box("Member Search", "No Member Found")
			'To "undo" the last query:
			Topparent.QueryRun(old_filter)
			notfoundflag = 1
 		end if
		
	case Isnull(srchstr) = .f.
		'this looks for a single word entered, first or last
		dim filter as c
		dim qry as P
		dim tbl as P
		dim frm as P
		tbl = table.open("tblMemberInfo")
		filter = "left(First_name,len("+Quote(srchstr)+")) = "+Quote(srchstr) + " .or. left(Last_name,len("+Quote(srchstr)+")) = "+Quote(srchstr)
		query.filter = filter
		query.order = "Last_Name"
		qry = tbl.query_create()
		nrecs = qry.records_get()
		if nrecs > 1 then
			frm = form.viewqueried("frmMemberSearch", filter,"","dialog", "center", "center")
			frm.close()
			If varSelMember <> -1 then
				filter = "Member_Id=" + varSelMember
				result = :frmMembershipEntry.QueryRun(filter)							
				If (result = 0) then
					ui_msg_box("Member Search", "No Member Found")
					'To "undo" the last query:
					Topparent.QueryRun(old_filter)
					notfoundflag = 1										
		 		end if
			end if
		else
			result = :frmMembershipEntry.QueryRun(filter)			
			If (result = 0) then
				ui_msg_box("Member Search", "No Member Found")
				'To "undo" the last query:
				Topparent.QueryRun(old_filter)
				notfoundflag = 1														
	 		end if
		end if
		query.filter = ""
		qry.drop()
		tbl.close()
	case else
		ui_msg_box("Member Search", "Not sure what you're looking for.  Contact David")
End Select	
	
		
'now reset the query and allow access to all records
filter = ""	
':frmMembershipEntry.QueryRun(filter)			
parent.show_all()
if (notfoundflag = 1) then
	end 
end if

I search on Membership #, first and last, first or last, any part of first or last etc.

If more than one match is found I pop up a form and let the user select what they want. I hope the code is ok... if not I'll try to pull it out and put it in a sample. I'm sure I found the basis of the code here.

David

[Stan Mathews]

Re: What is it called a search like this

In general it is called a progressive find. Have you noticed the Find Customer button on the AlphaSports Customer form?

[peterth]

Re: What is it called a search like this

Yes, It's the same Find Customer button I will try with my local langquage.

Thanks
Peter-th

[peterth]

Re: What is it called a search like this

David

Thank you for your sample code too. I will try to adap in my application too.

Thanks
Peter-th

[Mike Wilson]

Re: What is it called a search like this

Here are two other examples of progressive lookups, one uses action scripting List View Control, and the other a much simpler modified key list build.

[peterth]

Re: What is it called a search like this

Mike,

It looks good and easy to learn with more details

Thank you for your sample very much
Peter-th

[peterth]

Re: What is it called a search like this

Mike,

I can do and modity your sample but I have the problem with font to display.
It shows ???? in the name list. Usually I can use my lanquage on form to key in data and print report with no problem. How and where I can change font to effect the default browse? I try in System Font but it stil displays ???

Is it possible, let it display on the custom browse

Thanks
Peter-th

[Mike Wilson]

Re: What is it called a search like this

Can you take a screen shot of what you are referring to and post it on the message board. I am not exactly certain where it is that you are seeing this problem. Also, if the problem is within the progressive lookup function, which one are you using (hopefully the key list build one ). Thanks.

[peterth]

Re: What is it called a search like this

I use first button style
I can display same with your sample but it cannot display my local font corretly. However I can use form,report with my local font (my langquage with no problem)
Please see attached file

I think if I can edit property like the embeded browse I can display font correctly

Thanks
Peter-th

[peterth]

Re: What is it called a search like this

Mike,

I also try the prog_lookup2()
and I have the result as the attached pictures

What I miss?

Thanks
Peter-th

[Mike Wilson]

Re: What is it called a search like this

Progressive lookup2 requires changes to code to point to your table and fields that you must have missed. It is a bit difficult. It is easiest if you have a field in the table that is a single calculated field that concatenates the lastname, firstname middlename and that is the field that you search on. I do not know why the names are showing up as ??. If you give me the names of your table and fields I can try to assure that you have the code correct. If you can zip a copy of the database and post it, that would be best. I also have another version I generated for myself this morning below. The red are the tables and fields you would need to change for you. This searches within values so it finds 'warewolf' when the word 'wolf' is typed in. This is accomplished by the astericks in lime color (line 39). Remove that and it will narrow by first letter onward. This works well for a list of 1000. I don't know the performance for many more records.

' generate dialog
	dim vfind_text as C
	dim vLdocs as C=""
	dim vLdocs_show as C=""
	dim vresult as C
	dim dlg_body as C
	dim dlg_code as C
 	dim insearch as C
	
	dlg_body=<<%dlg%
	{background=#180,185,185}{font=arial,10,b}{frame=1,0}
	{xmargin=3,3}{ymargin=1,1}{justify=center}{startup=init}
	{region}
	{lf};
	[.50vfind_text!newd_*];
	{endregion};
	{region}
	{lf};
	[.75,25vChoice^#vLdocs_show];
	{lf};
	{endregion};
	{lf};
	<15OK> <15Cancel>
	%dlg%
	
	dlg_code=<<%code%
	
	IF a_dlg_button = "init" then
		a_dlg_button = ""
		' start with a full dialog
			vLdocs=table.external_record_content_get("[COLOR="Red"]main[/COLOR]","[COLOR="red"]doc_name[/COLOR]","[COLOR="red"]doc_name[/COLOR]","unique_key_value()")
			vLdocs_show=vLdocs
	END IF
	
	IF left( a_dlg_button, 5 ) = "newd_"
	 IF a_dlg_button = "newd_change"
		insearch=[COLOR="Lime"]"*"[/COLOR]+alltrim(vFind_text)+"*"
		vLdocs_show=filter_string_smatch(vLdocs,insearch,crlf())	 	
	 END IF
	 a_dlg_button = ""
	END IF
	%code%
	
	vresult=ui_dlg_box("Document Search",dlg_body,dlg_code)
	if vresult="cancel"
		end
	end if

' get the record
	vfilter="[COLOR="Red"]doc_name[/COLOR]="+quote(vchoice)
	topparent:tables:[COLOR="red"]main[/COLOR].filter_expression =  vfilter
	topparent:tables:[COLOR="red"]main[/COLOR].order_expression = ""
	topparent:tables:[COLOR="red"]main[/COLOR].query()

[peterth]

Re: What is it called a search like this

Mike,

I try your new code but it displays not correctly.
I have table name g1member.dbf and field name is Name and Surname
so I change it in your red is ("g1member","name","surname","unique_key_value()")
vLdocs_show=vLdocs


and

' get the record
vfilter="name="+quote(vchoice)
topparent:tables:g1member.filter_expression = vfilter
topparent:tables:g1member.order_expression = ""
topparent:tables:g1member.query()

right?
I replace "main" to "g1member" (my table name is g1member)
I replace "doc_name" to "name" ( a field in g1member)
I replace "doc_name" to "surname" ( a field in g1member)

When I run it the result is not correct
What I do wrong?

I have a table name g1member and what to display fields (member_id , Name, Surname)
what I should replace in your red?
and what I should replace if I try prog_lookup2?

I want to post my application but it's all my local langquate, let I cut it down and change form to english.

Thanks
Peter-th

[Mike Wilson]

Re: What is it called a search like this

Here is an example with your table names and field names. I added the calculated field alphaname to the table for use in the search.

[peterth]

Re: What is it called a search like this

Mike,

With new sample I try it and it works great now

Thanks
Peter-th