About “UI Elements Removal” feature


About “UI Elements Removal” feature

 

I never talked about this subject because is “daily bread” for NAV users, however, since some of NAV customers (and some of my readers) are still in old versions of NAV and they want to understand how this technology works, i collected a bit of existing documentation about this topic; below you will find many links from MSDN.

 

About “UI Elements Removal” feature

“UI Elements Removal” feature was introduced with NAV 2015; Microsoft Position at the end of 2014 before NAV 2015 launching date: “To simplify the users’ experience in Microsoft Dynamics NAV 2015, we have introduced the UI Elements Removal feature. The feature can be used to remove user interface (UI) elements, such as fields and actions from pages in Microsoft Dynamics NAV so that the Role Center and pages become simpler to look at, easier to navigate on, and faster to get through.”

Access By Permission on NAV 2015 (v=nav.80).aspx

How to: Try Out the UI Elements Removal Feature based on Demo Permission Set

(v=nav.80).aspx

How System Works with “LicenseFileAndUserPermissions” option active?

When you configure user interfaces in Microsoft Dynamics NAV, you should make sure that the UI Elements Removal field is set to its default, LicenseFileAndUserPermissions, so that you only see the relevant UI elements in the Customize window.

 

What objects will be removed objects by LicenseFileAndUserPermissions option?

All types of UI elements will be removed if they relate to objects that are not included in the license or the user does not have the required permission to the objects:

• Fields, Actions, Page parts

 

“UI elements” removed “By Design”

Directly Related

UI elements that are directly related to an object through the TableRelation or the CalcFormula property can be removed automatically according to the license file and/or user permissions

Not Directly Related

For UI elements that are not directly related to an object, you can use the AccessByPermission property to remove the element according to the user’s permission to a related object

“All types” of UI elements will be removed if they relate to an object to which the user does not have the required permissions:

  • Fields on pages, including FactBoxes
  • Actions on pages, including toolbars and navigation panes

  • Page parts, such as Lines FastTabs

 

About “FOUNDATION” permissions set

A prerequisite for all other permission sets. The FOUNDATION permission set grants access to system tables and application setup tables that are required for most application features to work.

This permission set is “recommended” when using the UI Elements Removal feature to automatically remove UI elements according to user permissions.

To use the LicenseFileAndUserPermissions option in the UI Elements Removal field, you must assign the special FOUNDATION permission set to the user along with the relevant application permission sets that define which application objects the user will access. Unlike the BASIC permission set, the FOUNDATION permission set only grants access to application setup and system tables and requires that other assigned permission sets define which specific application objects can be accessed. For more information, see Special Permission Sets.

Note that all of the 167 default permission sets that are provided with Microsoft Dynamics NAV are ready to support the FOUNDATION permission set to remove UI elements according to user permissions. You must therefore edit the relevant permission sets as explained in the following procedures.

Important ! 1

“If you defined the property for a table field, then all instances of the field on pages are removed from the user’s view. To override the property on an individual page field, delete the permission value after the = sign in the Value field. Do not delete the whole string as that will be replicated on the property of the table field.”

Important! 2

“The majority of the permission sets that are provided with the CRONUS demonstration database cannot be combined with the FOUNDATION permission set to fully use the UI Elements Removal feature. You must first create or edit the relevant permission sets to avoid that the user is blocked from performing the involved tasks. For more information, see How to: Create or Modify Permission Sets.”

 

Post Based on MSDN Documentation and others sources

Accessbypermission Property (NAV 2017 HELP on MSDN)

Source https://msdn.microsoft.com/en-us/dynamics-nav/accessbypermission-property

Source https://msdn.microsoft.com/en-us/dynamics-nav/how-to–remove-ui-elements-using-the-accessbypermission-property

 

SAMPLES

NAV Service Configuration

“UI Elements Removal” property with “LicenseFileAndUserPermissions” option

To use this property, the UI Elements Removal field in the Microsoft Dynamics NAV Server Administration tool must be set to LicenseFile or LicenseFileAndUserPermissions.

