A Relational Operation Example

by Dr. Peter Wayne

At the June 1998 Learn Alpha Five course, Rick, one of the course attendees, presented an interesting problem. His company, an intermediary in the trucking business, maintains a database of trucking companies. If you need a shipment brought from, say, Los Angeles to New York, you can call Rick's company and they will arrange shipping with a shipper that has a truck of the right size that is going to the same region.
Rick's first concern is to match a client's needs - region and truck size - with their database of vendors (trucking companies). Rick was stymied by the problem, because

1. his company has a list of 16 regions
2. he has more than 30 different types of trucks that he tracks
3. frequently more than 1 region or 1 truck will be suitable for a shipment. For example, Rick may need to send a shipment to New York. A shipper with the right size truck may be sending a truck to New England, and a small detour to New York may be the fastest and cheapest way to get the shipment there. Or a shipment that fits on a 48 foot truckbed will also fit on a larger truck.

So what Rick needs is not simply to match clients with vendors but he needs to run 2 queries:

1. look through his vendors for matching regions, and then
2. look through his vendors for matching equipment (trucks).

Then he has to combine the results of the 2 queries to find those vendors that are found in both queries. I want to thank Finian Lennon, a New York-area Alpha Five developer and my co-faculty for the course, for pointing out that a relational intersection operation is exactly what is needed to solve the problem. Using Finian's insight as a guide, I was able to come up with the solution prsented here.
Let's start with the data structures. I decided I needed 5 tables:

• a region table, listing each possible region
• a vendor table, listing each vendor
• an equipment table, listing the types of trucks available
• a table of vendors and their equipment, the vendor_equipment table
• a table of vendors and the regions they service, the vendor_regions table

The structure of each table follows:

The vendor_regions table ties together vendors and regions:

Now for the equipment and vendor_equipment tables:

Set up the form

I thought that for ease of use it would be best for the user to select her choices from drop-down lists on the form. I created a set, VendorToEquipment.set, that is a one-to-many link from vendors.dbf to vendor_equipment.dbf. I then created a blank form based on that set, and created 5 form-level variables, 1 for each of 3 potential regions and 1 for each of 2 possible types of equipment. The variables are simply R1, R2, R3, equip1 and equip2, and they are all layout-level variables on the form:

Figure 1. Declaring layout-level form variables.

I added a 6th layout-level variable, matching_vendors, to hold and display the vendors that match the search criteria.
The form then has fields for all the variables, plus a few buttons for which we'll write scripts shortly:
Figure 2. Placing the fields on the form.

The field objects are defined as drop down lists in the field's Properties under Setup:

Figure 3. A field is defined as a drop down list under Properties, Setup.

A drop down list has associated with it a set of choices. The number of choices can be programmatically set through its choice.list.redim() method. For example, if we want to set the number of choices in field R1 to 7, we write
R1.choice.list.redim(7)
Each individual list choice has both a text and a value property. In most cases, they are the same, although you may wish to set the displayed text to something other than that to which the choice will translate. The text property of the 3rd list item is set with the following syntax:
R1.choice.list[3].text="Mid Atlantic"
The code to initialize all the variables is contained in the form's OnInit event:

''XBasic
dim regiontbl as p
dim ix as p
dim nrecs as n
dim i as n

regiontbl=table.open("region.dbf",file_ro_shared)
ix=regiontbl.index_primary_put()
nrecs=ix.records_get()
regiontbl.fetch_first()
R1.choice.list.redim(nrecs)
R2.choice.list.redim(nrecs)
R3.choice.list.redim(nrecs)
for i=1 to nrecs

R1.choice.list[i].text=regiontbl.region_id
R1.choice.list[i].value=regiontbl.region_id

R2.choice.list[i].text=regiontbl.region_id
R2.choice.list[i].value=regiontbl.region_id

R3.choice.list[i].text=regiontbl.region_id
R3.choice.list[i].value=regiontbl.region_id

regiontbl.fetch_next()

next
regiontbl.close()

dim equipment as p

equipment=table.open("equipment",file_ro_shared)
ix=equipment.index_primary_put()
nrecs=ix.records_get()
equipment.fetch_first()
equip1.choice.list.redim(nrecs)
equip2.choice.list.redim(nrecs)
for i=1 to nrecs

equip1.choice.list[i].text=equipment.equipment
equip1.choice.list[i].value=equipment.equipment

