Showing posts with label C/AL Programming. Show all posts
Showing posts with label C/AL Programming. Show all posts

Friday, April 21, 2017

Microsoft Dynamics NAV 2016 – Business Chart

Microsoft Dynamics NAV 2016 – Business Chart

👋 Hi all,

    Today i came across with different type of requirement, Sometime you might be show the inventory Location wise in the form of chart.

In this article, I’m going to discuss about Business Charts in detail and how you can create them.
Building Business Charts is one of the ways you can take advantage of the powerful charting capabilities in Dynamics NAV.
WHAT ARE BUSINESS CHARTS?
Business charts are based on Business Chart control add-in which is provided by the Microsoft.Dynamics.Nav.Client.BusinessChart.dll assembly.
As compared to other chart types, Business charts provide advanced functionalities such as:
·         Displaying complex data that is calculated on a page.
·         Allowing end users to filter data and drill down to the details from the chart
·         Allowing dynamic filtering of data
·         Being supported on Dynamics NAV Web client.
BUILDING BUSINESS CHARTS FOR ITEM LIST.
At the end of this blog, you will know how to build a simple Business chart which can be used as a factbox in the Item list page showing location wise Inventory.
This chart updates automatically when you move to any particular line on the item list page [See bellow screenshot].



Location wise Inventory Business Chart:
We need to mainly use Business Chart Buffer table (485) to build the business charts. This table holds chart related values and also has multiple functions pre-defined which can be used to build our chart on a page object.
Following objects are required to build Location wise Inventory Business Chart:
·         A Query to calculate Location wise remaining quantity for all the items
·         A Codeunit to use the above query result and prepare Business Chart Buffer table.
·         A Page to display the chart.
To achieve this follow the steps:
1.       Design a new Query object which shows Location wise remaining quantity for all the items. We will be using the query result as chart data.
a)      Open Dynamics NAV development environment and click on Query and next click on New to design a new query
b)      Specify the query DataItem and columns as shown below:


  c)      Save the query. (ex: ID-50027 and Name-Item Qty. By Location)
        d)      Run the query (50027) to see location wise total quantity of items.


2.       Design a new CodeUnit to populate the Business chart buffer table with the above query result.
a)      In the Dynamics NAV development environment, click on Codeunit and next click on New to design a new Codeunit.
b)      In the codeunit designer, click on View–> Globals–> Functions tab to create a new function in the Codeunit. Enter the function name as GenerateInventoryByLoc and click on Locals button to enter parameters and variables for the function as shown in the below image:



