This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

The Kit Box: You 'Auto' Complete Your Editboxes

Andy Kramek and Marcia Akins

Textbox Auto Completion, introduced in VFP 9.0, is very cool but works only for the Textbox control. However, there are many other scenarios where an auto-text feature would add great value to an application. In this month's column, Andy Kramek and Marcia Akins show how you can implement similar functionality for Editboxes, both in VFP 9.0 and in earlier versions of VFP.

Marcia: You know, I really like the new Auto Completion feature for Textboxes in Visual FoxPro 9.0. It's a real pity that they didn't make it available for Editboxes, too, because I could really use it right now in the application I'm working on.

Andy: Seems to me that we should be able to create an Editbox class that duplicates the functionality. At least, we might if we knew exactly what you wanted to do.

Marcia: Several forms in this application require the user to enter free-form text into "notes" fields. When I look at the data, I see that the gist of the notes is the same, but because they've been entered by different users, the phrasing is slightly different.

Andy: And your point is? Isn't this the point of free-form text to begin with? If users are allowed to use only standard phrases, you should be using a different control (some sort of multi-select list, perhaps?).

Marcia: But these fields must allow free-form text because, even though some portions of the text contain standard phrases, other portions are entirely unique. Take, for example, the notes field for a customer service representative. It often contains phrases like "This customer was dissatisfied with the service because," but the reason customers were dissatisfied varies considerably.

Andy: It seems to me that in this case your field should be something like "Customer Complaint" and the need for the "standard text" disappears. QED!

Marcia: As usual, you're missing my point entirely. Did it not occur to you that the customer might call for several reasons–to ask questions about a pending delivery, question a charge on an invoice, or even praise an employee for great service?

Andy: Ah, yes–I see. Are there any other requirements?

Marcia: Yes. The users need the ability to add "standard fragments." As they're typing, they may realize that they've typed the same phrase several times before and want the phrase available for use later to save keystrokes. Furthermore, the selections need to be "context-sensitive."

Andy: Huh? What do you mean by "context-sensitive"?

Marcia: The types of fragments used in, say, a customer service notes field would be different from those used in the notes field in an order entry form.

Andy: Ah! You mean making available only those fragments that are relevant at a given time.

Marcia: I thought that's what I said.

Andy: Well, the first thing we'll need is a table in which to store these "fragments." Then, we'll need some way of displaying the available options. How much text do you want to store?

Marcia: Why limit it? Just use a memo field.

Andy: I suppose we could, but the problem then becomes how to display it. I was thinking that we'd use a pop-up menu or list to display the fragments. Essentially, that will mean that entries should really be one line.

Marcia: Now that I think about it, it seems that the objective here should be to allow users to create reusable "fragments" of text. So, in practice, the text is probably going to be pretty short. Let's choose an arbitrary value of 120 characters for now.

Andy: Seems reasonable, since your earlier example of "This customer was dissatisfied with the service because" is only 55 characters long. So, we'll need a key field and a character field.

Marcia: We'll also need a field that we can use to filter the entries–remember, context-sensitive is a requirement.

Andy: Ah, yes, I was trying to forget about that. So, the required table structure is going to be as shown in Table 1.

Table 1. Structure for the standard phrases table (stdtext.dbf).

Marcia: Good. Next, we have to decide how to trigger the list and how we're going to display the fragments.

Andy: I was thinking in terms of a pop-up menu called from the RightClick event of the Editbox.

Marcia: You mean you want to display the text fragments directly in the pop-up menu?

Andy: Sure, why not?

Marcia: Well, for one thing, how will they add a fragment to the table? For another thing, what happens if a specific Editbox points to 30 text fragments? Isn't that going to be rather cumbersome in a pop-up menu?

Andy: The first issue is easily solved: Just add an Add Item option to the menu. The second issue, however, is more of a problem. The pop-up menu could end up exceeding the available screen real estate.

