C/AL Differences: SQL vs. Navision Database

C/AL is supposed to work the same with the SQL database as with the Navision database. Not surprisingly, this is not always the case. I have recently encountered a couple situations where the behavior of C/AL is different in the two database options. Being an add-on developer it is important for me to know of such differences since I have to deliver a product that will work with both database options. It is equally important for other developers who have grown comfortable with one of the database options and now must develop for the other. I have asked Microsoft if they maintain a list of such differences; their response was to consult the Application Developer’s Guide. So either they don’t have such a list or they are not sharing it.

I thought I would share my experiences and perhaps this thread can become that list. I’ll get it started with the following two differences. Both compare FINSQL.exe (4.0 SP2, build 22611) and FIN.exe (4.0 SP2, build 22611).

CALCSUMS

SQL will calculate the sum for all SumIndex fields associated with the current key. Consider the following (Cronus):

ItemLedger.SETCURRENTKEY(“Item No.”,“Entry Type”,“Variant Code”,

“Drop Shipment”,“Location Code”,“Posting Date”);

ItemLedger.CALCSUMS(Quantity);

The CALCSUMS in SQL will also calculate the sum of the “Invoiced Quantity” field because it is also a SumIndex field for this key. In the Navision database only Quantity will be calculated and the value of “Invoiced Quantity” will remain unchanged.

Additionally, SQL will calculate sums for fields that are not even setup as SumIndex fields. If the previous example is modified to:

ItemLedger.SETCURRENTKEY(“Item No.”,“Entry Type”,“Variant Code”,

“Drop Shipment”,“Location Code”,“Posting Date”);

ItemLedger.CALCSUMS(“Remaining Quantity”);

SQL will calculate the sum of the “Remaining Quantity” field while the Navision database will report an error indicating that “The sum of the values in the Remaining Quantity field cannot be calculated.”

AutoIncrement

With the Navision database it is possible to insert a record into a table with an AutoIncrement field and to set the value of that field to some value other than zero. In this case, the AutoIncrement feature is not trigged and the AutoIncrement field will have the value set prior to the insert. It works the same way in SQL provided the user that is doing the insert is a DB owner; otherwise the user will get a permission error on a SQL “SET IDENTITY_INSERT” command.

I did have the autoincrement issue at a client side, and had to see the properties of the table to find out why it was erroring out. I junior developer had set that value by mistake.

Hi, CALCSUMS This is known issue and i guess there is KB article about this. Answer from design team was: - there are many reasons why it is so in SQL. And there are no plans to change. I can only warn all of you that be carefully using calcsums in SQL. Example: record1.SETFILTER(FLOWFILTERFIELD, ‘filtervalue’); record1.CALCSUMS(“SUMINDEXFIELD1”);//<< calculates all sumindex fields regarding flow filter settings, so amounts can be wrong record2.INIT; record2.TRANSFERFIELDS(record1); //<