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!
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
Hello, Kindly please report the bug so that we can further investigate and resolve it. Thank you. Best regards, Benjamin...
11:31 3 May 2024
Hi, No changes should have been made that would increase client's HDB file. Please try to delete HANSA.HDB from client folder and reconnect to server and check if it will grow to 30MB again. ...
10:14 30 Apr 2024