Can i use codeunit to modify data in a table?

tinoruijs

It is caused by the old and new numbers that are being hussled.
Declare lrecEmp for local record Employee.

Emp.RESET; 
IF Emp.FIND('-') THEN BEGIN; 
  REPEAT; 
    IF Emp."SSNo." <> '' THEN BEGIN; 
      "TemSSNo." := "SSNo."; 
      Emp."Old No." := Emp."No."; 
      Emp."SSNo." := ''; 
      Emp.MODIFY(TRUE); 
      lrecEmp.GET(Emp."No.");
      lrecEmp.RENAME("TemSSNo."); 
    END; 
  UNTIL Emp.NEXT = 0; 
END;

DenSter

That's just never going to work properly using FIND('-') because by renaming the record, you change where the record set is pointing. I really fail to see the logic for this requirement, but technically I'd probably use a temporary variable, move existing records into the temporary table, delete all current records, and transfer them back from the temporary table using the new primary key values.

Something like this:

// first fill the temp table
Emp.RESET; 
IF Emp.FIND('-') THEN BEGIN; 
  REPEAT; 
    // copy the record over to the temp table, with the new field as PK value
    TempEmp := Emp;
    TempEmp."No." := Emp."SSNo.";
    TempEmp.INSERT;
  UNTIL Emp.NEXT = 0; 
  Emp.DELETEALL;
END; 

// Now copy them back to the table
IF TempEmp.FIND('-') THEN BEGIN
  REPEAT
    Emp := TempEmp;
    Emp.INSERT;
  UNTIL TempEmp.NEXT = 0;
END;

tinoruijs

DenSter wrote:

That's just never going to work properly using FIND('-') because by renaming the record, you change where the record set is pointing. I really fail to see the logic for this requirement, but technically I'd probably use a temporary variable, move existing records into the temporary table, delete all current records, and transfer them back from the temporary table using the new primary key values.

Something like this:

// first fill the temp table
Emp.RESET; 
IF Emp.FIND('-') THEN BEGIN; 
  REPEAT; 
    // copy the record over to the temp table, with the new field as PK value
    TempEmp := Emp;
    TempEmp."No." := Emp."SSNo.";
    TempEmp.INSERT;
  UNTIL Emp.NEXT = 0; 
  Emp.DELETEALL;
END; 

// Now copy them back to the table
IF TempEmp.FIND('-') THEN BEGIN
  Emp := TempEmp;
  Emp.INSERT;
END;

This will certainly work.
Except again:
It all depends on records connected to the record you're copying.
So if there are for example hour registration records connected to an employee, you have to modify this records to the new employee record...

DenSter

Yes that's a problem. It sucks for performance, but if there are related records there's really no other way than to repeatedly loop through all records until all of them have the same value for the old and the new numbers. That is the only way to ensure that you cover all records. I'd put a sample code snippet in here but I don't have that kind of time today.

<edit> wait *really looking at it now* your example with the local record might actually work too, worth a try </edit>

kolaboy

tinoruijs wrote:

It is caused by the old and new numbers that are being hussled.
Declare lrecEmp for local record Employee.

Emp.RESET; 
IF Emp.FIND('-') THEN BEGIN; 
  REPEAT; 
    IF Emp."SSNo." <> '' THEN BEGIN; 
      "TemSSNo." := "SSNo."; 
      Emp."Old No." := Emp."No."; 
      Emp."SSNo." := ''; 
      Emp.MODIFY(TRUE); 
      lrecEmp.GET(Emp."No.");
      lrecEmp.RENAME("TemSSNo."); 
    END; 
  UNTIL Emp.NEXT = 0; 
END;

I am having this error: "You cannot rename a record because the new value of the field below is ''
Field: No.
Table: Employee"
I have check the table to make sure the is no empty column for the clolumn concern but ity is stiill persisting.

tinoruijs

DenSter wrote:

That's just never going to work properly using FIND('-') because by renaming the record, you change where the record set is pointing.

That's why the IF Emp."SSNo." <> '' THEN BEGIN; is used.

If you have the following list of records:
old - new.
1 - 5
4 - 2
8 - 11
10 - 14

The records being looped will be: 1, 4, 5, 8, 10, 11, 14.

Only the records 1, 4, 8 and 10 have SSNo. <> '' and will be renamed.
5, 11 and 14 have SSNo. = '' so, they will not be renamed, because they already are..

So I think it can work properly.
Or am I wrong?
:-k

kolaboy

The Below code has done what i wanted. I had wanted to rename the primary key as i posted and then change the Alphabets to two digits.

recEmployee.RESET;

recEmployee.SETFILTER("No.",'????'); // I assume the codes are always 4 characters

 IF recEmployee.findset(TRUE,TRUE)THEN

  REPEAT

    recEmployee2 := recEmployee;

    recEmployee2."SSNo." := recEmployee2."No.";

    CASE COPYSTR(recEmployee."No.",1,1) OF

      'A': codNewNo := '01';

      'B': codNewNo := '02';

      'C': codNewNo := '03';

      ...
    END;

    recEmployee2.RENAME(codNewNo + COPYSTR(recEmployee."No.",2));

    COMMIT; // this commit can be useful in case the process is interupted. With this you can continue where it left of

                   // the records already renamed will NOT be renamed again because of the SETFILTER("No.",...)

  UNTIL recEmployee.NEXT = 0;

\:D/

tinoruijs

Great! You worked it out. 8)

The COMMIT is indeed a good tip in this kind of processing.