Everyday Dynamics 365 Business Central #D365BC is opening up to new countries. Business Central general availability is in 19 countries now and if you are already onboard to Microsoft Ready-to-Go program, the Developer Preview images are available for 23 countries. Here is the list of countries details in one single place –
How much time it will take to recreate an Unposted Purchase invoice from a Posted invoice keeping all information intact?
Well, that’s a no brainer! You can simply use ‘copy document‘ function in Purchase Invoice window. Keep ‘Include Header‘ ticked & ‘Recalculate Lines‘ not ticked [this will retain same vendor for header & same information from lines]
But what about if you need to do the same 400 times?
Let me give you the background from where I am coming from. Right now I am helping a company in Bracknell, UK [the silicon valley of UK] with lot of enhancements. And they seems pretty excited [first time it’s realised that they can do so much more from their almost obsolete system (they are using NAV 2009 classic)]
I am not going to talk about what kind enhancements / automation I did for them in this post rather will speak about what happened today morning when their CFO rushed to me and informed me that the purchase lady by mistake posted all the unposted invoices pending in the system instead of posting invoices pertaining to only a particular vendor [She executed ‘Post batch‘ and forgot to mention the ‘Buy-from Vendor No.‘ filter in the request form.
CFO: Can you do something about it? I will take necessary steps to reverse the financial impacts of those invoices but can you help me in recreating those unposted purchase invoices?
I said ‘Yes‘ and here what I did:
A. Study the ‘Copy Purchase Document’ function:
I have used this function plenty of times and understand technically what exactly it is doing. But hardly ever I had gone into the details or checked the code written there [Report 492]. So basically what it does –
This works for one document. Question is ‘How to automate it for multiple documents [in this case 400]?
B. Gather the information:
Post study, i concentrated on gathering information about documents wrongly posted [I need to recreate new unposted purchase invoices copying info from these documents only].
CFO promptly shared me a list of wrongly posted invoices in excel. Though he shared me lot of additional info for this process I needed only the posted invoice nos. So I decided to put this info in a Dynamics NAV table and created it with following structure –
1. Invoice No. [Code 20]- This will hold the posted invoice nos.
2. Recreated [Boolean] – This will be marked ‘true’ if an unposted purchase order gets created successfully.
3. PO Created [Code 20] – to store the corresponding unposted purchase invoice created by the report (don’t bother about the naming convention).
Created a dataport and imported the list in this table [How to create a dataport and import these info into a table is out of scope of this post]
C. Build the Automation :
Now I have all the information and it’s time to automate a process which will read through these invoices and call the ‘Copy Document’ again and again for each posted invoice.
To build this, I could use a codeunit, a form or a report. And I choose a report [probably I felt it’s simpler to manage].
So I created a ‘ProcessingOnly‘ report with the DataItem = the table I created above in step B. Below is the DataItem properties, Report Properties, Global variables defined and the Code written in the report
- DataItem Properties
- Report Properties
- Global Variables
- Code written
Below is the explanation of the code:
D. Run the Report:
Run the report and at the end of it, it will display you ‘how many invoices are regenerated’. This will also update your custom table with ‘Recreated’ tag true and corresponding Invoice No generated –
Report is successfully run and the records are updated.
Voila, all invoices which was posted wrongly are recreated. Check them up. Remember there will be an additional line [first line] in all newly created unposted Purchase Invoices mentioning the posted invoice no [source document] from where it is recreated [see below screenshot]
Hope this helps if you come across a similar situation. You can download the zipped objects created by me here or from my Downloads section. You can actually modify them to suit your requirements or even for other module to manage similar situation.
In case of any confusion / queries, do write to me [email@example.com] or put your comments below.
With Microsoft releasing GST -Indian localization for Dynamics NAV, Dynamics community is all excited and jumped to work on it and so did I. Of course, the first step was to upgrade the database with GST objects. This also lead me to think about what approach to follow such that it be simple and effective for all the different customers those looking for upgrading to GST. Some of the customers are in latest version of NAV, while there are customers who are also on older versions for which it is highly unlikely that Microsoft is going to release an update for GST.
Those who are starting with a fresh implementation, can directly download of CU 17 (build 48067) setup and install and are ready to go. For those who are way ahead in their implementation phase with an older version of NAV or have already gone Live, will have to go through an upgrade process with GST.
Upgrading GST means to merging the changes done to objects for GST to the existing objects. Changes to GST will impact most of the areas of the product for e.g. Masters, Business logic, Screens etc and in all these area there will also be custom codes that will make the merging a little challenging. The intention of this post was to cover how to approach merging of objects in Dynamics NAV. Below approaches can be taken up for upgrading to GST.
- Merging all the custom code to GST changes
- Merging all the GST changes to Custom implementation
- Mix of approach 1 and 2
While deciding upon the approach, some of considerations that will influence decisions are the effort involved, availability of Resources, time required etc. For e.g. bringing all the customer changes to GST would mean to have different upgrade strategy to each customer, while bring the GST changes to different implementation will mean having a uniform approach for upgrading process. For making these decision points that needs to considered are
- Type of Import option i.e. Merging feature supported by Dynamics NAV
- Objects that have been impacted by GST changes
Type of Import options (Merging features available in Dynamics NAV)
While importing the objects to Dynamics NAV, following options are available:
- Create: The new object will be added to the database. This option is only valid if no such object already exists.
- Replace: The existing object will be replaced by the new object.
- Merge: Existing<-New: Only valid for tables. All fields in the existing table will remain and any additional fields from the new object will be added.
- Merge: New<-Existing: Only valid for tables. All fields in the new table will be imported and any additional fields in the existing table will be added to the new table.
- There are two more “Skip” and “Delete”
Steps involved in merging objects are –
Step 1: Import the GST objects to the existing Database, which will appear as a list in Import worksheet. All new tables will have “Create” option set in field “Action” as shown in below image –
Step 2: Patch also has changes to existing standard tables and in the Import worksheet they will appear with option “Replace” in field “Action”. All those standard tables that are not modified or customized can straight away be set to “Replace” as shown in below image
Step 3: Those standard Tables that are customized, set the “Action” field with option “Merge: Existing<-New”. For e.g. in case Vendor table is customized. Go to the Vendor table line in Import worksheet and in Action field, select option “Merge: Existing<-New” as shown in below image –
Merging using Action “Merge:Existing<-New”
While merging with option “Merge:Existing<-New”, system considered are Global variables, Local C/AL function including local variables and Table properties. Similarly from New table items considered are New fields, Field properties that are different and Trigger level code on field level including local variables.
Fields are merged based on the field number. The following scenarios are possible:
- The field is located in the old object, but not in the new. The field will remain as it was.
- The field is located in the new object, but not in the old. The field will be added.
- The field is located in both objects. If the properties are different in the new object, these new properties will be used.
Sample Walk-through of Vendor table Merge
Below are List of new fields added as part of GST changes to Vendor table.
|Field No.||Field Name|
|16600||GST Registration No.|
|16609||GST Vendor Type|
Changes to Existing Field
|Field No.||Field Name|
‘State Code’ field has new C/AL included in Trigger as shown in below image –
Below are steps to import GST changes to Vendor Table –
Step 1: Create a .FOB of Table 23 GST changes and Import. On importing .FOB, an error may popup as shown in below image. This is due of code existing in Triggers of New fields as well as existing fields. To resolve the error, one of the approach would be to open the object in GST Build (48067) and comment all the C/AL code existing in Trigger related to GST changes and save and compile another .FOB
Step 2: Import .FOB with Option “Merge: Existing<-New”. Merging will create all the new fields and also the triggers in the new fields. It may not bring the C/AL trigger for field “State Code” and this will have to be manually merged.
Step 3: Uncomment all the GST changes and recompile the object.
Step 4: After the Merge, it is always better to do a text compare and check what is missing and change it manually.
Objects Impacted by GST changes
Below is list of new table that are part of GST patch
|Table||1531||Workflow Step Argument Archive|
|Table||16400||GST Registration Nos.|
|Table||16401||GST Accounting Period|
|Table||16402||GST Accounting Sub Period|
|Table||16403||GST Claim Setoff|
|Table||16406||GST Posting Setup|
|Table||16412||Detailed GST Entry Buffer|
|Table||16418||GST Ledger Entry|
|Table||16419||Detailed GST Ledger Entry|
|Table||16420||GST Posting Buffer|
|Table||16421||GST Calculation Buffer|
|Table||16422||Adv. GST Calculation Buffer|
|Table||16423||GST Application Buffer|
|Table||16424||GST Liability Line|
|Table||16425||Posted GST Liability Line|
|Table||16426||e-Commerce Merchant Id|
|Table||16429||GST Recon. Mapping|
|Table||16430||GST Reconciliation Lines|
|Table||16431||Periodic GSTR-2A Data|
|Table||16432||Posted GST Reconciliation|
|Table||16434||GST Credit Adjustment Journal|
|Table||16435||Invoice Adjustment Journal|
|Table||16436||GST Payment Buffer|
A Sample list of existing standard Tables that are modified as part of GST –
|Table||5||Finance Charge Terms|
|Table||6||Customer Price Group|
|Table||21||Cust. Ledger Entry|
|Table||25||Vendor Ledger Entry|
|Table||32||Item Ledger Entry|
There is no fixed way or right way of merging objects from new releases, intention is to provide an overview of what is possible so that upgrades become very smooth affair. This is very critical as GST in India has to be implemented to most of the customers and within a given time frame. I believe this post will bridge that gap.
Microsoft Dynamics NAV 2016 (Corfu) is on its way. Till now whatever communications we got from Microsoft, NAV 2016 (Corfu) will be released in 4th quarter of this year (Between October – December 2015). If you remember, NAV 2015 was released in October 2014. Here is a short video on what to expect from NAV 2016 (Corfu) as per the current communication from Microsoft) –
If you are unable to view the video, you can download a pdl version of the same from here – Roadmap_MicrosoftDynmamicsNAV
What I understand, major concentration is on –
- Power BI
- A new client on Mobile
- Native integration with CRM Online
- Electronic Invoice exchange
- Third Party data source for certain associated masters (Like Currency exchange Rates)
- More elaborate Workflow
- Posting Preview (not sure at this moment what exactly it will be)
Let me get a copy of NAV 2016 (Corfu) soon and I will start writing about my experiences regarding the individual topics.
You can send your specific questions on NAV 2016 (Corfu) to my mail id.
Fun Facts – Annual Report – NavCanbeFun.Com 🙂
It Seems that Microsoft Dynamics team is bang on their target and managed to stick to NAV product Roadmap released earlier –
Congrats to Microsoft Dynamics Team for their great work.
There are a lot of speculation about New Microsoft Dynamics NAV 2015 release date and it seems that Microsoft has announced in WPC 2014 (happened last weekend) that NAV 2015 (or CRETE) will be launched on October 1, 2014. Surprisingly Microsoft already have released an app on Microsoft Dynamics NAV 2015 for windows 8.1 and the same is available to be downloaded from Microsoft App Store. You can find out more about this app in the below link –
This App will work in both in Windows 8.1 Laptop as well as Tablet. Here is few screen shots of the app for your ready reference –
It’s available in English (United States) and 24 other languages. Below is more details from Microsoft App Store about this app –
Your experience will vary depending on the Dynamics NAV server that you are connected to.
Get an overview of your business in the Role Center.
Tap and swipe to get to your data and drill down into details.
Search across all columns in a list.
Send lists to Microsoft Excel or Office 365.
Shoot pictures using the camera on your mobile device and upload them to Dynamics NAV.
Rotate your device to get an overview of your data or focus on content.
Use two apps side by side and see, for example, customer details while you talk via Skype or Lync.
I am eagerly waiting to put my hand on the new Microsoft Dynamics NAV 2015 and start experimenting with features mentioned in the Microsoft Readiness portal and apply them in some real life scenarios 🙂
You can refer to my earlier post describing interactive sorting in NAV 203 R2 in the following link –
We will use our existing report designed in my last post [Interactive Reports in NAV 2013 R2] and will incorporate a drill down facility from the ‘Inventory’ column in the report to the Item Ledger Entry corresponding the report’s item No. Probably the design should be as below –
Now to achieve this we need to do 3 things –
Create a link to ‘Item Ledger entry’ page (Page no 38) and
Connect the same link with ‘Inventory’ field in the report and
Enable your report to use an external link (URL)
Create a link to ‘Item Ledger entry’ page:
Creating a link to a page in NAV 2013 R2 is simple. Open any Item Card and drill down from the inventory field –
This will open the ‘Item Ledger Entry’ page filtered out by the item from whose card you are drilling down –
Click on the down arrow key in the upper left corner of the page and go to Page > Copy Link to Page. Open a notepad and paste the link (you need to further edit it)-
here is the link URL pasted (This link relates to my NAV server and Company):
dynamicsnav://dss-282:7046/DynamicsNAV71/CRONUS%20India%20Ltd./runpage?page=38&personalization=38& bookmark=12%3BIAAAAACHNAE%3D&$filter=%27Item%20 Ledger%20Entry%27.%27Item%20No.%27%20IS%20%271000%27&mode=View
This link contains a lot of information about the target page and some part of this link can easily be removed to make it more generic. This link has the following information –
|dynamicsnav://dss-282:7046/DynamicsNAV71||Target Server Instance and port no|
|./runpage?page=38&personalization=38& bookmark=12%3BIAAAAACHNAE%3D&$||Target page no 38 and bookmarking|
|filter=%27Item%20 Ledger%20Entry%27.%27Item%20No.%27%20IS%20%271000%27||Filer information. Here the data is getting filtered by ‘1000’ on ‘Item No.’ field in target data (Item Ledger Entry)|
|&mode=View||Open the page in View mode|
So I will remove the ‘Target Server’, ‘Port No’, ‘Target Company’ and the ‘Personalisation’ part to make it usable for any server, any database or company (you can port this object to any other database in any other server and it will work fine). post my changes, the link will look like this –
dynamicsnav://///runpage?page=38&filter=’Item Ledger Entry’.’Item No.’ IS ‘1000’&mode=View
[I have replaced the %20 with space and %27 with a single quote (‘)]
Right now the above link will open the Page 38 filtering the ‘item ledger entries’ with the Item No. ‘1000’. We need to change it further to make it filter data dynamically with the item no. from the report. We will make this change directly in report builder in our next step.
Connect the same link with ‘Inventory’ field in the report:
Open the report in NAV 2013 R2 development environment and go to View > Layout. This will open the report layout in SQL Server Report Builder.
Note: To learn how to use SQL Server Report Builder 3.0 (instead of Visual Studio 2013) for NAV 2013 R2 report layout designing, you can refer to my earlier post NAV 2013 R2 Report layout design with Report Builder
Right click on the ‘Inventory’ textbox and go to ‘Text Box Properties’.
Go to ‘Action’ tab and select the radio button in ‘change action option’ to ‘Go to URL’. After this click on the ‘fx’ (as circled above) button next to ‘Select URL’ text box.
Here I have modified the URL with the following changes:
Added a ‘=’ sign in front of the URL
Included the entire URL inside double quotes
Replaced the text ‘1000’ (the item no.) with the text ‘+Fields!No.Value+’. This makes the URL to pick up the Item No. from the value displayed in ‘No.’ field in report.
Instead of manually typing it, you can actually select the ‘Fields (DataSet_Result) in Category box, Select ‘<All>’ in Item box and then double-click on ‘No.’ in the Values box.
Post the above changes, my final URL looks like this –
=”dynamicsnav://///runpage?page=38&$filter=’Item Ledger Entry’.’Item No.’ IS ‘”+Fields!No.Value +”‘&mode=View”
Now the field ‘Inventory’ is perfectly linked to the page 38 (Item Ledger Entry). Next let us complete our 3rd step –
Enable your report to use an external link (URL):
Save the report layout and come back to NAV 2013 R2 development environment (Report designer). Go to a blank line at the bottom of the report and click on View > Properties –
Change the property ‘EnableHyperlinks’ to ‘Yes’.
And that’s it. You are ready with your NAV 2013 R2 interactive report from where you can drill down to more detailed data. Let us test the same.
Save the report and run it from the Object designer itself (that’s the good thing you can do in NAV 2013 R2 but not in NAV 2009 ).
Once the request page opens, just click on the ‘Preview’ button. This will open the report viewer window. Hover your mouse on the ‘Inventory’ field values and you will see the cursor is getting changed to a ‘hand’ denoting that you can drill down from that value. Click on the value and Item ledger entry page will open up with the entries corresponding to the item no in the report.
Try and see if all values are opening it’s corresponding item ledger entries only.
Hope you are able to follow the steps mentioned here in creating your own interactive reports. You can leave your comment here in case you face any difficulty or you can write to me on the same at firstname.lastname@example.org.
Catch you in my next post / session on NAV 2013 R2. Till then happy reading.
With the world cup football 2014 in full swing, I am struggling to catch up up with my goal of weekly post to my blog . But thought of catching up on the same today.
In this edition of post, let me show you some of the interactive features of Microsoft Dynamics NAV 2013 R2 reports
NAV 2013 R2 reports can be pretty interactive in terms of –
Drilling down to detailed data
Toggling the visibility of any field [say image] based on user action.
These features are kind of giving more power to end users of NAV to view their reports in their own way. Let them decide how they want to sort the report and help them with details of information wherever possible.
To demonstrate the same, let me create a report in NAV 2013 R2 and take you through these interactive features.
I will create a simple report based on Item Master – ‘Item List Interactive’ with the following columns displayed in the report –
– Base Unit of Measure
– Costing Method
– Inventory and
– Picture of the item
So I opened the developer environment of NAV 2013 R2, created a new report and inserted the DataItem with the Data Source ‘Item’ table and added the above mentioned columns in the Report Dataset Designer as below –
Now let us design the layout of the report. [I am using SQL Server 2012 Report Builder 3.0 for layout Designing]. Go to View > Layout and this will open the Report Builder’s Layout Designer [a blank one]. Just add a table and drag and drop the fields displayed in Dataset_Result under the group Datasets in the Report data pane in the left [You could use a Table Wizard (Insert > Table > Table Wizard) also to include the fields in the layout] –
Your layout will look more or less similar to my above screenshot.
[Note: Use a image control instead of a textbox control to display the picture field. Otherwise the the picture column will not show the images of the items and will display an ‘Error’ wherever picture is present for the item.]
Now your basic report is complete. Close the report builder and save the report and run it from the object designer itself. Though we have not selected any key in the DataItem’s property ‘DataItemTableView’, you will see that the list of items are sorted on [No] column as that’s the primary key of the item table.
Also, wherever the item has a picture stored in the database, the same will be displayed in the Picture column of the report.
Our next job is to make it more interactive and enable the major columns [Say ‘No’, ‘Description’ and ‘Costing Method’] dynamically sortable during runtime. For that we will go back to our report and open the layout in report builder.
In the layout design, right click on the header textbox of the [No] column and open the text box properties.
In the properties window, go to the Interactive Sorting tab and in the right hand side pane, tick the [Enable interactive sorting on this text box]
Keep the [Choose what to sort] on [detail row] selected and go to the field [sort by]. Here select [No] column. Click on the ‘Ok’ and you are done with making the [No] column interactively sortable during runtime.
Similarly make the [Description] and [Costing Method] column interactively sortable by repeating the above steps (Right click on the header of the column and open the text box properties. Go to the interactive Sorting tab and tick the [Enable interactive sorting on this text box]. Also select the corresponding field in [sort by]) .
Save the report layout and come out of the Report Builder. In NAV report designer window, click anywhere and it will prompt you to synchronise the report object and the modified RDLC [.rdl] file –
[This happens every time you modify anything in the report layout in either Report Builder or Visual Studio and come back to NAV report designer]
Let us test how the report is behaving now after all the above changes. Select the report in Object Designer window and run it. This will open the request page of the report where click on the preview button to display the report –
In the preview, immediately you can notice that there are some visual changes in the report and the [No], [Description] and [Costing Method] columns have up/down arrows in their header. This denotes that these columns are enabled with interactive sorting.
Click on the column header of [No] and you will see the sorting of the data is getting changed from ascending to descending and if you click again, the sorting is reverting back to ascending way. The same sorting behaviour you can see for the other 2 columns too ([Description] and [Costing Method]).
So, we just made our simple ‘Item List Interactive’ report dynamically / interactively sortable on the basis of multiple columns.
That’s closes this session on Interactive features of Microsoft Dynamics NAV 2013 R2 reports. Do send / write to me your feedback on the same. In my next session I will continue with my discussion on rest of the interactive features [Drill down and Image Toggling] of NAV 2013 Report. Till then happy reading and enjoy the Football World Cup matches .
[Going to watch Brazil vs Mexico match tonight]