NAV 2015 Virtual\Hidden Tables


If you need to read data from Virtual Tables in NAV 2013 and NAV 2015

My Best NAV Virtual Tables\Hidden Tables

best vistual tables

NAV Developer Environment

Buld a new page on virtual table, run page, Filter Records with NAV Filters

example : Table Information

virtual table1

virtual table2

Advertisements

“Power BI”, You are welcome !


Power BI is now Generally Available with a big list of new features and capabilities. Read from the blog.

“Announcing Power BI general availability coming July 24th”

http://blogs.msdn.com/b/powerbi/archive/2015/07/10/announcing-power-bi-general-availability-coming-july-24th.aspx#whatsnew

power bi nav 2015

Power BI “Topics”

Power BI Desktop, Power BI web authoring and data exploration enhancements, Power BI visuals open source project,Team collaboration and enhanced Excel support, New Power BI mobile app for Android, many other features added.

About NAV  2015 & Power BI

PowerBI.com and Microsoft Dynamics NAV 2015

nice post from NAV Team Blog about NAV and Power BI Integration here : http://blogs.msdn.com/b/nav/archive/2015/03/27/powerbi-com-and-microsoft-dynamics-nav-2015.aspx

try Power BI now    http://powerbi.microsoft.com/?WT.mc_id=Blog_GA_0710

Power BI Desktop   https://powerbi.microsoft.com/desktop

Power BI Services   https://powerbi.microsoft.com/?WT.mc_id=Blog_GA_James

Upgrading to NAV 2015 and automation with Powershell


Nice updated pages on MSDN about “Upgrading to NAV 2015 and automation with Powershell Scripts”

upgrade

Upgrade

To upgrade to Microsoft Dynamics NAV 2015, you must complete the following main steps: 1) Convert the database, 2) Upgrade the code, 3) Upgrade the data  https://msdn.microsoft.com/en-us/library/dn271649(v=nav.80).aspx

Convert the database https://msdn.microsoft.com/en-us/library/dn271650(v=nav.80).aspx

Upgrading the Application Code https://msdn.microsoft.com/en-us/library/dn271652(v=nav.80).aspx

Comparing and Merging Application Object Source Files https://msdn.microsoft.com/en-us/library/dn789581(v=nav.80).aspx

Merging Application Objects using the Example Scripts https://msdn.microsoft.com/en-us/library/dn762354(v=nav.80).aspx

Upgrading the Data https://msdn.microsoft.com/en-us/library/dn271668(v=nav.80).aspx

Powershell

Automating the Upgrade Process using Sample Windows PowerShell Scripts  https://msdn.microsoft.com/en-us/library/dn414687(v=nav.80).aspx

“ALL” about “Deploying Microsoft Dynamics NAV on Windows Azure”


“ALL” about “Deploying Microsoft Dynamics NAV on Windows Azure”

azure NAV 2015

“Preparing”

Planning and Preparing
https://msdn.microsoft.com/en-us/library/dn271683(v=nav.80).aspx

Preparing a Microsoft Azure Virtual Machine Image for Microsoft Dynamics NAV
https://msdn.microsoft.com/en-us/library/dn271692(v=nav.80).aspx

Preparing for Deploying Microsoft Dynamics NAV on Windows Azure
https://msdn.microsoft.com/en-us/library/dn271686(v=nav.80).aspx

Scaling the Microsoft Dynamics NAV Network Topology
https://msdn.microsoft.com/en-us/library/dn271671(v=nav.80).aspx

“Deploying”

Walkthrough: Deploying Microsoft Dynamics NAV on a Microsoft Azure Virtual Machine
https://msdn.microsoft.com/en-us/library/dn271695(v=nav.80).aspx

Deploying Microsoft Dynamics NAV Using the Example Scripts
The Example-1VM.ps1 and Example-2VM.ps1 scripts are located in the
\WindowsPowerShellScripts\Cloud\HowTo folder on the Microsoft Dynamics NAV installation media (DVD).
https://msdn.microsoft.com/en-us/library/dn271679(v=nav.80).aspx

Deploying and Managing Microsoft Dynamics NAV on Microsoft Azure
https://msdn.microsoft.com/en-us/library/dn271710(v=nav.80).aspx

“How-to”

How to: Create a Windows Azure Virtual Machine Operating System Image for Microsoft Dynamics NAV
https://msdn.microsoft.com/en-us/library/dn169001(v=nav.80).aspx

How to: Get the Microsoft Azure Image Name
https://msdn.microsoft.com/en-us/library/dn271690(v=nav.80).aspx

