It's actually quite easy....... let Alpha Five write the query for you! With just a little bit of guidance, you will be writing query filters with the experts. You might ask why you would want to write your own query filters when, while viewing a form, Alpha allows you to perform a Query by Form, a Query by Table, run the Query Genie, or run a saved query. If you desire to use Xbasic, there will be many times when you will need to process a subset of records without going through a form or browse. A query is a way to find a certain group of records. It is made up of two parts; the query filter, and the sort order. The filter selects records that meet your definitions, while ignoring those that do not. The sort defines the way in which the records will be displayed.

Queries can be run against a single table, using fields from the table, a one-to-one set using fields from the parent and/or child tables, or a one-to-many set using fields from the parent and/or child tables, or any combination of the above. Alpha Five basically treats a one-to-one relationship with the parent as a single table. The difference being the way a reference is made to the one-to-one child field, you must use this syntax: "ChildTableName->FieldName". If you want to query at a one-to-many child level, you must use the flattenquery() function, which I'll address later in the article.

For these examples I am going to use the sample Invoice application that ships with Alpha Five. The Invoice application contains the invoice set (invoice.set) that we will be using. Open up this application and get to the control panel. Go to the Operations tab of the control panel. Right click on an open space in the Operations page. Select New. This new operation will be a query operation, so select 'Query records' from the list that appears. Now you need to select a table or set to query. Select INVOICE.SET. Alpha Five now asks you whether you want to use the Query genie, or go directly to the Query card. For these examples I will be using the query card. You can use either, depending on your preference. Your screen should look like this:


As you can see, only the parent table and a one-to-one child table show on the Query card. We can still use the one-to-many child tables in a query, which I'll show later in this article.
We will start with a query that contains two conditions and a sort order. Let's assume we want to get all invoices for the state of Massachusetts that fall between the dates of 01/01/1995 and 12/31/1995. And to make it more difficult, we also want the query to order the records by the customer's last name in descending order. Using Alpha Five's built in query card shown above it would now appear like this:


Before we run the query, we have to start the script recorder. Go to the View menu choice. Select Script Recorder. On the Script Recorder form, check the 'Recording' check box. Click on the Query card to change focus back to it, or, if the Script Recorder window is maximized, click on Window and select INVOICE.SET: Query (untitled). Now that we have told Alpha Five what we want in our query, we are ready to let the program create our first query filter. In the Icon Tool Bar, you will see a lighting bolt icon. Click on it to run the query. The default browse for the invoice set comes up and displays the records that matched our query definition. Close the browse and return to the Script Recorder. It should appears thus:


That is it, we have our first multi-field and multi-type query filter. Click on the Copy button on the Script Recorder to copy the Xbasic syntax into the clip board for pasting into your script. Of course all we really want is the query filter definition, which is:

query.filter="CUSTOMER->BILL_STATE_REGION=\"MA\".AND.BETWEEN(DATE,{01/01/1995},{12/31/1995})"

At this point, you might complain that this still isn't flexible enough. The query Alpha just created is based on static values. What if I want to base a query upon a variable. Well, query expert, help is just around the Alpha corner. Open up any script in design mode. Now go to the interactive tab of the script editor. Assume the variables, that will hold the beginning and ending dates of the query, will be named vBeginDate, and vEndDate. While at the interactive tab, enter the following:

Dim global vBeginDate as d Dim global vEndDate as d

Now just so we can play, set the variables in the interactive window as follows:

VBeginDate = {01/01/1995} VEndDate = {12/31/1995}

Your screen should look like this:


Make sure that you press the Enter key after each line in the interactive tab of the Code Editor. It is not until you press Enter that Alpha Five executes the line. That is all there is to it. Close the current query operation we were working on, and begin another new query operations using the same Invoice set. Now you will have the variables that you just defined available to you for your query. If we wanted to create the exact same query as we did earlier, but using the new variables instead of the static date values, the query card would be filled in like this:


You will notice that the condition for the date value now uses the between() function. For whatever reason the between operator ".." does not work with date variables in the Query card. Just use the between() function, or use the greater than (>), equals (=) and less than (<) operators and you'll be all set. Now with the script recorder open and recording, run the operation. Here is the syntax this query creates:


Please note that, in the picture above, the end of the expression is shown below the line at the right.  This was done to fit the picture on the page.  It will not show on your screen this way, but will instead show as one line going across the screen to the right.

Let's define a query using an 'or' condition. To find all records where the customer's state was either Massachusetts or New York, the query card would look like this:


This results in the following query filter syntax:

query.filter="(CUSTOMER->BILL_STATE_REGION=\"MA\".OR.
CUSTOMER->BILL_STATE_REGION=\"NY\")"

If we wanted to create a query where the 'or' condition applied to two different fields, it is not readily apparent how to do this. Assume we wanted to select all records where the customer's state was New York, OR records where the Sales_rep field equals "PRM". You might be tempted to enter the query card as follows:


This would not work as expected. It returns the following syntax:

query.filter="CUSTOMER->BILL_STATE_REGION=\"NY\".AND.SALES_REP=\"PRM\""

This query filter would return records where the state = "NY" AND Sales_rep = "PRM". To get the query we wanted, the query card should be filled in as follows:



This returns the following query filter syntax:

query.filter="(CUSTOMER->BILL_STATE_REGION=\"NY\".OR.(SALES_REP=\"PRN\"))"

For the final example I want to use what might be perceived as a difficult query filter to construct. But with Alpha Five doing the heavy lifting, it is a breeze. Assume this query should select only those records where an item purchased was over $10, and the customer's state is New York. For this query, we will have to write a short expression because, as you will notice, the Invoice_items table, a one-to-many child table, is not available in the Query card. To do this we will use the flattenquery() function. The expression will be as follows:

flattenquery(Customer->Bill_State_Region="NY".and.
invoice_items->price > 10)

Here is how it looks in the Query card:


And here is the query filter it returns:

query.filter = "(FLATTENQUERY(CUSTOMER->BILL_STATE_REGION=\"NY\".AND.
INVOICE_ITEMS->PRICE > 10))"

As you can see, in this case the Field/Expression is not filled in as the fields are specified in the condition expression. You could also fill in the Query card as shown below, to get the same results:


The Script recorder returns the following query filter for this definition:

query.filter = "CUSTOMER->BILL_STATE_REGION=\"NY\".AND.
(FLATTENQUERY(INVOICE_ITEMS->PRICE > 10))"

With Alpha Five version 5 about to be released, I should mention that this technique will be even easier to accomplish. Version 5's Query Operations cards are entirely different, but I think you'll like them better. You can also 'see' the Xbasic code without having to run the Script Recorder. Also, version Five adds several new type specific between() functions to make query filters even easier. Look at it this way, when you are old and gray, you can tell your grandchildren that you had to walk to kindergarten 10 miles each way through the snow barefooted, but what will really astound them is learning that you had to create query filters the 'hard' way, with Alpha Five version 4.5.

That's about it, quick, check your watch. How long did it take for you to become a "Query Filter Expert"?