Dear All,

I have written a form which consists of a list, 2 grids, and some command buttons. Which is used to
1. count the numbers of some inspections, and show them in the list,
2. As the inspection item in the list is selected, the sample codes associated with this inspection are shown in the Grid.
3. After the user assigns the sample codes to be inspected to a staff, then a command button issue the actions of change the states of the data in the table.

At the list init stage, a sql statement is executed to show show the inspect items in the list. the code associated follows,
SET DELETED ON
SET SAFETY OFF

**set step on
** ­pºâ©Ò¦³ªº¥i¬£¤u¼Ë«~¼Æ¨Ã±N¨ä¦s©óANA_SUM¤¤(ºâ¥X¨C¤@­ÓÀË´ú¶µ¥Ø¦³¦h¤Ö¼Ë«~¶·­n¬£¤u)
public dimension tmp(3,30)

select spitem.ins_id, count(sampnum),inspect.Eng_nam;
from spitem , inspect ;
into array tmp;
where spitem.pre_stat="5";
and spitem.ins_id=inspect.ins_id;
group by spitem.ins_id;
order by spitem.ins_id

origdim=_tally
dimension tmp(origdim+2,3)
SELECT count(SAMPNUM) into array num1 from rep_samp where state="1" && HAVE TO BE "µÑ¨ú"ED
SELECT count(SAMPNUM) into array num2 from rep_samp where state="3" && HAVE TO BE "®ø¤Æ"ED
tmp(origdim+1,1)="PP01"
tmp(origdim+1,2)=num1(1)
tmp(origdim+1,3)="µÑ¨ú"
tmp(origdim+2,1)="PP02"
tmp(origdim+2,2)=num2(1)
tmp(origdim+2,3)="®ø¤Æ"

thisform.list1.rowsource="tmp"


2. The interactiveChange event of the list execute the following codes to show the samples codes in two grids,
Because the sample codes come from two tables, therefore, one grid one table is arranged. Depending on which inspect item is selected,
the related grid is set to visible.
public ins_idr
store this.value to ins_idron
if ins_idron="PP01 "
select rep_samp
set filter to state="1"
go top

else
if ins_idron="PP02 "
select rep_samp
set filter to state="3"
go top
thisform.grid1.visible=.T.
else
select spitem
set filter to ins_id=ins_idron .and. pre_stat="5"
go top
thisform.grid1.visible=.F.
endif
endif
thisform.refresh()


3. the user assign work to staff. Items in the grid with a field set to 1 by a check box to indicate being assigned.
And the states of the records with the sample code and inspect item then is changed to a number indicated the situation,
******************************************************************************************************************************************
****** BEGIN OF MAIN PROCEDURE ***** BEGIN OF MAIN PROCEDURE***** BEGIN OF MAIN PROCEDURE***************
******************************************************************************************************************************************
**==This block of code is for idea briefing=============== && Ron's modification , 30/5/1997, overall re-writing, focus on structurising
if messagebox("½T©w«ü¬£¦¹¶µ¤u§@¶Ü?",1,"¬£¤u½T©w")=1
newabsanaid=get_new_abs_ana_id()
newanaid=get_new_ana_id(ins_idron) && ¦¹¬q¤D¬°¦¹¦¸¬£¤u¨ú±o¬£¤u³æ¸¹

SELECT ANA_ASS && Appending new assignment to ANA_ASS
APPEND BLANK
replace ana_id with newanaid
replace ana_id_abs with newabsanaid
REPLACE EMP_ID WITH THISFORM.Text1.Value
REPLACE ASS_DAY WITH THISFORM.Text2.Value
replace ins_id with ins_idron
REPLACE QC_MEMO WITH THISFORM.Edit1.Value

TABLE_OPENED=OPEN_ITEM_TABLES(ins_idron) && for explording items
IF TABLE_OPENED=.T.
DO CASE
CASE ins_idron="PP01"
SELECT rep_samp
set filter to state="1" .and. ana_ass=1 && filter out for only those assigned
go top
K=0
do while !eof() && find out the ins_id related to the sample code, FROM THE FIRST SAMPLE CODE
replace state with "2"
select spitem
set filter to sampnum=rep_samp.sampnum .and. pre_stat="1" &&»Ý­nµÑ¨úªÌ
go top
do while ! eof() && process from the first ins_id after filtering out, until the last one.
if inlist(ins_id, "C015","C006","CS06","C012","C013","CS01","CS02","CS03",;
"CS04","CS05","CS06","C001","C002","C003","C004","C005")
replace pre_stat with "2" && 2 stands for µÑ¨ú¤¤
endif
skip
enddo
k=k+1
pag=ceiling(k/10)
=append_to_item_table(newanaid,rep_samp.sampnum,PAG) select anaid_sa
append blank
replace ana_id with ana_ass.ana_id
replace sampcode with rep_samp.sampnum
select rep_samp
skip
enddo