c)       Now in the Codeunit designer you see an new function trigger called GenerateInventoryByLoc. Enter the code as shown in the below image. The in-line comments within the Codeunit explains more about each line of code:




 Save the Codunit (ex: ID-50027 and Name- Items Qty. By Loc. - Chart Mgt

3.       Create a ”Items Qty. By Location Chart” page of CardPart type for displaying the Business Chart.

To create a CardPart page follow thr bellow steps:

Ø  Open the Object Designer from Development Enviornment -> Click on Page Type -> Click New.
Ø  Select on Create blank page and then Click on OK.
Ø  Goto Page Properies and give SourceTable as 27 i.e. Item, PageType as CardPart, ID as per your range, in our demo i used 50027, Name Items Qty. By Location Chart. -> Save the Page.
Ø  Now Design your page -> In our case -> Choose 1st Row as a
Type  as Container, SubType as ContentArea, and Name as Container. In 2nd row
Type as Field, Name as BusinessChart -> Goto properties of this line and give the value for ControlAddIn. [Click on Lookup button in ControlAddIn Value and then select  Microsoft.Dynamics.Nav.Client.BusinessChart]
Ø  Now Save the Page and close this designer.
Ø  See the bellow screenshot of Items Qty. By Location Chart.











4.       Now design the Item List page i.e 31, and add a new factbox that we created earlier Items Qty. By Location Chart.

To achieve this we have to do some changes in Item List, follow the bellow procedures.


Ø  Design the Item List Page from Object designer.
Ø  In FactBoxAre add a new line for Items Qty. By Location Chart, and Map it.
Ø  See the bellow screenshot for creating new factbox for Items Qty. By Location Chart.









Note: How you can Open Related page on Click of ”Business Chart” i’ll show you on next article, Thank You...


Credit goes to Subhidha






Wednesday, March 1, 2017

FILTER OBJECT ID’s in NAV

FILTER OBJECT ID’s

👋 Hi all,

    Today i came across with different type of requirement, Sometimes you might be need to filter object ID’s from excel sheet or other locations – IF your ID is Starting number to Ending Number Like – 1..458 you can filter easily.

But if your ID is random then you have to use pipe symbol ”|” and if you are using manually it takes more time, To overcome i developed a Function in NAV and that function i want to share, so those who have need they can consume this function and make smile in your face L.

Solution:

            To Achieve this write a function where you feel free, in our demo i used a custom codeunit.
See the bellow screenshot of Function.





See the Bellow screenshot of local variables












Friday, February 24, 2017

Add Users in NAV 2016 from SQL Server

Add Users in NAV 2016 from SQL Server
Hi all,

    Today i came across with different type of requirement, Sometimes you might be got an error while opening the RTC. Error – ”You do not have permission to access .....”. See the bellow screenshot.




This error came, because user not exist in User Table, So if you are a first user then simply you can execute the SQL query, and you will resolve your issue.
The Sql Query is given bellow, just Open your Database and run this query.

USE [Demo Database NAV (9-1)]
GO
     DELETE FROM [dbo].[User]
     DELETE FROM [dbo].[Access Control]
     DELETE FROM [dbo].[User Property]
     DELETE FROM [dbo].[Page Data Personalization]
     DELETE FROM [dbo].[User Default Style Sheet]
     DELETE FROM [dbo].[User Metadata]
     DELETE FROM [dbo].[User Personalization]
GO


Now you are abale to open RTC.


Note: But whenever you have multiple users then you can’t execute this query because all users will be deleted, for that you have to do some more steps, that steps i’m going to describe.


Step 1:  Open Powershell ISE with Administrator privilege. Now execute this command

$objUser = New-Object System.Security.Principal.NTAccount("CHALLANGER\BINESH.SINGH")
$strSID = $objUser.Translate([System.Security.Principal.SecurityIdentifier])
$strSID.Value

See the bellow screenshot for better understanding.






Once you will execute the above command, you will get the SID number. Save it off in Notepad OR somewhere. It will be used in the second step.

Step 2: Now open SQL Server and you need to execute the following Query for that Database in which you wants your respective login. SID and the Username copied from above. 

USE [Demo Database NAV (9-1)]
GO
DECLARE @USERSID uniqueidentifier, @WINDOWSSID nvarchar(119), @USERNAME nvarchar(50), @USERSIDTXT varchar(50)

SET @USERNAME = 'CHALLANGER\BINESH.SINGH'
SET @USERSID = NEWID()
SET @USERSIDTXT = CONVERT(VARCHAR(50), @USERSID)
SET @WINDOWSSID = 'S-1-5-21-611616715-3189593580-2499604960-1499'

INSERT INTO [dbo].[User]
 ([User Security ID],[User Name],[Full Name],[State],[Expiry Date],[Windows Security ID],[Change Password],[License Type]
 ,[Authentication Email],[Contact Email])
 VALUES
 (@USERSID,@USERNAME,'Binesh Singh',0,'1753-01-01 00:00:00.000',@WINDOWSSID,0,0,'','')

INSERT INTO [dbo].[User Property]
 ([User Security ID],[Password],[Name Identifier],[Authentication Key],[WebServices Key],[WebServices Key Expiry Date],
 [Authentication Object ID])
 VALUES
 (@USERSID,'','','','','1753-01-01 00:00:00.000','')

INSERT INTO [dbo].[Access Control]
 ([User Security ID],[Role ID],[Company Name],[Scope],[App ID])
 VALUES
 (@USERSID,'SUPER','',0,'{00000000-0000-0000-0000-000000000000}')
GO


See the bellow screenshot for Sql Query.





Execute the above command. If it gets successfully executed then you can go and Restart the NAV Server Instances and then try to login and you will get logged in to RTC.









Wednesday, December 21, 2016

How Do I Split Comments or Copy from somewhere and paste in comments.

How Do I Split Comments or Copy from somewhere and paste in comments.
Hi all,
    Today i came across with different type of requirement, where someone asked in community forum, How Do I Split Comments or Copy from somewhere and paste in comments.

This solution for Contact comments, you can use similar in your sales documents also, only you have to change the Record variable as per your requirement.

To achieve these requirement i created a Page of card type and two functions and two global variables.

Technology
    Microsoft Dynamics NAV 2017

Scenarios
   Demo 1 – Binesh creates a new page i.e. Name “Wrap Text” ID 50246.
   Demo 2 – Binesh calls the earlier created page in comments action.

Demo 1 – Binesh creates a new page i.e. Name “Wrap Text” ID 50246.
To achieve this follow the bellow steps:

Step 1:        Open Development environment and create a new page.
Step 2:       Create Global variables i.e.:

Name
DataType
Subtype
Length
CommentTxt
Text


RMCommentG
Record
Rlshp. Mgt. Comment Line


Step 3:       Design your page, see the bellow screenshot for better understanding.

               
              
                            
Step 4:       Create two functions in this page, see the bellow screenshot.

      
     

Step 5: In Function GetRMParam pass the record variable of "Rlshp. Mgt. Comment Line" as var true.
        And
In Function RMCommentEditor function declare local variables, See bellow screenshot.




Step 6:       Write the code, see the bellow screenshot







Step 7 :      Now call the function in OnQueryClosePage Action of this page,
See the bellow screenshot.

  


Step 8:       Now Open the Rlshp. Mgt. Comment Sheet  Page, After that open Page Action, and then create an Action i.e. Wrap Comment, See the bellow screenshot.




Step 9:       Go to C/Al Editor of Wrap Comment Action, and declare a local variable of page type that are earlier created, and pass the record parameters and then Run the page, See the bellow screenshot for better understanding.




Step 10: Testing solution -> Open Windows/Web client -> Open Contact List page -> select any contact -> click on Comments Action - > you will get Comment page -> In comment page if you have more comments, or you want to use copy, paste then click on Wrap Comment Action -> A Comment Editor page will open, In that editor you can paste your comments (or) type your comments -> After that click OK or Close button -> Comments updated in comment page.
For better understanding see thee bellow screenshot.














Friday, October 21, 2016

DATA MIGRATION IN NAV 2016 FROM NAV 2009

DATA MIGRATION IN NAV 2016 FROM NAV 2009
Hi all,
Today i came across with different type of requirement, Sometimes we need to move/Copy our NAV data from one Database to others, OR One server to other server.
we can do this task using "Departments -> Administration -> Application Setup -> RapidStart Services for Microsoft Dynamics NAV -> Config. Package Card.
Note: In this scenario, BLOB fields are not copied, Due to overcome this we have to do using SQL Server.
But Now today i am going to talk about Data migration using Rapid Start Services.
Look guys, Earlier we are simply Export the Excel format and Import it, but in NAV 2016 if we go for import we will get exception, see the bellow screenshot.



To Overcome this we need to customize our NAV 2009 Objects, But before going i just want to tell you why this issue came.
See the bellow screenshot of NAV 2016 Excel file, there i marked PackageCode that are added.



You can also check NAV 2009 Excel, In that you can’t find PackageCode.
So now our job to add this PackageCode in NAV 2009 Excel file, To do this we have to customize several objects, these are as:
                Table ID                :               8613, 8614, 8615, 8616
                Form ID                                :               8614
                XmlPort ID          :               8610
                Codeunit ID        :               8611
                                In this Codeunit we have to upgrade 2 functions, these are as:
ExportSetupDataToExcel
CreateRecordNodes
Step 1:  Add the PackageCode field of type Code in Tables 8613, 8614, 8615, 8616.
In Table 8614 and 8615 give the Table relation to 8613 i.e. ”Migration Table”.
After adding the field write code for initializing the PackageCode, See the bellow screenshot.

               



Step 2:  Add codes in Codeunit 8611 ”Migration Management”, in this codeunit we have to upgrade two function as earlier i discussed. These are
                                ExportSetupDataToExcel
CreateRecordNodes
See the bellow screenshot for added code.



Find the ExportSetupDataToExcel function and update as like in screenshot.



Now update the second function i.e. ”CreateRecordNodes”, See the bellow screenshot
Note:    In Both images only the customize code, remaing Upper and lower code as in standard.
                Just match the code something like:
For ExportDataSetupToExcel find the CreateBook and start writting from screenshot, because some code are removed.
Also go thru something like other function called ”CreateRecordNodes”.





Now Compile, Save, Exit from this codeunit.

Step 3:  In this step we are going to upgrade XmlPort 8610 "Setup Data Schema" something like NAV 2016.
Design  XmlPort 8610 "Setup Data Schema" and upgrade as it is in bellow screenshot.



After designing the Xmlport Open the CAL Editor in this Xmlport only. And make changes



Now Compile, Save, and exit from this Xmlport.
Step 4:  Upgrade the Form 8614 ”Migration Overview” , Add the PackageCode field, so we can export.
See the bellow screenshot for Form, and update as it is.




Compile, Save & Exit from this form.

Step 5:  Now export the Data from Data migration, see the bellow screenshot.



Note: Whatever you given here PackageCode value that you need to give in NAV 2009.
Also remeber onething still it create a .xls file, So you have to just open and save as .xlsx format.
Also remeber one thing, if you are going to export multiple tables then you have to delete manually Sheet1 from exported file, it creates by default sheet1 extra. So to avoid exception delete manually sheet1.
See the bellow screenshot for exported excel file from NAV 2009 after upgraded.
Once the file is exported and save as with .xlsx, then you can import in NAV 2016 directly.


                                                       

Step 6 :                                         Now i am going to import this file. See the bellow screenshot.











Popular Posts