Search for a Match on a Child Table

by Dr. Peter Wayne

Gary has a problem - he needs to search on a one-to-many set by the child record. He has an invoicing application and wants to search for an invoice by a line item number, display the invoice, and then set the focus to the asked-for line item. How can he do it?

Built-in Search Methods

There are 2 ways that Alpha Five lets you search on a child record:

1. with the flattenquery() function, and
2. by inverting the set, i.e., making the child table the parent in a new set with a 1:1 link.

The flattenquery() function doesn't display the child records, and inverting the set won't let Gary use his original invoicing form to edit the selected line item.

The Party Planner Application

For this article, I had to find an application that everyone could use to illustrate these ideas. The Party Planner application has a one-to-many link on between the invitations table and the guests table, and each guest has a unique guest_id. The Invitation/Guest Entry form has a browse of guests, with multiple guests for each invitation:

Figure 1. The Invitation/Guest Entry form

Notice that I added a button, Find guest, to the form. If you press the button, you can enter the id number of a guest and Alpha Five will search for the guest:

Figure 2. Pressing the Find guest button triggers this dialog box.

In searching for the guest, Alpha Five will have to find the matching invitation and then place focus on the line with the selected guest:

Figure 3. Alpha Five has now located guest #11 and set the focus to that guest.

As easy as 1, 2, 3...

We're going to break down the task of finding and displaying the guest into small logical steps:

1. Get the guest number from the user, in this example, guest 11.
2. Transform the guest number into a guest_id (in this application, guest_ids all begin with "G" and are 4 characters, e.g., G011 is guest 11).
3. Find which invitation includes guest 11.
4. Display the invitation that includes guest 11.
5. Move the focus to guest 11 in the displayed invitation.

Here we go:

guest=ui_get_number("Find by Guest number","Enter guest number:")
if guest="" then
	end 
end if

Step 1. Get the guest number from the user and quit if no guest is specified.

guest="G"+padl(guest,3,"0")

Step 2. Convert the guest number to a guest_id

guestlist=table.open("guests",file_ro_shared)
query.filter="guest_id='"+guest+"'"
query.order=""
query.options=""
qry=guestlist.query_create()
if qry.records_get()=0 then
	guestlist.close()
	end 
end if
invite_id=guestlist.invite_id
guestlist.close()

Step 3. Find which invitation includes the specified guest, and save that invitation number in the variable invite_id.

invitations=table.current(1)
invitations.index_primary_put("invite_id")
invitations.fetch_find(invite_id)
parent.resynch()

Step 4. Display the invitation that includes the chosen guest.

guests=table.current(2)
guests.fetch_first()
while .not. guests.fetch_eof()
	if guests.guest_id=guest then
		exit while 
	end if
	guests.fetch_next() 
end while
browse1.refresh()

Step 5. Move the focus to the chosen guest in the browse.

Putting it all together

Steps 1-5 are all part of one script:

guest=ui_get_number("Find by Guest number","Enter guest number:")
if guest="" then
	end 
end if
guest="G"+padl(guest,3,"0")
guestlist=table.open("guests",file_ro_shared)
query.filter="guest_id='"+guest+"'"
query.order=""
query.options=""
qry=guestlist.query_create()
if qry.records_get()=0 then
	guestlist.close()
	end 
end if
invite_id=guestlist.invite_id
guestlist.close()
ui_freeze(.t.)
invitations=table.current(1)
invitations.index_primary_put("invite_id")
invitations.fetch_find(invite_id)
parent.resynch()
guests=table.current(2)
guests.fetch_first()
while .not. guests.fetch_eof()
	if guests.guest_id=guest then
		exit while 
	end if
	guests.fetch_next() 
end while
browse1.refresh()
ui_freeze(.f.)

Script 1. The entire button script.

The only change I made in Script 1 was the addition of the lines, ui_freeze(.t.) and ui_freeze(.f.), which keep the display from flickering as Alpha Five resynchronizes first to the invitation and then to the guest.

6/12/99

Don't forget, we need your feedback to make this site better!

Return to home