Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Panu Modak
  Where is Panu Modak?
 Chandannagar
 India
 Panu Modak
 Tags
Subject: Filter data
Thread ID: 21650 Message ID: 21650 # Views: 55 # Ratings: 0
Version: Visual FoxPro 7 Category: General VFP Topics
Date: Friday, March 28, 2003 2:42:46 PM         
   


I would like to make a programme to filter data as per user given request. I have written my programme in the following way.
1. Creating a combo list of files with ADIR() command.
2. User selecting a file and also giving a request in text box.
3. As per user's request a list of fileds is creating with AFIELD() command.
4. A string for WHERE clause of SELECT - SQL command is being made with the user given text and information of the fields.
5. Running SELECT - SQL command
6. A grid is being set as per the field list of output file and showing result.

When the selected file has some fields it's working fine but giving problem [too long] when the selected file has so many fields.

How I could overcome the problem. Any other idea is also overcome.

Some syntax is mentioned below for your kind perusal.
xLength = LEN(THISFORM.cFileName) - 4
THISFORM.cName = SUBSTR(ALLTRIM(THISFORM.cFileName), 1, xLength)

IF USED('SelectFile')
	SELECT SelectFile
	USE
ENDIF

SET SAFETY OFF
select FieldList
ZAP
SET SAFETY ON
USE (gcWorkingDrive)+(gcYearFolder)+(THISFORM.cWorkingFolder1)+(THISFORM.cFileName) IN 0 ALIAS SelectFile SHARED

SELECT SelectFile
gnFieldcount = AFIELDS(gaMyArray)  && Create array
THISFORM.nFieldCount = gnFieldcount

IF gnFieldcount > 0
	FOR nCount = 1 TO gnFieldcount 
		SELECT FieldList
		APPEND BLANK
		REPLACE FieldList.fieldname  WITH gaMyArray(nCount,1)
		REPLACE FieldList.fieldtype  WITH gaMyArray(nCount,2)
		REPLACE FieldList.fieldwidth WITH gaMyArray(nCount,3)
		REPLACE FieldList.fielddeci  WITH gaMyArray(nCount,4)
	ENDFOR
ENDIF


THISFORM.Grid1.Visible = .F.
IF USED('OutputFile')
	SELECT OutputFile
	USE
ENDIF

IF FILE ((THISFORM.cTempDbfFile1))
	DELETE FILE ((THISFORM.cTempDbfNo1 + '.*'))
ENDIF

IF NOT EMPTY(THISFORM.cFindText)
	lcOption1 = ALLTRIM(THISFORM.cFindText)
	lcOutTable = "INTO TABLE ((THISFORM.cTempDbfFile1))"
	SELECT FieldList
	GO TOP
	SCAN WHILE NOT EOF()
		DO CASE
			CASE FieldList.Fieldtype = 'C'
				lcWhere = lcWhere + ' ' + 'SelectFile.' + ALLTRIM(FieldList.fieldname) + ' LIKE ' + "'%&lcOption1%'" + ' OR '
			CASE FieldList.Fieldtype = 'N'
				lcWhere = lcWhere + ' ' + 'STR(SelectFile.' + ALLTRIM(FieldList.fieldname) + ',' + ALLTRIM(STR(FieldList.fieldwidth)) + ',' + ALLTRIM(STR(FieldList.fielddeci)) + ')' +  ' LIKE ' + "'%&lcOption1%'" + ' OR '
			CASE FieldList.Fieldtype = 'D'
				lcWhere = lcWhere + ' ' + 'DTOC(SelectFile.' + ALLTRIM(FieldList.fieldname) + ')' +  ' LIKE ' + "'%&lcOption1%'" + ' OR '
		ENDCASE
	ENDSCAN

	lnWhere = LEN(lcWhere)-4
	lcWhere = SUBSTR(lcWhere, 1, lnWhere)

	SELECT * ;
	FROM SelectFile;
	WHERE &lcWhere;
	&lcOutTable
	USE

	USE ((THISFORM.cTempDbfFile1)) IN 0 ALIAS OutputFile
	SELECT OutputFile
	THISFORM.Grid1.ColumnCount = THISFORM.nFieldCount
	THISFORM.Grid1.RecordSource = 'OutputFile'
	lNumber = 1
	SELECT FieldList
	GO TOP
	SCAN WHILE NOT EOF()
		xNumber = ALLTRIM(STR(lNumber))
		lcFieldName = ALLTRIM(FieldList.fieldname)
		lnFieldWidth = IIF(FieldList.fieldwidth<ROUND(LEN(lcFieldName),0),ROUND(LEN(lcFieldName),0)*10,FieldList.fieldwidth*10)
		lcFieldName = ALLTRIM(FieldList.fieldname)
		THISFORM.Grid1.Column&xNumber .ControlSource = '&lcFieldName'
		THISFORM.Grid1.Column&xNumber .Width = lnFieldWidth
		THISFORM.Grid1.Column&xNumber .Header1.Caption = '&lcFieldName'
		lNumber = lNumber + 1
	ENDSCAN

	THISFORM.Grid1.SetAll("dynamicbackcolor", ;
		"IIF(RECNO()%2 = 0,RGB(194,219,222), RGB(255,255,255))", "Column")
	THISFORM.Grid1.Refresh()
	THISFORM.Grid1.Visible = .T.
	THISFORM.Optiongroup2.Value = 1
	THISFORM.OptionGroup2.Visible = .T.
ENDIF

THISFORM.Refresh()

Waiting a favourable reply.

COMPLETE THREAD

Filter data Posted by Panu Modak @ 3/28/2003 2:42:46 PM
RE: Filter data Posted by Boudewijn Lutgerink @ 3/28/2003 3:14:56 PM
RE: Filter data Posted by Ken Blum @ 3/28/2003 3:19:52 PM
RE: Filter data Posted by Panu Modak @ 3/29/2003 4:56:36 AM
RE: Filter data Posted by Ken Blum @ 3/31/2003 4:59:05 PM
RE: Filter data Posted by Panu Modak @ 4/8/2003 3:35:21 PM
RE: Filter data Posted by Ken Blum @ 4/8/2003 5:45:23 PM