I have a Queue with a number of records (MyQueue, PRE(MQ)).
Then a TPS file with main records (MyMainFile, PRE(MMF)).
Then a TPS file with records that have a value in of the Main file (MyRefFile, PRE(MRF)).
The MyRefFile can contain many records with the same MRF:CustNo, the file can contain more then 500 000 records.
Some of the records for the same MRF:CustNo can contain ‘AAA’ and some might contain anything else.
The MRF:CustNoExstra_Key consists of the fields: MRF:CustNo & MRF:Exstra
I do the following:
LOOP MyQueue = 1 TO RECORDS(MyQueue)
MMF: CustRecNo = MQ:CustRecNo
ACCESS:MyMainFile.FETCH(MMF:CustRecNo_Key)
MRF:CustNo = MMF:CustNo
MRF:Exstra = 'AAA'
SET(MRF:CustNoExstra_Key,MRF:CustNoExstra_Key)
LOOP WHILE ACCESS:MyRefFile.NEXT() = LEVEL:BENIGN
!Here I do some Calculations
END
END
I would have thought that the SET(MRF:CustNoExstra_Key,MRF:CustNoExstra_Key) would only loop the records that contains the MRF:CustNo and MRF:Exstra that was assigned before the SET.
However it loops all the records till the end of the MyRefFile for every MyQueue.
Is there another way to do this maybe with a get or some other set on the MyRefFile.
Read the Help. SET(K,K) starts sequential processing. As you found it does not limit it.
SET initializes sequential processing for a FILE or VIEW. SET does not get a record, but only sets up processing order and starting point for the following NEXT or PREVIOUS statements.
Suppose you had an Invoice file with a Key by Date. If you wanted to process invoices from last month August you would:
Clear(Inv:Record, -1)
Inv:Date = Date(8,1,2025)
Set(Inv:KeyByDate , Inv:KeyByDate)
Loop
Next(invoice)
If ErrorCode() Then Break.
If Inv:Date > Date(8,31,2025) Then Break. !end month?
Do Process
End
If SET(K,K) limited to just records that matched it would only show invoices dated August 1st when I need 1st to 31st. The SET(K,K) speeds processing by jumping past all the invoices dated before August.
so putting together everyone’s comments/suggestions (and adding my own), try this:
LOOP Counter = 1 TO RECORDS(MyQueue)
GET(MyQueue, Counter)
MMF:CustRecNo = MQ:CustRecNo
get(MyMainFile,MMF:CustRecNo_Key)
if errorcode() then cycle.
MRF:CustNo = MMF:CustNo
MRF:Exstra = 'AAA'
SET(MRF:CustNoExstra_Key,MRF:CustNoExstra_Key)
LOOP
next(MyRefFile)
if errorcode() or MRF:CustNo <> MMF:CustNo or MRF:Exstra <> 'AAA' then break.
!Here I do some Calculations
END
END
I like to stack the Break criteria on multiple lines. That way its easier to see the Break IF’s match up to the Set Assignments above. It also allows adding or removing them a bit easier.
MRF:CustNo = MMF:CustNo !<-- Assign Here CustNo =
MRF:Extra = 'AAA' !<-- Assign Here Extra =
SET(MRF:CustNoExtra_Key,MRF:CustNoExtra_Key)
LOOP
Next(MyRefFile)
IF ErrorCode() |
OR MRF:CustNo <> MMF:CustNo | !<-- Break check CustNo <>
OR MRF:Extra <> 'AAA' | !<-- Break check Extra <>
THEN BREAK
END
!Here I do some Calculations
END
Above I moved over the = to align with the IF <> to make it obvious they are the same on the right side
If you don’t mind another code example, here’s a version using TableManager:
!Data section
tm Tablemanager
!Code
tm.SET(MyQueue)
LOOP UNTIL tm.NEXT(MyQueue)
IF NOT tm.GET(MMF:CustRecNo_Key,MQ:CustRecNo) THEN CYCLE.
tm.AddRange(MRF:CustNo,MMF:CustNo)
tm.AddRange(MRF:Exstra,'AAA')
tm.SET(MRF:CustNoExstra_Key)
LOOP UNTIL tm.NEXT(MyRefFile)
!Here I do some Calculations
END
END
Before priming the MMF:CustRecNo do a CLEAR(MMF:Record)
Before priming the MRF: fields do a CLEAR(MRF:Record)
If you are working with multi-field keys and you are not priming all the fields in the key, you may end up looping through the wrong data or less data than you expect. Consider a key with a Year and a CustomerID, in it and you are only priming the CustomerID. It will then use whatever value may have been set for Year, or 0 if none has been set. Also make sure that you know when you need to use CLEAR() with -1 as the second parameter. I have been going through old code in a client project that could cause problems because certain values could be less than the standard CLEAR() set, so data could be excluded under certain rare circumstances
On this point of using Clear(), where it comes to Clear(Cstring), because it zero’s the Cstring, the previous data is still visible in memory and the debugger.
A numeric constant; either 1 or -1. If omitted, numeric variables are cleared to zero, STRING variables are cleared to spaces, and PSTRING and CSTRING variables are set to zero length.
It would be nice as a feature if it could zero the entire Cstring field, just to avoid anyone reading old memory spaces and pulling data out that way.
If your password is stored in a cstring and the only character you need to guess is the first character because its Zero or null, then life becomes much easier for hackers, trying to spread around networks.
Your first stop should be to read the Clarion Help on CLEAR() so you know what it can do. Second search LibSrc for a statement and see how it’s used. Much of that code was written by those that created Clarion so is a good example. You can search Accessory/LibSrc also.
The second parameter can be omitted, set to -1 or 1. -1 means that the field is cleared to the LOWEST value. A byte would be 0, a string would be cleared to <0>, a LONG to -(2^31) etc. If omitted, the value is cleared to 0 for numeric values or space for strings. If it is set to 1, the values are cleared to the HIGHEST value. A Byte would be 255, a string would be ALL(‘<255>’) and a LONG would be 2^31. As Rick pointed out, CLEARing a CSTRING can have issues.
We usually use a VIEW instead of using SET(Key,Key).
LOC:Filter CSTRING(256)
LOC:Order CSTRING(256)
MyRefFile:View VIEW(MyRefFile)
END
...
LOOP MyQueue = 1 TO RECORDS(MyQueue)
MMF: CustRecNo = MQ:CustRecNo
ACCESS:MyMainFile.FETCH(MMF:CustRecNo_Key)
!--- Some ErrorChecking...
OPEN(MyRefFile:View)
IF (ERRORCODE()) THEN
MESSAGE('Error while opening MyRefFile:View|' & ERRORCODE() & ' - ' & ERROR(),'Error',ICON:Hand)
EXIT
END
LOC:Filter = '(MRF:CustNo = ' & MMF:CustNo & ') AND (MRF:Exstra = '''AAA''')'
MyRefFile:View{PROP:Filter} = LOC:Filter
LOC:Order_Sprak = 'MRF:SomeColumn'
MyRefFile:View{PROP:Order} = LOC:Order
SET(MyRefFile:View)
LOOP
NEXT(MyRefFile:View)
IF (ERRORCODE()) THEN BREAK END
!--- Here you can do your Calculations...
END
CLOSE(MyRefFile:View)
END
Thanks for your reply.
I have never done a View manually in code, I never understood how it works and how to do it..
I will definitely try it with your code example, thanks for the detailed example code.
Will a View be faster than my SET(Key,Key) way?
I have a Key on the two fields I want to check.
Views are not necessarily faster, but they can be easier to work with due to being able to define any relations, the filter and sort easily. This is particularly so for SQL backends, but even TPS files it can be easier. Of course on simple things they can also be a bit more complex. YMMV
