CodeUnit guidence

lavakumar

I am first time programer for Navision and need some guidence

I have two tables in the Navision Sql database one is navisions own table and the other one is a new table created by me. Now on a scheduled time every day I want to transfer rows from my new table to navisions table and delete rows in my table after the transfer is complete.

can some one give me a sample codeunit example to carry out this job

Thanks

Kumar

Dmitry_Chadaev

Hi Kumar,

May be this could help...

Make a new codeunit (you might need a SingleInstance codeunit - read about them in Navision programmer's guides).

Try to declare an Automation variable there for Navision Timer 1.0 (dll) - it is included into a standart Navision installation. In its properties define that it goes "WithEvents". Now you can code on the event triggers.

You may set the time interval when the event trigger should be called when you start this codeunit for example.

dmccrae

There's 2 approaches you could take but the actually copying of the fields depends on the layout of the two tables. Here I assume they are the same.

The Navision way:

You need to create a Navision table to match the SQL table and set the LinkedObject property to Yes. This wont actually create a table in SQL but will allow Navision to use it as a regular table. So you can write code like this - sorry cant remember exact function syntax:

IF recFrom.FIND('-') THEN
BEGIN
  REPEAT
    recFrom.TRANSFERFIELDS(recTo);
    recTo.INSERT;
  UNTIL recFrom.NEXT = 0;
END
recFrom.DELETEALL;

You would have to schedule this with a timer or use NAS.

The SQL Way:

Make a sheduled SQL job and run a simple script:

BEGIN TRAN
INSERT INTO NavTable SELECT * FROM MyTable
DELETE FROM MyTable
COMMIT

lavakumar

I am extremely thank ful to Dimitry and Tobertc for their guidence this was the perfect guidence I needed. However as an extension to my earlier help request!

If RecTo table has lot more fields than RecFrom table and I want to map spaecific fields for example

RecTo has fields A,B,C,D,E,F
and
RecFrom has fields X,Y,Z only

and I want to map A to X,C to Y and E to Z. Of course their respective field types are same.

then how does the code in the Navision way will look like?

Thanks and best regards

Kumar

RobertMo

RecTo.A := RecFrom.X;
RecTo.C := RecFrom.Y;
RecTo.E := RecFrom.Z;

or using validate function:
RecTo.VALIDATE(A,RecFrom.X);
...

lavakumar

Thanks you very much RobertMo. I am beginer to Navision so one more question(do not laugh if it is silly):

In codeunit if I use following

IF recFrom.FIND('-") THEN


The the compilation error says that I have to declare a global variable
recFrom. Now I do not know how to make a table as global variable.

Thanks and regards

Kumar

RobertMo

I thought that you have already defined it ?
Select menu View / C/AL Globals and enter:

Name=recFrom
DataType=Record
Subtype=<Your table>

<Your table> can be selected from list or you type in the Table ID or TableName. You can type only part of name, but it must be selective...

How is your recTo defined ? You should define vars before writing code. Then you can use F5 (Symbol Menu) to select from defined varibles (records and fields), functions, etc. to build your code.

lavakumar

Thank you very much RobertMO. It was so kind of you to guide me through some basic C/SIDE programing.

I wrote the following code and compiled it declaring the variables as told by you.


IF recFrom.FIND('-') THEN
BEGIN
REPEAT
recFrom.TRANSFERFIELDS(recTo);
recTo.INSERT;
UNTIL recFrom.NEXT = 0;
END


It compiles well. But nothing happens. No record is populated in the recTo table. Do I have to set up some properties like InitValue etc. of
table or key field .

Thank you very much and best regards

Kumar

kine

Problem is in transferfield

Definition from Help>

Record.TRANSFERFIELDS(FromRecord [, InitPrimaryKeyFields])

I means that>

recFrom.TRANSFERFIELDS(recTo);  //is wrong

recTo.TRANSFERFIELDS(recFrom); //is OK

lavakumar

Thank you very much Kine. But unfortunately the code still does not work
Instead it gives me folllowing error

recTo indexNo 0 already exists

In the recTo table indexNo is the first field and the table is empty.

Best regards

Kumar

kine

lavakumar wrote:

recTo indexNo 0 already exists

In the recTo table indexNo is the first field and the table is empty.

May be, that fields in recTo have another FieldNo that in recFrom... TransferFields transfer values from fields with same no. as destination fields.

Can you post there structure of your two tables? (list of fields, types, field nos.)
:?:

lavakumar

Thank you very much for the help. Here are the details of fields in the two tables:

recFrom table

No , Integer , Field No 1
Identification , Text , Field No 2
OrderNumber , Text , Field No 3
Date , Date , Field No 4
Inventory , Text , Field No 5
Qty , Integer , Field No 6


recTo table

Serial , Integer , Field No 1
IdNumber , Text , Field No 2
OrderNumber , Text , Field No 3
Date , Date , Field No 4
SKU , Text , Field No 5
Qty , Integer , Field No 6



This is the code


IF recFrom.FIND('-') THEN
BEGIN
REPEAT
recTo.TRANSFERFIELDS(recFrom);
recTo.INSERT;
UNTIL recFrom.NEXT = 0;

recFrom.DELETEALL;
END


This is the error message

recTo Serial 0 already exists

regards

Kumar

kine

OK, is recTo empty table? Or you want to replace existing records? This code is for inserting all from recFrom into recTo when recTo is empty table. If there is something, there will be this kind of error.

lavakumar

recTo is an emply table in fact a newly created table.

Kumar

RobertMo

please check primary key fields. (first row in Keys). number of fields in keys sohuld match for this example (beside other things that should be checked usually).

DenSter

Try changing

recTo.INSERT;

to this:

IF NOT recTo.INSERT THEN
  recTo.MODIFY;

lavakumar

Thanks sir for the reply . If I change to following code


IF recFrom.FIND('-') THEN
BEGIN
REPEAT

recTo.TRANSFERFIELDS(recFrom);
IF NOT recTo.INSERT THEN
recTo.MODIFY;

UNTIL recFrom.NEXT = 0;

recFrom.DELETEALL;
END

then codeunit compiles well and on runing it gives no error message but no record is inserted in recTo table. However last statement executes and rows in recFrom table are deleted.

Regards

Kumar

RobertMo

like i said check the primary keys. be sure that the values in PK are distinctive for recTo.

eksekj

maybe to late and you get by your own

but i would write the code

IF recFrom.FIND('-') THEN
BEGIN
REPEAT

recTo.TRANSFERFIELDS(recFrom,true);

IF recTo.INSERT THEN; // this wiil not acure an error if cant insert


UNTIL recFrom.NEXT = 0;

recFrom.DELETEALL;
END