user interface

[dragancesu]

I am still trying to make a user interface that has a search function, it's almost over, just one more thing

Something as in the SQL is WHERE clause and LIKE operator

SELECT * FROM table WHERE field1 LIKE ''%AB%'

Clipper has FILTER and it could be as AT ('AB', field1)

Does anyone have a better solution?

[andyglezl]

Hola Dragan

Desde Clipper tambien existe lo siguiente:
-----------------------------------------------------
Hi Dragan

From Clipper there is also the following:

$
Substring comparison--binary (Relational)
------------------------------------------------------------------------------
Syntax

<cString1> $ <cString2>

Type

Character, memo

Operands

<cString1> is a character or memo value that is searched for in
<cString2>.

<cString2> is a character or memo value within which <cString1> is
sought.

Description

The $ operator is a binary relational operator that performs a case-
sensitive substring search and returns true (.T.) if <cString1> is found
within <cString2>.

Examples

. This example illustrates the case-sensitivity of the substring
operator ($):

? "A" $ "ABC" // Result: .T.
? "a" $ "ABC" // Result: .F.

[andyglezl]

Otro ejemplo...
---------------------------
Another example ...

ReEncab0->( DBEval( { || Form_3.Encs.AddItem( { FOLIO, DToC(FEC_RECIBO) } ) }, ;
{ || ( "12345" $ cCodigo .And. ! CVE_CANCEL .And. ! CVE_COBRAD) } ) )

[dragancesu]

it is, okay, but it may need to make a statement and apply it to the filter, something like

SELECT * fom table WHERE fild1> 15 and fild2 like '% AB%'

SET FILTER TO fild1> 15 .AND. AT('AB', fild2)

[andyglezl]

Here...

viewtopic.php?f=5&t=3939&p=36875&hilit=andyglezl#p36875

[serge_girard]

Dragan,

I use following constructing for many years:

Code: Select all

Filter := {}

USE some_DB

IF !EMPTY(pfilnr)
   AADD(Filter, {|| some_DB->FIL_NR = pfilnr})
ENDIF

IF !EMPTY(pkapnr)
   AADD(Filter, {|| some_DB->KAP_NR = pkapnr})
ENDIF

IF WZVERKOOP ="J"
   IF !EMPTY(WZBEDRAGKL)
      AADD(FILTER, {||some_DB->VERKOOP >= WZBEDRAGKL})
   ENDIF

   IF !EMPTY(WZBEDRAGGR)
      AADD(FILTER, {|| some_DB->VERKOOP <= WZBEDRAGGR})
   ENDIF
ENDIF

IF WZVERKOOP == "N"
	AADD(FILTER, {|| some_DB->VERKOOP == 0})
ENDIF

IF (WZVERKOOP == "J")
   AADD(Filter, {|| some_DB->VERKOOP > 0})
ENDIF

// add as many filters as you like

DO WHILE .not. eof() 
   IF EvalFilter()
   	// MATCH !!
   ENDIF
   SKIP
ENDDO
CLOSE ALL

STATIC FUNCTION EvalFilter()
****************************
LOCAL retval := .T.
LOCAL i

FOR i := 1 TO LEN(Filter)
   retval := retval .AND. EVAL(Filter[i])
NEXT


RETURN retval

No need to SET FILTER and very fast

Greetings,

Serge

[dragancesu]

Nice solution when it comes to processing it can, but when used in the form then I think you must FILTER, not the best (fastest) or works

I solved my problem

Compile example, so try to look for (F7) and in the field JOB enter %MAN%, this will create a filter expression

SET FILTER TO IF(AT("MAN", JOB)> 0),.T., F)

[dhaine_adp]

Hi Dragancesu,

Nice solution when it comes to processing it can, but when used in the form then I think you must FILTER, not the best (fastest) or works

In the DBFCDX driver you can use ORDSCOPE() function to filter the records however you would need to properly design the tables to properly index them. Just in case you'd like to try DBFCDX see the code snippet below (untested) as demonstration only.

Code: Select all

***********
func Main()

   PRIVATE AppHome      := LOWER( GETSTARTUPFOLDER() )
   PRIVATE dbHomeFolder := AppHome + "\so-data\"
   
   PRIVATE dbSOrder := dbHomeFolder + "SOHEADER"
   
   ANNOUNCE RDDSYS
   SET FIXED ON
   SET DECIMALS TO 2
   SET MULTIPLE OFF WARNING
   SET DATE AMERICAN
   SET CENTURY ON
   SET EPOCH TO 1950
   SET WRAP ON
   SET SCOREBOARD OFF
   SET DELETED ON
   SET CONFIRM ON
   SET BELL ON
   SET FONT TO "Arial" , 9
   SET NAVIGATION EXTENDED
   SET BROWSESYNC ON
   SET TOOLTIPSTYLE BALLOON
   SET TOOLTIPBACKCOLOR { 255 , 255, 0 }
   **SET TOOLTIPFORECOLOR { 0 , 0 , 0 }
   RDDSETDEFAULT( "DBFCDX" )   
   
   IF .NOT. NETUSE( ( dbSOrder ), "SORDER", Shared, ReadWrite )
      RETURN NIL
   ENDIF
   ORDLISTADD( ( dbOrder ) )
   ORDSETFOCUS( "SODate", ( dbColTbl ) )
   DBGOTOP()

   **pseudocode only: retrieve unserved or incomplete sales order
   dbSetFilter( "SORDER", cProductCode+SONumber, cProductCode+SONumber, "Unserved", dbSorder )
   
   **pseudocode only: Check Status:
   cStatus := "I"  // In-process
   *------------------------------------------------
   *- You can also set the scope as below
   *------------------------------------------------
   *cStatus := "F"+cProductcode
   *cStatus := "P"+cProductcode+Customercode
   
   
   dbSetFilter( "SORDER", cStatus, cStatus, "Status", dbSorder )

   /* your processing here */
   
   RETURN nil


