Partial search on a numeric field.

[mroth36]

HI Everyone, is it possible to do a partial search on a numeric field in a table that is indexed in numeric sequence? I have the following situation, I have a numeric field in a table, I whould like the user to be able to enter a number, lets-say 3 & i will be able to get to the first record that has that numeric field that starts with a 3. If i am using a numeric index the seek will return an eof() condintion if i dont have the number 3 (in-other-words if the first '3' number is 30, it wont find it. I changed the index to an alltrim(str(__)), then my browse does not display in numeric seq, 300 displays before 31. Can I manipulate my seek somehow?

Thank You

 

[craigsboyd]

You could try using LOCATE instead...

lcLookFor = "6*" && wildcard match, begins with "6"
SELECT MyTable
LOCATE FOR LIKE(lcLookFor, ALLTRIM(STR(MyTable.MyField)))

SweetPotato Software Website
My Blog

 

[mroth36]

Thank you very much! Locate is working fine! in all of my over 20 years of Xbase experience, i never used it.

Thank You Very Much!

 

[MarciaAkins]

I have a numeric field in a table, I whould like the user to be able to enter a number, lets-say 3 & i will be able to get to the first record that has that numeric field that starts with a 3.

Here is an incremental search textbox that works with both numeric and character data types:

**************************************************
*-- Class:        txtsearch
*-- ParentClass:  textbox
*-- search textbox: synchronizes all other form controls with search results
*
DEFINE CLASS txtsearch AS textbox
  HEIGHT = 23
  WIDTH = 209
  *-- Alias to search in. Required if the text box is not bound
  calias = ""
  *-- Tag to use in search
  ctag = ""
  *-- Name of the field in the alias to use in the SEEK or LOCATE into that alias
  cfield = ""
  cOldExact = ""
  *-- When set to true, refreshes the parent container (usually a page or a form) as the value is typed into the text box
  lrefreshparent = .T.
  NAME = "txtsearch"

  *-- As the name implies...
  PROCEDURE handlekey
    LOCAL lcSofar, lnSelect, lnSelStart, lnSelLength
    WITH THIS
      *** Get the value typed in so far
      lnSelStart = IIF( LASTKEY() # 127, .SELSTART, .SELSTART - 1 )
      *** Handle and empty value in the text box
      IF lnSelStart = 0
        .VALUE = ''
        .SELSTART = 0
        GO BOTTOM IN ( .calias )
        SKIP IN ( .calias )
      ELSE
        lcSofar =  UPPER( LEFT( TRANSFORM( .VALUE ), lnSelStart ) )
        .VALUE = lcSofar
        *** Use seek to find the record if a tag was provided
        IF ! EMPTY( .ctag )
          IF SEEK( Str2Exp( lcSofar, TYPE( .calias + [.] + .cfield ) ), .calias, .ctag  )
            *** Make sure the value in the textbox is always a character string
            .VALUE = TRANSFORM( EVAL( .calias + '.' + .cfield ) )
          ENDIF
        ELSE
          *** Otherwise, save the current work area before swithching to the specified table
          lnSelect = SELECT()
          SELECT ( .calias )
          *** And locate the specified record
          IF TYPE( .cfield ) $ [C,M]
            LOCATE FOR UPPER( ALLTRIM( EVAL (.cfield ) ) ) = lcSofar
          ELSE
            *** Make numeric fields behave more like character fields
            LOCATE FOR TRANSFORM( EVALUATE( .cfield ) ) = lcSofar
          ENDIF
          IF FOUND()
            *** Make sure the value in the textbox is always a character string
            .VALUE = TRANSFORM( EVAL( .calias + '.' + .cfield ) )
          ENDIF
          SELECT ( lnSelect )
        ENDIF
      ENDIF
      *** If we need to refresh the parent container (usually a form or a page, do it here
      IF .lrefreshparent
        .RefreshParent()
      ENDIF
      *** Highlight the portion of the value after the insertion point
      .SELSTART = lnSelStart
      lnSelLength = LEN( TRANSFORM( .VALUE ) ) - lnSelStart
      IF lnSelLength > 0
        .SELLENGTH =  lnSelLength
      ENDIF
      *** If we have refreshed the controls in the parent container, there are timing issues to overcome
      *** Even though .SelStart and .SelLength have the correct values at this point, the search box
      *** does not appear highlighted correctly without this delay
      DOEVENTS
    ENDWITH
  ENDPROC

  *-- Synchronise the text boxe's value with the rest of the data on the form, for example, when the user navigates to a new record using the toolbar or other means
  PROCEDURE synchronize
    LOCAL lnSelStart, lnSelLen
    *** Synchronize the text box's value with the current record on the screen
    WITH THIS
      lnSelStart = .SELSTART
      .VALUE = TRANSFORM( EVAL( .calias + '.' + .cfield ) )
      .SELSTART = lnSelStart
      lnSelLen = LEN( ALLTRIM( TRANSFORM( .VALUE ) ) ) - lnSelStart
      .SELLENGTH = IIF( lnSelLen > 0, lnSelLen, 0 )
      DOEVENTS
    ENDWITH
  ENDPROC

  *-- Refresh all the controls in the parent without refreshing this one
  PROCEDURE RefreshParent
    LOCAL loControl

    FOR EACH loControl IN THIS.PARENT.CONTROLS
      IF loControl.NAME # THIS.NAME
        *** Make sure the control has a refresh method!!!!
        IF PEMSTATUS( loControl, 'Refresh', 5 )
          loControl.REFRESH()
        ENDIF
      ENDIF
    ENDFOR
  ENDPROC

  PROCEDURE REFRESH
    *** Synchronize the display when another means is used to navigate to a new record
    THIS.synchronize()
    DODEFAULT()
  ENDPROC

  PROCEDURE INTERACTIVECHANGE
    *** If the key pressed was a printable character or a backspace, handle the keystoke and search
    IF THIS.SELSTART > 0
      IF ( LASTKEY() > 31 AND LASTKEY() < 128 ) OR ( LASTKEY() = 7 )
        THIS.handlekey()
      ENDIF
    ENDIF
  ENDPROC

  PROCEDURE GotFocus
    WITH This
      .uOldVal = .Value
      .cOldExact = SET( "EXACT" )
      SET EXACT OFF
      IF .SelectOnEntry
        TextBox::GotFocus()
        .SelStart = 0
        .SelLength = LEN( This.Text )
        NODEFAULT
      ENDIF
    ENDWITH		

  PROCEDURE LostFocus
    IF This.cOldExact = "ON"
      SET EXACT ON
    ENDIF

ENDDEFINE

Marcia G. Akins

http://www.TightlineComputers.com