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
Hi Piotr! This is still not a supported feature and we are not sure if it will be with this design. However, you can activate browse filtering per register in Technics>>Settings>>Regis...
09:53 29 Apr 2024
Hello Dave! Regarding Standard ID password expiry. Does the users have ticked checkbox on person record "Password never expires"? By activating this all users should message to reset pass...
09:49 29 Apr 2024