|
| SET INDEX ALL |
 |
Wed, 7 Jun 2006 01:30:54 +1200 |
Hi
I perform a lookup on a current file but sometimes the lookup works but then
fails to keep the pointre on the record it found.
I trap the issue and make the procedure fail.
Anyone see how I could improve this code?
Thanks in advance :)
FILE "FRETAIL":INDEX "RetailClient_No"
IF LOOKUP (RCN%,RetailClient_No.FRETAIL) THEN
SET INDEX ALL
SELECT CURRENT LOCK FILE "FRETAIL"
IF RCN% <> RetailClient_No.FRETAIL THEN
REQUEST "There is a problem. The client number = "
+ STR$
(RCN%,"999999"),"but we are looking at =" + STR$
(RetailClient_No.FRETAIL,"999999"),100
REQUEST "I will cancel the procedure","If this
persists
please call support on 07 572-1821",100
Fail$ = "Y"
END IF
more code here but not relevant
|
| Post Reply
|
| Re: SET INDEX ALL |
 |
Thu, 08 Jun 2006 17:34:12 +010 |
Ian Stephenson wrote:
> Hi
>
> I perform a lookup on a current file but sometimes the lookup works but
then
> fails to keep the pointre on the record it found.
> I trap the issue and make the procedure fail.
> Anyone see how I could improve this code?
>
> Thanks in advance :)
>
> FILE "FRETAIL":INDEX "RetailClient_No"
> IF LOOKUP (RCN%,RetailClient_No.FRETAIL) THEN
> SET INDEX ALL
> SELECT CURRENT LOCK FILE "FRETAIL"
> IF RCN% <> RetailClient_No.FRETAIL THEN
> REQUEST "There is a problem. The client number =
" + STR$
> (RCN%,"999999"),"but we are looking at =" + STR$
> (RetailClient_No.FRETAIL,"999999"),100
> REQUEST "I will cancel the procedure","If
this persists
> please call support on 07 572-1821",100
> Fail$ = "Y"
> END IF
> more code here but not relevant
First off, if RetailClient_No is a NUM field, change it to an NML. There
is generally no good reason to use NUM fields as indexes since the
release of v2, and they can have problems because of floating point
rounding errors. Try to get switch any fields that are indexed NUMs to
NML to save yourself some bother.
|
| Post Reply
|
| Re: SET INDEX ALL |
 |
Fri, 9 Jun 2006 07:29:51 +1200 |
Thanks Neil, this may tidy up a few issues we have been having. I am really
looking forward to SBNG.
:)
"Neil Robinson" <neilr@superbase.co.uk> wrote in message
news:e69ji2$car$3@ipx22096.ipxserver.de...
> Ian Stephenson wrote:
>> Hi
>>
>> I perform a lookup on a current file but sometimes the lookup works but
>> then
>> fails to keep the pointre on the record it found.
>> I trap the issue and make the procedure fail.
>> Anyone see how I could improve this code?
>>
>> Thanks in advance :)
>>
>> FILE "FRETAIL":INDEX "RetailClient_No"
>> IF LOOKUP (RCN%,RetailClient_No.FRETAIL) THEN
>> SET INDEX ALL
>> SELECT CURRENT LOCK FILE "FRETAIL"
>> IF RCN% <> RetailClient_No.FRETAIL THEN
>> REQUEST "There is a problem. The client number
= " +
>> STR$
>> (RCN%,"999999"),"but we are looking at =" + STR$
>> (RetailClient_No.FRETAIL,"999999"),100
>> REQUEST "I will cancel the
procedure","If this
>> persists
>> please call support on 07 572-1821",100
>> Fail$ = "Y"
>> END IF
>> more code here but not relevant
>
> First off, if RetailClient_No is a NUM field, change it to an NML. There
> is generally no good reason to use NUM fields as indexes since the
> release of v2, and they can have problems because of floating point
> rounding errors. Try to get switch any fields that are indexed NUMs to
> NML to save yourself some bother.
>
> Ciao, Neil
|
| Post Reply
|
| Re: SET INDEX ALL solution |
 |
