Dynamics Nav – Business Central Developer Blog

Dynamics Nav 2016 Enable NavUserPassword authentication in simple steps

This post describes the simple steps to enable the NavUserPassword authentication

Download the poweshell script form Dropbox Link

Run Script as administrator

Run mmc.exe to open management console
File -> Add/Remove Snap-in

Select Certificate -> Add -> Select Computer account finish

New Certificate created on Personal Certificate folder with 10 year validity

Copy Certificate to Trusted Root Certification Authorities

Create New service with login account windows user

Copy certificate thumbprint from the certificate

Right click certificate open Details tab select Thumbprint Copy certificate Thumbprint

Update the certificate thumbprint and credential type NavUserPassword -> Restart the service

Create New NavUserPassword user in Navsion

Update Client Configuration file New Service details and authentication Details

Nav Client Open with NavUserPassword login Screen

Client opened with navuser password

Thanks to Saurav Dhyani
Thanks to MSDN

Navision Schedule Report warning There is no running job queue for scheduled reports.

Scheduled reports are entered in the job queue and processed at the scheduled time, similar to other jobs. If we choose to save the report to a file, then the processed report will sent to the Report Inbox,

While Scheduling the report, the scheduler page hitting warning message There is no running job queue for scheduled reports.

This is the standard error , you have to create new job queue with Job queue Category filter blank . Start Job queue and try again

Schedule Adjust Cost report on navision 2013 and higher versions

Job queues enables users to schedule and run specific reports and codeunits. You can set jobs to run one time, or recurring basis

Previous versions we have to install NAS to process job Queue , 2013 and higher versions default service will start process this job queue entries .

Create New Job Queue Category ADJUSTCOST

Create New Job Queue ADJUSTCOST
Select Job Queue Category to ADJUSTCOST
Start Job Queue

Make sure the running User ID has enough permission to run the report

Create New Job Queue Entry
Object Type Report
Report ID 795 Adjust Cost – Item Entries
Select Job Category ADJUSTCOST
Recurrence Tab Select days and Starting Time
Set Status to Ready
testing you may change the Earliest Start Date /Time

Job Queue Log Details
You may check the error and job status details on job queue log entries
Select Job –> log entries

How to save outlook attachments files from Navision ?

we have one new requirement to read outlook from Navision

Details

User will send one text file. The text file will be on specific format, then program should read new mails from outlook and download the file to a pacific place . Read the text file and creates item journals and posts it.

The main point program should download the attachment file, through outlook automation we can download the attachment .

Here is one example for downloading the mail

Create the automation variables check the image

Outlook mail Codeunit

IF ISCLEAR(outlookapplication) THEN
CREATE(outlookapplication,FALSE);
outlooknamespace:=outlookapplication.GetNamespace('MAPI');
outlooknamespace.Logon('Outlook','',TRUE,TRUE);
outlookMAPIFolder:=outlooknamespace.GetDefaultFolder(6);  //6 means inbox
outlookitems:=outlookMAPIFolder.Items;
Findcriteria:='[ReceivedTime]>'+Text001+FORMAT(TODAY-1)
+Text001+' AND [Unread] =true ';
outlookitems:=outlookMAPIFolder.Items.Restrict(Findcriteria);
I:=1;
endofloop:=outlookitems.Count;
WHILE I<=endofloop DO
BEGIN
outlookemail:=outlookitems.Item(I);
outlookAttachments:=outlookemail.Attachments;
AttCount:=outlookAttachments.Count ;
IF  AttCount>0 THEN
FOR K:=1 TO AttCount DO BEGIN
Path:='E:\Outlook Attachment path\'+outlookAttachments.Item(K).FileName;
outlookAttachments.Item(K).SaveAsFile(Path);
END;
I+=1;
END;

create one Outlook Attachment path folder and run the codeunit

now the codeuint will filter last two days unread mails from outlook and all the attachment files will save to E:\Outlook Attachment path\

you can download the object file from here

Tips

Find criteria we can use different conditions

eg: [ReceivedTime]>’18/08/13′ AND [Unread] =true AND [Subject] = ‘item’

check the link

http://msdn.microsoft.com/en-us/library/office/aa210275%28v=office.11%29.aspx

How Create Excel Pivot table report from Navision?

A pivot table is a data processing tool used to query, organize and summarize data or information between spreadsheets, tables or databases. Dragging and dropping fields into a pivot table facilitates rotational, or pivotal, structural changes.

Using excel automation we can create the excel Pivot table report from Navision

This is one example to analyze the customer ledger entries records in table 21

Create one blank report with dataitem Cust. Ledger Entry and update the reports Processonly status to true

Add below variable’s in report (click the image to view the enlarge image)

