Lookups

In a relational database you reduce data duplication by putting similar data into separate tables and linking the data with foreign keys. Rather than storing the same CountryName over and over in the Address table, we store the all the countries in a Country table and then save the primary key of the correct Country record in each record of the Address table.

When the user creates a new Address record and they get to the CountryName field it is helps to provide the user with a list of countries from the Country table. If they type the letter U they appreciate it if you open a droplist of all the countries in the Country table that begin with the letter U. After they select a country, the Country_fkey field needs to be set in the Address record to the primary key of the selected country.

Based on the SQL meta-data the StudioWorks framework can automate all of the above. The StudioWorks tutorial takes you through the basics of setting this up. This section dives into the details of controlling lookups in StudioWorks.

Lookups Meta-Data

The following meta-data properties are used to control lookups:

1. lookupsqlclassname - This specifies which SQL class is to be used when fetching and displaying lookup records of a schema class. By default the schema class points the lookupsqlclassname to itself. A developer can create a special lookup query class and then point the schema class's lookupsqlclassname to the query class.
2. lookupcaninsert - If set to false users will not be able to insert new lookup records from the lookup droplist field. They will only be able to add new lookup records by opening to the lookup records window. If set to true and the user has security clearance, the user can create new lookup records on-the-fly.
3. lookupstartchar - This is the number of characters the user has to type before matching records are fetched from the database. If the lookup table is a fetchall table, the lookupstartchar is set to 1, otherwise the default is 2. A developer can increase or decrease the lookupstartchar value except for fetchall tables which must be 1. The larger the number of lookup records, the higher you will want to set the lookupstartchar property.
   
   If you set the lookupstartchar to a value greater than the length of the field, (e.g. 99), the field handler will not do a lookup until the user leaves the field On evAfter. If a single matching record is found it will be used, if more than one match is found the user will be prompted to select a record.
4. lookupcontains - If set to true, the any lookup record which contains the user entered value will be fetched. If set to false only records which begin with the user entered value will be fetched. If the lookupstartchar is set to 1, lookupcontains must be set to false, otherwise far to many lookup records will be fetched. The default value for lookupcontains is true if the lookupstartchar is great than one.
5. lookupwhere - If lookupwhere text is entered in the meta-data it will be added to the SQL text that is used to fetch lookup records.
6. includeinprompts - Only the columns which are checked as includeinprompts are displayed in the lookup droplist. It is important that at least one column in the lookup SQL class is set to includeinprompts.

There are 3 different locations in the SQL Meta-Data Editor where you can set lookup meta-data:

1. Schema class meta-data In the schema class meta-data you can set the lookupsqlclassname, lookupcaninsert, lookupstartchar, lookupcontains properties.
2. Query class meta-data In the query class meta-data you can set the lookupcaninsert, lookupstartchar, lookupcontains properties.
   
   By default these properties are inherited from the base schema class. You can right-click and override any of the properties to set them to something different than the base schema.
3. Query columns meta-data In the query columns meta-data you can set the lookupsqlclassname, lookupcaninsert, lookupstartchar, lookupcontains, lookupwhere properties.
   
   By default the lookupsqlclassname property is set to the lookupsqlclassname property of the lookup column's schema class. The lookupcaninsert, lookupstartchar, lookupcontains, properties are then inherited from the SQL class specified by the lookupsqlclassname. Every one of these lookup properties can be overridden at the query column level and set by the developer. The lookupwhere property can be entered by the developer.

Lookup Fields

Lookup fields help the user join a record in the main table to a record in the lookup table. e.g. Address to a Country.

To use the StudioWorks framework lookups you will need an edit query class which joins the main table to the lookup table and includes at least one includeinprompts column from the lookup table. e.g. qAddressEdit which includes all the Address table fields plus the CountryName from the Country table.

When the field handler generates the field properties list is detects any lookup columns in the query class and sets the lookup... properties based on the meta-data.

When the lookup field gets the focus (evBefore) the field handler detects that it is a lookup field and the oFieldHandlerLookupTypeAhead object is asked to handle the field events.

Based on the lookup meta-data the field handler will suggest to the user possible lookup records to choose from based on the characters they type into the lookup field.

There are some special power user tricks built into the lookup field handler:

1. If the user types a ? character a list of all possible lookup records up to a the fetch batch limit of 50 records will be listed. A more button appears at the bottom of the droplist if there are more records to fetch.
2. If the user types one or more characters followed by a ? character a list of all possible lookup records matching what they entered less the ? character will be listed. The fetch batch limit will be ignored.
3. If the user types +? a list of all possible lookup records with no fetch batch limit will be listed.

Intercepting Lookups

