CodeUnit guidence

lavakumarlavakumar Member Posts: 21
edited 2004-07-09 in Navision Attain
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

Comments

  • Dmitry_ChadaevDmitry_Chadaev Member Posts: 52
    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.
    Best regards,
    Dmitry
  • dmccraedmccrae Member, Microsoft Employee Posts: 144
    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
    
    Dean McCrae - Senior Software Developer, NAV Server & Tools

    This posting is provided "AS IS" with no warranties, and confers no rights.
  • lavakumarlavakumar Member Posts: 21
    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
  • RobertMoRobertMo Member Posts: 484
    RecTo.A := RecFrom.X;
    RecTo.C := RecFrom.Y;
    RecTo.E := RecFrom.Z;

    or using validate function:
    RecTo.VALIDATE(A,RecFrom.X);
    ...
               ®obi           
    ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
  • lavakumarlavakumar Member Posts: 21
    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
  • RobertMoRobertMo Member Posts: 484
    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.
               ®obi           
    ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
  • lavakumarlavakumar Member Posts: 21
    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
  • kinekine Member Posts: 12,562
    Problem is in transferfield

    Definition from Help>
    Record.TRANSFERFIELDS(FromRecord [, InitPrimaryKeyFields])
    

    I means that>
    recFrom.TRANSFERFIELDS(recTo);  //is wrong
    
    recTo.TRANSFERFIELDS(recFrom); //is OK
    
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • lavakumarlavakumar Member Posts: 21
    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
  • kinekine Member Posts: 12,562
    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.)
    :?:
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • lavakumarlavakumar Member Posts: 21
    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
  • kinekine Member Posts: 12,562
    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.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • lavakumarlavakumar Member Posts: 21
    recTo is an emply table in fact a newly created table.

    Kumar
  • RobertMoRobertMo Member Posts: 484
    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).
               ®obi           
    ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
  • DenSterDenSter Member Posts: 8,307
    Try changing
    recTo.INSERT;
    
    to this:
    IF NOT recTo.INSERT THEN
      recTo.MODIFY;
    
  • lavakumarlavakumar Member Posts: 21
    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
  • RobertMoRobertMo Member Posts: 484
    like i said check the primary keys. be sure that the values in PK are distinctive for recTo.
               ®obi           
    ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
  • eksekjeksekj Member Posts: 4
    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
Sign In or Register to comment.