Small note: When moving this article to dynamicstailor.eu, I decided to shorten this article by about 50%, by removing a few wishes that Microsoft has granted us in the meanwhile, or is about to give us. Two of these were a better editor and/or Visual Studio integration, and version control (any!).
I’ve been programming in Dynamics NAV since 2013, but I wrote my first code over 20 years earlier. It was 1993, I was still living with my parents and I hadn’t even met my first girlfriend yet. My father came home with a white box saying “Microsoft Visual Basic 3”. Before I knew it I was determined to build my own games in stead of save money to buy the newest titles.
A few disks, a big stack of books: Coding in Visual Basic was so incredibly simple, a 10 (and-a-half!) year old could get going within weeks!
A few years later, I switched to VB6, and after this VB.NET. Both steps were a lot more complex, but after you grab the concept, it’s suddenly possible to write more efficient code.
When I started working at a Microsoft Dynamics NAV partner in 2013, I had my first encounter with C/AL. 20 years later, yet I felt like in a playground again. C/AL is incredible versatile, incredibly simple, and incredibly fast (in terms of coding).
With the release of Dynamics NAV 2016, things even got better: Suddenly there’s TRY…CATCH functionality (although Vjeko already blogged about it’s darkside – I’ll get back to this later), and then there’s that editor that finally moved past Notepad-level! Then we suddenly got VS Code, Extensions, AL… but I’m still missing a few things.
Somewhere in 2009, I did a project where a lot of Transact SQL programming was envolved, and learned to work with database triggers and transactions. You can imagine my surprise when an NAV developer told me that COMMIT exists in NAV, but using it is asking for a death sentence. The surprise got even bigger when I heard there is no BEGIN TRAN!
By now, I’ve learned how to use COMMIT, but to this date, I still try to stay away frOMMIT.
Concerning transaction management, I don’t have any ideas on how to implement it exactly, but it would be very cool to control database writes the way we can on SQL level.
Data Object datatype
I’ve seen so many developers bump their proverbial nose on this one, and there’s such a simple solution. Take a look at tables 36, 37, 110, 111, 112, 113, 114, 115, 5107, 5108, 5126 and I’m probably forgetting a few: The famous Sales-tables. These have a lot in common, for example:
- They all use fields Document Type (an Option) and (Document) No. (Code 20) in their primary key.
- All the lines tables have a Description field (Text 30).
Now, let’s implement a few function that use this standard (Document) No. field:
All working fine! We can build similar functions all through the application, and use them without any problems.
Now… let’s imagine Microsoft is getting complaints about the size of the Document No. field. They decide to make it a Code 25, in stead of a code 20.
Still nothing wrong. Your code will compile, and if you also work at the customer which is using this code, you won’t hear anyone complain either. Right until the moment someone actually uses these extra characters: You will see the fames “Overflow under type conversion of Text to Text”.
Fixing this is a matter of switching on the debugger and tracing exactly where this field is thrown in functions, then editing the length of the variable in the function parameters. Maybe you’ll need to do some data recovery (if someone else threw that dreadful COMMIT in the code above yours…), but usually nothing terrible happens.
My point is that it can be solved, by Microsoft, fairly easily!
There. Fixed. It’s right below the MenuSuite, and it’s a Data Object.
What it does is simple, and could work well in two ways. I’ll explain both in a few steps.
Table-based Data Reference Object
After creating table 36, we create a Data Reference Object for the primary key: Document Type, and Document No. This Data Reference Object “remembers” that it’s based on T36, and will continue to mirror vital properties of the fields. As soon as this exists, it’s possible to reference the Data Reference Object for function parameters, as the sweetspot between a Record parameter and a load of hand-built parameters. Doing so gives us at least three advantages;
- We should be able to call the function without first preparing a record, for example: DoSomething(‘Sales Order’, ‘SO00001’);
- It should be possible to reference option values that come from the referenced object, hence are always the same as in the table;
- If someone decides to change the design of any of the sales tables fields, it should not compile until all Data Reference Objects are updated as well;
This would already be a big advantage. However, there are other ways:
Data (Reference) Object based Tables
One of the first things I learned about databases (in university) were the objectives of normalization; third normal form quickly became our holy grail. Let’s turn this thought process around for a second, and go back as far to the flat table as possible. Let’s also ignore the record size limit. Dynamics NAV could probably do with no more than 50 tables. Don’t worry, my next suggestion won’t be to switch to NoSQL and forget about relational databases altogether ;=)
The next step is where it gets interesting: Let’s separate our flat tables from our table structure: We now have ourselves a data model.
Now let’s take the Sales data model (which contains everything from Customers through Sales Headers, Sales Lines, Sales Prices etcetera), and build a new Sales Header table by simply selecting which fields from our data model should be there. We can now build the Sales Line table by selecting a few different fields. It gets even more interesting if we look at the Sales Price table: That should be in Item data model as well. Maybe it’s a good idea to reference from one data model to another as well: Table relations will be easy (if not automatically generated), and we’ll have infinity integrity!
Okay, cool, but do we need this?
Well, no. We don’t need it. Then again, we didn’t need a Web Client, but people use it every day… And if you’d think nobody ever changes field properties, think again. Or take a good look at that Description field I mentioned earlier. It’s not a Text 30 anymore, it’s a Text 50…