The developer can intercept the lookup field handler at several locations:

1. In the $event method of the lookup field.
   
   You can trap any event (evBefore, evKey, evAfter) in the $event method of the field. Always remember to Quit event handler (Pass to next handler) if you want the field handler to do anything.
2. Add a $setLookup method to the lookup field with the parameter pLookupRow.
   
   When the lookup field handler has finished doing its work it sends a $setLookup message to the lookup field and passes the current lookup list as a parameter.
3. Add a $setMainListValues method to the lookup field with the parameter pLookupRow to intercept the lookuup field handler setting the main list values. If a $setMainListValues method exists the $setLookup message will not sent to the lookup entry field.
4. Add a $promptLookupRecordNotFound and/or$promptNewLookupRecord and/or $promptNewCopyLookupRecord method to the lookup field if you want to take over these methods. The lookup field handler checks for these methods before prompting the user, and if found, passes control to the lookup field method.
5. $:LookupOrderBy - If you add this method to the lookup entry field, the lookup field handler will take the value you return from this method and pass it as the pExtraSQL_opt parameter it sends with the $selectWhere(pWhere) message to the table class when fetching the records to be listed in the lookup droplist. The $:LookupOrderBy method must return a valid SQL string that begins with ORDER BY
6. $:LookupWhere - If you add this method to the lookup entry field, the lookup field handler will take the value you return from this method (must be a WhereRow) and pass it as the pWhere parameter it sends with the $selectWhere(pWhere) message to the table class when fetching the records to be listed in the lookup droplist. The $:LookupWhere method can be as complicated as you like. It is often used to reduce the list of lookup records based on data the user has entered in other fields in the same window.
7. $:SelectDistinct - If you add this method to the lookup entry field and return kTrue, the lookup field handler will set to kTrue the pbSelectDistinct parameter it sends with the $selectWhere(pWhere,pExtraSQL,pbSelectDistinct) message to the table class when fetching the records to be listed in the lookup droplist. You will need to create a special query class when using select distinct because the combination of all the columns in the query class must be distinct. e.g. If you include the primary key in the select distinct all of the records would be listed, negating the effect of using select distinct.

Lookup List

The lookup list is actually a window class, wLookupList, which is positioned directly below the lookup field and made to appear as a droplist.

The oFieldHandlerLookupTypeAhead controls the lookup list.

Refs Lookups

The Refs table can be used for non-relational lookups. These would be lookups where you want to copy the lookup list value to the record. You may want to give the user a list of suggested job titles or give the user 3 possible choices to enter for the person's gender. (F=Female, M=Male, Blank=Unknown)

To use the Refs table for lookups you need to create a set of Refs records with the RefsType set to lookup.

The following schema class columns meta-data properties are used to point to the Refs table lookup records which you create:

1. validaterefsgroup - The RefsGroup column value in the Refs table records.
2. validaterefssubgroup - The RefsSubGroup column value in the Refs table records.
3. validaterefsmandatory - If set to true, the user must select one of the Refs lookup records. If set to false, the user can select one of the Refs lookup records or enter their own value.

The RefsSortOrder can be used to controlling the order of the lookup records in the list. The RefsDisplayText and RefsDesc will appear in the lookup list. (If they match, only the RefsDisplayText will appear in the lookup list). The RefsDisplayText will be copied to the actual lookup field.

Troubleshooting Lookups

There are several common problems that will stop the lookup field handling from working.

1. The $keyevents property must be set to kTrue in the Action tab properties of the library which contains the window class.
   
   If you use Programmer Menu > Create New Module..., the StudioWorks framework should set the $keyevents property to kTrue for you.
2. The $event method of the lookup entry field has On evBefore and/or On evAfter in the method and nothing to pass the event up to the next handler.
   
   

   On evBefore
   ; This traps the evBefore event but fails to pass it to the field handler.
   
   On evAfter
   ; This traps the evAfter event but fails to pass it to the field handler.

   
   
   This is easy to do if you manually drag an entry field out of the Component Store. To prevent this from happening I usually remove the On ev... lines of code from the kEntryField inside the component store.
3. You have added some custom event handling code to the lookup entry field and have not ended your custom code with Quit event handler (Pass to next handler).
   
   

   On evBefore
   
   ; Do my special custom code.
   
   ; Always end with this next line, unless you don't want the field handler to process the event.
   Quit event handler (Pass to next handler)

4. You are attempting to do a lookup on a number field. It is not possible in SQL to do a LIKE on number fields. The lookup field handler will switch to fetching exact matches if you try to do a lookup on a number field.

StudioWorks Documentation - Copyright 2005 Vencor Software