**************************
static function MakeFile()

   LOCAL lRetVal := .f.
   LOCAL cBreakMsg := ""
   LOCAL cCaption  := "ERROR"


   BEGIN SEQUENCE

   IF .NOT. FILE( ( "&dbSOrder" + ".cdx" ) ) 
      DBCREATE( ( dbSOrder ) ),;
      { { "F1",  "C",  13, 0 },;    // Sales Order #
        { "F2",  "C",  10, 0 },;    // Customer/client Code
        { "F3",  "C",  13, 0 },;    // Product Code
        { "F4",  "C",  13, 0 },;    // Size
        { "F5",  "N",  13, 0 },;    // Quantity
        { "F6",  "D",   8, 0 },;    // Date Stamp \ Field to be use to determine priority
        { "F7",  "C",   8, 0 },;    // Time Stamp / (a.k.a. first come, first serve)
        { "F8",  "D",   8, 0 },;    // SO Date
        { "F9",  "N",  13, 0 },;    // Quantity Served (Finished)
        { "F10", "N",  13, 0 },;    // Balance
        { "F11", "C",   1, 0 },;    // Status: (P)artial, (I)n-Process, (F)ull, (C)ancelled
        { "F12", "C",   1, 0 },;    // Priority Level: (V)ery Critical, (C)ritical, (H)igh, (M)edium, (L)ow
        { "F13", "N",  13, 0 },;    // Priority in Julian Date using January 1, 2000 24:00:00 as base date
        { "F14", "C", 200, 0 } },;  // Remarks
      RDSETDEFAULT() )
   ENDIF
   IF .NOT. FILE( "&dbSOrder" + ".cdx" )
      IF .NOT. NETUSE( ( dbSOrder ), "SORDER", Exclusive, ReadWrite )
         cBreakMsg := "Cannot open file " + dbLoanReg + " exclusively."
         BREAK
      ENDIF
      INDEX ON F1 TAG SONumber TO ( dbSOrder )
      INDEX ON F1+F3 TAG SOProdCode TO ( dbSOrder )   // Index by SO Number, then product code
      INDEX ON F12+F3+F2 TAG CustPriority TO ( dbSOrder ) // Index by Priority, Product Code then by Customer
      INDEX ON F11+F3+F2 TAG Status TO ( dbSOrder )       // Index by Status, Product Code then by Customer
      INDEX ON F3+F1 TAG Unserved TO  ( dbSOrder ) FOR F5-F9 > 0   // Unserved Sales Order
      INDEX ON DTOS( F8 ) TAG SODate TO ( dbSOrder )
      ORDLISTADD( ( dbSOrder ) )
      SORDER->( DBCLOSEAREA() )
   ENDIF
   lRetVal := .t.

   END SEQUENCE

   IF .NOT. lRetVal
      MSGSTOP( cBreakMsg, cCaption )
   ENDIF
   RETURN lRetVal



**********************
function dbSetFilter()

   PARAMETERS cAlias, cTopKey, cBottomKey, cOrderBag, cdxFile  // declaration defined like this so those parameters
                                                               // are created as private variables.
                                                               // If it is defined on function declaration inside the
                                                               // parenthesis, variables are created as LOCAL.
   DBSELECTAREA( ( cAlias ) )
   ORDSCOPE( 0, NIL ); ORDSCOPE( 1, NIL ); DBGOTOP()  // clear the scope
   IF .NOT. VALTYPE( cOrderBag ) == "U"
      ORDSETFOCUS( ( cOrderBag ), ( cdxFile ) )
   ENDIF
   ORDSCOPE(0, "&cTopKey" )
   ORDSCOPE(1, "&cBottomKey" )
   DBGOTOP()
   RETURN NIL

ORDSCOPE( 0, <cFilter> ) => record top bound
ORDSCOPE( 1, <cFilter> ) => record lower bound

[dragancesu]

There are many ways to do better, but the combination of DBF/NTX is not exactly a happy solution

What I am trying (perhaps manage) to make a "query by example" in the form while the program is running

Should enable the user to set query what he needs and it is not easy, to enable query by all fields

[mol]

I've created something like extended filter definition, when user can select any column from table, desired value and operator, can compare columns to value or column to column and can concatenate filters:





Column names are taken straight from Browse or Grid control.

If you are interested in such a solution, I'll post source code and prepare a little sample.