equip2.choice.list[i].text=equipment.equipment
equip2.choice.list[i].value=equipment.equipment

equipment.fetch_next()

next
equipment.close()

Script 1. Form's OnInit event
The form will now load with blank selection fields, but pressing the drop down arrows on each drop down field will display the list of available choices:

Figure 4. The drop down lists can be used to choose regions and equipment.

The search through the tables and the relational intersection are performed in the OnPush script for the button labeled "Search:"

dim vendors as p
dim result as p
dim resultname as c
dim tries as n

resultname="result"+right(str(toseconds(time())),3)+".dbf"
if file.exists(resultname) then

sleep(3) ' give another user a chance to finish

end if

vendors=table.open("vendor_regions")

intersect.t_db="vendor_equipment.dbf"
intersect.o_file=resultname
intersect.m_key="vendor_id"
intersect.t_key="vendor_id"

filter="region_id='"+R1.text+"'"
if trim(R2.text)>"" then

filter=filter+" .or. region_id='"+R2.text+"'"

end if
if trim(R3.text)>"" then

filter=filter+" .or. region_id='"+R3.text+"'"

end if
intersect.m_filter=filter

filter="equipment='"+equip1.text+"'"
if trim(equip2.text)>"" then

filter=filter+" .or. equipment='"+equip2.text+"'"

end if
intersect.t_filter=filter
intersect.delete_o_dd=.t.
intersect.m_count=1
intersect.m_field1="vendor_id"
vendors.intersect()
vendors.close()

tries=0
on error goto result_in_use
result=table.open(a_db_current_path+chr(92)+resultname,file_rw_exclusive)
on error goto 0
query.filter=".t."
query.order="vendor_id"
query.options="U"
ix=result.query_create()
count=ix.records_get()
if count>0 then

matching_vendors.choice.list.redim(count)
result.fetch_first()
i=1

while .not. result.fetch_eof()

' note - a long line follows, although it may break
'on your browser into 3 lines
matching_vendors.choice.list[i].text=result.vendor_id + " " + lookup("vendors","vendor_id='"+result.vendor_id+"'","telephone")
'end of long line
result.fetch_next(1)
i=i+1
end while

else

matching_vendors.choice.list.redim(1)
matching_vendors.choice.list[1].text="(no matches)"

end if
result.close()
table.erase(a_db_current_path+chr(92)+resultname,.t.)
end

result_in_use:
on error goto 0
tries=tries+1
if tries<3 then

sleep(1)
resume 0

end if
' else
reply=ui_msg_box(Resultname+" in use.","Should I keep trying?",UI_RETRY_CANCEL+UI_ATTENTION_SYMBOL)
if reply=UI_RETRY_SELECTED then

resume 0

else

end

end if
end

Script 2. OnPush script for the Search button

This is a very complicated script, but it can be broken down into segments. In the first segment, a relational intersect is programmed between the vendor_regions table and the vendor_equipment table. The parameters of the intersect are obtained from the drop-down lists on the form.
The output of the intersect is written out to a temporary table whose name is "result" plus the last 3 characters in toseconds(totime()). After the result table is written, a query is run to obtain unique vendor_ids, because the result table will have duplicate entries.
A search is performed by making choices from the drop down lists:

Figure 5. Picking options from the drop down lists on the form.
The result table from these choices has several repeating entries. This is a consequence of the relational intersect:

Fig. 6. The result table has repeating entries.

The final portion of the script reads through the result table, using a query which obtains unique values of the vendor_id field. The results of the query are then scanned into the matching_vendors listbox and displayed on the form. The full database search with its results can be seen together on the form:

Fig. 7. The 2 matching vendors are shown on the form.
The form can be cleared by pressing the "Clear Search" button. The OnPush script for the "Clear Search" button is:

R1.text=" "
r2.text=" "
R3.text=" "
equip1.text=" "
equip2.text=" "

size=matching_vendors.choice.list.size()
matching_vendors.choice.list.delete(1,size)

Script 3. OnPush script for the Clear Search button.
Once again, I would like to thank Finian Lennon for having the insight to see that a relational intersect operation is just the ticket to solve Rick's problem. While working on the problem, we also got to play a little with listboxes and drop down lists, two form objects that many developers avoid out of ignorance or apprehension.

6/30/98 - pkw

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

Return to home