Cust. Ledger Entry - OnPreDataItem()
CREATE(Excel);
Excel.Workbooks.Add();
Sheet:=Excel.ActiveSheet;
Sheet.Name := 'CUST_LEDGER';
Window.OPEN(Text00001);
Excel.Visible(TRUE);
I:=5;
Sheet.Range('A1').Value:= "Cust. Ledger Entry".TABLECAPTION;
Sheet.Range('C1').Value:= 'USER ID ';
Sheet.Range('D1').Value:=USERID;
Sheet.Range('C2').Value:= 'DATE ';
Sheet.Range('D2').Value:=TODAY;
UpdateExcelrange;
Sheet.Range(ExcelRange[1]).Value:="Cust. Ledger Entry".FIELDCAPTION("Cust. Ledger Entry"."Entry No.");
Sheet.Range(ExcelRange[2]).Value:="Cust. Ledger Entry".FIELDCAPTION("Cust. Ledger Entry"."Posting Date");
Sheet.Range(ExcelRange[3]).Value:="Cust. Ledger Entry".FIELDCAPTION("Cust. Ledger Entry"."Customer No.");
Sheet.Range(ExcelRange[4]).Value:=Cust.FIELDCAPTION(Cust.Name);
Sheet.Range(ExcelRange[5]).Value:="Cust. Ledger Entry".FIELDCAPTION("Cust. Ledger Entry"."Document No.");
Sheet.Range(ExcelRange[6]).Value:="Cust. Ledger Entry".FIELDCAPTION("Cust. Ledger Entry".Description);
Sheet.Range(ExcelRange[7]).Value:="Cust. Ledger Entry".FIELDCAPTION("Cust. Ledger Entry"."Global Dimension 1 Code");
Sheet.Range(ExcelRange[8]).Value:="Cust. Ledger Entry".FIELDCAPTION("Cust. Ledger Entry"."Global Dimension 2 Code");
Sheet.Range(ExcelRange[9]).Value:="Cust. Ledger Entry".FIELDCAPTION("Cust. Ledger Entry"."Amount (LCY)");
Sheet.Range(ExcelRange[10]).Value:='YEAR';

UpdateExcelrange is a function to update the Excelrange array variable

in excel YEAR column will update with month + year such as if 1st January 2013 then year value will be Jan 2013


Cust. Ledger Entry - OnAfterGetRecord()
CLEAR(Cust);
IF Cust.GET("Cust. Ledger Entry"."Customer No.") THEN ;
K:=ROUND(((I-5)/"Cust. Ledger Entry".COUNT) * 10000,1);
Window.UPDATE(1, K);
"Cust. Ledger Entry".CALCFIELDS("Cust. Ledger Entry"."Amount (LCY)");
UpdateExcelrange;
Sheet.Range(ExcelRange[1]).Value:="Cust. Ledger Entry"."Entry No.";
Sheet.Range(ExcelRange[2]).Value:="Cust. Ledger Entry"."Posting Date";
Sheet.Range(ExcelRange[3]).Value:="Cust. Ledger Entry"."Customer No.";
Sheet.Range(ExcelRange[4]).Value:=Cust.Name;
Sheet.Range(ExcelRange[5]).Value:="Cust. Ledger Entry"."Document No.";
Sheet.Range(ExcelRange[6]).Value:="Cust. Ledger Entry".Description;
Sheet.Range(ExcelRange[7]).Value:="Cust. Ledger Entry"."Global Dimension 1 Code";
Sheet.Range(ExcelRange[8]).Value:="Cust. Ledger Entry"."Global Dimension 2 Code";
Sheet.Range(ExcelRange[9]).Value:="Cust. Ledger Entry"."Amount (LCY)";
Sheet.Range(ExcelRange[10]).Value:=FORMAT("Cust. Ledger Entry"."Posting Date",0,'')+' '+
FORMAT("Cust. Ledger Entry"."Posting Date",0,'');

the Pivot table is creating on the on post data item trigger

Updated one option field in request form , user can select customer or dimension, based on the choice the pivot table row values will change

Cust. Ledger Entry - OnPostDataItem()
xlPivotCache:=Excel.ActiveWorkbook.PivotCaches.Add(1,'CUST_LEDGER!A6:J'+FORMAT(I));
xlPivotCache.CreatePivotTable('','PivotTable1');
Sheet :=Excel.ActiveSheet();
xlPivotTable := Sheet.PivotTables('PivotTable1');
Sheet.Name := 'Cust_Ledger_Pivot';
xlPivotField := xlPivotTable.PivotFields('YEAR');
xlPivotField.Orientation := 1;
xlPivotField.Position := 1;
IF "Pivot Base"="Pivot Base"::Customer THEN
xlPivotField := xlPivotTable.PivotFields(Cust.FIELDCAPTION(Cust.Name))
ELSE IF "Pivot Base"="Pivot Base"::Dimension THEN
xlPivotField := xlPivotTable.PivotFields("Cust. Ledger Entry".FIELDCAPTION("Cust. Ledger Entry"."Global Dimension 1 Code"));
xlPivotField.Orientation := 1;
xlPivotField.Position := 2;
xlPivotField := xlPivotTable.PivotFields("Cust. Ledger Entry".FIELDCAPTION("Cust. Ledger Entry"."Amount (LCY)"));
xlPivotTable.AddDataField( xlPivotField);
xlPivotField := xlPivotTable.DataPivotField;
xlPivotField := xlPivotTable.PivotFields('YEAR');
xlPivotField.Orientation := 2;
xlPivotField.Position := 1;

pivot table output

code for update excel range function

UpdateExcelrange()
I+=1;
ExcelRange[1]:='A'+FORMAT(I);
ExcelRange[2]:='B'+FORMAT(I);
ExcelRange[3]:='C'+FORMAT(I);
ExcelRange[4]:='D'+FORMAT(I);
ExcelRange[5]:='E'+FORMAT(I);
ExcelRange[6]:='F'+FORMAT(I);
ExcelRange[7]:='G'+FORMAT(I);
ExcelRange[8]:='H'+FORMAT(I);
ExcelRange[9]:='I'+FORMAT(I);
ExcelRange[10]:='J'+FORMAT(I);

you can download the object and sample output from here

Tips

For any other update use macro functionality from excel

steps

start record the macro
update the required changes in excel file
Stop macro
Edit macro then you will get the visual basic code check the image
Convert VB code to Cal code update navision

	gilbert ssenyonjo on Creating Matrix Reports in Nav…
	Akhilesh E Kartha on How to save outlook attachment…
	elhamdi on How to save outlook attachment…
	Michael Alter on Dynamics Nav 2016 Enable NavUs…
	Akhilesh E Kartha on Dynamics Nav 2016 Enable NavUs…