How to: Create a Microsoft Azure Virtual Machine Operating System Image for Microsoft Dynamics NAV
https://msdn.microsoft.com/en-us/library/dn271689(v=nav.80).aspx

“Video”
…nice Videos by Aleksandar Totovic here:

How Do I Deploy Microsoft Dynamics NAV 2015 on One Microsoft Azure VM https://msdn.microsoft.com/en-us/dynamics/nav/mt147285

How Do I Deploy Microsoft Dynamics NAV 2015 on Two Microsoft Azure VMs
https://msdn.microsoft.com/en-us/dynamics/nav/mt147286

With “Powershell”

great post by Vjeko here…

http://vjeko.com/blog/creating-azure-vm-nav-2015-using-powershell

Design Patterns Wiki: “Think NAV in design patterns”


Design Patterns Wiki: “Think NAV in design patterns”

nav patterns

Design Patterns Wiki https://community.dynamics.com/nav/w/designpatterns

NAV Design Patterns Repository https://community.dynamics.com/nav/w/designpatterns/105.nav-design-patterns-repository

Get Involved !!!!    https://community.dynamics.com/nav/w/designpatterns/106.get-involved

You have a pattern that you have used successfully? You have ideas on new patterns? You’ve found some existing design patterns which are used in the product but nobody (except a few) knows how it works, but you find it worth it to explain it for the NAV C/AL developers out there?

Send your pattern idea to Bogdana Botez as a private message on the community webpage. Once your first pattern is ready, we will review it as a team, and when signed off, you get author permissions on the Wiki site and from then on, you can continue publishing and editing existing patterns. However, only publish on the Wiki materials that we had signed-off (we don’t have moderation capabilities yet, so we count on each author to only make meaningful/agreed changes).

You and your company also get credit by being mentioned on the pattern and also on our patterns authors page.

Once you have the idea, writing it down shouldn’t take long. You will be helped by adopting the template that we’ve used.

Remember the rules

When handling design patterns, content quality is critical. We are trying our best to only publish content that is correct, relevant and has been reviewed by multiple developers. Therefore, we review and sign-off all patterns before publishing them (except for the videos made prior to 2015). All text content found on this Wiki and on the NAV Team Blog has been through one, usually multiple iterations of review. If you find something to correct, please comment on the pattern or contact Bogdana Botez, and we will review and update it.

We are working on creating a set of rules, which would help keeping the content clean and the project on the correct track. Find the rules here.

https://community.dynamics.com/nav/w/designpatterns/106.get-involved

Template for writing NAV Design Patterns https://community.dynamics.com/nav/w/designpatterns/107.template-for-writing-nav-design-patterns

“Check NAS” Process execution and Send Mail (Batch control)


“Check NAS” Process execution & Send Mail (Batch control)

How System Works

> System every “X minutes” check Status of Scheduled Processes and send a mail with Status (batch control)

Scheduling

> You can schedule this codeunit in some or in another Process Category Queue of your NAS; you can use also another separate NAS Server if you need parallelasynchronous check.

 

Codeunit “Check NAS” C/AL Code

Documentation

RS, NAS01, 20150710, “Check NAS” Codeunit

*** Function “CreaFileErrori

*** Function “ErrorSendMail

*** Function “JobStatus

OnRun()

//RS, NAS01, .sn

//Call Create Errors File

CreaFileErrori;

//RS, NAS01, .en

JobStatus()

//RS, NAS01, .sn

// Check “Job Log Entry” Process Status & Execution Time

IF recJobQEntry.FINDSET THEN

REPEAT //controllo se è in esecuzione da troppo tempo (check for LONG running time execution)

recJobQEntry.”Send error email” := FALSE;

IF (recJobQEntry.Status = recJobQEntry.Status::”In Process”) THEN

BEGIN

recJobQLogEntry.SETRANGE(“Object Type to Run”,recJobQEntry.”Object Type to Run”);

recJobQLogEntry.SETRANGE(“Object ID to Run”,recJobQEntry.”Object ID to Run”);

IF recJobQEntry.”Duration Process Max” > 0 THEN

IF recJobQLogEntry.FINDLAST THEN

IF CURRENTDATETIME – recJobQLogEntry.”Start Date/Time” > recJobQEntry.”Duration Process Max” THEN

recJobQEntry.”Send error email” := TRUE;

END

ELSE //se è in errore , IF process Is in “Error” State

IF (recJobQEntry.Status = recJobQEntry.Status::Error) THEN

recJobQEntry.”Send error email” := TRUE;

recJobQEntry.MODIFY;

UNTIL recJobQEntry.NEXT = 0;

//RS, NAS01, .en

