Create a table with CAL code ?

azerty74azerty74 Member Posts: 82
Is it possible in a codeunit (or processing only report) in Navision, to create a table via code ?
I could create a temp table and store my data in there, but that's not what I want.
In Sql there's the Create Table statement, is there something like that in CAL ?
I don't think it is possible, please proove me wrong.
Debugging is twice as hard as writing code. Therefore if you write the code as cleverly as possible you are by definition not smart enough to debug it.

Comments

  • azerty74azerty74 Member Posts: 82
    I think it might be possible, with C-Front. I see there's a function CreateTable in CFront. Does anyone have any experience with this ?
    Debugging is twice as hard as writing code. Therefore if you write the code as cleverly as possible you are by definition not smart enough to debug it.
  • Saint-SageSaint-Sage Member Posts: 92
    Basically what most people do is find an existing table with an appropriate field, and create a record variable of that table with the property Temporary Table set to TRUE. Then in code you can do something like...

    TempTable.RESET;
    TempTable.DELETEALL;

    REPEAT

    ....
    ....
    TempTable.INSERT;

    UNTIL ...

    And then you have your temporary records in a temporary table that will disappear after the codeunit runs, all without having to instantiate a new table etc.... I guess one of the restrictions for not allowing dynamic tables is due to licensing errors, unlike any other database in the world, Navision charges for tables.

    * NOTE * : IF YOU DO NOT SET THE TEMPORARY FLAG YOU WILL WIPE OUT YOUR EXISTING TABLE WHEN YOU DO DELETEALL!!!

    I hope this helps, have a great day!

    No one loves you like the one who created you...
  • DenSterDenSter Member Posts: 8,163
    You can even design a table outside the customer's object range and use it in a temporary rec variable without having to add it to their license. At least that used to work.
  • krikikriki Member, Moderator Posts: 8,875
    DenSter wrote:
    You can even design a table outside the customer's object range and use it in a temporary rec variable without having to add it to their license. At least that used to work.
    It still works.

    And it is also possible to create extra fields in existing tables by inserting records in table Fields. But the problem was that you couldn't used them. Now with the recordrefrence and fieldreference I think it is possible to use them.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
    NAV TechDays 2020: 19 & 20 November 2020, Antwerp (Belgium)
  • Captain_DX4Captain_DX4 Member Posts: 230
    kriki wrote:
    And it is also possible to create extra fields in existing tables by inserting records in table Fields. But the problem was that you couldn't used them. Now with the recordrefrence and fieldreference I think it is possible to use them.

    Could you explain this a bit further? Do you mean if you insert a record with 6 columns into a record variable based on a table with only 5 columns, that a 6th column will be created?
    Kristopher Webb
    Microsoft Dynamics NAV Developer
  • Captain_DX4Captain_DX4 Member Posts: 230
    DenSter wrote:
    You can even design a table outside the customer's object range and use it in a temporary rec variable without having to add it to their license. At least that used to work.

    That's just bloody cool! *hehe*
    Kristopher Webb
    Microsoft Dynamics NAV Developer
  • DenSterDenSter Member Posts: 8,163
    Opens up all kinds of possibilities huh :mrgreen:
  • krikikriki Member, Moderator Posts: 8,875
    kriki wrote:
    And it is also possible to create extra fields in existing tables by inserting records in table Fields. But the problem was that you couldn't used them. Now with the recordrefrence and fieldreference I think it is possible to use them.

    Could you explain this a bit further? Do you mean if you insert a record with 6 columns into a record variable based on a table with only 5 columns, that a 6th column will be created?
    No,
    You need to create a new record in table "Field". This is a system table that shows the fields in the DB. And it is possible to delete or create fields. So if you create a field in this table, later you have it in your table.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
    NAV TechDays 2020: 19 & 20 November 2020, Antwerp (Belgium)
  • jensthomsenjensthomsen Member Posts: 171
    I would like to do the same thing in Nav 2017: Create a table directly from C/AL. Anyone?
  • TallyHoTallyHo Member Posts: 339
    Still not possible..
  • jensthomsenjensthomsen Member Posts: 171
    Doesn't seem to be possible inserting records in tables like 2000000041 Fields??
  • HaxiboyHaxiboy Member Posts: 9
    I think it's possible from a different perspective, so you can create a table directly from C/AL even from native SQL, or as NAV App object.
    My method would be, you create the table you'd want to use in dev environment, then export it as txt.
    You can run any exe with parameters from C/AL, or you can use the powershell method. You can store the txt in a text constant in your C/AL code, or even in a table's blob field.
    Then you delete your exported table from dev environment.
    Your code should do the following -> Export your string into TXT -> Call FinSQL with parameters pointing to your created txt, import, then compile.

    If you only need an SQL table, you just simply run your SQL commands on the server from C/AL code just like in C# or any other language.
Sign In or Register to comment.