Sun, 11 Jun 2006 11:43:38 -040 |
Ian,
Here is some code that may interest you. I've used it for several years to
modify fields on the fly. It writes a .SBP file and runs it. Sorry I was slow on
the uptake and didn't send it sooner.
'this modifies or adds a field, and updates the contents if there is a formula
applied;
'nffn$ field name to change or add
'tp$ field type
'fla$ formula
'hlp$ help text
'rmtx$ remark (COM text)
'c%% force UPDATE routine after modification
SUB SpecFld(nffn$,tp$,fla$,hlp$,rmtx$,c%%)
DIM qp$:qp$ = Superbase.QuotePlaceholder
IF (nffn$ <> "") THEN
DIM z$,props$,pfil$,fmt$,modf%%,rmtex$,crlf$,ixthere%%
crlf$ = CHR$ (13) + CHR$ (10)
CURFLD$ = nffn$
IF NOT FldFoun%%(CURFIL$,CURFLD$) THEN modf%% = 0 ELSE modf%% = - 1
IF modf%% THEN
IF IzIndx%%(CURFLD$) THEN ixthere%% = - 1
END IF
props$ = ";0 ;0 ;30 ;1 ;100 ;"
IF rmtx$ <> "" THEN rmtex$ = "COM " + rmtx$
IF nffn$ LIKE "note*" THEN rmtex$ = ""
IF modf%% THEN
IF fla$ = "" THEN fla$ = FIELDINFO$ (nffn$,5)
fla$ = IF (fla$ <> "","FRM " +
fla$,"")
c%% = IF (fla$ <> "", - 1,c%%)
IF (rmtex$ = "" AND FIELDINFO$ (nffn$,8) <> "")
THEN rmtex$ = "COM " + FIELDINFO$ (nffn$,8)
fmt$ = FilDefFmt$(tp$,c%%)
z$ = "MODIFY " + nffn$ + CHR$ (34) + nffn$ + fmt$ + props$ +
CHR$ (34)
ELSE
IF fla$ <> "" THEN fla$ = "FRM " + fla$
fmt$ = FilDefFmt$(tp$,c%%)
z$ = "ADD " + CHR$ (34) + nffn$ + fmt$ + props$ + CHR$ (34)
END IF
CALL Convert34(fla$)' <-- this converts "~" to CHR$(34) in case
there are
' quote marks in the string, or long path/file names
that
' Windows creates with "~" in them
Superbase.QuotePlaceholder = "@" '<-- also about long path/file
names
z$ = z$ + IF (fla$ <> "","," + CHR$ (34) + fla$ +
CHR$ (34),"")
IF hlp$ <> "" THEN z$ = z$ + "," + CHR$ (34) +
"HLP " + hlp$ + CHR$ (34)
IF rmtex$ <> "" THEN
z$ = z$ + "," + CHR$ (34) + rmtex$ + CHR$ (34)
END IF
CURFLD$ = nffn$
pfil$ = "XPROG.SBP"
IF PROGRAMFILE (pfil$) THEN CLOSE PROGRAMFILE pfil$
IF EXISTS (pfil$) THEN DELETE pfil$
OPEN pfil$ FOR OUTPUT
? @1"SUB " + "XPROG()" + crlf$;
IF ixthere%% THEN ? @1" REMOVE INDEX " + CURFLD$ + crlf$;
? @1" " + z$ + crlf$;
? @1" SAVE FILE FILE" + crlf$;
? @1" CREATE INDEX ON " + nffn$ + crlf$;
IF c%% THEN
? @1" UPDATE " + nffn$ + "." + FILE + " = "
+ nffn$ + "." + FILE + crlf$;
? @1" END UPDATE" + crlf$;
END IF
? @1" END SUB" + crlf$;
CLOSE OUTPUT
SET ERROR OFF 135,442
CALL XPROG()
CLEAR ERRNO
SET ERROR ON ALL
IF PROGRAMFILE (pfil$) THEN CLOSE PROGRAMFILE pfil$
END IF
Superbase.QuotePlaceholder = qp$
END SUB
FUNCTION FldFoun%%(CURFIL$,CURFLD$)
IF NOT (CURFIL$ = "" OR CURFLD$ = "") THEN
DIM a$(300)
FILLARRAY a$,1
FldFoun%% = IzInArray%(a$(),CURFLD$)
END IF
END FUNCTION
FUNCTION IzIndx%%(fld$)
DIM finx$
finx$ = FIELDINFO$ (fld$,3)
IF INSTR (finx$,"IX") THEN IzIndx%% = - 1
END FUNCTION
SUB Convert34(a$)
DIM qp$:qp$ = Superbase.QuotePlaceholder
DIM l%,i%,t$,w$
l% = LEN (a$)
i% = 1
WHILE i% < l% + 1
t$ = MID$ (a$,i%,1)
w$ = w$ + IF (t$ = CHR$ (34),qp$,t$)
i% = i% + 1
WEND
a$ = w$
END SUB
FUNCTION FilDefFmt$(typ$,cal%%)
SELECT CASE typ$
CASE "TXT"
IF cal%% THEN
FilDefFmt$ = ";TXT CLC;4000 "
ELSE
FilDefFmt$ = ";TXT ;4000 "
END IF
CASE "NUM"
IF cal%% THEN
FilDefFmt$ = ";NUM CLC;-999999999.0000 "
ELSE
FilDefFmt$ = ";NUM ;-999999999.0000 "
END IF
CASE "PHN"
FilDefFmt$ = ";TXT ;100 "
CASE "EXT":REM
FilDefFmt$ = ";EXT ;100 "
CASE "LOG"
FilDefFmt$ = ";LOG ;1 "
CASE "DAT"
FilDefFmt$ = ";DAT ;mmm zd,yyyy "
END CASE
END FUNCTION
Peter
On Sun, 11 Jun 2006 05:18:53 -0400, Ian Stephenson <ian@soeasy.co.nz>
wrote:
> We have changed all Indexed NUM/NMI to NML and it has worked well.
> I used the following code to identify and build the changes into a text
file
> which I then converted to SBL.
>
> If anyone else would like to use the code please feel free to do so:
> If you would like help on this please ask.
> Once run I received error 61 on some files.
> To resolve I just REORGANIZED the files.
> Check each filed modification and do not change true monetary value fields
> from NUM
>
> REQUEST "Have you opened all the files you want to","create
the modification
> code for?",130,ansd%%
> IF ansd%% = 0 THEN END
>
> GLOBAL m$(2),OpnFil$(100)
> df$ = "MODIFY "
>
> REM Fill array with names of open files
> FILLARRAY OpnFil$,0
>
> REM Send output to a text file
> OUTPUT TO "Build2.TXT"
>
> REM work through each file and build the code
> FOR f% = 0 TO 100
> Fils$ = OpnFil$(f%)
> IF Fils$ <> "" THEN ' this proc set up for 100 files
so need to trap
> and exit when the files are complete
> FILE Fils$
> Fil$ = FILE
> a% = FILEINFO (Fil$,1)
>
> REDIM m$(a%)
> FILLARRAY m$,1
> ? "REM START ===this is the start of a particular files
> modification requirement ="
> ? "clsfil$ = ~~"
> ? "IF NOT OPEN (" + "~" + Fil$ +
"~" + ") THEN clsfil$ = ~Y~: OPEN
> FILE SHARE ,1~" + Fil$ + ".SBF~"
> REM =================================
> ? "File " + "~" + Fil$ + "~"
> ? "SELECT FIRST"
> NEWLINE 1
> FOR b% = 0 TO (a% - 1)
> IF NOT (m$(b%) = "") THEN
> IF ( FIELDINFO$ (m$(b%),0) = "NMI" OR
FIELDINFO$
> (m$(b%),0) = "NUM") AND FIELDINFO$ (m$(b%),3) = "IXD"
THEN
> ? "REMOVE INDEX " + m$(b%)
> rem make next two lines into one
> ? df$ + m$(b%) + "~" + m$(b%) +
";" + "NML" + "
> " + FIELDINFO$ (m$(b%),1) + " " + TRIM$ ( FIELDINFO$
(m$(b%),2)) + " " + ";"
> + "z999999." + "~,~" + TRIM$ ( FIELDINFO$ (m$(b%),5)) +
"~"
>
> ? "CREATE INDEX ON " + m$(b%) +
"." + Fil$
>
> END IF
> END IF
> NEXT
>
> rem the next line needs to be moved to after all modifications
> ? "SAVE FILE " + "~" + Fil$ + "~"
> ? "IF clsfil$ = ~Y~ THEN CLOSE FILE ~" + Fil$ +
"~"
> ? "=========================="
> NEWLINE 2
>
> END IF
> NEXT
>
> Rem Open the text file (all ~ need to be replaced with " and the
> instructions need to be grouped per file
> rem i.e all INDEX commands need to be grouped together for each file
> CLOSE OUTPUT
> CALL "Notepad.exe" + " Build2.TXT"
> REQUEST "Done","",100
>
>
>
> "Ian Stephenson" <ian@soeasy.co.nz> wrote in message
> news:e69trk$fmg$1@ipx22096.ipxserver.de...
>> Thanks Neil, this may tidy up a few issues we have been having. I am
>> really looking forward to SBNG.
>> :)
>> "Neil Robinson" <neilr@superbase.co.uk> wrote in
message
>> news:e69ji2$car$3@ipx22096.ipxserver.de...
>>> Ian Stephenson wrote:
>>>> Hi
>>>>
>>>> I perform a lookup on a current file but sometimes the lookup
works but
>>>> then
>>>> fails to keep the pointre on the record it found.
>>>> I trap the issue and make the procedure fail.
>>>> Anyone see how I could improve this code?
>>>>
>>>> Thanks in advance :)
>>>>
>>>> FILE "FRETAIL":INDEX "RetailClient_No"
>>>> IF LOOKUP (RCN%,RetailClient_No.FRETAIL) THEN
>>>> SET INDEX ALL
>>>> SELECT CURRENT LOCK FILE "FRETAIL"
>>>> IF RCN% <> RetailClient_No.FRETAIL THEN
>>>> REQUEST "There is a problem. The client
number = " +
>>>> STR$
>>>> (RCN%,"999999"),"but we are looking at =" +
STR$
>>>> (RetailClient_No.FRETAIL,"999999"),100
>>>> REQUEST "I will cancel the
procedure","If this
>>>> persists
>>>> please call support on 07 572-1821",100
>>>> Fail$ = "Y"
>>>> END IF
>>>> more code here but not relevant
>>>
>>> First off, if RetailClient_No is a NUM field, change it to an NML.
There
>>> is generally no good reason to use NUM fields as indexes since the
>>> release of v2, and they can have problems because of floating
point
>>> rounding errors. Try to get switch any fields that are indexed NUMs
to
>>> NML to save yourself some bother.
>>>
>>> Ciao, Neil
>>
>>
>
>
>
|
| Post Reply
|
| Re: SET INDEX ALL solution |
 |
