Imagine that whenever you edit an object in C/SIDE Object Designer, and save it, the system automatically saves it in the version history. Imagine that it never simply overwrites your previous version, but keeps all of them there for you. Imagine that you can see all of the modified versions of any object, then make any version the current version by doing as much as a single mouse-click, no tedious imports and exports needed. Imagine that this is true for all of the developers on your team. Imagine that it is completely, fully automated.
They say a picture is worth a thousand words. So, imagine your Object Designer look like this:
Microsoft Dynamics NAV integrated C/SIDE development environment leaves a lot to be desired, with version management being close to the top of the most wanted features list. Now you can have it. Download it here.
(Two notes on the download file. First, it is .doc, and I am sorry about it. WordPress doesn’t allow me to post .zip files, so I had to do a workaround: I created a doc file and embedded two other files in it. Second, the disclaimer on the main page of the blog is here, and it applies to this blog post as much as any other: this is provided AS IS, with no warranties, and no rights, so use this at your own risk. Not that I want to scare you off.)
What is this stuff? As my intro tries to convey, this is a simple utility which can do automated version management for you. It employs a single simple mechanism to allow you to keep all of the previous versions of the object whenever a user saves it in Object Designer. For every single object that was ever changed, all of the versions (from the moment this tool was put in place) are kept in the Object Versions list, so you can review all of the changes and then by a click of a mouse replace the currently active version of an object with any of its previous versions in the history list. Simple.
The tool works only with SQL Server 2005 (it can be easily adapted to work on SQL Server 2000, too), does not work with native Database Server, and consists of the following:
-
SQL Server trigger which is used to update your object version history every time you update an object.
-
A global table (data per company = no) called Object Version, which is basically a copy of the table Object, with some more fields to specify the version number, the user who did the modification and whether the version is active or not.
-
A form which looks exactly like Object Designer, except for New, Design and Run buttons, but with subform which lists all existing versions for the object selected. There is also a function Restore, which replaces currently active version with the selected version, while maintaining both versions in the database so no version is ever lost.
To install this tool, you need to follow these two steps:
-
In the provided doc file, open the Version Management.txt file, save it to the file system.
-
Open Microsoft Dynamics NAV, connect to the database you want to use this feature with, then open the Object Designer.
-
Import the file saved in the first step.
-
In the provided doc file, open the Version Management – SQL Trigger.txt file, copy the contents to the clipboard.
-
Start SQL Server Management Studio, connect to the Microsoft Dynamics NAV database you want to use this feature with, then click the New Query button.
-
Paste the contents from the clipboard, and execute the script.
-
That’s all folks.
To test the functionality, open a form in the updated NAV database, then save it. Then run form 94092 Object Versions and locate the object you modified. When you found it, note that version history in the bottom part of the form contains your saved version. Now you go to the same form you modified, modify it again, this time adding a button to it, then save it. In the Object Versions history you can see that now you have two versions, and that second version is active. Select the first version, click Functions, Restore, and confirm the message. At that point, your previous version of the object is restored, but the second one is kept in the history as well. You can confirm that your original version is kept by running (or designing) the form, and noticing that the button you added is not present there.
There are some known issues with this first version:
-
It doesn’t store initial versions of objects existing at the time the utility is put in place. It stores every new version. To make it store all objects, you can loop through all objects and do a dummy update (calling modify with changing nothing), it would save all initial versions for you.
-
It doesn’t keep the true SQL login or Windows AD user name, it simply keeps dbo or application role name. It can be changed by some modifications to the SQL trigger, but I was too lazy to do this for the first version, and too excited to post this as soon as possible.
I don’t recommend using this trick in production environment, both because it is not really useful there (in case you have good deployment procedures in place and your customer doesn’t have access to Object Designer), and because it works by employing a mechanism outside Microsoft Dynamics NAV. However, in any development environment, I am sure it can save hours, if not days, of work.
Please let me know if this worked for you, and what you think of this. I hope you find this as useful as I think it might be.
This looks amazing – I can’t wait to try it out. Now if only we knew how to decode the blobs that contain the code, we could make something _really_ useful!
Wow I love that blog and you solution for a version management!
Dave and Jut,
Thank you for your comments. Have you had a chance to try it out yet? I hope it works for you and you find it useful in the end.
Best regards,
Vjeko
Yes I like it – it all works and is pretty cool. I am using on my current project so let’s see how useful it is. I love the fact that I can never loose stuff by accidentally saving over a previous version. Amazing – it also is a safety net for those lost update errors (two devs working on the same database last one to save wins).
I actually quite like the Word doc as a means of deploying add-ons. First of all because the objects are in text format you can guarantee that they are only going to be imported by someone with a developers license which should mean the person knows what they are doing. You can also easily see what objects you are going to import before you do it – nice to know what code is in there before you run it eh? It is also a nice compact way of getting non-NAV objects packaged together with the NAV objects.
Some suggestions – you need to compile the objects after importing them. You should add the installation instructions into the word document rather than on your blog as this means people can just forward the word doc around to share your add-on – what a great way to share add-ons! You should add some version release information in the word document – contact details for you, history of changes if any, description of how to use it (avoid screen shots as this makes it too big but maybe provide a link to screenshots stored on your blog). Something about the licensing of this – is it freely distributable, etc.
I would love to be able to get the actuall user name in the object history. My most wanted feature would be to be able to compare versions – but I think this is impossible unless you know how to decode the BLOB or are brave enough to restore it export it using keystrokes stored in the keyboard buffer – scary!
If I restore a version I am guessing I could run into problems with other objects that have changed after the version I am restoring that reference my object.
What happens if I restore a table object – do I need to re-compile?
Nice work! Vjeko.
Yes, I should put the instructions into the Word file, but I am not sure if I am going to maintain it. Maintaining it as a product would have certain implications. The way it is now it’s just a proof of concept, and I would prefer it to stay that way.
About the actual user name, it should be fairly easy. You need to update the SQL trigger to query some system tables which contain this information. Maybe I will do this, it would really be nice to have.
Reading a BLOB must somehow be possible, maybe not directly. But given that you have available tools such as Developer’s Toolkit prove that it is possible to get source code from NAV. However, that would really go into a domain of creating a product, which is something I can’t do.
I am not sure if I quite get the last part of your comment, about restoring versions. It is true that there are no dependencies defined among objects, and that if you have one object version depending on another, bringing back an old version of an object might make the other one not work. If you restore a table object, you might need to recompile forms, reports, etc. which depend on it, I am really not sure because I didn’t test it that far.
As I said, this is just a concept. It could have huge potential if you made a list of features it could have and actually developed it afterwards. But I am now not interested in pursuing that path. I feel better in an idea-generator role 🙂
Hi Vjeko,
the point about the table was that I am assuming that your restore option simply replaces the Object record with the Object Version record (I haven’t looked through the code). But in SQL I am not sure whether this would actually change the table definition – I would expect not. This normally happens when you compile the object and your record update will not trigger the compilation and hence not really change your table. I haven’t tried it because I am too scared. Not a big deal – just explaining what my point was.
Cheers, Dave.
Hi Dave,
I must admit that your comment scared me a bit. So I tested it on version 5.0, and it seems to work. As soon as you update the BLOB, the table definition in SQL also gets updated. It does all the checks NAV usually does, such as whether you try to drop fields which contain data, etc. So, I’d say this use case works.
Best regards,
Vjeko
You wrote:
The tool works only with SQL Server 2005 (it can be easily adapted to work on SQL Server 2000, too).
How do i get this Working on SQL Server 2000?
Hi Ronnie,
I don’t have SQL Server 2000 to test it, but I imagine the procedure would be something like this:
1) Start query analyzer
2) Connect to the database (NAV database) to which you want to install this functionality
3) Open the Version Management – SQL Trigger.sql file in Notepad
4) Copy all text in notepad and paste it to Query Analyzer
5) Change the keyword AFTER to keyword FOR so that instead of AFTER INSERT, UPDATE you have FOR INSERT, UPDATE
6) Execute the query (press F5)
Please, have in mind that I haven’t touched SQL Server 2000 for quite a while, and I have no means to test it, and I provide these steps AS IS without any warranties and confer no rights, and all standard mumbo-jubmo from disclaimer applies here. If I catch time, I will try to test it on SQL Server 2000, and post the solution here, in case this doesn’t work. Please let me know of the results.
Cheers,
Vjeko
Yes your instructions work on SQL 2000 if you replace AFTER with FOR. But interestingly it also just works on SQL 2000 with no changes.
Well yes, you are right. I forgot that AFTER and INSTEAD OF triggers were introduced in SQL Server 2000, not 2005. That’s why both FOR and AFTER will do. If I remember correctly, there should be no difference in functionality between AFTER and FOR. Version 7.0 had only FOR triggers, and when they introduced INSTEAD OF triggers, they introduced AFTER keyword so that it would be immediately obvious what is the difference between the two, because FOR could be understood as either of these. Whatever 🙂
Yes, I tried the tool successfully! It would be toooo cool if the versions could be exported or viewed as text…
I managed to get the Windows-Login for the User ID- Column instead of the User_Name. Just change USER_Name in the inner Select to SYSTEM_USER – et voila 🙂
Pingback: Automated Version Management 2.0 - An Update « Navigate into success
Pingback: Automated Version Management 2.0 - An Update - Navigate into Success – Microsoft Dynamics NAV
Hi Vjeko, I’m trying to use the tool on Nav 2017 with SQL 2014, but I have some problem like page doesn’t save and Blob field doesn’t save when I try to restore object. Have you any solutions for that? Thank you for reply and great blob.
Hi there! Wow, where did you find this one 🙂 It has been nine years, a lot has changed since then and I would really be surprised if it still worked under NAV 2017 on SQL 2014. Sorry, I can’t help you here.