How to display sum value in factbox on base of multiple line selection

How to display sum value in factbox on base of multiple line selection

Typical issue is to show a calculated value in a factbox on base of the currently selected line in a list page or the lines in a subpage. Is it possible to get a calculated value on base of more than one selected line? Let’s check out.

Let’s say we want to display the sum of field “Credit Limit” in the customer list when selecting multiple lines. So first we edit page 9082 “Customer Statistics FactBox”. There we add a global variable SumCredLimit (decimal) to the page, then we add a new line in page with sourceexpr = SumCredLimit.

Now we add a new global function SetSumCredLimit.

Save and close the page. Now we edit page 22 customer list. There we rename page part “Customer Statistics FactBox” to CustStatFbx. That’s needed, so that we can reach the custom functions in page “Customer Statistics FactBox” from page “customer list”.

After that edit the page code (F9). Goto trigger OnAfterGetCurrRecord, there add local variable Cust (record|Customer). Add following code to trigger:

Save and close the page. Now run the Nav windows client and goto customer list.
The result:

Reference: jonthan

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

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

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("DOMAIN\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 = 'DOMAIN\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.