Marcia: Besides, finding specific items and navigating in long pop-up menus is a pain (look at the right-click menu in any method editing window and you'll see what I mean).

Andy: So, what you're saying is that you don't like the idea of a pop-up menu?

Marcia: No, I'm saying that the pop-up menu should have two options only. One will launch a form that displays the available text fragments, and the other will add an item to the list of standard phrases.

Andy: That raises another question. How are users supposed to add a fragment? Via another form?

Marcia: That's too cumbersome. Besides, when they decide to add a text fragment, they've already typed it–why make them type it again? Just let them highlight the text they want to add and then select the Add Item option from the pop-up menu.

Andy: I like that. We can even make the Editbox smart enough so that the pop-up menu will display only when there's highlighted text; otherwise, it can go straight to the list of options. In fact, we can make it so smart that it offers the pop-up menu only when the Editbox has some text fragments defined for it and the user has some text highlighted. In all other situations, we can take the appropriate action without user intervention.

Marcia: I think I want to see the truth table for that statement! (See Table 2.)

Table 2. Handling the right-click pop-up menu.

Andy: The Editbox class is going to need some additional properties. I assume you'll want to account for the possibility of different tables to store your fragments.

Marcia: Yes, if only because the Auto Complete Textbox allows you to define the source table, and we're trying to duplicate its functionality. We'll also need properties to store the list of fields to be selected and the filter condition to apply. The properties we need to add are shown in Table 3.

Table 3. Custom properties for the auto-text Editbox class.

Andy: What about custom methods? Obviously, we'll need the RightClick() event to call a method that will decide what action must be taken–let's call that the OnRightClick() method.

Marcia: We'll also need methods to add text to the table and to insert text selected from the table. Furthermore, we'll need a pop-up form to allow the user to select from the available fragments (see Table 4).

Table 4. Custom methods for the auto-text Editbox class.

Andy: Let's start with the OnRightClick() code. The first task is to see whether there are any text fragments already defined for the Editbox. Basically, all we need to do is extract the key from the cTextKey filter property, run a query, and set a local flag based on the result:

  IF NOT EMPTY( .cTextSource )
  *** Do we have any auto-text data
  lcSql = "SELECT " + ALLTRIM( .cFieldList ) ;
        + " FROM " + .cTextSource ;
        + IIF( NOT EMPTY( .cTextKey ), ;
           + " WHERE " + ALLTRIM( .cTextKey ), "" );
           + " INTO CURSOR curText ORDER BY 1 NOFILTER"
  &lcSql
  llHasAutoText = (_TALLY > 0)
ENDIF

Marcia: Good. The next step is to check for selected text in the Editbox and set a flag for that, too. We can read the Editbox's SelText property and get rid of any unwanted characters with CHRTRAN(), and then set the llHasSelected flag:

  *** Do we have any selected text
IF .SelLength > 0
  *** Clean out CRLF and TAB characters first
  lcText = CHRTRAN( .SelText, CHR(13)+CHR(10)+CHR(9), '')
ENDIF
llHasSelected = NOT EMPTY( lcText )

Andy: Next, we must decide what action to take as defined by our truth table (Table 2). There's not much code needed for that:

  IF llHasSelected AND llHasAutoText
  *** All options are available, Show the pop-up menu
  STORE 0 TO pnChoice
  DEFINE POPUP showtext SHORTCUT ;
               RELATIVE FROM MROW(),MCOL()
  DEFINE BAR 1 OF showtext PROMPT "Insert AutoText"
  ON SELECTION BAR 1 OF showtext pnChoice = 1
  DEFINE BAR 2 OF showtext PROMPT [\-]
  DEFINE BAR 3 OF showtext PROMPT "Add Selected Text"
  ON SELECTION BAR 3 OF showtext pnChoice = 3
  ACTIVATE POPUP showtext
ELSE
  IF NOT llHasSelected AND NOT llHasAutoText
    *** Nothing selected and nothing defined
    lcMsg = "There is no auto text for this field," + CRLF
    lcMsg = lcMsg + "and nothing is selected"
    MESSAGEBOX( lcMsg, 64, "There is nothing to do" )
    *** Set selection value to null
    pnChoice = 0
  ELSE
    *** Only one option available, set pnChoice explicitly
    pnChoice = IIF( llHasAutoText, 1, 3 )
  ENDIF
ENDIF

Marcia: I notice that pnChoice is being set by the menu. Obviously, this means that it must have been declared as private in the calling routine.

Andy: Yes, it is. I didn't show all of the declarations here, but since you mention it, we should point out that CRLF is explicitly defined in the code as "CHR(13) + CHR(10)".

Marcia: The final step is to take action according to the value of pnChoice, like this:

  DO CASE
  CASE pnChoice = 1
    *** They want to pick from the auto-text 
    *** Pass the name of the cursor to use to the form
    DO FORM frmautotext WITH 'curtext' TO lcText
    IF NOT EMPTY( lcText )
      *** We have some text, so add it to the field
      .InsertText( lcText )
    ENDIF
  CASE pnChoice = 3
    *** Add the selected text to the autotext list
    .AddTextToTable( lcText )
  OTHERWISE
    *** Do nothing
    lcText = ""
ENDCASE

Andy: Another slightly tricky part is the code that inserts the selected text into the Editbox. This is handled by the InsertText() method, which receives the text from the pop-up form as a parameter:

  WITH This
  *** Ignore trailing spaces 
  tcText = ALLTRIM( tcText )
  lcText = ALLTRIM( NVL(.Value,"") )
  lnLen = LEN( lcText )
  *** Get the position of the insertion point
  lnPos = .SelStart
  DO CASE
    CASE lnPos <= 1
      *** We are at the beginning so 
      *** Add a space AFTER the text
      .Value = tcText + " " + lcText
      lnNewPos = LEN( tcText ) + 1
    CASE lnPos < lnLen
      *** We are in the middle, add spaces both sides
      .Value = LEFT( lcText, lnPos ) + " " + tcText 
             + " " + SUBSTR( lcText, lnPos + 1 )
      lnNewPos = lnPos + LEN( tcText )  + 2
    OTHERWISE
      *** We are right at the end 
      *** Add a space BEFORE the text
      .Value = lcText + " " + tcText
      lnNewPos = lnPos + LEN(tcText) + 1
  ENDCASE
  *** Re-position the insertion point
  .Refresh()
  .SelStart = lnNewPos  
ENDWITH

Marcia: Good. Now we have to handle the addition of a fragment to the table. This isn't too difficult, because we're using an auto-incrementing integer on the table, and we can get the names of the text and id fields from the cFieldList property and the name of the code field from the cTextKey property. That way, the code will work no matter what table is the source for the auto-text fragments. I like it when things can be data-driven like this:

  *** Insert the text into the table
lcTable = This.cTextSource
 
*** The target field is the first word in the fieldlist
lcField = GETWORDNUM( This.cFieldlist, 1, ',' )
 
IF NOT EMPTY( This.cTextKey )
 
  *** The key field is the first word in the cTextKey
  lcField = lcField + "," + ALLTRIM( ; 
    GETWORDNUM( This.cTextKey, 1, '=' ))
 
  *** Deal with embedded quotes 
  *** (Convert Smart Quotes to standard first)
  tcText = CHRTRAN( tcText, CHR(147)+CHR(148), CHR(39) )
 
  *** Remove trailing blanks and add enclosing delimiters
  *** NOTE: Use '[]' to avoid issues with embedded quotes
  tcText = "[" + RTRIM( tcText ) + "]"
 
  *** The value of the text key is the second word 
  *** in cTextKey
  tcText = tcText + "," + ALLTRIM( ;
    GETWORDNUM( This.cTextKey, 2, '=' ))
ENDIF
*** Build and execute the insert statement
lcSql = "INSERT INTO " + lcTable + " (" + lcField + ") ;
  VALUES (" + tcText + ")"
&lcSql

Andy: Well, it does rely on the properties being set correctly, but that's a design-time issue–I suppose it's fair enough to assume that it will be done correctly.

Marcia: Of course it is! We have to assume that developers will do things correctly, just as we have to assume that they'll test their code.

Andy: Now, there's an assumption for you! Are we done? Can we test it now?

Marcia: Yes. Figure 1 and Figure 2 show a little form (included with this month's Download file) that uses the auto-text Editbox class.

Note: Although designed and tested in VFP 8.0, this code will run in Version 7.0 if the source table is changed to remove the auto-incrementing field on the primary key, and even in VFP 6.0 if the GetWordNum() function calls are replaced with calls to the foxtools.fll equivalent in the class definition.

Download 407KITBOX.ZIP

To find out more about FoxTalk and Pinnacle Publishing, visit their Web site at http://www.pinpub.com/

Note: This is not a Microsoft Corporation Web site. Microsoft is not responsible for its content.

This article is reproduced from the July 2004 issue of FoxTalk. Copyright 2004, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. FoxTalk is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.