Sesame Library | New Search Options in Sesame

Anyone who knows me knows that Ive got a thing about database searching. I believe everyone should learn all search techniques thoroughly. Only by being proficient at finding the records you need can you build efficient databases. Otherwise, you are forced to compartmentalise your datasplitting it up artificiallyrather than keeping it all together and picking out just the parts you need at any given time. Compartmentalising your data costs you in terms of overall efficiency.

Adding Smart fields to your database

Take a company that creates a new sales database for every financial year. Okayits easy to understand why you might do that. But what happens when you want to look at an order from last year or the year before? You find yourself searching one database, then another. Likewise, why delete customers who are no longer active? Or products that have been discontinued? Why not keep them in the database so you can refer to them or resurrect them if needed. Whats the difference between the former simplistic approach and what Im suggesting? The difference is that you will need to add a field or two to store information such as Current or Activeor better yetStatus: Active or Lapsed, and use retrieval parameters in your reports, Print Specs and the like to include only the records you need. When you split up your data, you cannot perform analyses across financial years. Whats more, your old databases cannot benefit from the snazzy new reports, programming, and so forth that you add to the latest version.

Search options beyond Q&A

So much for the philosophy. All Im saying is that youll reap greater benefits by having fields that will enable you to select the data you need from the much larger data setthat is, the entire database. The only additional requirement is that you need to be able to construct a retrieve that will give you just the records you want. In Q&A you have a Retrieve Spec and a host of search optionssome easy to use, some more esotericthat allow you to perform some pretty complex searches. Furthermore, Q&A has Retrieve Spec programming for when a standard search just cant do the job. What does Sesame offer? Well, a whole lot. You have support for all of Q&As standard search patterns with just one exception: soundexsounds likesearches will not be supported in Sesame version 1.0. Chances are youve never used a soundex search anyway and you probably didnt even know it was available in Q&A. (See the Users Guide, p. 3-57.) In addition, Sesame has a whole raft of new search/retrieval options that allow you to refine any result set. This will be a new concept to those who know only Q&A, so let me elaborate.

Whats a result set?

When you perform most actions in Q&A the sequence is 1) Specify Action, 2) Specify Records to Include, 3) Perform Action. For example, after specifying a Report, you get the Retrieve Spec where you specify which records to include. Likewise with a Mass Updateyou select Mass Update from the File menu and the next thing you see is the Retrieve Spec for this single Mass Update operation. File / Remove, File / Postall these operations first present you with a blank Retrieve Spec. If the set of records you want to include is a complicated one, you can easily find yourself printing or Mass Updating the wrong records, or being unable to get the exact result set you need and abandoning the procedure. You might then spend an hour or more rehearsing your retrieval parameters before taking another run at it. Sesame works differently. The order in Sesame is 1) Specify Records to Include, 2) Specify Action, 3) Perform Action. This allows you to do whatever you need to do to get that set of records right before performing an action with them. Its an important change. It might feel a bit odd at first, but I believe you will soon grow to prefer it. I certainly do. That set of records in Sesame is called the result set. Q&A deals in result sets, tootheyre just not called that. But its the same thingthe stack of records you get when you press F10 at a regular Search/Update Retrieve Spec. The one case where Q&A allows you to search like Sesame (that is, get some records first and then decide what to do with them) is when you opt to print a group of records by pressing Ctrl-F2 after retrieving them. This prints the current stack of records, as it is called in Q&A.

New tools in Sesame

To perform a normal search in Sesame, you fill in the Retrieve Spec just as you do in Q&A (all the familiar search patterns are valid, such as S.. for fields beginning with the letter S) then press F10. With Q&A, thats it. But in Sesame, what youre looking at is only the beginning. The result set can be further refined by further searches, until it meets your exact needs. Ill show you how. Imagine that you want to prepare a list of customers from your customer database. You have a number of ideas about the sort of people you want to include, but its rather complicated. Okay, you start with a simple search:

Town: London

When you run this search, you see the records for these customers71 of them. All very good. But while browsing through these records you can think of several other groups you want to include, so you press F7 to return to the Retrieve Spec and clear it. Its at this stage that Q&A runs out of steam. All you can do is another new search. You want to include people in Middlesex as well. (Middlesex is an urban county to the West of London which is wholly subsumed by London.) You enter the new criteria:

County: Middlesex

This time, though, you do not press F10 to run the searchthat would simply replace the record set. From Sesames Search Commands menu, you select Add to Current Results. You now see 84 records. Browsing through this record set you realise that not all the Middlesex customers are included. The reason for this is that Middlesex can be abbreviated as Middx. F7 again and change the criteria to:

County: Middx

And again, Add to Current Results brings the total to 91. Good. But what about the area to the north? Much of Hertfordshire is actually the London suburbs. So back to the Retrieve Spec via F7 or by clicking on the menu command and (not making the same mistake again, as Hertfordshire can be abbreviated to Herts):

County: Hert..

And again Add to Current Results. The total is now 102. Scanning through the record set this time shows that some of the Hertfordshire records are out in the Stevenage area. This is too far out. What these records have in common is that they have a Postcode starting with SG. So, changing the Retrieve Spec to:

County: Hert.. Postcode: SG..

Will find those records. This time, you want to remove them, so you use Sesames menu command, Remove from Current Results. (See under Search Commands in the screen shot below.)

This removes three such records, bringing the total to 99. You return to the Retrieve Spec again and clear it with F3, just like in Q&A. The trouble is that now youve forgotten where you are! No problem, you simply select the menu command, View Current Records. This allows you to just review the current state of play. You decide to view the records in Table View. Sesames Table View is much more versatile than its Q&A for DOS counterpart. (See New Table View Features in Sesame, February, 2002.) You arrange the columns as you like, and scanning down you see that there is a record for a customer in Hertfordshire who, judging from the Postcode, should really be in Essex. Closing Table View leaves you looking at this record in Form View. You want to remove this customernot from the database by deleting the record(!), but by removing it from the result set. You do this by clicking on Remove from Current Results, this time from the Record Commands menu which is available when you are viewing records in browse mode. (See screen shot below.)

This menu pertains to the single record showing at the moment. This processadding and removing recordscan be continued until you are happy with the results. What then? Well if you go to the Results Commands menu, you will see that you can perform all sorts of actions such as Mass Update, Export, Remove, Print, and so forth. Whatever action you choose will apply to the Current Record Setthe records you are looking at.

Changing the search logic

Two advanced search features in Q&A which people rarely take advantage of are the options to use OR across fields, and perform a negative search. This is because these options are well hidden. To access them, you have to press Ctrl-F7 at the Retrieve Spec. And to do that you have to know it exists, which many dont!

Select those records which >DO< DO NOT meet >ALL< ANY of the restrictions

The default search logic in both Q&A and Sesame is AND across fields. So, if you put London in the Town field and Mr in the Title field, you are seeking people who are both male and in London. Were it otherwise, you would retrieve all the Mrs along with all the Londonsa very much larger result set. Should you want to use this type of search, you change the setting to ...meet ANY of the restrictions. Sesames equivalent of this is found on its Search Options menu. (See screen shot below.)

The default is Meet ALL Restrictions. The alternative, Meet ANY Restriction, is the equivalent of Q&As option of the same name. You might like to think of it as OR across fieldsLondon OR Mr

Inverted searches

The other option at Q&As Ctrl-F7 search options boxSelect those records which DO NOT meet is rarely used. When used with the above option, it can be especially hard to work out just whats going on. Sesame offers an alternative thats much easier to understand. At the Retrieve Spec, you enter your search criteria, then select Inverted Search. You get the records other than the ones you would get had you simply run the normal search. Easy! So:

Town: London; Liverpool

Finds all the records for customers who are not in London or Liverpool.

Search in current results

This is a super Sesame feature youre going to love! Its a drill-down search. How many times have you repeatedly refined a search, adding criteria, but each time had to return to the Retrieve Spec to run the whole search again? Search in Current Results applies the new criteria you have entered to search the current result set, rather than the whole database again. As an example of where this would be very useful, lets say you wanted to retrieve all those customers who were in the city of London or the counties of Middlesex or Hertfordshire. So far so good, but you only want to include those where the Money field is greater than 300. In Q&A, you cant do this in one retrieve without using programming. Its the classic mixing AND and OR dilemma. But you can do it easily in Sesame. You could do the following. First search, with Search Options set to Meet ANY restriction (OR):

Town : London County: Mid..x ; Hert..

This retrieves all the records based on geography. Now you need to restrict the money. Its easy. Return to the Retrieve Spec, clear it, and enter this:

Money : >300

And this time select Search in Current Results. (See screen shot below.)

Universal search

Ive saved the best til last! All the above techniques illustrate something thats easier to do in Sesame than in Q&A. Heres one thats impossible to do in Q&A! It has long been a personal quest of mine to find a viable way to search all fields in a database for some word or phrase. I know its possible to design macros to do this, but thats lot of work for just one database. Its inflexible and hardly worth the effort. In fact, I have posed this challenge to your editor more than once, and we still dont have a solution! Well help is at hand. Sesame has a new search feature independent of all the othersits called Universal Search. This does just whats required: Sesame searches all fields for the given parameter. This is made even more flexible by allowing you to use wildcards. Suppose you dont know the name of a particular customer, but you think there is the word Wall in the address. At the Retrieve Spec you select Universal Search and in the special dialog box that pops up (see screen shot below) you enter WALL.

Click on Accept and you find only one recordsomeone by the name of Wall. You then realise that Sesame searched for any records where the whole word Wall was the complete entry in any field. (Like Q&A, Sesame does not use implied wildcards.) By repeating the search, this time using:

..wall..

You will find all the records where Wall occurs anywhere in any field. Im sure you wont have to think long and hard to see how this might be useful!

Alec Mulvey is a director of Lantica Software, LLC and also owns Keyword Software & Consultancy in Ascot, near London, England. Alec has been building Q&A applications and training clients for 12 years. Keyword Software is the UK distributor for the International English edition of Q&A. alec@keywordsoftware.com, http://www.keywordsoftware.com.