ErrorSendEmail(txtFile01 : Text[100])

//RS, NAS01, .sn

//————— Build & Send Mail ———————-//

recSMTP.GET;

cuSendEmail.CreateMessage(recSMTP.”User ID”,

recSMTP.”Sender Address”,

recSMTP.”Receiver Address Log Interface”,

‘Errori Job Schedulate’,’In allegato i job in errore’,

FALSE);

//Add attachment

cuSendEmail.AddAttachment(txtFile01);

//Send attachment

cuSendEmail.Send;

//RS, NAS01, .en

CreaFileErrori()

//RS, NAS01 .sn

// Registrazione Errori NAS, Wrinting NAS Errors in Outpout File

GLOBALLANGUAGE(1040);

CLEAR(OutFile);

OutFile.TEXTMODE := TRUE;

OutFile.WRITEMODE := TRUE;

recSMTP.GET;

txtFileName_00 := recSMTP.”Error Folder Log” + recSMTP.”Error File Log”

+ DELCHR((FORMAT(TODAY,0,'<Year4><Month,2><Day,2>’) +’_’+ FORMAT(TIME)), ‘=’,’/ : .’) + ‘.csv’;

IF OutFile.OPEN(txtFileName_00) THEN

BEGIN

OutFile.CLOSE;

EXIT;

END;

OutFile.CREATE(txtFileName_00);

txtFineRiga:='<CRLF>’;

cr:=13;

lf:=10;

recJobQEntry.SETRANGE(“Send error email”,TRUE);

IF  recJobQEntry.FINDSET THEN

BEGIN

REPEAT

txtOutText:= ‘TIPO;’ + ‘NR JOB;’ + ‘JOB;’;

OutFile.WRITE(txtOutText); //scrivo riga

txtOutText:=FORMAT(FORMAT(recJobQEntry.”Object Type to Run”)+ ‘;’ +

FORMAT(recJobQEntry.”Object ID to Run”)+ ‘;’ +

recJobQEntry.Description, -250);

OutFile.WRITE(txtOutText); //scrivo riga , Write Line

txtOutText := ‘MESSAGGIO;’;

OutFile.WRITE(txtOutText); //scrivo riga, Write Line

OutFile.WRITE(FORMAT(recJobQEntry.”Error Message”,-250)); //scrivo riga

OutFile.WRITE(FORMAT(recJobQEntry.”Error Message 2″,-250)); //scrivo riga

OutFile.WRITE(FORMAT(recJobQEntry.”Error Message 3″,-250)); //scrivo riga

OutFile.WRITE(FORMAT(recJobQEntry.”Error Message 4″,-250)); //scrivo riga

CLEAR(txtOutText);

UNTIL  recJobQEntry.NEXT = 0;

END

ELSE

BEGIN

txtOutText:=’Nessun Errore’;

OutFile.WRITE(txtOutText);

END;

OutFile.CLOSE;  //chiudofile

// INVIO EMAIL CON ALLEGATI, SEND MAIL WITH ATTACHMENTS

ErrorSendEmail(txtFileName_00);

//RS, NAS01 .en

CODEUNIT SCHEDULING SAMPLE

Schedule Codeunit on NAS (example every 60 minutes)

nav 1

“SMTP MAIL Setup” Table with New Fields

smtp

New Fields to insert:

  • Field: File Attachment
  • Field: Patch Attachment
  • Field: Receiver Address
  • Field: VBScript Folder
  • Field: Receiver Address Log Interface
  • Field: Error Folder Log
  • Field: Receiver Warning
  • Field: Error File Log
  • Field: Sender Name
  • Field: Sender Address

RUN TABLE AND ENTER DATA IN NEW FIELDS (add fields on your Standard NAV Page)

nas 4

File Directory

  • Files are stored in “\NavisionShared\Log” > you can change by Setup

nas 6

Error File Sample NavJob20150706_170544”  FILE DETAILS

nas 7

NAV 2015, “How-to” solve “You are not authorized to sign in..” error


you are not aut

How to login in “any database”

Follow this and you can login in any database (delete ALL existing configuration and can login)

Source Waldo Blog  http://dynamicsuser.net/blogs/waldo/archive/2012/12/05/nav-2013-how-to-log-in-any-database.aspx

By SQL Script > Grant SUPER to Role ID in Access control table

There are 2 ways for your case, run SQL script on your database:

1. Come back to no user as before you create the user:

DELETE [Access Control]

DELETE [User]

2. Set all users as SUPER users:

UPDATE [Access Control] SET [Role ID] = ‘SUPER’

Source https://community.dynamics.com/nav/f/34/p/166672/408485#408485

by “PowerShell”, nice RESCUE Solution

1. Start the NAV 2015 Administration Shell or Powershell ISE

2. Execute the command:

  1. NewNAVServerUser ServerInstance YOURINSTANCENAME WindowsAccount YOURUSERACCOUNT

(of course replace the ‘YOUR…’ with your data 😀  ; probably YOURINSTANVCE should be: DynamicsNAV80 )

3. Execute the command:

  1. NewNAVServerUserPermissionSet ServerInstance YOURINSTANCENAME WindowsAccount YOURUSERACCOUNT PermissionSetID SUPER

Source https://community.dynamics.com/nav/f/34/p/166672/408485#408485

NAV MSDN Bible

You can find info on assigning permissions here: https://msdn.microsoft.com/en-us/library/dd338839(v=nav.80).aspx

And also general info on creating users here: https://msdn.microsoft.com/en-us/library/dd355093(v=nav.80).aspx

Source https://community.dynamics.com/nav/f/34/p/166672/408485#408485

Have Fun !

Check Objects “Modify Date” using “sys.all_objects”


If you need to check “Modify Date” for NAV Objects on SQL Server, you can use SQL Standard Function “sys.all_objects“; you don’t need to use standard NAV Objects Table.

Miodify Date

 

Examples:

USE [Demo Database NAV (7-1)]  GO

— VIEWS

SELECT * FROM sys.all_objects WHERE type_desc like ‘VIEW’ ORDER BY modify_date DESC

— TABLES

SELECT * FROM sys.all_objects WHERE type_desc like ‘USER_TABLE’ ORDER BY modify_date DESC

— VIEWS & TABLES

SELECT * FROM sys.all_objects WHERE type_desc like ‘VIEW’ or type_desc like ‘USER_TABLE’ ORDER BY modify_date DESC

— KEYS

SELECT * FROM sys.all_objects WHERE type_desc like ‘%KEY%’ ORDER BY modify_date DESC

— COMPANY OBJECTS (ex Cronus Italy)

SELECT * FROM sys.all_objects WHERE name like ‘CRONUS%’ ORDER BY modify_date DESC

— ALL OBJECTS

SELECT * FROM sys.all_objects ORDER BY modify_date DESC

Have Fun !

NAV Services and Sessions: Counting, Killing, Timeout


NAV Services & Sessions: Counting, Killing, Timeout

Kill session

Understanding and Configuring Session Timeout

“When you start the Microsoft Dynamics NAV Windows client or the Microsoft Dynamics NAV Web client, a connection is established with the Microsoft Dynamics NAV Server instance, and a corresponding session is added on Microsoft Dynamics NAV Server. Microsoft Dynamics NAV Server includes several timeout settings that determine when a session closes because of inactivity, a lost connection, or the client closes.

Source NAVTeam & NAV MSDN Online

http://blogs.msdn.com/b/nav/archive/2014/01/23/understanding-and-configuring-session-timeouts.aspx

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

NAV 2015 Session Counting

Nice post about Session Counting in 4 tier environment
Source Kine
http://mibuso.com/blogs/kine/2015/04/30/nav-20132015-session-counting/

Stop Idle using Powershell
Stopping Idle Microsoft Dynamics NAV Services using Powershell

Nice Post about “How to Stop” Idle Nav Services

“PowerShell has a powerful set of commands that allow you to start new sessions and execute commands in those sessions within the context of your main script.”
Source Softwareanswers.co.uk
http://www.softwareanswers.co.uk/software_answers/2015/03/stopping-idle-microsoft-dynamics-nav-services.html

Kill Sessions in Dynamics NAV 2013/2015

Source Olof Simren

Kill Sessions in Dynamics NAV

NAV 2015 “Full” Tracking
NAV 2015 TRACKING SESSIONS, USERS ACTIVITY, CHANGE LOG
Source me
https://robertostefanettinavblog.wordpress.com/2015/06/09/nav-2015-tracking-sessions-users-activity-change-log/

……………………

KILL INACTIVE NAV SESSIONS FROM SQL
(only dor Old Releases NAV 3.10 .. NAV 2009)

// KILL SQL INACTIVE SESSIONS FOR NAV APPLICATIONS, ONLY FOR SLEEPING SESSIONS
Source “Me”
http://blogs.dotnethell.it/dynamicsnav/SQL-KILL-INACTIVE-NAV-SESSIONS__19611.aspx

Kill Idle Connections using ADO
“To save concurrent uses in NAV it might be a good idea to kill idle connections automatically.”
Nice with ADO Connerction”

Source Gunnar

Kill Idle Connections