Skip to content

peSuggestionMode - cDbCJGridColumnSuggestion

Determines the suggestions shown in the list for a suggestion form

Type: Property
Access: Read/Write
Data Type: Integer
Parameters: None

Syntax

Property Integer peSuggestionMode
Access Type Syntax
Read Access: Get peSuggestionMode to IntegerVariable
Write Access: Set peSuggestionMode to IntegerVariable/Value

Description

peSuggestionMode determines how a suggestion list behaves - how it is filled and how it updates the invoking dbSuggestionForm or cDbCJGridColumnSuggestion.

Valid values are:

Constant Meaning
smFind (Default) Searches for suggestions in the database table. It requires the field to be data-aware.
smValidationTable Loads the suggestions from a validation table, making the control a good alternative for a dbComboForm control that has too many options.
smCustom Allows the developer to provide the options by implementing the OnFindSuggestions and OnSelectSuggestion events. Providing the suggestions is done by filling an array of tSuggestion structs.

smFind

This will be the most used mode and is the default. It allows the suggestion list to be used with a data aware form much the same way a selection list is used. With each keystroke, a list of appropriate suggestions are shown for the form's (or grid column's) field. When selected, a record is found in the DataDictionary object (DDO) and the entire view is updated with the new record. These records are found and displayed in index order. The index is usually determined by the File.Field defined in the data entry object (DEO). It uses DF_FIELD_INDEX as the best index. This can be overridden by setting piFindIndex.

Sample

This shows a basic suggestion list for customer name, which is an indexed field.

Object oSuggestionForm1 is a dbSuggestionForm
    Set Location to 44 74
    Entry_Item Customer.Name
    Set size to 14 146
    Set Label to "Customer Name:"
    Set piStartAtChar to 1
End_Object

Normally, a suggestion list uses an incremental search strategy for showing records. All records that have the same left-most value as the value typed into the form are shown in a list. As more characters are typed, the list is refined. With string searches, this means that typing "ac" would show records starting with "ac", such as "Ace Hardware" and "Acme Games". Setting pbCaseSensitive determines if the search should pay attention to character casing.

There is a special behavior used when finding numbers (presumably integers), where the values are displayed as if they were strings (e.g., you enter 1 and you are shown all numbers starting with 1, 10, 11, 100, 110; you enter 11 and you see 11, 110). The finding logic performs the necessary index jumps to find these records quickly. This is referred to as incremental search and is the default behavior.

When peSuggestionMode is smFind, there is another type of search behavior that can be used with strings. Setting pbFullText to True changes the search from leftmost incremental to text sub-string matching. When True, the search will include sub-strings that occur anywhere within the field where a search of "ac" would find "Jack's Place" as well as "Ace Hardware" and "Acme Games". There are optimization considerations when using full text search. See pbFullText for more information about this and how some of the searching can be moved to the SQL server.

smValidationTable

Setting peSuggestionMode to smValidationTable allows you to use suggestion lists with validation tables. If you have a large number of validation choices, this may provide a good alternative to a combo list (dbComboForm). For this mode to work, you suggestion form's Entry_Item File.Field must have a DDO that has a validation table assigned to that field. If it does, it will all work automatically.

An smValidationTable will display both the validation code and description in a suggestion line (e.g., "CA - California"). When a selection is made, the code value will be moved to the invoking form. This mode can also be used with full text search by setting pbFullText to True.

Sample

This example uses a validation suggestion list for Customer.State, which we assume has a DD validation table associated with it for all states and their names.

Object oSuggestionForm4 is a dbSuggestionForm
    Set Location to 89 74
    Entry_Item Customer.State
    Set Size to 14 50
    Set Label to "Customer State:"
    Set peSuggestionMode to smValidationTable
    Set piStartAtChar to 1
End_Object

smCustom

Setting peSuggestionMode to smCustom allows you to create your own custom suggestion list. While this offers great flexibility, it requires that you provide the suggestion list fill and update logic. Each time a key is pressed and the list needs to be updated, the OnFindSuggestions event is called, passing the current search string. You will have to write code to fill and return an array of suggestions (which is returned using an array of tSuggestion structs). When a selection is made, the OnSelectSuggestion event is called, passing the selected suggestion in a tSuggestion variable. You must write the code to update your form or grid column with the selected suggestion. Creating your own custom suggestion list objects, or more likely, sub-classes is considered to be an advanced technique. If you do this, you may wish to study the package code to see how this is handled.

Sample

This sample shows a custom suggestion list that gets its values from a static array. It shows the basics of building your own list. OnFindSuggestion is used to fill a tSuggestion array, which is passed by reference. Because these items are not even records, we simply set a unique ID string as the sRowId member. We also set this to the same string as the display value member. Upon selection, we use OnSelectSuggestion to take the selected suggestion and set the form's changed value to the value. 

// this shows how to create your own custom suggestion list
Object oSuggestionForm5 is a dbSuggestionForm
    Set Location to 175 74
    Set Label to "Custom Id:"
    Set Size to 14 100
    Set peSuggestionMode to smCustom
    Set piStartAtChar to 1

    // even though we handle searching manually in OnFindSuggestions, pbFullText
    // is still used to determine the display and bolding of matched text. Setting
    // this true tells it we are matching anywhere within the string.
    // Since this is a custom list, it is the developer's job to make sure that
    // OnFindSuggestions follows these rules as well.
    Set pbFullText to True

    // augment to create an arbitrary list of Ids to be used for our search
    Property String[] pCustomIds

    // augment to create a list of Ids
    Procedure Activating
        String[] sIds
        Forward Send Activating
        Move "JSON" to sIds[0]
        Move "XML" to sIds[1]
        Move "SQL" to sIds[2]
        Move "SQLExpress" to sIds[3]
        Move "SQLServer" to sIds[4]
        Move "DF" to sIds[5]
        Move "XQuery" to sIds[6]
        Move "JScript" to sIds[7]
        Move "Java" to sIds[8]
        Move "UTF-8" to sIds[9]
        Move "UTF-16" to sIds[10]
        Set pCustomIds to (SortArray(sIds,Desktop,RefFunc(DFSTRICMP)))
    End_Procedure

    // custom code to find all matches for the search
    // You can write whatever code you want here to find matched items
    Procedure OnFindSuggestions String sSearch tSuggestion[] ByRef aSuggestions
        String[] sIds
        Integer i iLen iIds iCount
        Move (Lowercase(sSearch)) to sSearch
        Move (Length(sSearch)) to iLen
        Get pCustomIds to sIds
        Move (SizeOfArray(sIds)-1) to iIds
        For i from 0 to iIds
            If (Lowercase(sIds[i]) contains sSearch) Begin
                Move sIds[i] to aSuggestions[iCount].sRowId
                Move sIds[i] to aSuggestions[iCount].aValues[0]
                Increment iCount
            End
        Loop
    End_Procedure

    Procedure OnSelectSuggestion String sSearch tSuggestion Suggestion
        Set Value to Suggestion.sRowId
        Set Item_Changed_State to True
    End_Procedure

End_Object