Create a table with CAL code ?

azerty74

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.

Marije_Brummel

You are correct. This is not possible.

azerty74

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 ?

Saint-Sage

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!

DenSter

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.

kriki

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.

Captain_DX4

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?

Captain_DX4

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*

DenSter

Opens up all kinds of possibilities huh

kriki

Captain DX4 wrote:

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.

jensthomsen

I would like to do the same thing in Nav 2017: Create a table directly from C/AL. Anyone?

TallyHo

Still not possible..

jensthomsen

Doesn't seem to be possible inserting records in tables like 2000000041 Fields??

Haxiboy

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.