CASE ins_idron="PP02"
SELECT rep_samp
set filter to state="3" .and. ana_ass=1
K=0
do while !eof()
go top
replace state with "4"
select spitem
set filter to sampnum=rep_samp.sampnum .and. pre_stat="3" &&»Ý­n®ø¤ÆªÌ
go top
do while ! eof() && (SPITEM) process from the first ins_id after filtering out, until the last one.
if INLIST(ins_id,"A012","A013","A014","A015","B037","B043","B044","B045","B047",;
"B050","B053","B054","B058","B066","B071","B072","C012","C013") ;
.or. inlist(ins_id,"CS01","CS02","CS03","CS04","CS05","CS06","C001","C002","C003",;
"C004","C005")
replace pre_stat with "4" && 4 stands for ®ø¤Æ¤¤
endif
skip
enddo
k=k+1
pag=ceiling(k/10)
=append_to_item_table(newanaid,rep_samp.sampnum,PAG)
select anaid_sa
append blank
replace ana_id with ana_ass.ana_id
replace sampcode with rep_samp.sampnum
select rep_samp
skip
enddo
OTHERWISE
select spitem
set order to ans_state
set filter to ins_id=ins_idron and ana_ass=1 and pre_stat="5" &&¥iª½±µ¬£¤u, ©Î¤w§¹¦¨®ø¤ÆorµÑ¨ú«á¥i¬£¤uªÌ, ¦Ó¥B¤w³Q¬£¤u(ana_ass=1)ªÌ
go top
k=0 && K¥Î¥H°O¿ý¨ì©³¦³¦h¤Öµ§°O¿ý³Q·s¼W
do while !eof()
** scan for ins_id=left(ana_sum.ins_id,4) and ana_ass=1 and pre_stat="5"
IF INLIST(ins_id,"A002","A003","A004","A005","A006","A007","A008","A009","A010","A012")
FOR M=1 TO 4
k=k+1
pag=ceiling(k/12)
=append_to_A_item_table(newanaid, spitem.ass_id, spitem.sampnum,pag)
NEXT
ELSE
k=k+1
pag=ceiling(k/10)
=append_to_item_table(newanaid, spitem.sampnum,pag)
ENDIF

select anaid_sa && Appending to ana_sa
append blank
replace ana_id with ana_ass.ana_id
replace sampcode with spitem.sampnum
select spitem
replace pre_stat with "6"
skip
enddo
**endscan
ENDCASE

** APPEND TABLES *MAIN AND *PAGE
SELECT MAINT
APPEND BLANK
REPLACE ANA_ID WITH NEWANAID
REPLACE ANA_EMP WITH THISFORM.TEXT1.VALUE
replace ana_dayb with date()+1
REPLACE PAG_SUM WITH CEILING(K/10)

** APPEND TABLES *PAGE
SELECT PAGET
FOR I=1 TO CEILING(K/10)
APPEND BLANK
REPLACE ANA_ID WITH NEWANAID
REPLACE PAG_NO WITH I
NEXT I


**SELECT Ana_Sum
THISFORM.List1.Init()
THISFORM.Refresh()

ELSE && WHEN NO TABLE ARE OPENED.
=MESSAGEBOX("¦¹¶µ¥Ø¤£¾A¥Î©ó¦¹¬£¤u")
ENDIF
ENDIF && IF MESSAGEBOX()=.T.

******************************************************************************************************************************************
****** END OF MAIN PROCEDURE ***** END OF MAIN PROCEDURE***** END OF MAIN PROCEDURE**********************
******************************************************************************************************************************************


However, as I executed these functions, the speed was quite slow. It even takes about 5 minutes to complete a cycle of assignment. The platform for execution are windows 95 based PC, and a novel server to keep the data. The size of table spitem is around 10 fields, and with near 30,000 records.

I need some suggestion to restructure the source codes, so that the speed can be improved. Many thanks for your time to read this through!

One additional question, does Novell affect the execution speed? Will it be better to port the database to a 95 machine?

Many thanks in advance!