Can i use codeunit to modify data in a table?

asembereng · 2008-03-25

Hi
Can i use codeunit to modify data in a table. for example i have created new field but i want to copy data from the same table but different field to this new field created. Can codeunit do that? If so, how?
Thanks.

navuser1 · 2008-03-25

You can modify the table data through Codeunit or Report(Batch Job).

Captain_DX4 · 2008-03-25

You should run through NAV development tutorials.

Basically, you setup a variable:

grecTable, DataType=Record, Subtype=table

In the code window, you can enter:

grecTable.NewField := grecTable.OldField;
grecTable.MODIFY;

This would be the simplest method, regardless of any validates on the fields and code in the OnModify.

We've now openned Pandora's Box for you. Now it's up to you to make sure you review your tutorials and documentation to make sure you don't destroy your database.

kolaboy · 2008-03-26

I am having similar problem.
Where actually can one place your code.
I created a new code unit with you code place on the onrun trigger.
but when i click on run, i have an error which reads "Employee no. does not exist"
Any ideas please? where do you thing the code is best place? any other sample code ?

Alex_Chow · 2008-03-26

1. Get the record of the table you're trying to modify
2. Set the value on the record
3. MODIFY the record

kolaboy · 2008-03-26

This is what i an doing:

Documentation()

OnRun()
IF Emp.GET("No.") THEN
Emp."SSNo." := Emp."No.";
Emp.MODIFY;

what am i not doing correctly.
Its still gives me the same error.

tinoruijs · 2008-03-26

It should be something like this:

Emp.RESET;
IF Emp.FINDSET(TRUE, FALSE) THEN BEGIN;
  REPEAT;
    Emp."SSNo." := Emp."No.";
    Emp.MODIFY(TRUE);
  UNTIL Emp.NEXT = 0;
END;

DenSter · 2008-03-26

IF Emp.GET('Some Value') THEN BEGIN
  Emp."SSNo." := Emp."No."; 
  Emp.MODIFY;
END;

1:
You have to pass the GET method an actual value.

2:
Always use BEGIN and END statements. You NEED them when you have more than one line of code to execute, and I think it's a good habit to just always use them, so you can explicitly tell which lines are meant to be executed.

kolaboy · 2008-03-26

I have use one value in the get and it does what i wanted, but how can i do it for all automatically?

i have tried tinoruijs code but is giving me eroror which says " You have specified an unknown variable FINDSET when i try to compile it. What is the datatype of FINDSET?

tinoruijs · 2008-03-26

kolaboy wrote:

I have use one value in the get and it does what i wanted, but how can i do it for all automatically?

Have you tried the I wrote above?
Then you will loop through all employees.
If you want to filter on the employees, you will have to use something like:

Emp.SETRANGE(blnField, TRUE);

tinoruijs · 2008-03-26

DenSter wrote:

I think it's a good habit to just always use them, so you can explicitly tell which lines are meant to be executed.

Certainly a good habit. I find it also makes the code easier to read.
Another good habit is using ; after every line when possible. The ; is used in the debugger and code coverage. If you don't use the ;, the debugger will skip the line. And Code Coverage won't mark it read.

kolaboy · 2008-03-26

I have tried both but the FINDSET is not working for me. Giving the same error.

DenSter code does it but on only the one specified. I need it done on all at once.

Emp.SETRANGE(blnField, TRUE);

what is the datatype of blnField i made it boolean but does not work.Any ideas please

tinoruijs · 2008-03-26

kolaboy wrote:
I have tried both but the FINDSET is not working for me. Giving the same error.

DenSter code does it but on only the one specified. I need it done on all at once.
Emp.SETRANGE(blnField, TRUE);
what is the datatype of blnField i made it boolean but does not work.Any ideas please

Emp.SETRANGE(blnField, TRUE); was just an example of a filter on a boolean field.

Which version are you on? FINDSET is present in NAV from version 4.0 SP1 and later.

Maybe the code shoud be:

Emp.RESET; 
IF Emp.FIND('-') THEN BEGIN; 
  REPEAT; 
    Emp."SSNo." := Emp."No."; 
    Emp.MODIFY(TRUE); 
  UNTIL Emp.NEXT = 0; 
END;

kolaboy · 2008-03-26

I am working on 4.0.
I did this but it gives me error like "Employee does not exist. Identification fields and value: No. = '463',SSNo.= '463'. " I have both No. and SSNo. as primary key.

what is the problem?
Thanks

tinoruijs · 2008-03-26

kolaboy wrote:

I am working on 4.0.
I did this but it gives me error like "Employee does not exist. Identification fields and value: No. = '463',SSNo.= '463'. " I have both No. and SSNo. as primary key.

what is the problem?
Thanks

Is SSNo. part of the primary key?
Why?

kolaboy · 2008-03-26

Sorry i has worked. I just remove the new no. which is SSNo as a primary key and it worked. How then can it work when both are primary keys?

tinoruijs · 2008-03-26