This Property Applies To

  • Fields in tables (affecting all related fields on pages)
  • Fields on pages

  • Actions on pages

  • MenuSuite items

  • Page parts, such as a Lines FastTab

  •  

    “AccessByPermission “Property Values

    When you choose the AssistEdit button, the Access By Permission window opens.

    Field Description

    Object Type Specify the type of object to which permission is required to display the UI element

    Object ID Specify the object to which permission is required to display the UI element

    Read Specify if Read permission is required to display the UI element

    Insert Specify if Insert permission is required to display the UI element

    Modify Specify if Modify permission is required to display the UI element

    Delete Specify if Delete permission is required to display the UI element

    Execute Specify if Execute permission is required to display the UI element

     

    Sample on table 137 “Sales Invoice Line”, on field “Line Amount”

    Others samples (Standard NAV)

    Table 37 “Sales Line”

     

    Page 42 “Sales Order”

     

    MenuSuite

    Bank Accounts List Page

    Item PropertiesAccess by Permission

     

    AVAILABLE DOCUMENTATION ON MSDN, Microsoft NAVHelp on MSDN

    Accessbypermission Property (NAV 2017)

    https://msdn.microsoft.com/en-us/dynamics-nav/accessbypermission-property

    Removing Elements from the User Interface According to Permissions

    https://msdn.microsoft.com/en-us/dynamics-nav/removing-elements-from-the-user-interface-according-to-permissions

    How to: Remove UI Elements Using the AccessByPermission Property

    https://msdn.microsoft.com/en-us/dynamics-nav/how-to–remove-ui-elements-using-the-accessbypermission-property

    How to: Specify When UI Elements Are Removed

    https://msdn.microsoft.com/en-us/dynamics-nav/how-to–specify-when-ui-elements-are-removed

    ABOUT DEMO PERMISSIONS SETS

    How to: Try Out the UI Elements Removal Feature Based on Demonstration Permission Sets

    https://msdn.microsoft.com/en-us/dynamics-nav/how-to–try-out-the-ui-elements-removal-feature-based-on-demonstration-permission-sets

    Advertisements

    “How -to” restrict multiple logins for same user in NAV


    Force logins limitations for some users can boost\improve system (few active Sessions..) as standard users can login in NAV multiple times, ex of real Scenario: CEO can have multiple logins, operational people NO !!

     NAV 2013 and Later

    From NAV 2013 Microsoft created a table named: Active Sessions – Table ID 2000000110 This table contains information about all the active sessions, showing Connected Client Type\PC and Users)

    Take a look to my previous post about “ALL ABOUT” Kill (In)active Sessions when “no more licenses”, with the VB.NET app in this post, you can show Client Type\PC and Connected User from this app to decide “who is the user” than try to use more than one NAV session.

    Source https://robertostefanettinavblog.wordpress.com/2017/01/15/all-about-kill-inactive-sessions-when-no-more-licenses/


    Starting from User Setup Table

    You can add a boolean field “Multilogin” to User Setup Table (table 91)” ; with this boolean field you can check if user can login more times in NAV instead of only one time.

    User Setup – Table ID 91 User Setup

    ml-1

    Before you need to add the new field “Multilogin” on Page “User Setup” (…or comment C/AL check code… you can’t login in anyway if you haven’t configured your user in this table)

    ..We can’t use System Table User.. because is used by System on Login process .. if you try to use this table you can’t start Windows Client…

    ml-2

    ml 3.png

    To Restrict multiple logins we can use Login Codeunit

    Codeunit 40 – LogInManagement

    This codeunit is ht first fired on Login Time and I can add C/AL code here to trap user logins.
    Steps: Open Codeunit 40, goto function CompanyOpen()

    In CompanyOpen function add two new local variables: locUser and locActiveSessione

    ml-4

    Modify the CompanyOpen function in this way:

    C/AL Code “Before” 

    CompanyOpen()

    IF GUIALLOWED THEN   LogInStart; C/AL Code “After”

    CompanyOpen()

    locUser.GET(UPPERCASE(USERID));

    IF NOT locUser.Multilogin THEN

    BEGIN

    locActiveSession.RESET;

    locActiveSession.SETRANGE(“User ID”,UPPERCASE(USERID));

    locActiveSession.SETRANGE(“Client Type”, locActiveSession.”Client Type”::”Windows Client”); //For   RTC\Windows Clients

    IF locActiveSession.COUNT > 1 THEN

    ERROR(‘You are currently logged in NAV, you can’t have more sessions!’);

    END;

    IF GUIALLOWED THEN

    LogInStart;

    // Register all Microsoft Dynamics CRM connection strings

    IF CRMConnectionSetup.GET THEN

    CRMConnectionSetup.UpdateAllConnectionRegistrations;

    Results

    System abort login codeunit and display the Error Message “’You are currently logged in NAV, you can’t have more sessions!”; you can use\trap also others login types: ex: Web Client, Web Service, Background Sessions and so on.

    ml 5.png

    Another Nice Feature

    You can decide also “how many sessions” each user can launch adding a “No. Sessions” field in User Table.

     ml-6

    C/AL Code

    CompanyOpen()

    locUser.GET(UPPERCASE(USERID));

    IF NOT locUser.Multilogin THEN

    BEGIN

    locActiveSession.RESET;

    locActiveSession.SETRANGE(“User ID”,UPPERCASE(USERID));

    locActiveSession.SETRANGE(“Client Type”, locActiveSession.”Client Type”::”Windows Client”); //For   RTC\Windows Clients

    IF locActiveSession.COUNT > 1 THEN

    ERROR(‘You are currently logged in NAV, you can’t have more sessions!’);

    END

    ELSE

    BEGIN

    locActiveSession.RESET;

    locActiveSession.SETRANGE(“User ID”,UPPERCASE(USERID));

    locActiveSession.SETRANGE(“Client Type”, locActiveSession.”Client Type”::”Windows Client”); //For   RTC\Windows Clients

    IF locActiveSession.COUNT > locUser.”No. Sessions” THEN

    ERROR(‘You are currently logged in NAV, you can’t have more than ‘ + FORMAT(locActiveSession.COUNT-1) + ‘ sessions!’);

    END;

    IF GUIALLOWED THEN

    LogInStart;

    // Register all Microsoft Dynamics CRM connection strings

    IF CRMConnectionSetup.GET THEN

    CRMConnectionSetup.UpdateAllConnectionRegistrations;

    Results

    ml-7

    Bingo !

    “ALL ABOUT” Kill (In)active Sessions when “no more licenses”


    Sometimes it happens that there are “no more licenses available” for Dynamics NAV, from NAV 2013 R2 and next releases, it’s necessary to run the Windows Client or to use Powershell statements to kill some sessions and unlock others.
    If you are unable to access to NAV (“no more licenses”), you should need to use SQL Console and kill sessions from there (reading from Active Sessions Table of from sysprocesses View on master db..)
    “Some user don’t want to use SQL Server to achieve this !” …
    WARNING : “No more licenses !!”   … Immediately Buy Others !!!
    license1.png
    There are many ways to kill sessions, i list below some methods.
    With NAV Server Login
    – C/AL
    – Powershell
    Without NAV Server Login
    – SQL Server Stored Procedures\T-Sql
    – VB .NET Application
    Below some methods to kill the sessions even in the case of “no more licenses available“: SQL Script and VB.NET
    C/AL Statement to Kill Sessions
    The Standard Statement for C/AL Code is:  STOPSESSION(“Session ID”);
    Example:
    IF CONFIRM (‘Kill Session ?’) THEN STOPSESSION(“Session ID”) … GOAL !
    …. BUT YOU NEED TO CONNECT TO NAV SERVER !  .. sometimes is NOT possible!
    ———————————————————-
    My VB.NET Solution
    “I wrote a simple solution on VB.NET to Kill Active Sessions using a SQL Connections,
    you cand find it on GITHUB and TECHNET.”
    ON GITHUB
    ON TECHNET
    Post link
    Download link
    About My VB.NET Simple Solution, some pictures
    kill1
    kill-2
    kill-3
    App Config File
    <?xml version=”1.0″ encoding=”utf-8″ ?>
    <configuration>
    <configSections>
    </configSections>
    <connectionStrings>
    <add name=”NAV_2017_KIll_SQL_Inactive_Sessions.My.MySettings.Demo_Database_NAV__10_0_ConnectionString”
    connectionString=”Data Source=STEFANETTILEN\SQL2012ENT;Initial Catalog=&quot;Demo Database NAV (10-0)&quot;;Integrated Security=True”
    providerName=”System.Data.SqlClient” />
    </connectionStrings>
    <startup>
    <supportedRuntime version=”v4.0″ sku=”.NETFramework,Version=v4.5″ />
    </startup>
    </configuration>
    ———————————————————-
    SQL KILL STATEMENTS
    KILL (Transact-SQL)
    – Syntax for SQL Server, Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse

    KILL ‘session_id’ [;]    
    Source https://msdn.microsoft.com/it-it/library/ms173730.aspx

    KEEP IN MIND !!!   DON’T KILL PROCESSES IN THIS STATES:
    -AWAITING COMMAND
    -CHECKPOINT SLEEP
    -LAZY WRITER
    -LOCK MONITOR
    -SIGNAL HANDLER
    My Customized release
    1) SP_SQL_KillNavServerSession
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    GO
    — =============================================
    — Description: KILL ALL NAV ACTIVE USER SESSSIONS WITH MULTIPLE INSTANCES
    — =============================================
    CREATE PROCEDURE [dbo].[SP_SQL_KillNavServerSession]
    AS
    BEGIN
    DECLARE @RowIndex AS INT;
    DECLARE @MaxIndex AS INT;
     SET @RowIndex = 1;
    SELECT @MaxIndex =
    (SELECT top 1 MIN([Session ID])
    FROM [ITA].[dbo].[Active Session]
    WHERE [User ID] = ‘************’
    group by [Session ID])
     — Remove Active Session
    DELETE FROM [DB].[dbo].[Active Session] where [Session ID]>@MaxIndex AND [User ID] = ‘*********’
    END
    2) SP_SQL_KillNONavSession
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    GO
    — =============================================
    — Description: KILL SLEEPING NO NAV SESSIONS
    — =============================================
    CREATE PROCEDURE [dbo].[SP_SQL_KillNoNavSession]
    AS
    BEGIN
    DECLARE @Session TABLE
    (
    id INT IDENTITY(1,1) PRIMARY KEY,
    [sid] int,
    ltime DATETIME,
    hname varchar(MAX),
    uid varchar(100),
    lret datetime,
    etime int
    );
    DECLARE @NotAffectedUserList TABLE
    (
    id INT IDENTITY(1,1) PRIMARY KEY,
    [uid] varchar(MAX)
    );
    DECLARE @session_time as int;
    DECLARE @vSID as int;
    DECLARE @command as varchar(MAX);
    DECLARE @RowIndex AS INT;
    DECLARE @MaxIndex AS INT;
    set @session_time=15; — Assign Session time in Minutes
    — Assign List of Users who “will not be affected” by this Session Kill
    INSERT into @NotAffectedUserList SELECT ‘sa’
    INSERT into @NotAffectedUserList SELECT ‘NT AUTHORITY\SERVIZIO DI RETE’
    INSERT into @NotAffectedUserList SELECT ‘**************’
    INSERT into @NotAffectedUserList SELECT ‘scheduler’
    INSERT into @Session
    SELECT
    session_id as [Session ID],
    login_time as [Login Time],
    host_name as [Host Name],
    LOGIN_NAME as [Login ID],
    last_request_end_time as [Last Request End Time],
    DATEDIFF(minute,  last_request_end_time,getdate()) as[Elapsed Time (minutes)]
    FROM
    sys.dm_exec_sessions
    WHERE
    open_transaction_count = 0
    AND host_name <> ‘NAVSRV’ COLLATE Latin1_General_CI_AS
    AND host_name is not NULL
    AND DATEDIFF(minute,  last_request_end_time,getdate())>=@session_time
    –AND client_version=4
    AND login_name COLLATE Latin1_General_CI_AS not in
    (
    SELECT DISTINCT uid
    FROM @NotAffectedUserList
    )
    SET @RowIndex = 1;
    SELECT @MaxIndex = MAX(id) FROM @Session;
    WHILE( @RowIndex <= @MaxIndex)
    BEGIN
    SELECT @vSID = sid
    FROM @Session
    WHERE id = @RowIndex;
     — String together the KILL statement
    SELECT @command = ‘KILL ‘ + CAST(@vSID AS varchar(5))
     BEGIN TRY
      — Execute the final string to KILL the spids
    EXEC (@command)
    END TRY
    BEGIN CATCH
    declare @emsg varchar(500);
    SELECT
    @emsg=ERROR_MESSAGE();
      print @emsg;
    END CATCH
     SET @RowIndex = @RowIndex + 1;
    END;
    END
    // KILL SQL INACTIVE SESSIONS FOR NAV APPLICATIONS
    // ONLY FOR SLEEPING SESSIONS – OLD NAV CLIENTS
    a very Simple SQL Stored Procedure to ‘kill’ sql inactive sessions
    idle (sleeping) for 10 minutes related to “Nav Client” applications
    *** it’s necessary to identify correct “application name” ***
    *** ex: program_name LIKE N’%NAV client% *** or %NAV Web%
    DECLARE @v_spid INT
    DECLARE c_Users CURSOR
    FAST_FORWARD FOR
    SELECT SPID
    FROM master..sysprocesses (NOLOCK)
    WHERE spid>50
    AND loginame <> ‘DIR%’
    AND program_name LIKE N’%NAV client%’
    AND status=’sleeping’
    AND DATEDIFF(mi,last_batch,GETDATE())>=10
    AND spid <> @@spid
    OPEN c_Users
    FETCH NEXT FROM c_Users INTO @v_spid
    WHILE (@@FETCH_STATUS=0)
    BEGIN
    PRINT ‘Killing ‘+ CONVERT(VARCHAR,@v_spid)+’…’
    EXEC(‘KILL ‘+ @v_spid)
    FETCH NEXT FROM c_Users INTO @v_spid
    END
    CLOSE c_Users
    DEALLOCATE c_Users
    ———————————————————-
    OTHERS NICE SOLUTIONS (NAV LOGIN IS REQUIRED)
    Olof Simren Solution
    Kill Sessions in Dynamics NAV (from NAV Page)
    Source https://www.olofsimren.com/kill-sessions-in-dynamics-nav/
    Gunnar Solutions
    ADO NET Solution
    Kill Idle Connections
    Source http://www.dynamics.is/?p=471
    Poweshell Solution
    Stopping Idle Microsoft Dynamics NAV Services
    Nice Sessions Killing !

    Word Layout reports data control by C/AL


    word layout

    Some have asked me (both on Dynamics Community and Mibuso) how to use in NAV Word Layout reports property to hide the value 0 or other values in fields under conditions.

    Sample of question: “I have made a report in NAV 2016 using Word and XML. Everything is very well, but I have one problem. When “quantity” is added to the report, writes NAV “0” in all cells with no information. In the attached picture saying “with error” you can see That the “quantity” cell with no information is filled with “0”.”

    Question ref. http://forum.mibuso.com/discussion/67111/nav-2016-word-report-is-generating-extra-0
    Answer
    No, only in C / SIDE in this case (simple common solution), the use in this way (no logic in Word, all in C (SIDE) .. but .. i think That you can create functions in Word (macro) ,in word template. you can modify XML an add also Javascript support… an so on..”

    Some functions can be used with C / AL
    With these sample functions you can simulate what is done by the visibility property of the RDLC report or by VBNET Functions included in the report (Public Functions on CODE section of reports).

    Examples

    Num2Str (IdecValue: Decimal) OtxtValue: Text [100]
    IF IdecValue <> 0 THEN BEGIN
    OtxtValue: = FORMAT (IdecValue, 0, ‘<Precision, 2: 2> <Standard Format, 0>’);
    E
    ND;

    Num2Str2 (IdecValue: Decimal) OtxtValue: Text [100]
    OtxtValue:=FORMAT(IdecValue,0,'<Precision,2:2><Standard Format,0>’);

    Int2Str (inValue: Integer) OtxtValue: Text [100]
    IF inValue <> 0 THEN BEGIN
    OtxtValue: = FORMAT (inValue);
    END;
    ..
    etc etc.

    How to Use MVC.Net on the Dynamics NAV Database Structure


    How to Use MVC.Net on the Dynamics NAV Database Structure

    MVC

    nice post about ASP MVC.Net integration with NAV (post by Codeproject).

    About ASP MVC.Net

    ASP MVC.Net is an robust & good pattern-based way to build dynamic websites; if you don’t like to use Web Services & OData this is a valid alternative (old but actual). You can use to build Website based on NAV database structure. “How you can use the Entity Framework in an MVC.Net web application with Dynamics NAV ?”

    Tutorial

    This tutorial will show you how you can use the Entity Framework in an MVC.Net web application with Dynamics NAV.

    Take a look to project.

    Source: http://www.codeproject.com/Articles/421643/How-to-Use-MVC-Net-on-the-Dynamics-NAV-Database-St

    NAV 2013 SQL Query Codeunit Tool (SQL From NAV Part II)


    NAV 2013 SQL Query Codeunit tool

    i found this nice demo tool by Dynamic Manufacturing Solutions on Mibuso.

    Source http://mibuso.com/downloads/nav-2013-sql-query-codeunit
    nice tool, based on .NET integration (AD), usefull if you don’t like Powershell.

    Purpose of this tool
    “Simple codeunit allows you to execute an SQL query to populate any NAV (temporary) table using one call.  Test page is included to demonstrate functionality with a simple query.”

    … Nice tool (with demo page & codeunit objects) tu run SQL Statements directly from NAV using .NET integration (ADODB); you can run SQL Select statements and any sql commands (if you have rights).

    Modifications

    I did some modifications to load a generic table using dynamics fields.. example field1. field2, fiels3 etc. and possibility lo load a saved SQL Statement from “.SQL” Script file or from BLOB field in “SQL Commands Table” etc. ..

    Example of utilization
    ..you can retrieve Intercompany “Contacts” running a simple “SELECT with UNION SQL statement”…
    .. also you can publish this page “as a web service” to retrive\expose data from multi-sources database using only a web service connection!

    SQL RUN COMMAND

    SIMPLE QUERY
    SELECT SUM(Quantity) AS QuantityTot, [Item No_], [Location Code], [Lot No_], [Serial No_]
    FROM dbo.[CRONUS Italia S_p_A_$Item Ledger Entry]
    GROUP BY [Item No_], [Location Code],[Lot No_],[Serial No_]
    HAVING ([Location Code]<>” AND (SUM(Quantity)) >0)

    MORE COMPLEX QUERY WITH HAVING FILTERS
    SELECT SUM(Quantity) AS Field1,  [Item No_] as Field2, [Location Code] as Field3,
    [Lot No_] as Field4, [Serial No_] as Fileld5 FROM dbo.[CRONUS Italia S_p_A_$Item Ledger Entry]
    GROUP BY [Item No_], [Location Code],[Lot No_],[Serial No_]
    HAVING ([Location Code]<>” AND (SUM(Quantity)) >0)

    SQL STATEMENT TO “CREATE A NEW TABLE”
    CREATE ACTIVITY 2 TABLE
    CREATE TABLE [dbo].[CRONUS Italia S_p_A_$Activity_2](
    [timestamp] [timestamp] NOT NULL,
    [Code] nvarchar NOT NULL,
    [Description] nvarchar NOT NULL,
    CONSTRAINT [CRONUS Italia S_p_A_$Activity$10] PRIMARY KEY CLUSTERED
    (
    [Code] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    ANY SQL COMMAND EX  BACKUP…..

    etc. etc.

    Great tool!

    Source http://mibuso.com/downloads/nav-2013-sql-query-codeunit

    Load Data into NAV using C/AL and Powershell (SQL From NAV Part I)


    Load Data into NAV using C/AL & Powershell

    powershell cAL 

    Sample Scenario

    “Read distributed data from Sql Server and load a data warehouse using NAV C/AL with Powershell integration”

    If you need to retrieve (and aggregate) data from more than ONE company or from more than ONE database , in NAV you can use “SQL Server Views” objects mapped as “NAV linked objects”.

    … but if don’t want to use NAV Linked objects (SQL Tables or SQL View because is complex and time consuming to managing with many companies\many databases or because you don’t’ want to consume NAV tables for linked objects) and you don’t like “.net integrations” or utilization of external applications, you can use Powershell in NAV (and outside NAV certainly…)

    You can achieve this goal using a Hybrid Methods to load data into NAV from different sources

    • Read data from Powershell
    • Write Data\Processing in NAV with C/AL Coding

    You can use two functions by Waldo to do this:

    • Powershell Wrapper for NAV C/AL function
    • Invoke-NAVSQL function

    Scenario

    • Use good Waldo function to launch powershell fro NAV (after this you can schedule on NAV NAS load data process) – PowerShell Wrapper
    • Read Sql Views from Powershell using Waldo Invoke-NAVSQL’ function
    • Write into NAV table using C/AL logic & data validations mixing C/AL and Powershell commands.

    NB: Powershell consumes a concurrent user.

    Details about these good functions by Waldo

    “How-to” use powershell in NAV (NAV Powershell Wrapper)

    “How- to” load data into NAV with Powershell? (Invoke-NAVSQL)

    Example

    Get all companies, and get the customers of the first company:

    $Mycompanies = Invoke-NAVSql -ServerInstance ‘dynamicsNAV90’ -SQLCommand ‘Select * From Company’

    $Customers = $Mycompanies |

    select -First 1 |

    foreach{ Invoke-NAVSQL -ServerInstance ‘dynamicsNAV90’ -SQLCommand “select * from [$($_.Name)$(‘$Customer’)]” -ShowWriteHost }

    $Customers | select ‘No_’, Name, ‘Credit Limit (LCY)’ | Format-Table –AutoSize

    ALL Source links:

    http://www.waldo.be/2014/12/17/running-powershell-from-nav/

    http://mibuso.com/downloads/running-powershell-in-nav

    http://www.waldo.be/2016/06/08/invoke-navsql-execute-sql-queries-on-nav-databases-with-powershell/

    https://github.com/waldo1001/Cloud.Ready.Software.PowerShell/blob/master/PSModules/Cloud.Ready.Software.NAV/SQL/Invoke-NAVSQL.ps1

    Some links about Powershell “cmdlets” for NAV


    Some links about Powershell “cmdlets” for NAV

    CMDLETS” IN NAV

    The cmdlets are available in two Windows PowerShell modules: Microsoft Dynamics NAV 2016 Administration Shell and Microsoft Dynamics NAV 2016 Development Shell.

    • The Microsoft Dynamics NAV 2016 Administration Shell includes cmdlets for administering the Microsoft Dynamics NAV deployment, such as adding and configuring Microsoft Dynamics NAV server instances, databases, and users. Also included are cmdlets for administering extension packages. The Microsoft Dynamics NAV 2016 Administration Shell is installed with the Microsoft Dynamics NAV Server.
    • The Microsoft Dynamics NAV 2016 Development Shell includes cmdlets for merging and modifying application object files. Also included are cmdlets for creating extension packages. The Microsoft Dynamics NAV 2016 Development Shell is installed with the Microsoft Dynamics NAV Development Environment.

    https://msdn.microsoft.com/en-us/library/hh166599(v=nav.90).aspx

    “ALL” about CMDLETS LINKS

    A list of bestlinks about cmdlets in NAV 2016 (MSDN)

    Administration Cmdlets for Microsoft Dynamics NAV

    https://msdn.microsoft.com/en-us/library/jj672916(v=nav.90).aspx

    Development Cmdlets for Microsoft Dynamics NAV

    https://msdn.microsoft.com/en-us/library/dn789712(v=nav.90).aspx

    Microsoft Dynamics NAV Windows PowerShell Cmdlets

    https://msdn.microsoft.com/en-us/library/hh166599(v=nav.90).aspx

    Comparing and Merging Application Object Source Files

    https://msdn.microsoft.com/en-us/library/dn789581(v=nav.90).aspx

    Administration Cmdlets for Microsoft Dynamics NAV Extensions

    https://msdn.microsoft.com/en-us/library/mt584148(v=nav.90).aspx

    Development Cmdlets for Microsoft Dynamics NAV Extensions

    https://msdn.microsoft.com/en-us/library/mt584152(v=nav.90).aspx

    Changes to Microsoft Dynamics NAV Cmdlets From Previous Release

    https://msdn.microsoft.com/en-us/library/dn951485(v=nav.90).aspx

    Exporting and Importing Companies and Other Data

    https://msdn.microsoft.com/en-us/library/dn789595(v=nav.90).aspx

    Others nice links below:

    Guida introduttiva a Windows PowerShell

    https://msdn.microsoft.com/powershell/scripting/getting-started/getting-started-with-windows-powershell

    Sample Windows PowerShell Scripts for Microsoft Dynamics NAV

    https://msdn.microsoft.com/en-us/library/dn271676(v=nav.90).aspx

    Managing Dynamics NAV with PowerShell

    http://www.softwareanswers.co.uk/software_answers/2014/09/managing-dynamics-nav-with-powershell.htm

    About Microsoft Dynamics NAV 2016 Development Shell

    CMDLETS LIST

    For a complete list of cmdlets:

    • Get-Command -Module Microsoft.Dynamics.Nav.Model.Tools, Microsoft.Dynamics.Nav.Apps.Tools, Microsoft.Dynamics.Nav.Apps.Management

     

    Cms Type Name
    Function Compile-NAVApplicationObject
    Function Create-NAVDatabase
    Function Delete-NAVApplicationObject
    Function Export-NAVApplicationObject
    Function Import-NAVApplicationObject
    Function Invoke-NAVDatabaseConversion
    Cmdlet Compare-NAVApplicationObject
    Cmdlet Export-NAVApplicationObjectLanguage
    Cmdlet Export-NAVAppPermissionSet
    Cmdlet Get-NAVAppInfo
    Cmdlet Get-NAVApplicationObjectProperty
    Cmdlet Get-NAVAppManifest
    Cmdlet Get-NAVAppTenant
    Cmdlet Import-NAVApplicationObjectLanguage
    Cmdlet Install-NAVApp
    Cmdlet Join-NAVApplicationObjectFile
    Cmdlet Join-NAVApplicationObjectLanguageFile
    Cmdlet Merge-NAVApplicationObject
    Cmdlet New-NAVAppManifest
    Cmdlet New-NAVAppManifestFile
    Cmdlet New-NAVAppPackage
    Cmdlet New-NAVCrmTable
    Cmdlet Publish-NAVApp
    Cmdlet Remove-NAVApplicationObjectLanguage
    Cmdlet Repair-NAVApp
    Cmdlet Set-NAVApplicationObjectProperty
    Cmdlet Set-NAVAppManifest
    Cmdlet Split-NAVApplicationObjectFile
    Cmdlet Split-NAVApplicationObjectLanguageFile
    Cmdlet Test-NAVApplicationObjectLanguage
    Cmdlet Uninstall-NAVApp
    Cmdlet Unpublish-NAVApp
    Cmdlet Update-NAVApplicationObject

    MY FAVORITE COMMANDS: “JOIN & SPLIT”

    JOIN

    Join-navapplicationobjectfile (SINGLE FILE CREATION COMMAND (CREATION WITH FIRST CHECK SYNTAX IN “JOIN” COMMAND)

    System stops single “file merging procedure” if any error is ecountered during converting process; system make a first check objects structure during this process.

    Command

    PS C:> cd E:MERGEPages

    PS E:MERGEPages> Join-navapplicationobjectfile -source ‘E:MERGEPages*.txt’ –destination ‘E:MERGEPagesPages_OutPages_OutMerged.txt’

    SAMPLE OUTPUT

    SAMPLE ERROR

    “WARNING: E:MERGEPagesPAG12133.TXT (252,28) = expected.”

    -a— 08/07/2016 10:28 147645 Pages_OutMerged.txt

    IF FILE EXIST….

    Overwrite?

    E:MERGEPagesSplitFilesPagesPAG12190.TXT

    [Y] Yes [A] Yes to All [N] No [L] No to All [S] Suspend [?] Help (default is “Y”):a

    Well done > File correctly created after syntax parsing.

    SPLIT

    Split-navapplicationobjectfile (SPLIT FILES CREATION COMMAND (WITH FIRST CHECK SYNTAX IN “SPLIT” COMMAND)

    System stops single “file merging procedure” if any error is ecountered during converting process; system make a first check objects structure during this process.

    Command

    PS C:Windowssystem32> Split-navapplicationobjectfile –source ‘E:MERGEPagesMergedFilesPages_OutMerged_12000.txt’ –destination ‘E:MERGEPagesSplitFilesPages12000_Split’

    SAMPLE OUTPUT

    HR (Human Resources) module in Dynamics NAV


    HR (Human Resources) module in Dynamics NAV

    NAV HR Module

    Some people have asked me about the HR NAV module, below some definitions and links.

    HR Module’s Topics Details
    The Human Resources feature lets you keep detailed records of your employees.
    You can register and maintain employee information, such as employment contracts, confidential information, qualifications, and employee contacts.
    You can also use Human Resources to register employee absences, which allows you to analyze registered absences as necessary.
    To start using Human Resources, you must set up employees and other basic information. You can also associate various codes to an employee, which allows you to filter information and view specific employees.

    Best Goals of Module
    – Candidate profiling (skills matrix)
    – Employees confidential information
    – Managing employees absences
    – Tracking of expensive equipments (assignements of benefits..etc.)
    – Possibile integration with NAV resources (ex: timesheets)
    – Possibile integration with external payroll system (NAV addons or Others..)

    MSDN, NAV 2016 Human Resources Help Page
    https://msdn.microsoft.com/en-us/library/hh167801(v=nav.90).aspx

    nice PDF here
    http://libertygrove.com/wp-content/uploads/2012/02/microsoft-dynamics-nav-human-resources.pdf

    nice yuoutube videos here


    NAV Project Management Module with resources integration

    Jobs & Projects
    https://msdn.microsoft.com/en-us/library/hh172378(v=nav.90).aspx

    Best Topics
    The Jobs area supports common project management tasks, such as configuring a job and scheduling a resource, as well as providing the information needed to manage budgets and monitor progress.
    With the Jobs feature, you can manage long-term projects that involve the use of man hours, machine hours, inventory items, and other types of usage that you want to keep track of.
    You can track machine and employee hours on the project by using time sheets. Using the Jobs functionality will provide a good overview, not only of individual jobs,
    but also of the allocation of employees, machinery and other resources being used in all projects. You can also use this functionality for many types of services and consultancy tasks.

    Task with resources
    Create, schedule and manage resources
    Integrate time sheets with job orders

    Resource Planning Topics
    https://msdn.microsoft.com/en-us/library/hh175161(v=nav.90).aspx

    Create and price resources, both persons and machines
    Set up time sheets and the approval process.

    Open Pages and Reports with filters using NAV Command lines


    You can have directs shortcuts (or hyperlinks in your emails) to open directly pages & reports passing filters.
    page2
    Example (page):
    – Base Command  dynamicsnav://server.local:port/Db/Company/runpage?page=9307
    – Filter  parameter  $filter=%27Purchase%20Header%27.%27No.%27%20IS%20%27OF16000933%27
    – View Mode  mode=View
    Sample Full Command
    dynamicsnav://server.local:port/Db/Company/runpage?page=9307&$filter=%27Purchase%20Header%27.%27No.%27%20IS%20%27OF16000933%27&mode=View
    Results 
    >  in this case system runs on NAV Client Page “9307 Order List” , the page is filtered on purchase Order No.’OF16000933
    ..you can build parametric strings to launch your pages\reports from command line (like existing features in NAV Approval Workflow).