Sun, 11 Jun 2006 21:18:53 +120 |
We have changed all Indexed NUM/NMI to NML and it has worked well.
I used the following code to identify and build the changes into a text file
which I then converted to SBL.
If anyone else would like to use the code please feel free to do so:
If you would like help on this please ask.
Once run I received error 61 on some files.
To resolve I just REORGANIZED the files.
Check each filed modification and do not change true monetary value fields
from NUM
REQUEST "Have you opened all the files you want to","create the
modification
code for?",130,ansd%%
IF ansd%% = 0 THEN END
GLOBAL m$(2),OpnFil$(100)
df$ = "MODIFY "
REM Fill array with names of open files
FILLARRAY OpnFil$,0
REM Send output to a text file
OUTPUT TO "Build2.TXT"
REM work through each file and build the code
FOR f% = 0 TO 100
Fils$ = OpnFil$(f%)
IF Fils$ <> "" THEN ' this proc set up for 100 files so
need to trap
and exit when the files are complete
FILE Fils$
Fil$ = FILE
a% = FILEINFO (Fil$,1)
REDIM m$(a%)
FILLARRAY m$,1
? "REM START ===this is the start of a particular files
modification requirement ="
? "clsfil$ = ~~"
? "IF NOT OPEN (" + "~" + Fil$ + "~" +
") THEN clsfil$ = ~Y~: OPEN
FILE SHARE ,1~" + Fil$ + ".SBF~"
REM =================================
? "File " + "~" + Fil$ + "~"
? "SELECT FIRST"
NEWLINE 1
FOR b% = 0 TO (a% - 1)
IF NOT (m$(b%) = "") THEN
IF ( FIELDINFO$ (m$(b%),0) = "NMI" OR
FIELDINFO$
(m$(b%),0) = "NUM") AND FIELDINFO$ (m$(b%),3) = "IXD" THEN
? "REMOVE INDEX " + m$(b%)
rem make next two lines into one
? df$ + m$(b%) + "~" + m$(b%) +
";" + "NML" + "
" + FIELDINFO$ (m$(b%),1) + " " + TRIM$ ( FIELDINFO$ (m$(b%),2))
+ " " + ";"
+ "z999999." + "~,~" + TRIM$ ( FIELDINFO$ (m$(b%),5)) +
"~"
? "CREATE INDEX ON " + m$(b%) +
"." + Fil$
END IF
END IF
NEXT
rem the next line needs to be moved to after all modifications
? "SAVE FILE " + "~" + Fil$ + "~"
? "IF clsfil$ = ~Y~ THEN CLOSE FILE ~" + Fil$ + "~"
? "=========================="
NEWLINE 2
END IF
NEXT
Rem Open the text file (all ~ need to be replaced with " and the
instructions need to be grouped per file
rem i.e all INDEX commands need to be grouped together for each file
CLOSE OUTPUT
CALL "Notepad.exe" + " Build2.TXT"
REQUEST "Done","",100
"Ian Stephenson" <ian@soeasy.co.nz> wrote in message
news:e69trk$fmg$1@ipx22096.ipxserver.de...
> Thanks Neil, this may tidy up a few issues we have been having. I am
> really looking forward to SBNG.
> :)
> "Neil Robinson" <neilr@superbase.co.uk> wrote in message
> news:e69ji2$car$3@ipx22096.ipxserver.de...
>> Ian Stephenson wrote:
>>> Hi
>>>
>>> I perform a lookup on a current file but sometimes the lookup works
but
>>> then
>>> fails to keep the pointre on the record it found.
>>> I trap the issue and make the procedure fail.
>>> Anyone see how I could improve this code?
>>>
>>> Thanks in advance :)
>>>
>>> FILE "FRETAIL":INDEX "RetailClient_No"
>>> IF LOOKUP (RCN%,RetailClient_No.FRETAIL) THEN
>>> SET INDEX ALL
>>> SELECT CURRENT LOCK FILE "FRETAIL"
>>> IF RCN% <> RetailClient_No.FRETAIL THEN
>>> REQUEST "There is a problem. The client
number = " +
>>> STR$
>>> (RCN%,"999999"),"but we are looking at =" +
STR$
>>> (RetailClient_No.FRETAIL,"999999"),100
>>> REQUEST "I will cancel the
procedure","If this
>>> persists
>>> please call support on 07 572-1821",100
>>> Fail$ = "Y"
>>> END IF
>>> more code here but not relevant
>>
>> First off, if RetailClient_No is a NUM field, change it to an NML.
There
>> is generally no good reason to use NUM fields as indexes since the
>> release of v2, and they can have problems because of floating point
>> rounding errors. Try to get switch any fields that are indexed NUMs to
>> NML to save yourself some bother.
>>
>> Ciao, Neil
>
>
|
| Post Reply
|
|
|