Hi, i am a biginner in Navision and i am task to write a codeunit that will go through a table called Employee and take the data in the primary filed called “No.” and insert it into the same table in a column called “SSNo”. Of course i can do it using dataports but they want it done in codeunit. The second Assignment is to use codeunits again to go through the same table and field name called “No.” if it sees the data it should change the first character and append it to the remaining characters and rename the previous data on that field. for example… If the data is A555 it should rename it to 01555, if B it sholud append 02, C–> 03, D–>04 etc all the way to Z–>26. Can you help me on this please.
Something like this:
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;
I have been trying to do a similar thing. My problem is FINDSET. Am using 4.0 version but findset is creating errror and it needs to be defined. Can you also indicate the datatype of each variables please.
Thanks
FINDSET is only available from 4.0SP1. Use FIND(’-’) instead of FINDSET(…).
-recEmployee,recEmployee2 are records of type Employee
-codNewNo is a code20.
What is the datatype of the variable used here?
Which variable do you mean?
In my previous post I wrote the datatypes of the variables I am using in my example.
Ok i got it… Thanks
Thanks alo. The codes are working but it is extremely slow. Any suggestions on how to make it fast? Being running it for the past three hours but is still not finished. The table has about 90,000 records and two table tables has relations to it but very few data in it.
That is the problem with the RENAME-function. It has to scan the WHOLE database to search for tablerelated-fields to be changed. The only way to speed it up would be to make a program that scans the tables once and changes all. But you would spend some time to program it.
It isn’t to difficult to do acutally. You can find all the fields that have a table relationship to this table be iterating through the field table, and finding all of the fields that have a where fieldref.relation = your table no.
Once you get the set of all tables that are related to your field, you can set a filter on that table where the field equals the record you are trying to change and do a modify all.
Then instead of doing a rename on your record, you could get much better performance from doing a delete and an insert with the new value.
There is one thing I’m not sure of – I don’t know what “Relation” returns for tables that could relate to multiple tables. For example, if you do SalesHeader.relation(“No.” ) I wonder what value I would get? 27 for the Item table, or 15 for the G/L Account table, or something else?
I guess I could just try it and find out…
In that case the tablerelation is 0.
This is the reason I mentioned it is not so easy. If you are sure this is NOT the case you can risk it. But if it is used in a tablerelation to multiple tables, you have to find those manually.