proper looping while updating
Aldevinas Katkus
10-11-23
Operating system: macOS
Simple(?) question:
How should we update a number of related records in a loop?

As far as I understand the table "cursor" moves to the new value, does not stay in the same place in the following case:
If we have SDNr values in the table like 79,79,78 and we would like to "update FuelSDrsr set FuelSDrsr.SDNr=81 where FuelSDrsr.SDNr=79" only the first occurence with SDNr=79 will be updated.


Is it a bug or feature? I (wrongly?) believe cursor/pointer should not move to the new record while we are looping the table if we do not call SetLoopPosition, ReadFirstxxx etc.

TrHs = true;
While LoopKey("SDNr",FuelSDrsr,1,TrHs) begin
if (FuelSDrsr.SDNr!=oldNr) then begin TrHs=false; end;
if (TrHs) then begin
RecordCopy(oldFuelSDrsr,FuelSDrsr);
FuelSDrsr.SDNr=newNr;
if (RecordUpdate(oldFuelSDrsr,FuelSDrsr,false)==0) then begin end; //why this moves table pointer to new position?
end;
end;

I have looked at IVVcOKTool2.hal where we have
if (found) then begin
RecordCopy(oldTBIVr,TBIVr);
TBIVr.Invoice = IVp.SerNr;
if (RecordUpdate(oldTBIVr,TBIVr,false)) then begin end;
// StepBack(TBIVr); stops loop too quickly
end;

and result is the same: if we change project invoice number to greater one, then only the first project transaction record will be updated with the new invoice number, others will have the old (wrong) invoice number.
Aldevinas Katkus
10-12-23
I guess there is same bug with rental invoices: will be there wrong links depends on whether the new inoice number will be higher or lower than the old one, but I do not have rental configured and cannot check.

I will answer myself:


//proper way of updating one-to-many linked records ("on update cascade" in SQL terms)

FuelSDrsr.SDNr=oldNr;
While ReadFirstKey("SDNr",FuelSDrsr,1,true) begin
RecordCopy(oldFuelSDrsr,FuelSDrsr);
FuelSDrsr.SDNr=newNr;

if (RecordUpdate(oldFuelSDrsr,FuelSDrsr,false)==0) then begin end;
ResetLoop(FuelSDrsr);//Is it necessary?
FuelSDrsr.SDNr=oldNr;
end;
Aldevinas Katkus
11-8-23
Update: because of this or/and missing IVVcRecordRemoveAfter() we have over 60 project transactions with wrong/nonexisting invoice number.
Tendai Mataranyika
11-12-23
Hi Aldevinas

I have assigned to Martins to have a look, will update once I get feedback
Aldevinas Katkus
12-13-23
FYI: Bug reported and verified: SL>>Invoices>>changing invoice number - lost referential integrity
Brittany McGrath
12-19-23
Hi Aldevinas,
Providing an update - It is to my understanding that this issue is still under investigation by our Development team.
We will come back to you once we have further information.
Thank you for your patience.
4-9-24
Hi Aldevinas,

Latest update - We have given this issue a push now to get it fixed as soon as possible.
We will come back to you once we have further information.

Thank you for your patience!
Brittany McGrath
11-21-24
Hi Aldevinas, I've been advised that this bug is no longer repeatable and appears to be fixed now.
Can you pls confirm if you are still experiencing such issue?
Thank you.
Leave Comment
You can subscribe to notifications for this post by selecting the 'star' icon on the top right corner of the post.
Back to the list
Latest Posts
Ibrahim Bashir
Hello, System >> Settings >> Optional Features >> Idle User Timeout (Minutes) this is configured to 15 minutes in my setup. is there a way to make this works per user? or skip s...
04:56 29 Nov 2024
Reino Botha
Please check your version of android if it is the same as the server you are connecting too. ...
08:24 21 Nov 2024