Enhanced Queries in Alpha Five v. 4

by Dr. Peter Wayne

Alpha Five allows several ways of constructing multitable queries. In versions 1 through 3, you can create queries on 2 tables

• if the two tables are linked one-to-one, or
• if you place the child table's query filter in the detail filter of a report

We shall start by creating 2 simple tables to illustrate how multitable queries are constructed. The 2 tables, teachers and pupils, are related one-to-many on the teacher_number field.

The two tables are linked one-to-many in a set, schools.set:

Figure 1. The 2 tables are linked one-to-many.

Make the following entries into the two tables:

Now, let's suppose we want to find in whose class is Ann. In versions 1 through 3 of Alpha Five, there are 2 ways to perform this query:

• through a report, or
• by inverting the set

Using a Report

We can perform a query through a report. Create a Quick Report for the school set, and create a detail filter on the student_name field of pupils:

Figure 2. Detail filter of report

The quick report shows that only Alice satisfies the filter:

Figure 3. The output of the Quick Report.

Using an Inverted Set

Now, in v3 and prior versions of Alpha Five, we could only create a multitable query if all the tables are related one-to-one. We can make a one-to-one set out of any one-to-many set by reversing the orders of the tables, making the former child tables into the parents. We'll create a set, invertedSchool, from pupils to teachers, linked one-to-one on the same field of teacher_number.

Figure 4. InvertedSchool.set

Using this inverted set, we can create a query using fields from either table. To find the teacher who has Ann in her class, we only have to do query this set on the student_name field in pupils and output the Name field in teachers:

Figure 5. Query By Form for InvertedSchool.set.

Notice there is no browse object in this default form for the set. That's because the only linkage is a one-to-one linkage. To Alpha Five, all the fields in the tables appear like one large virtual table. In Query By Table view, all the fields from both tables are available for constructing the query, "Show me all teachers whose names have a "B" in them who are teaching female students":

Figure 6. All the fields from both tables are available in the Query By Table card.

The Flattenquery() function

We have seen how, in Alpha Five version 3 and earlier, we can construct a set-level or multitable query either through a report or by inverting the set. Both of these methods have disadvantages: either you create a report that you may not need, or you create a new set for the sole purpose of running this query. Alpha Five version 4 offers a new option, the flattenquery() function, which lets you perform a multitable query without the necessity of creating a new set or a new report.

The flattenquery() function creates a temporary composite table out of the parent and child tables in the set, and then performs the query on the composite table (I don't know if this is the actual way in which flattenquery() works, but it helps to think about it in this way!). The composite table is similar to a set-level browse: the parent fields are repeated once for every record in the child table:

Figure 7. A set-level browse

Notice that the set has been "flattened out" so that the distinction between parent and child records is lost. When a flattened query is run, this flattening is preserved, so that the query can reference fields from both tables. The syntax of the flattenquery() function is:
query.filter="flattenquery(query_filter_spanning_set)"

Interactively, you can perform a flattened query through Query by Form. You have to explicitly create the query filter by choosing to Edit Filter Expression after choosing Query by Form:

Figure 8. Query by Form lets you choose to Edit Filter Expression

Once you have chosen to edit the filter expression, you enter flattenquery(pupils->Student_name="ann") in the Filter Expression window:

Figure 9. The flattenquery() expression in the Filter Expression window.

Figure 8. Using FlattenQuery() in the Expression Builder.

The syntax pupils->Student_name identifies the table and the field. If the query references any other fields other than those in the parent table, then you must identify the field with the table prefix to Alpha Five.

Running the query produces this result:

Figure 10. Result of flattened query.

Notice that the flattened query only displays records from the parent table, even though the record selection was based on fields from the child table. If you want to see fields from the child table as well as the parent table, you must use an inverted set or a report.

Coding a flattenquery() button

Of course, you are not limited to interactive uses of the flattenquery() function. Indeed, it is probably most useful in Xbasic scripts, and what do you know - I just happen to have a few here to illustrate the point! Let's create the default form to display the school.set and add a button to look for Ann. The Xbasic for the Query for Ann button is straightforward:

teacher=table.current(1)
query.filter="flattenquery(pupils->student_name='ann')"
query.order="teacher_number"
teacher.query_create()
parentform.resynch()

Script 1. Query for Ann button script.

By now, you should see that the button query is just a simple translation of the interactive query. We can make a slightly more complicated query, by asking for all teachers whose names contain a "b" and who are teaching boys. The script for this query is:

teacher=table.current(1)
query.filter="flattenquery('b'$name .and. pupils->sex='m')"
query.order="teacher_number"
parent.resynch()

Script 2. A more complex multitable query.

I have one warning for you. When you perform this query, the number of records returned may not be what you think. Let's amend the script to show us the number of records returned:

teacher=table.current(1)
query.filter="flattenquery('b'$name .and. pupils->sex='m')"
query.order="teacher_number"
ix=teacher.query_create()
parent.resynch()
nrecs=ix.records_get()
ui_msg_box("You found",ltrim(str(nrecs))+" matches")

Script 3. Same script as #2 but now giving the number of records retrieved.
When we run this script, Alpha Five reports 2 records found:

Figure 11. Alpha Five finds 2 records
But Alpha Five only shows one record on the form:

Figure 12. Only 1 record shows on the form
So what gives? Remember the idea of the composite record that we showed in Figure 7? Alpha Five is returning the number of composite records that match the query: in this case, 2 records. We can access the individual fields of the composite record by fetching through the composite table one record at a time:

teacher=table.current(1)
query.filter="flattenquery('b'$name .and. pupils->sex='m')"
query.order="teacher_number"
ix=teacher.query_create()
parent.resynch()
nrecs=ix.records_get()
dim pupil as p
dim i as n
dim students[nrecs] as c

pupil=table.current(2)
teacher.fetch_first()
for i=1 to nrecs
	students[i]=pupil.student_name
	teacher.fetch_next()	 
next
chosen=ui_get_list_array("Choose a student",1,"students")

Script 4. Fetching through the child records in the composite table

Script 4 produces this output:

Figure 13. Bob's boys

In conclusion...

We have seen how it has always been possible to produce multitable queries in Alpha Five, through report detail filters and through set inversion. Alpha Five version 4 adds a new flattenquery() function which makes it possible to perform multitable form-level queries without creating an inverted set. Maybe you can think of some devilishly clever use for the flattenquery() function. If so, write it up and send it to me and I'll post it here!

revised 10/14/98 - pkw

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

Return to home