inm logo
Corporate Website | Contact | Store | Support | Login  
     


Knowledgebase
View categorized listing of all common frequently asked questions.
Downloads
View our categorized library of downloads for all necessary manuals, software, etc.
 Article Options
Support Center » Knowledgebase » INM V12 Database for Director » Performing advanced Boolean searches
Support Page Banner
 Performing advanced Boolean searches
Product:Platform:Area:Version:
INM V12 Database for Director Windows, Macintosh Searching and sorting Versions 1.x-3.x

Summary

Two or more search criteria can be chained in a single query with INM V12 Database.

Example

mSetCriteria(gT, "DishType", "=", "soup")
mSetCriteria(gT, "AND", "TimeToPrepare", "<=", 20)
mSelect(gT)
--or:
mSetCriteria(gT, "Ingredients", "WordStarts", "celery")
mSetCriteria(gT, "OR", "Ingredients", "WordStarts", "pumpkin")
mSetCriteria(gT, "OR", "Ingredients", "WordStarts", "carrot")
mSetCriteria(gT, "OR", "Ingredients", "WordStarts", "eggs")
mSelect(gT)

You can also mix ANDs and ORs, as follows:
mSetCriteria(gT, "DishType", "=", "soup")
mSetCriteria(gT, "OR", "DishType", "=", "appetizer")
mSetCriteria(gT, "AND", "TimeToPrepare", "<=", 30)
mSelect(gT)

The precedent query finds all soups and appetizers that require less than 30 minutes of preparation time. This is not equivalent to:
mSetCriteria(gT, "TimeToPrepare", "<=", 30)
mSetCriteria(gT, "AND", "DishType", "=", "soup")
mSetCriteria(gT, "OR", "DishType", "=", "appetizer")
mSelect(gT)

The precedent query finds all soups that require less than 30 minutes of preparation time and all appetizers regardless of the time required to prepare them. Thus, the order in which criteria are expressed to INM V12 Database is important.

However, INM V12 Database cannot handle four criteria or more with alternating ANDs and ORs. For example, the query "(Dish is soup or appetizer) and (Main Ingredient is celery or eggplant)" cannot be directly expressed to INM V12 Database. With a little bit of lingo knowledge, you will easily find some workaround to get rid of this problem. Here are three possible solutions:

Solution 1: Merging Selections
This technique requires to act on two V12table instances of the same table and to merge the resulting selections.

Define a separate table instance of the same V12 table for each set of query your are performing. For example:
set gT1 = new (Xtra "v12Table, mgetRef(gBD), "Recipes")
set gT2 = new (Xtra "v12Table, mgetRef(gBD), "Recipes")

Run your segments of query on each table instance:
-- first on gT1
mSetCriteria(gT1, "DishType", "=", "soup")
mSetCriteria(gT1, "OR", "DishType", "=", "appetizer")
mOrderBy(gT1, "RecipeID") -- sort by a uniquely indexed field
mSelect(gT1)
-- then on gT2
mSetCriteria(gT2, "ingredient", "WordEquals", "celery")
mSetCriteria(gT2, "OR", "ingredient", "WordEquals", "eggplant")
mOrderBy(gT2, "RecipeID") -- sort by the same field as gT1 above
mSelect(gT2)

You end up with gT1 and gT2 referencing two separate selections, both ordered by RecipeID. You can run a small Lingo loop to intersect them:
set field "result" = EMPTY      -- Dir member. Can also be a Lingo list
repeat while not V12Error()  --  by "Next" on last rec of gT1 or gT2
set s1 = (mGetField(gT1, "RecipeID")
set s2 = (mGetField(gT2, "RecipeID")
    if (s1 = s2) then
        put mGetField(gT1, "RecipeName")&RETURN after field "result"
        mGoNext(gT1)
        mGoNext(gT2)
      else if (s1 < s2) then
          mGoNext(gT1)
      else
          mGoNext(gT2)
    end if
end repeat

This Lingo loop performs an AND (intersection) between two selections. It can easily be modified to perform an OR (union). If you need to run your query with 3, 4, 5,... sets of criteria, just modify the above Lingo loop to operate on 3, 4, 5,... V12table instances.

Solution 2: Marking records
This technique may be faster if the number of records satisfying the search criteria is large, but it works only if your database is on a writeable volume (ie, not on CD-ROM). In this case, you work with a single gTable instance. In your database structure, for that table, define an additional indexed field of type Integer named "Marker". Initially, this field contains zeros (0) for all records.

Then, run the "(Dish is soup or appetizer) and (Main Ingredient is celery or eggplant)" query in two rounds:
mSetCriteria(gTable, "DishType", "=", "soup")
mSetCriteria(gTable, "OR", "DishType", "=", "appetizer")
mSelect(gtable)

Then, mark all found records:
repeat with i = 1 to mSelectCount(gtable)
mEditRecord(gtable)
mSetField(gtable, "marker", 1)
mUpdateRecord(gtable)
end repeat

Now, run the second part of the query:
mSetCriteria(gTable, "ingredient", "WordEquals", "celery")
mSetCriteria(gTable, "OR", "ingredient", "WordEquals", "eggplant")
mSetCriteria(gTable,"and","marker","=", 1)
mSelect(gtable)

If there is a third part to you query, mark your selection with a 2 in the "Marker" field, and keep on going. For best performance, the first query must be the most restrictive, that is, the one that yields the least results.

At the end, restore the marked fields to 0 to prepare them for another query.

Solution 3: Field concatenation
The field concatenation technique works for queries that use ANDs at the lowest level, and ORs a the higher level, such as:
(Origin is Italian AND Dish is appetizer) or (Origin is French AND Dish is soup)

Furthermore, all of your search criteria - with the possible exception of the last one - must use the "=" operators. The last operator can be either "=" or "starts".

This technique requires some preparation at the database design step. Such a preparation is sometimes called data preconditioning.

When designing your database, you plan for additional fields that would hold the concatenated result of the lowest level queries. For example, in the above example you would create an additional field named OriginType that would hold the concatenation of DishOrigin and DishType. That field would contain values such as:
"Italian Asparagus al dente"
"Italian Prociutto e melone"
"French Soupe à l'oignon"
"Italian Melanzane del re"
"French Bouillon de merguez au miel"

At runtime, to perform a user query such as:
(Origin is Org1 AND Dish is Type1) or (Origin is Org2 AND Dish is Type2)
you should run the following script:
mSetCriteria(gTable, "OriginType", "=", Org1 & Type1)
mSetCriteria(gTable, "OR", "OriginType", "=", Org2 & Type2)
mSelect(gTable)



Article Details
Article ID: 113
Created On: 26 Sep 2006 02:41 PM

 This answer was helpful  This answer was not helpful

inm general footer
Services Xtras Go Products Support Gallery Store Download About Us Contact Newsroom