Sesame Library | Programmed Retrieves

What is a Programmed Retrieve?

Q&As database search options are extensive. When these run out you can always use a programmed Retrieve Spec. This more recent capability programmable Retrieves, added in Q&A version 4.0is not used nearly as much as it ought to be. The good news is that Sesame supports Retrieve Spec programming just like Q&A. There are some relatively minor differences between the two, however. I want to tell you what these are, and in the process show you how you can use Retrieve Spec programming to good effect.

Retrieve Programming: two methods

Retrieve programming must be enclosed by what are called French braces { }. They contain the programming expression you want Q&A to evaluate, which will determine which record or records Q&A returns to your desktop, or to a mail merge, Mass Update, Report or other task that includes record selection as part of it. Your expression can take one of two forms: Either a value, such as a number, a date or a text string, or a Boolean expression that evaluates to either True or False. (Q&A's "Yes/No" fields are Boolean). As an example of a value expression, lets take a search for dates falling due within the next seven days, where the due date will always be in the future. At the Retrieve Spec, you could type the following into the Due Date or similarly named field, as long as its a proper date type field:

< { @Date + 7 }

Notice that the less-than operator (<) is outside the main expression enclosed by the French braces. In this case, the expression is evaluated by Q&A and returns a date seven days in the future from the current date. When coupled with the <, this tells Q&A to find all the records where the Due Date field contains a date that is earlier than the date that is seven days from today. Another example would be to find people with four-character surnames:

Surname: ={@Len(Surname) = 4}

This would produce records containing surnames such as Bird and Shaw.

This is the first way to use Retrieve programming. You could call it implied comparison, since the expression directly relates to the contents of the Retrieve Spec field in which youve typed it.

The second methodcall it truth-of-expressionis independent of the field into which it is typed. It can be considered universal to all the fields on the form. It starts with the French braces:

Surname: {@Len(Surname) = 4}

When the expression inside the French braces is evaluated and then tested against a particular record, it will result in either a True or a False. If true, the record is included; if false it is not. A programming expression used in this way does not relate to the field it happens to be put in. Place it in a different field and the result will be the same.

About Sesame

Q&A and Sesame support both these methods. But there is a difference you need to know.

We all know that Q&A is a very forgiving product. You can put Dont Know in a date field. You can name a field 150 and have a programming statement that says:

Total = #200 + 150

Q&A will use the value thats in field 150 rather than add the number 150. Thats neat. But its extremely difficult to replicate in a modern program that has a wider scope than Q&A.

Q&As forgiving nature extends to Retrieve Spec programming as well. Youve seen these two methods. If you use the first method, your programming expression produces a value. If you want that value to be compared to a field, you can put it in that field and put an equal sign in front of the French braces. In Q&A, if you omit the equal sign, Q&A will handle it anyway. Likewise if you do the opposite, and put an equal sign in front of the French braces when using method two, Q&A accommodates these errors. Sesame doesn't.

The following are valid in Sesame:

Rating: ={Key - 14}

Any Field: {Rating = Key - 14}

However, if you swap the equal sign to the second example, it is incorrect syntax and wont work in Sesame, even though this improper practice is tolerated in Q&A.

One thing you wont be able to carry forward to Sesame is the use of logical field numbers in programming. You have to use field names, plain and simple. Logical field numbers are actually a carryover from Q&A 3.0, where you couldnt use field names in programming and were forced to assign a logical field number to any field you wanted to reference in programming.

Again, this can be justified by stating that Q&A is extravagantly accommodating regarding such numbering. The numbers used are just for the Program Spec. So, you can have a numbering scheme in the Program Spec, another in the Navigation Spec, another in each of many saved Mass Update Specs, more yet in saved Retrieve Specs, and so on.

Using field names is ultimately just simpler

Why would you want Retrieve Spec programming?

Being able to use Retrieval expressions can come in handy in various situations. They come into their own when you use them as Saved Retrieves. This can be as standalone Retrieve Specs, or as the Retrieve Spec saved as part of Reports or Print Specs.

Compare to current date

As you saw in the first example, you can compare a date field to the current date. This is invaluable for finding records for the next week, for the last 14 days, for next month, for this month, for the past 90 days, and so forth. You dont have to type in a new range of dates every time you perform the task, such as:

Date Due: >=8/14/2003..<= 8/21/2003

or simply this:

Date Due: 8/14/2003..8/21/2003

Here are a few more examples of using Retrieve Spec expressions with date ranges:

{JobDate >= @Date - 7 And JobDate <=@Date + 7}

(JobDate 1 week either side of today.)

{@Month(JobDate) = @Month(@Date) And @Yr(JobDate) = @Yr(@Date)}

(JobDate this month.)

{@Replace(DOB, @Yr(DOB), @Yr(@Date)) >= @Date and @Replace(DOB, @Yr(DOB), @Yr(@Date)) < @Date + 7}

(Birthday (DOB) within the next week.)

Searching for non-standard characters

Retrieve Spec programming can help you find characters that shouldn't be in a field. For example, to search for ñ in a field name Reading1:

{@Instr(Reading1, @Chr(241)) > 0}

Complex string manipulation

To find records where the third-from-last character in the Item field is a backslash:

{@Mid(Item, @Len(Item)-2, 1) = \}

To find field values of a certain length

Short, or especially, long values, which might cause problems with mailing labels, merge documents or exports:

{@Len(Comments) > 255}

Although the @Len function is commonly used to determine the length of a text string, it can also be used though to find the length of a number field. An integer from 0 to 9 will have a length of 1, an integer from 10 to 99 will have a length of 2, and so forth. A definite case of a solution in search of a problem!

Mixed search logic

Where Retrieve Spec programming really comes into its own, is where you have mixed And and Or conditions across fields. Such retrieves are impossible to do without programming.

The best example is the Chase Creditors situation. You want to print out a list of, or send letters to, all those people who are overdue on their payments. Who do you the send the letters to?

You want those who are overdue a long time who owe a moderate amount, and you also want those who owe you a lot even if they are less severely overdue. In Retrieve Spec terms you want:

Overdue moderate time AND amount due is large

or...

Overdue long time AND amount due is moderate

You need criteria in two fields, and you need a mixed And and Or across them. Assuming theres a Date Paid which is blank if not paid, and fields for Invoice Date and Amount, the programmed Retrieve would look like this:

Date Paid:

Any Field: { (Invoice Date < @Date-30 AND Amount > 5000 ) OR (Invoice Date < @Date - 60 AND Amount > 2000 ) }

To repeatyou create and then save these Retrieve Spec programs, either with Reports or as standalone Saved Retrieve Specs. The example above will produce different records each day because the dates are being compared to the current date, no matter what date that is.

Furthermore, all these programmed Retrievesif they dont start with an equals sign, and if they use Field Nameswill not only translate into Sesame from your Q&A database, but will work in Sesame to give you the same consistent results.

New simple case-sensitive search support

Q&A offers you a way to perform a case-sensitive search: but it's pretty clumsy and hardly anyone knows it. Sesame does very much better. In Sesame, all you have to do is to prefix your retrieve parameter with the ~ symbol (called a tilde), and the whole text string is searched case sensitive. So, to find "BBC" in lower case:

Company: ~bbc

Youll get a match on bbc but not BBC or Bbc

Note that this replaces Q&As seldom-used Soundex (sounds like) feature. Sesame was not going to support Soundex anyway, and frankly it didn't work very well. There's still one guy in North Dakota who uses it, but he's been lookin' kinda unwell these past couple of summers.

This article by Alec Mulvey is adapted from one published in the August 2003 issue of The Quick Answer.