kolaboy wrote:

I am working on 4.0.
I did this but it gives me error like "Employee does not exist. Identification fields and value: No. = '463',SSNo.= '463'. " I have both No. and SSNo. as primary key.

what is the problem?
Thanks

The problem is that you have both No. and SSNo. as primary key.

tinoruijs · 2008-03-26

kolaboy wrote:

Sorry i has worked. I just remove the new no. which is SSNo as a primary key and it worked. How then can it work when both are primary keys?

Why do you want them both in the primary key?
Certainly when they are both the same, it has no use.

What are you trying to achieve with both the fields in the primary key?
Can't SSNo just be a secondary key?
:-k

kolaboy · 2008-03-26

I made SSNo. a primary key.

now i am trying it this way:

Emp.RESET;
IF Emp.FIND('-') THEN BEGIN;
  REPEAT;
    Emp."No." := Emp."SSNo.";
    Emp.MODIFY(TRUE);
  UNTIL Emp.NEXT = 0;
END;

Error " Employee No. 55123 does not exist"
The issue is No. = 123 and SSNo. = 55123, now i want to replace no. = 123 by no. = 55123, This is what i am trying to acheive.
Any ideas.
Thanks

tinoruijs · 2008-03-26

kolaboy wrote:

I made SSNo. a primary key.

It is not recommended to change the primary key. Why do you want to do that?

kolaboy wrote:
now i am trying it this way:
Emp.RESET;
IF Emp.FIND('-') THEN BEGIN;
  REPEAT;
    Emp."No." := Emp."SSNo.";
    Emp.MODIFY(TRUE);
  UNTIL Emp.NEXT = 0;
END;
Error " Employee No. 55123 does not exist"
The issue is No. = 123 and SSNo. = 55123, now i want to replace no. = 123 by no. = 55123,
Any ideas.
Thanks

Are you sure No. isn't in the primary key anymore?
If it is in the primary key, you should use RENAME instead of MODIFY.

kolaboy · 2008-03-26

No. is the primary key. SSNo. is now a secondary key.
How can the code work the way i just posted?

tinoruijs · 2008-03-26

kolaboy wrote:

No. is the primary key. SSNo. is now a secondary key.
How can the code work the way i just posted?

Before helping you with the code...
What do you want to achieve? And why?
(Perhaps I shoud have asked this question straight away..

)

I guess you want to change the primary key into a new number and store the old primary key in the field where the new number was stored? :-k

kolaboy · 2008-03-27

I guess you want to change the primary key into a new number and store the old primary key in the field where the new number was stored?

This is exactly what i want to achieve. Any idea please?
Thanks

tinoruijs · 2008-03-27

kolaboy wrote:

I guess you want to change the primary key into a new number and store the old primary key in the field where the new number was stored?

This is exactly what i want to achieve. Any idea please?
Thanks

It's better to use another new field called "Old No.".

But the looping through the records and renaming them, could be tricky, depending on the range of the old and new numbers.

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

kolaboy · 2008-03-27

Its the values of No. that i want to change using rename with the values of SSNo. and then give the values of No. to SSNo.

How about this:

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

But this give an error which runs thus: "Employee No. '' does not exist"
Any idea?

tinoruijs · 2008-03-27

I think the MODIFY shoud be before the RENAME.

markborges · 2008-03-27

Hi kolaboy,

In the past I tried to do exactly what you are doing, but RENAME function did not work properly for me. I'm not sure but I think Navision gets really pissed of when asked to RENAME a record thru code.

The, the best turnaround for this (that I found) is that, if you REALLY need to rename your record, then you should create a new one with the new data and then delete the old one. This applies if you are trying to change any fields on the primary key.

When you create the new record, use TRANSFERFIELDS, and just change the Primary Key before INSERTING... and don't forget to DELETE the old record.

kolaboy · 2008-03-27

You mean mean this:

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

This still gives the same error.

tinoruijs · 2008-03-27

Good suggestion by Marcelo.

But..
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...

The 'beauty' of RENAME is that it also renames the connected (by TableRelation) records.

tinoruijs · 2008-03-27

To make the code below work, you have to add a field "Old No.".
Because else SSNo. will never be empty and a strange loop through the records can be possible. Depending on the old and new range of the numbers.

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); 
      Emp.RENAME("TemSSNo."); 
    END; 
  UNTIL Emp.NEXT = 0; 
END;

kolaboy · 2008-03-27

tinoruijs wrote:
To make the code below work, you have to add a field "Old No.".
Because else SSNo. will never be empty and a strange loop through the records can be possible. Depending on the old and new range of the numbers.
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); 
      Emp.RENAME("TemSSNo."); 
    END; 
  UNTIL Emp.NEXT = 0; 
END;

I have use the above code but it seems its doing it for one record in the middle of the file and the last three. Its skipping the rest and not doing anything.
What is may be wrong?

Can i use codeunit to modify data in a table?

Comments

Categories