Advertisements

Working with Alerts in Power BI

Knowing about changes to important metrics quickly some time can help you taking quick decisions or help you keep eyes on things you care most.

With data driven alerts in Power BI, you can now get notifications when a metric you care about on your dashboard exceeds a set threshold.

Alert notifications will be sent to you as an email, and appear in your notification center on the web and in mobile apps.

You can set alerts for your numeric tiles featuring cards and gauges only. You have control over how often you want to be notified about your data, and whether you want to receive an email when your data goes beyond the limits you set.

Only you can see the alerts you set, even if you share your dashboard.

Alerts only work on data that is refreshed. When data refreshes, Power BI looks to see if an alert is set for that data. If the data has reached an alert threshold, an alert is triggered.

Alerts only work with numeric data types.

Alerts only work on data that is refreshed. They do not work on static data.

Today we will see how to work with Alert feature in Power BI.

Login to your Power BI using your credentials.

Switch to your Dashboard.

I have one ready to use dashboard from my previous post to show Alert feature I am using the same.

As discussed above it works only for Numeric & Gauges Tiles only, for others you will not get even the Alert option.

Luckily I have one on my Dashboard, if want to learn how to create Numeric Tiles or more details on working with Power BI you can refer to any of my previous posts on Power BI. However you can find here one direct Link to the topic in question.

You see I have selected my Numeric Tile in below screen shot.

DataAlert-1

How to Set Data Alerts:

From a dashboard tile, select the ellipses.

Select the bell icon  to add one or more alerts.

DataAlert-2

Click on Add Alert Rule.

To start, ensure the Active slider is set to ON, and give your alert a title.

DataAlert-3

 

Set your Condition, Threshold, and Notification Frequency also don’t forget to tick on send me mail too.

Click on Save to save your Alert.

 

Receiving alerts:

When the data being tracked reaches one of the thresholds you’ve set, several things will happen.

Power BI checks to see if time have lapsed or more than depending on the option you selected since the last alert was sent.

As long as the data is past the threshold, you’ll get an alert every hour or every 24 hours depending on option you selected.

If you’ve set the alert to send you an email, you’ll find something like as shown in below screen, this in your Inbox.

 

DataAlert-4

 

Power BI will add a message to your Notification center and adds a new alert icon to the applicable tile as shown in below screen.

DataAlert-5

 

Access to your Notification.

DataAlert-6

 

 

Manage alerts:

There are three ways to manage your alerts:

From the dashboard tile

From the Power BI Settings menu

On an individual tile in the Mobile App

DataAlert-7

 

As we have seen above how to create Alerts follow same steps to access Alert Window.

At the Left click on Expand make Necessary Modifications and save.

At the right select the trashcan to delete the Alert.

Select cancel to return to previous window.

 

DataAlert-8

From here you can turn alerts on and off, open the Manage alerts window to make changes, or delete the alert.

 

That’s all for today.

I will come up with more details in my upcoming posts.

Till then keep exploring and learning.

 

 

Advertisements

How to Setup Online Bank Feeds in Madeira

You need to be sure : The default company, CRONUS, that launches when you first log in to Project “Madeira” does not have electronic bank feeds enabled. To tryout this feature you must switch to trial Company “My Company“.

In Project “Madeira” you can connect to most of the online banks and download electronic bank feeds for use in bank reconciliation, avoiding tedious, manual reconciliation, reducing errors, and ensuring that your data is refreshed on a frequent basis.

When you log into MyCompany for the first time, a Company Setup wizard will guide you through setting up the basic company information, including using a bank feed service.

Dont forget to select “Use Bank Feed Service” in Company Setup Page to enable this Service.

You can Manage Bank Accounts & Feeds from Bank Account List Page.

BankStatementService-1

If you enable “Use Bank Feed Service” in Company Setup Page, a new section displays in the ribbon as shown in above screenshot with actions so that you can create a new bank account in Project “Madeira” based on the online bank feed, link already existing bank accounts to online bank feeds, unlinking online bank account feeds, or set up automatic download of feeds on a regular schedule.

When you reconcile bank accounts, you can download bank feeds on demand, as well as process automatically downloaded feeds if you have set up already.

In the Payment Reconcilliation Journal, you can import bank statements based on your bank feeds, and Project “Maderia“ will automatically suggest how payments are applied to ledger entries. You can change the application before you post.

Alternatively, you can import bank statements to the Bank Account Reconcilliation window and reconcile the statements with your ledger entries there.

I will come up with more details in my upcoming posts, till then keep practicing and Learning.

 

 

 

 

More about Loops in PowerShell

Do Until

The logic of this loop is to execute the Do {block} until (the condition is true).

As usual in PowerShell, pay close attention to the style of brackets, the curly brackets or parentheses guides you to write the correct code.

PS-L-1

Note: Don’t try: Until ($strQuit = “N”).  You need here to use -eq, this is PowerShell’s way of comparing two values.

Do While

The logic of ‘Do … While’ is the opposite of the Do Until. {Execute the block statement} while the (condition is true)
PS-L-1

Note: There are difference between until & while in above two examples: Until ($strQuit -eq “N”) While ($strQuit -ne “N”)

 

‘While’ on Its Own – No Explicit ‘Do’

This method is the more traditional way with the (condition) followed by the {Block command}.  Clearly, PowerShell’s While loop is simpler and more basic than the ‘Do … While’ construction in above two examples.

PS-L-1

Note: In this example the ‘While’ clause is at the beginning instead of the end.

 

‘For’ Loop

Below example is a simple method using the keyword ‘For’. As usual there is a (condition) and {Block Statement}.

The speciality of this loop is the <init> and <repeat> sections.

Here is the syntax:

For (<init>; <condition>; <repeat>) {<command_block>}

Example: Times Table for 25

PS-L-1

One side-effect of the For loop is that it always returns the <init> before it tests for the condition.

The <repeat> modifies the $i variable, which is then tested inside the <condition> of the next cycle.

 

‘Foreach’ loop

The PowerShell ‘Foreach’ loop is more complex, and has more arguments than the ‘for’ and ‘Do While’ loops.  The key feature is that the loop interrogates an array, known as a collection.  It then applies a {Statement Block} to each iteration.  In addition to the position and the type of bracket, observe the tiny, but crucial keyword – ‘In’.

 

PS-L-1

 

PS-L-1

# PowerShell ForEach loop to display files in C:\Program files

$Path = “C:\Program Files\” “{0,10} {1,-24} {2,-2}” -f ` ” Size”, “Last Accessed”, “File Name ” Foreach ($file in Get-Childitem $Path -recurse -force) {If ($file.extension -eq “.txt”)     {     “{0,10} {1,-24} {2,-2}” -f `     $file.length, $file.LastAccessTime, $file.fullname     } }

 

# PowerShell ForEach-Objcet piping into block statement

Clear-Host $Path = “C:\Program Files\” Get-Childitem $Path -recurse -force | Foreach-Object {If ($_.extension -eq “.txt”) {Write-Host $_.fullname        } }

 

PS-L-1

 

PS-L-1

 

I will come up with more stuffs in my upcoming posts.

Till then keep practicing and stay tuned for more details.

How Inventory is Calculated in Navision 2016

Today we will see terms used for Inventory and how Inventory is calculated in Navision.

InvProj-1

You can find details of Inventory on Item Card itself. Also how much Inventory is available or required at different area in ERP.

InvProj-2

If you open Item Availability by Location you will find in more details. When you drilldown you can find in more details from where these figure comes from.

Scheduled Receipts:

Here all the entries from below area is included:

a) Purchase Orders

b) Transfer Orders

c) Firm Planned Production Order

d) Release Production Order

e) Assembly Orders

How Navision calculates?

AvailType::”Scheduled Order Receipt”:
BEGIN
InsertEntry(DATABASE::”Purchase Line“,Item.FIELDNO(“Qty. on Purch. Order”),PurchLine.TABLECAPTION,Item.”Qty. on Purch. Order“);
InsertEntry(DATABASE::”Prod. Order Line“,Item.FIELDNO(“FP Order Receipt (Qty.)”),STRSUBSTNO(Text002,ProdOrderLine.TABLECAPTION),Item.”FP Order Receipt (Qty.)“);
InsertEntry(DATABASE::”Prod. Order Line“,Item.FIELDNO(“Rel. Order Receipt (Qty.)”),STRSUBSTNO(Text003,ProdOrderLine.TABLECAPTION),Item.”Rel. Order Receipt (Qty.)“);
InsertEntry(DATABASE::”Transfer Line“,Item.FIELDNO(“Qty. in Transit”),Item.FIELDCAPTION(“Qty. in Transit”),Item.”Qty. in Transit“);
InsertEntry(DATABASE::”Transfer Line“,Item.FIELDNO(“Trans. Ord. Receipt (Qty.)”),Item.FIELDCAPTION(“Trans. Ord. Receipt (Qty.)”),Item.”Trans. Ord. Receipt (Qty.)“);
InsertEntry(DATABASE::”Sales Line“,0,SalesLine.TABLECAPTION,Item.”Qty. on Sales Return“);
InsertEntry(DATABASE::”Assembly Header“,Item.FIELDNO(“Qty. on Assembly Order”),AssemblyHeader.TABLECAPTION,Item.”Qty. on Assembly Order“);
END;

Planned Receipts:

Here all the entries from below area is included:

a) Planned Production Order

b) Planning Worksheet

c) Requisition Worksheet

How Navision calculates?

AvailType::”Planned Order Receipt”:
BEGIN
InsertEntry(DATABASE::”Requisition Line“,Item.FIELDNO(“Purch. Req. Receipt (Qty.)”),ReqLine.TABLECAPTION,Item.”Purch. Req. Receipt (Qty.)“);
InsertEntry(DATABASE::”Prod. Order Line“,Item.FIELDNO(“Planned Order Receipt (Qty.)”),STRSUBSTNO(Text000,ProdOrderLine.TABLECAPTION),Item.”Planned Order Receipt (Qty.)“);
END;

Gross Requirement:

Here all the entries from below area is included:

a) Sales Order

b) Transfer Order

c) Firm Planned Production Order Components

d) Released Production Order Components

e) Job Planning Lines

f) Service Orders

g) Assembly Orders Components

How Navision calculates?

AvailType::”Gross Requirement”:
BEGIN
InsertEntry(DATABASE::”Sales Line“,Item.FIELDNO(“Qty. on Sales Order”),SalesLine.TABLECAPTION,Item.”Qty. on Sales Order“);
InsertEntry(DATABASE::”Service Line“,Item.FIELDNO(“Qty. on Service Order”),ServLine.TABLECAPTION,Item.”Qty. on Service Order“);
InsertEntry(DATABASE::”Job Planning Line“,Item.FIELDNO(“Qty. on Job Order”),JobPlanningLine.TABLECAPTION,Item.”Qty. on Job Order“);
InsertEntry(DATABASE::”Prod. Order Component“,Item.FIELDNO(“Scheduled Need (Qty.)”),ProdOrderComp.TABLECAPTION,Item.”Scheduled Need (Qty.)“);
InsertEntry(DATABASE::”Planning Component“,Item.FIELDNO(“Planning Issues (Qty.)”),PlanningComponent.TABLECAPTION,Item.”Planning Issues (Qty.)“);
InsertEntry(DATABASE::”Transfer Line“,Item.FIELDNO(“Trans. Ord. Shipment (Qty.)”),Item.FIELDCAPTION(“Trans. Ord. Shipment (Qty.)”),Item.”Trans. Ord. Shipment (Qty.)“);
InsertEntry(DATABASE::”Purchase Line“,0,PurchLine.TABLECAPTION,Item.”Qty. on Purch. Return“);
InsertEntry(DATABASE::”Assembly Line“,Item.FIELDNO(“Qty. on Asm. Component”),AssemblyLine.TABLECAPTION,Item.”Qty. on Asm. Component“);
END;

Planned Order Releases:

How Navision calculates?

AvailType::”Planned Order Release”:
BEGIN
InsertEntry(DATABASE::”Requisition Line“,Item.FIELDNO(“Purch. Req. Release (Qty.)”),ReqLine.TABLECAPTION,Item.”Purch. Req. Release (Qty.)“);
InsertEntry(DATABASE::”Prod. Order Line“,Item.FIELDNO(“Planned Order Release (Qty.)”),STRSUBSTNO(Text001,ProdOrderLine.TABLECAPTION),Item.”Planned Order Release (Qty.)“);
InsertEntry(DATABASE::”Requisition Line“,Item.FIELDNO(“Planning Release (Qty.)”),ReqLine.TABLECAPTION,Item.”Planning Release (Qty.)“);
END;

Finally we can calculate Projected Available Inventory as below formula:

Inventory + Scheduled Receipts + Planned Receipts – Gross Requirement

thats all for today, will come with more information in my upcomming posts.

 

Setup Send Documents by Email in Madeira

To enable emails from within Project “Madeira”, start the Set Up Email assisted setup on the Home page. We have already seen this in our previous post, you can find it here.

To offer your customers to pay for sales electronically using a payment service, such as PayPal, you can also have the PayPal information and hyperlink inserted in the email body. We have already covered this in our previous post, you can find it here.

From all supported documents, you initiate emailing by choosing the Send action, on posted documents, or the Post and Send action, on non-posted documents.

Let us Setup document-specific email body for sales invoices.

From the search page find Report Selections Sales:

SetupEmail-10

Fill the Value as shown in below screen in same sequence.

SetupEmail-11

If you want to offer customers to pay for sales electronically, you can set up the related payment service, such as PayPal, and then have the PayPal information and hyperlink inserted in the email body as well. We have already covered this in our previous post, you can find it here.

Will come up with more details & feature explaination in my upcomming posts.

 

How do I process Sales Return? – In Maderia

We have seen in our previous post the feature where if a posted sales invoice has not yet been paid, then you can use the Correct or Cancel functions on the posted sales invoice to automatically reverse the involved transactions.

There could be the situation where customer wants partial refund or return of sales.

You can create the Credit Memo from the Posted Sales Invoice, which will create the Credit Memo for you and leave to you to modify and post the credit memo.

Most fields on the sales credit memo header are filled with the information from the posted sales invoice. You can edit all the fields, with new information that reflects the return agreement.

You can apply the Customer Entries via selecting the line with the posted sales document that you want to apply the sales credit memo to.

The posted sales documents that you applied the credit memo to are now reversed, and a refund payment can be created for the customer.

The sales credit memo is removed and replaced with a new document in the list of posted sales credit memos.

Let us see how we can achieve this in Madeira.

 

CorrectCancelSalesInvoice-12

Select the Sales Invoice and open it for action.

CorrectCancelSalesInvoice-13

Select Create Corrective Credit Memo.

CorrectCancelSalesInvoice-14

Perform the required correction and choose Apply Entries.

CorrectCancelSalesInvoice-15

Select the document to which you want this Credit Memo to be adjusted.

CorrectCancelSalesInvoice-16

Once Application is done, Post the Credit Memo.

Respond to Yes to post the Credit Memo.

Respond to Yes to Open the posted Credit Memo.

CorrectCancelSalesInvoice-17

This is the posted Credit Memo for the Invoice.

Let us check the Ledger for this Customer:

CorrectCancelSalesInvoice-18

Here you can see the net effect of the Credit Memo on Invoice.

Thats all for this post will come up with more features and details in my upcomming posts.

 

 

Managing Multilanguage support in Navision Overview

language

Microsoft Dynamics NAV is Multilanguage enabled, which means that you can display the user interface (UI) in different languages.

A Multilanguage version of Microsoft Dynamics NAV is not the same as a localized version.

  • A localized version is a version that is adapted to a local market. All text that is displayed to the user is translated into the local language, and all functional areas are adapted to the requirements of the local market.
  • A Multilanguage version is a localized version that you can run in different languages, but all local functionality remains the same.

 

Language Module:

To be able to run a localized version in multiple languages, you must install language modules.

Language Module contains translated text strings of Logs & error reporting for:

  • Translated strings for the UI in the Microsoft Dynamics NAV Windows client.
  • Translated strings for the database.
  • Translated strings for Microsoft Dynamics NAV Server, such as for logs and error reporting.
  • Translated strings for the Microsoft Office Outlook Add-in for Microsoft Dynamics NAV.
  • Translated strings for charts on Role Centers in the Microsoft Dynamics NAV Windows client.
  • Localized versions of the client Help.

Language Modules are Binary Files which can’t be changed by any external tools for this you will require Partner Translation Tool Project and Visual Studio.

You can install language modules so that you can view text in the user interface in different languages.

Once you have installed a language module, you can select that language on the Select Language page in the UI to change the language of all text that is displayed in the UI in captions for text boxes, on command buttons, in menus, and so on.

Imp: When you change the language of Microsoft Dynamics NAV, you are not changing the language of the data that is stored in Microsoft Dynamics NAV. Changing the text that is stored as application data is not part of the language modules.

When you install a language module, a subdirectory for the language is created in the Microsoft Dynamics NAV directory structure. The .stx, .etx, .chm, and .hh files for each language are automatically installed in the subdirectory. The name of the subdirectory is the three-letter language code (Abbreviated Name) that is used by Windows for the particular language.

You can use this link to access NAV 2016 downloads for Available – Product, Translation Tool & Language Modules. https://mbs.microsoft.com/customersource/Global/NAV/downloads/product-releases/msdnav2016download

 

There are 3 main parts for creating translations that works in Dynamics NAV.

  1. Translate the platform by translating the resource file for each DLL file.
  2. Translate all the captions by adding a new language into captionML in each object
  3. Localize development environment by translating .stx and .etx files. These 2 files have to be sent to the Microsoft regional office and sealed by Microsoft. (Only required if you want the Development Environment to be in Unsupported Language as well)

 

Common available resources for supported/available Language Module.

To work with Language Modules what else you required to know about: You can check below links on MSDN.

  1. Windows Language Virtual Table
  2. How to: Install Language Modules
  3. How to: Add Translated Strings By Importing and Exporting Multilanguage Files
  4. How to: Add Translated Strings By Using the Multilanguage Editor
  5. How to: Uninstall Language Modules
  6. How to: Delete Translated Strings
  7. Multilanguage Development
  8. Viewing the Application in Different Languages

 

Translated User Interface Strings:

In Microsoft Dynamics NAV, the user interface includes the following types of translatable user interface strings:

Captions for application objects, such as pages, tables, reports, option fields, icons, menus, and so on, that are defined in the CaptionML property for an object. Text messages that are defined in the development environment, such as text constants and error messages that are defined in the C/AL Globals window or the C/AL Locals window.

Text messages and strings that are defined in Microsoft Dynamics NAV Server and Microsoft Dynamics NAV Windows client.

To add a language to the text messages that are defined in the resource files for Microsoft Dynamics NAV Server and Microsoft Dynamics NAV Windows client, special tools are required.

You can manage translations by using text files. In the development environment, you can export all strings for the specified objects to a text file, translate the strings in a translation tool of your choice, and then import the new translations. Then, when you compile the updated objects, the new translations are available to users in the Microsoft Dynamics NAV Windows client.

 

Multilanguage across Conflicting Text Encoding Formats:

To support users with translated strings across conflicting text encoding formats, you must save the translations to a text file in UTF-8 text encoding format.

This converts the file to Unicode, which Microsoft Dynamics NAV Development Environment cannot import. Instead, you add the files to a dedicated Translations folder on the Microsoft Dynamics NAV Server computer.

The default location of this folder is:

C:\Program Files\Microsoft Dynamics NAV\90\Service\Translations.

When you have more than one service instance, there is a Translations folder for each service instance, such as:

C:\Program Files\Microsoft Dynamics NAV\90\Service\Instances\MyInstance\Translations.

Microsoft Dynamics NAV Server adds any strings in text files that you place in the relevant Translations folder to the metadata for Microsoft Dynamics NAV.

 

To add translated strings for a conflicting text encoding format:

In the development environment, export the strings for the objects that you want to translate.

Translate the strings in the resulting text file into desired Language by using your preferred translation process.

When translations are complete, save the translated strings into a text file in the UTF-8 text encoding format. The extension of the file must be .txt.

It is recommend that you remove the other Language strings from the Unicode file before you copy it to the Microsoft Dynamics NAV Server computer.

Microsoft Dynamics NAV Server queries the Translations folder for updated strings, and for performance reasons the folder should only contain strings that you cannot import into the development environment because of conflicting codepages.

Copy the UTF-8 file to the Microsoft Dynamics NAV Server computer.

C:\Program Files\Microsoft Dynamics NAV\90\Service\Translations

When you have more than one service instance, there is a Translations folder for each service instance, such as:

C:\Program Files\Microsoft Dynamics NAV\90\Service\Instances\MyInstance\Translations.

Restart the Microsoft Dynamics NAV Server instance.

Microsoft Dynamics NAV Server adds the translated strings in the file to the metadata store for the Microsoft Dynamics NAV deployment.

Now Users of this Microsoft Dynamics NAV Server session can now change the user interface language from English to required Language.

The metadata store is updated whenever you add new strings to the Translations folder and then restart the Microsoft Dynamics NAV Server service instance. Cached strings are not deleted from the metadata store when you restart the service instance.

I will comeup with more details in my upcomming post soon.

Cumulative Update 7 for Microsoft Dynamics NAV 2016 – Released in May 2016

Cumulative Update 7 includes all application and platform hotfixes and regulatory features that have been released for Microsoft Dynamics NAV 2016.

The cumulative update includes hotfixes that apply to all countries and hotfixes specific to the following local versions:

  •   AU – Australia
  •   AT – Austria
  •   BE – Belgium
  •   CH – Switzerland
  •   CZ – Czech Republic
  •   DE – Germany
  •   DK – Denmark
  •   ES – Spain
  •   FI  – Finland
  •   FR – France
  •   IS – Iceland
  •   IT – Italy
  •   NA – North America
  •   NL – Netherlands
  •   NO – Norway
  •   NZ – New Zealand
  •   RU – Russia
  •   SE – Sweden
  •   UK – United Kingdom

Where to find Cumulative Update 7

You can download the cumulative update from KB 3157492 – Cumulative Update 7 for Microsoft Dynamics NAV 2016 (Build 45834).

Warning

Before you install a cumulative update in a production environment, take the following precautions:

  1. Test the cumulative update in a non-production environment.
  2. Make a backup of the system or computer where the cumulative update is to be installed.

Additional Information

For information about how to install the cumulative update, see How to Install a Microsoft Dynamics NAV 2016 Cumulative Update.

For a list of all cumulative updates for this version, see Released Cumulative Updates for Microsoft Dynamics NAV 2016.

Cumulative Update 31 for Microsoft Dynamics NAV 2013 R2 – Released in May 2016

Cumulative Update 31 includes all application and platform hotfixes and regulatory features that have been released for Microsoft Dynamics NAV 2013 R2.

The cumulative update includes hotfixes that apply to all countries and hotfixes specific to the following local versions:

  •   AU – Australia
  •   AT – Austria
  •   BE – Belgium
  •   CH – Switzerland
  •   CZ – Czech Republic
  •   DE – Germany
  •   DK – Denmark
  •   ES – Spain
  •   FI  – Finland
  •   FR – France
  •   IS – Iceland
  •   IT – Italy
  •   NA – North America
  •   NL – Netherlands
  •   NO – Norway
  •   NZ – New Zealand
  •   RU – Russia
  •   SE – Sweden
  •   UK – United Kingdom

Where to find Cumulative Update 31

You can download the cumulative update from KB 3157488 – Cumulative Update 31 for Microsoft Dynamics NAV 2013 R2 (Build 45822).

Warning

Before you install a cumulative update in a production environment, take the following precautions:

  1. Test the cumulative update in a non-production environment.
  2. Make a backup of the system or computer where the cumulative update is to be installed.

Additional Information

For more information about cumulative updates for this version, see Announcement of update rollups for Microsoft Dynamics NAV 2013 R2.

For a list of all cumulative updates for this version, see Released Cumulative Updates for Microsoft Dynamics NAV 2013 R2.

For a list of all hotfixes included in cumulative updates for this version, see the following CustomerSource and PartnerSource pages:

CustomerSource:

PartnerSource

Using Payment Services – in Madeira

It’s obvious you know we can include link in PDF documents, if you don’t know no issues everything is not known to every one until he uses it or someone inform them. Today we will be learning this feature for making payments using links in your PDF invoices generated from Madeira.

If you had setup the preview this feature is already available in CRONIUS US demo company.

How do we Setup:

Login to Madeira Project using your Credentials.

Click on Search Page from right top corner.

Search for Payment Services page.

PayPalPayment-1

 

 

PayPalPayment-2

Open the Page.

Click on Setup and enter your details as shown in above screen.

Click on Maximize button and Show more to see more details.

PayPalPayment-3

Payment Service Link:

In the CRONUS US demonstration company, the payment link inserted into invoices will link to the PayPal test site.

For your actual Company, we will set so that the payment link will point to the real PayPal site, where payments can be made.

It is advisable that not to do this up for the Actual Company until Project “Madeira” is generally available for purchase. And authorized documentation on this feature is released from Madeira.

If you have setup Always Include on Document then you need not to perform any additional step, but in case you want to add the Payments Service Link to individual invoices that too can be done.

Manually Selecting Payment Services:

Open your Invoice on which you want to include this option.

PayPalPayment-4

As you can see in above Invoice no Payment Service is included.

Click on the Lookup on right side of the field.

PayPalPayment-5

Select the Payment Service from the list of available Services, and click on OK.

PayPalPayment-6

When you send the invoice to your customer the Payment Service Link will be included in the Invoice PDF.

PayPalPayment-7

 

 

PayPalPayment-8

 

 

PayPalPayment-9

 

 

PayPalPayment-10

How it will look in Invoice:

The PDF will contain the link to your selected Payment Service using which your customer can do the payment.

PayPalPayment-11

Stay tuned for more Information in upcoming posts.

Till then keep exploring and learning.

 

 

Microsoft Power BI – Part X

Continuing from my previous post. Today we will downloading the dataset into Excel from Power BI Online for analysis.

In case you have missed my previous posts here I present the link to all previous posts below.

 

Microsoft Power BI – Part – I

Introduction to Power BI and Creating Report from Excel Data, Local Files.

Microsoft Power BI – Part – II

Introduction to few Features of Power BI

Microsoft Power BI – Part – III

Power BI Desktop, Creating Dataset & Reports from In Premise Database installation

Microsoft Power BI – Part – IV

Power BI Gateway usage

Microsoft Power BI – Part – V

Scheduling Refresh of Dataset & Report created using In Premise Database

Microsoft Power BI – Part – VI

Power BI Content Pack

Microsoft Power BI – Part – VII

Power BI Mobile App

Microsoft Power BI – Part – VIII

Power BI Content Pack

Microsoft Power BI – Part – IX

Power BI Publisher for Excel

 

Login to Power BI using your credentials.

PowerBI-111

Select the Dataset which you wish to analyse, click the three dots on right and from appearing menu choose ANALYZE IN EXCEL.

PowerBI-112

You will be prompted for Analyse in Excel (preview). If you are running first time please install it.

At the same time you will be prompted for (.odc) MS Office Data Connection file to save/open.

Save and then open the File in Excel.

PowerBI-113

On opening the file you will be prompted for security concern Enable to allow it.

PowerBI-114

You will be able to see Pivot Table Fields, containing all of the Tables available in the Dataset.

PowerBI-115

Now you can play with your data to analyse and create Pivot, Charts and share with others or you can Pin back your result to Power BI Dashboards using concept we used in our previous post.

 

That’s all for today, I will come up with more features in my future posts.

Till then keep practicing & Learning.

 

Using Argument Tables in Navision

When creating functions we sometimes need a lot of parameters to give the information we require. Number of arguments/parameters to function have limits.

Sometimes these parameters are optional, only required in some scenarios.

This makes calling them quite hard. The code becomes hard to read & understand, and sometimes spread across multiple lines.

When changing the signature of any std. function or already implemented functionality which is highly used over the system in several objects you might end up changing an endless chain of objects.

Imaging adding a field to such function, or change a datatype. The amount of code affected is pretty big and if you need to parameter only for one specific case you end up changing a lot of code for no purpose. Does this make sense?

These challenges can be solved by grouping commonly used arguments in a single table and use that table as the parameter of a function.

So the solution is to create a single table that holds the contract for the function.

When we add new fields to the table, we effectively do not change the signature of the function hence this acts as overloading allowing optional parameters to change the behavior of the code.

You can find usage of such Argument Tables in below objects:

Type      ID            Name

1              9500       Email Item

5              260         Document-Mailing

5              9520       Mail Management

Table is created to hold Parameter Values which will be passed to the function.

ArgumentTables-1

This Function in Table Pass the record as a parameter to the function, basically this is passing 10 Parameters using single record as a Parameter to the Function.

ArgumentTables-2

In Codeunit Document-Mailing below function, This is the extra work which you will be required to perform, if you miss this part you may not get errors at compile time but all the 10 Parameters will be passed as blank. This may be counted as side-effect of using such concept.

ArgumentTables-3

In Codeunit Mail Management below function, This Pass the record as a parameter to the function, basically this is passing 10 Parameters using single record as a Parameter to the Function.

ArgumentTables-4

In Codeunit Mail Management below function, This receives the record as a parameter to the function, basically this is receiving 10 Parameters using single record as a Parameter to the Function.

ArgumentTables-5

Best part is Argument tables can be outside of a customer’s license file since we never insert data in the SQL Server database.

For more details you can see this Link. https://community.dynamics.com/nav/w/designpatterns/245.argument-table

 

Accounts in the General Ledger

To reconcile inventory and capacity ledger entries with the general ledger, the related value entries are posted to different accounts in the general ledger.

From the Inventory Ledger

The below table shows the relationship between different types of inventory value entries and the accounts and balancing accounts in the general ledger.

Value entry General ledger accounts
Item ledger entry type Value entry type Variance type Expected cost Account Balancing account
Purchase Direct Cost Yes Inventory (Interim) Invt. Accrual Acc. (Interim)
Direct Cost No Inventory Direct Cost Applied
Indirect Cost No Inventory Overhead Applied
Variance Purchase No Inventory Purchase Variance
Revaluation No Inventory Inventory Adjmt.
Rounding No Inventory Inventory Adjmt.
Sale Direct Cost Yes Inventory (Interim) COGS (Interim)
Direct Cost No Inventory COGS
Revaluation No Inventory Inventory Adjmt.
Rounding No Inventory Inventory Adjmt.
Positive Adjmt.,Negative Adjmt., Transfer Direct Cost No Inventory Inventory Adjmt.
Revaluation No Inventory Inventory Adjmt.
Rounding No Inventory Inventory Adjmt.
(Production) Consumption Direct Cost No Inventory WIP
Revaluation No Inventory Inventory Adjmt.
Rounding No Inventory Inventory Adjmt.
Assembly Consumption Direct Cost No Inventory Inventory Adjmt.
Direct Cost No Direct Cost Applied Inventory Adjmt.
Indirect Cost No Overhead Applied Inventory Adjmt.
(Production) Output Direct Cost Yes Inventory (Interim) WIP
Direct Cost No Inventory WIP
Indirect Cost No Inventory Overhead Applied
Variance Material No Inventory Material Variance
Variance Capacity No Inventory Capacity Variance
Variance Subcontracted No Inventory Subcontracted Variance
Variance Capacity Overhead No Inventory Cap. Overhead Variance
Variance Manufacturing Overhead No Inventory Mfg. Overhead Variance
Revaluation No Inventory Inventory Adjmt.
Rounding No Inventory Inventory Adjmt.
Assembly Output Direct Cost No Inventory Inventory Adjmt.
Revaluation No Inventory Inventory Adjmt.
Indirect Cost No Inventory Overhead Applied
Variance Material No Inventory Material Variance
Variance Capacity No Inventory Capacity Variance
Variance Capacity Overhead No Inventory Cap. Overhead Variance
Variance Manufacturing Overhead No Inventory Mfg. Overhead Variance
Rounding No Inventory Inventory Adjmt.

From the Capacity Ledger

The below table shows the relationship between different types of capacity value entries and the accounts and balancing accounts in the general ledger. Capacity ledger entries represent labor time consumed in assembly or production work.

Value entry General ledger accounts
Work type Capacity ledger entry type Value entry type Account Balancing account
Assembly Resource Direct Cost Direct Cost Applied Inventory Adjmt.
Assembly Resource Indirect Cost Overhead Applied Inventory Adjmt.
Production Machine Center/Work Center Direct Cost WIP Account Direct Cost Applied
Production Machine Center/Work Center Indirect Cost WIP Account Overhead Applied

Assembly Costs are Always Actual

As shown in the table above, assembly postings are not represented in interim accounts. This is because the concept of work in process (WIP) does not apply in assembly output posting, unlike in production output posting. Assembly costs are only posted as actual cost, never as expected cost.

Calculating the Amount to Post to the General Ledger

The following fields in the Value Entry table are used to calculate the expected cost amount that is posted to the general ledger:

  • Cost Amount (Actual)
  • Cost Posted to G/L
  • Cost Amount (Expected)
  • Expected Cost Posted to G/L

The following table shows how the amounts to post to the general ledger are calculated for the two different cost types.

Cost type Calculation
Actual Cost Cost Amount (Actual) Cost Posted to G/L
Expected Cost Cost Amount (Expected) Expected Cost Posted to G/L

 

Working with Data Encryption in Navision 2016

You can encrypt data on the Microsoft Dynamics NAV Server by generating new or importing existing encryption keys.

How to: Enable Encryption Keys

You can enable data encryption on the Microsoft Dynamics NAV Server instance that connects to the database.

  • Open the Data Encryption Management Card, as path suggested in below Image.

DataEncryption-1

  • Click on Enable Encryption in Process of Home tab.

DataEncryption-2

  • On Confirmation Dialog box choose Yes.
  • On further Confirmation of Password choose Yes.

DataEncryption-3

  • Enter the Password as defined in Rule as shown in below Image.

DataEncryption-4

  • Upon entering Password choose OK to continue.
  • Key is created with password protected and offered to Export.

DataEncryption-5

  • Choose Save, and save it as Safe Location as you will require it again whenever you restore the database or Import Export the data. Please make sure you also store the password for further reference whenever required.

DataEncryption-6

  • Once Key is generated, you can see that the Encryption Enabled & Encryption Key Exists is checked TRUE.
  • Also in Ribbon you will find the Change Encryption Key, Export Encryption Key & Disable Encryption is now enabled.

DataEncryption-7

  • If Microsoft Dynamics NAV is configured with multiple service tiers (Microsoft Dynamics NAV Server instances), then you must first enable encryption on one server instance, and then export the key and import to other server instances where you enable encryption.

If you export companies and other data that is secured by data encryption, then remember to also export the encryption key so that you can access the data after you import it into another database, for example when you restore a backup. Creating a backup of encrypted data involves the following high-level steps.

  • Export the data from one database.
  • Export the data encryption key.
  • Import the data into another database.
  • Import the data encryption key.

How to: Export and Import Encryption Keys

Note: You cannot generate different keys within one multiple-server instance environment.

Exporting an Encryption Key

You export an encryption key to make a copy of the key or so that it can be imported on another server instance.

Exporting an encryption key stores the encryption key that is used by the current server instance to a file on your computer or network.

To export an encryption key
DataEncryption-8

  • On the message about saving the encryption key, choose Yes.
  • In the Set Password window, enter the password that will protect the exported key file, and then choose OK.

DataEncryption-9

  • In the Export File window, choose Save, choose a safe location where the key file is stored, and then choose Save.

DataEncryption-10

DataEncryption-11

Importing an Encryption Key

You can import an encryption key to a server instance from an encryption key file that was exported from another server instance or saved as a copy when the encryption was enabled.

You cannot import an encryption key on a server instance that already includes an encryption key. In this case, you must change the encryption key instead.

To import an encryption key
DataEncryption-12

  • On the Home tab, in the Process group, choose Import Encryption Key.
  • In the Select a key file to import window, choose the encryption key file, and then choose Open.

DataEncryption-13

  • In the Password window, enter the password that protects the key file, and then choose OK.

Changing an Encryption Key

If a server instance already has an encryption key, then you can replace the current encryption key with an encryption key that is stored in an encryption key file that was exported from another server.

To change an encryption key

  • On the Home tab, in the Process group, choose Change Encryption Key.

DataEncryption-14

  • In the Select a key file to import window, choose the encryption key file, and then choose Open. Same as in above process.
  • In the Password window, enter the password that protects the key file, and then choose OK. Same as in above process.

Working with Profiles in Navision 2016

How to: Create a Profile

You use the Profile card to create profiles for end users. Each profile is associated with a  Role Center. Profiles are typically based on job titles in a company.

To create a profile

Profiles-1

  • In the Profile ID field, enter a name that describes the intended role of the user.
  • In the Owner ID field, click the AssistEdit button to view all available logins, and then select a Windows user.
  • In the Description field, enter a description of the Profile ID, for example, Order Processor.
  • In the Role Center ID field, click the AssistEdit button to view all available Role Centers. Select a Role Center.
  • To make this Role Center the default for the profile, select Default Role Center.
  • Click OK to save your changes.

The procedure for modifying an existing profile is the same, except you select an existing profile in the Profiles page instead of clicking New.

How to: Assign a User to a Profile

You use the User Personalization page to assign a user to a profile.

To assign a user to a profile

Profiles-2

  • In the User Personalization window, fill in the fields described in the following table.
Field Description
User ID Choose the AssistEdit button to view all available user logins, select the relevant user, and then choose the OK button.
Profile ID Select a profile ID that you have already created. Choose the AssistEdit button to view all available profiles.
Language ID Select the appropriate language ID, or leave this field blank.
Company Select a company from the Companies window.
  • Choose the OK button.

Note The Language ID and Company values are overwritten by user values when the user starts a session.

How to: Open Microsoft Dynamics NAV in Configuration Mode

To configure a profile, for example to customize the profile’s user interface, you must open the Microsoft Dynamics NAV Windows client in configuration mode.

To open the Microsoft Dynamics NAV Windows client in configuration mode

  • At the command prompt, navigate to the root folder of the Microsoft Dynamics NAV Windows client. For example:

cd C:\Program Files (x86)\Microsoft Dynamics NAV\90\RoleTailored Client

  • Type the following command:

Microsoft.Dynamics.Nav.Client.exe -configure -profile:”profileid”

Replace profileid with the name of the profile that you want to configure.

Important You must be the owner of a profile to open it in configuration mode

For example, to configure the Accounting Manager profile, use this command to open the Microsoft Dynamics NAV Windows client in configuration mode:

Microsoft.Dynamics.Nav.Client.exe -configure -profile:”Accounting Manager”

Design Overview of Dimension handling in Navision 2016

Hold on,

Way of handling Dimension have changed from Navision 2009 not a new concept. Till now everyone is aware of this technical change, but still to keep handy reference today I decided to share the same with community, which can help for new comers in this industry.

Detailed technical insight into the concepts and principles that are used to redesign the dimension entry storing and posting feature in Microsoft Dynamics NAV 2016. Helpful when upgrading from earlier version to 2009.

Dimension Sets

A dimension set is a unique combination of dimension values. It is stored as dimension set entries in the database. Each dimension set entry represents a single dimension value.

The dimension set is identified by a common dimension set ID that is assigned to each dimension set entry that belongs to the dimension set.

Dimension Set Entries

Dimension sets are stored in the Dimension Set Entry table as dimension set entries with the same dimension set ID.

When you add, edit and close the Edit Dimension Set Entries window, a check is performed to see whether the combination of dimension values exists as a dimension set in the table.

If the combination occurs in the table, then the corresponding dimension set ID is assigned to the journal line, document header, or document line.

Otherwise, a new dimension set is added to the table, and the new dimension set ID is assigned to the journal line, document header, or document line.
Performance Improvement
By storing dimension sets once in the database, database space is preserved, and overall performance is improved.

Searching for Dimension Combinations

Building Search Tree
Table 481 Dimension Set Tree Node is used when Microsoft Dynamics NAV evaluates whether a set of dimensions already exists in table 480 Dimension Set Entry table.

The evaluation is performed by recursively traversing the search tree starting at the top level numbered 0.

The top level 0 represents a dimension set with no dimension set entries. The children of this dimension set represent dimension sets with only one dimension set entry.

The children of these dimension sets represent dimension sets with two children, and so on.

Finding Dimension Set ID

At a conceptual level, Parent ID, Dimension, and Dimension Value, in the search tree, are combined and used as the primary key because Microsoft Dynamics NAV traverses the tree in the same order as the dimension entries.

The GET function (record) is used to search for dimension set ID.

DimSet.”Parent ID” := 0;  // ‘root’

IF UserDim.FINDSET THEN

REPEAT

DimSet.GET(DimSet.”Parent ID”,UserDim.DimCode,UserDim.DimValueCode);

UNTIL UserDim.NEXT = 0;

EXIT(DimSet.ID);

However, to preserve the ability of Microsoft Dynamics NAV to rename a dimension and dimension value, table 348 Dimension Value is extended with an integer field of Dimension Value ID.

This table converts the field pair Dimension and Dimension Value to an integer value.

When you rename the dimension and dimension value, the integer value is not changed.

DimSet.”Parent ID” := 0;  // ‘root’IF UserDim.FINDSET THEN  REPEAT      DimSet.GET(DimSet.ParentID,UserDim.”Dimension Value ID”);  UNTIL UserDim.NEXT = 0;EXIT(DimSet.ID);

Table Structure

New Tables

Three new tables have been designed to manage dimension set entries.

Table 480 Dimension Set Entry
Table 480 Dimension Set Entry is a new table. You cannot change this table. After data has been written to the table, you cannot delete or edit it.

Deleting data requires that you check against all occurrences of the dimension set ID in the entire database, including partner solutions.

Field No. Field Name Data Type Comment
1 ID Integer >0.0 is reserved for the empty dimension set. References field 3 in table 481.
2 Dimension Code Code 20 Table relation to table 348.
3 Dimension Value Code Code 20 Table relation to table 349.
4 Dimension Value ID Integer References field 12 in table 349. It is the secondary key that is used when traversing table 481.
5 Dimension Name Text 30 CalcField. Lookup to table 348.
6 Dimension Value Name Text 30 CalcField. Lookup to table 349.

Table 481 Dimension Set Tree Node

Table 481 Dimension Set Tree Node is a new table. You cannot change this table.

It is used to search for a dimension set. If the dimension set is not found, a new set is created.

Field No. Field Name Data Type Comment
1 Parent Dimension Set ID Integer 0 for top level node.
2 Dimension Value ID Integer Table relation to field 12 in table 349.
3 Dimension Set ID Integer AutoIncrement. Used in field 1 in table 480.
4 In Use Boolean False if not in use.

Table 482 Reclas. Dimension Set Buffer
Table 482 Reclas. Dimension Set Buffer is a new table.

The table is used to edit a dimension set ID.

It is required when you edit a dimension value code and a new dimension value code, for example, in the Item Reclas. Journal table.

Field No. Field Name Data Type Comment
1 Dimension Code Code 20 Table relation to table 348.
2 Dimension Value Code Code 20 Table relation to table 349.
3 Dimension Value ID Integer References field 12 in table 349.
4 New Dimension Value Code Code 20 Table relation to table 349.
5 New Dimension Value ID Integer References field 12 in table 349.
6 Dimension Name Text 30 CalcField. Lookup to table 348.
7 Dimension Value Name Text 30 CalcField. Lookup to table 349.
8 New Dimension Value Name Text 30 CalcField. Lookup to table 349.

Modified Tables
All transaction and budget tables have been modified to manage dimension set entries.

Changes to Transaction and Budget Tables
A new field has been added to all transaction and budget tables.

Field No. Field Name Data Type Comment
480 Dimension Set ID Integer References field 1 in table 480.

Changes to Table 83 Item Journal Line
Two new fields have been added to table 83 Item Journal Line.

Field No. Field Name Data Type Comment
480 Dimension Set ID Integer References field 1 in table 480.
481 New Dimension Set ID Integer References field 1 in table 480.

Changes to Table 349 Dimension Value
A new field has been added to table 349 Dimension Value.

Field No. Field Name Data Type Comment
12 Dimension Value ID Integer AutoIncrement. Used for references in table 480 and table 481.

Tables That Get New Field 480 Dimension Set ID
A new field, 480 Dimension Set ID, has been added to the following tables.

For the tables that store posted data, the field only provides a non-editable display of dimensions, which is marked as Drill-down.

For the tables that store working documents, the field is editable. The buffer tables that are used internally do not need editable or non-editable capabilities.

The 480 field is non-editable in the following tables

Table No. Table Name
17 G/L Entry
21 Cust. Ledger Entry
25 Vendor Ledger Entry
32 Item Ledger Entry
110 Sales Shipment Header
111 Sales Shipment Line
112 Sales Invoice Header
113 Sales Invoice Line
114 Sales Cr.Memo Header
115 Sales Cr.Memo Line
120 Purch. Rcpt. Header
121 Purch. Rcpt. Line
122 Purch. Inv. Header
123 Purch. Inv. Line
124 Purch. Cr. Memo Hdr.
125 Purch. Cr. Memo Line
169 Job Ledger Entry
203 Res. Ledger Entry
271 Bank Account Ledger Entry
281 Phys. Inventory Ledger Entry
297 Issued Reminder Header
304 Issued Fin. Charge Memo Header
5107 Sales Header Archive
5108 Sales Line Archive
5109 Purchase Header Archive
5110 Purchase Line Archive
5601 FA Ledger Entry
5625 Maintenance Ledger Entry
5629 Ins. Coverage Ledger Entry
5744 Transfer Shipment Header
5745 Transfer Shipment Line
5746 Transfer Receipt Header
5747 Transfer Receipt Line
5802 Value Entry
5832 Capacity Ledger Entry
5907 Service Ledger Entry
5908 Service Header
5933 Service Order Posting Buffer
5970 Filed Service Contract Header
5990 Service Shipment Header
5991 Service Shipment Line
5992 Service Invoice Header
5993 Service Invoice Line
5994 Service Cr. Memo Header
5995 Service Cr. Memo Line
6650 Return Shipment Header
6651 Return Shipment Line
6660 Return Receipt Header
6661 Return Receipt Line

The 480 field is editable in the following tables

Table No. Table Name
36 Sales Header
37 Sales Line
38 Purchase Header
39 Purchase Line
81 Gen. Journal Line
83 Item Journal Line
89 BOM Journal Line
96 G/L Budget Entry
207 Res. Journal Line
210 Job Journal Line
221 Gen. Jnl. Allocation
246 Requisition Line
295 Reminder Header
302 Finance Charge Memo Header
5405 Production Order
5406 Prod. Order Line
5407 Prod. Order Component
5615 FA Allocation
5621 FA Journal Line
5635 Insurance Journal Line
5740 Transfer Header
5741 Transfer Line
5900 Service Header
5901 Service Item Line
5902 Service Line
5965 Service Contract Header
5997 Standard Service Line
7134 Item Budget Entry
99000829 Planning Component

The 480 field has been added to the following buffer tables.

Table No. Table Name
49 Invoice Post. Buffer
212 Job Posting Buffer
372 Payment Buffer
382 CV Ledger Entry Buffer
461 Prepayment Inv. Line Buffer
5637 FA G/L Posting Buffer
7136 Item Budget Buffer

Codeunit 408 Dimension Management
Codeunit 408 Dimension Management is a function library that handles common tasks that are related to dimensions, such as copying from one table to another or from one document to another.

Many functions are deleted because there is no need for copying between dimension tables at the other hand many functions are modified.

Modified Functions

Function Name Modification Description
CheckDimSetIDComb New function that substitutes the other check functions and takes a Dimension Set ID as an argument instead of a dimension table.
CheckDimSetIDComb

CheckDocDimComb

CheckServContractDimComb

CheckDimBuffer

CheckDimComb

CheckDimValueComb

Delete. All usage should be changed to CheckDimSetIDComb.
GetDefaultDim Modify to return an integer Dimension Set ID instead of a set of records.
CopyJnlLineDimToICJnlDim

CopyICJnlDimToJnlLineDim

CopyDocDimtoICDocDim

CopyICDocDimtoICDocDim

Modify to work with DimSetID -> ICJnlLineDim

Deleted Functions

Functions that are deleted from codeunit 408 in connection with the Dimension Set Entries feature are listed below.

During the upgrade of application code from Microsoft Dynamics NAV 2009 or earlier versions to Microsoft Dynamics NAV 2016, the following functions are not available in Microsoft Dynamics NAV 2016.

If you have customizations that use one or more of the functions, you must upgrade that code accordingly.

InsertJnlLineDim

UpdateJnlLineDefaultDim

GetJnlLineDefaultDim

GetPreviousDocDefaultDim

GetPreviousProdDocDefaultDim

InsertDocDim

UpdateDocDefaultDim

ExtractDocDefaultDim

InsertProdDocDim

UpdateProdDocDefaultDim

InsertServContractDim

UpdateServcontractDim

UpdateDefaultDimNewDimValue

GetDocDim

GetProdDocDim

TypeToTableID1

TypeToTableID2

TypeToTableID3

TypeToTableID4

TypeToTableID5

DeleteJnlLineDim

DeleteDocDim

DeletePostedDocDim

DeleteProdDocDim

DeleteServContractDim

ShowJnlLineDim

SaveJnlLineDim

ShowJnlLineNewDim

SaveJnlLineNewDim

ShowDocDim

SaveDocDim

ShowProdDocDim

SaveProdDocDim

ShowTempDim

SaveTempDim

ShowTempNewDim

SaveTempNewDim

SaveServContractDim

MoveJnlLineDimToLedgEntryDim

MoveDocDimToPostedDocDim

MoveOneDocDimToPostedDocDim

MoveLedgEntryDimToJnlLineDim

MoveDimBufToJnlLineDim

MoveDimBufToLedgEntryDim

MoveDimBufToPostedDocDim

MoveDimBufToGLBudgetDim

CopyJnlLineDimToJnlLineDim

CopyLedgEntryDimToJnlLineDim

CopyDocDimToDocDim

CopyPostedDocDimToPostedDocDim

CopyDocDimToJnlLineDim

CopyDimBufToJnlLineDim

CopyDimBufToDocDim

CopySCDimToDocDim

MoveDocDimToLedgEntryDim

MoveDocDimToDocDim

MoveDocDimArchvToDocDim

MoveLedgEntryDimToDocDim

MoveProdDocDimToProdDocDim

MoveJnlLineDimToProdDocDim

MoveJnlLineDimToDocDim

MoveJnlLineDimToJnlLineDim

CopyLedgEntryDimToLedgEntryDim

MoveTempFromDimToTempToDim

TransferTempToDimToDocDim

MoveJnlLineDimToBuf

CopyICJnlDimToICJnlDim

TestDimValue

TestNewDimValue

MoveDimBufToItemBudgetDim. (Delete because the ItemBudgetDim Table is deleted.

GetServContractDim

MoveTempDimToBuf

UpdateSCInvLineDim

CopyJnlLineDimToBuffer

UpdateDocDefaultDim2

Code Examples of Changed Patterns in Modifications

Posting a Journal Line

Key changes are listed as follows:

  • Journal line dimension tables are removed.
  • A dimension set ID is created in the Dimension Set ID field.

Old Code

ResJnlLine.”Qty. per Unit of Measure” :=  SalesLine.”Qty. per Unit of Measure”;

TempJnlLineDim.DELETEALL;

TempDocDim.RESET;

TempDocDim.SETRANGE( “Table ID”,DATABASE::”Sales Line”);

TempDocDim.SETRANGE( “Line No.”,SalesLine.”Line No.”);

DimMgt.CopyDocDimToJnlLineDim( TempDocDim,TempJnlLineDim);

ResJnlPostLine.RunWithCheck( ResJnlLine,TempJnlLineDim);

New Code

ResJnlLine.”Qty. per Unit of Measure” := SalesLine.”Qty. per Unit of Measure”;

ResJnlLine.”Dimension Set ID” := SalesLine.” Dimension Set ID “;

ResJnlPostLine.Run(ResJnlLine);
Posting a Document
When you post a document in Microsoft Dynamics NAV 2016, you no longer have to copy the document dimensions.

Old Code

DimMgt.MoveOneDocDimToPostedDocDim(

TempDocDim,DATABASE::”Sales Line”,

“Document Type”,

“No.”,

SalesShptLine.”Line No.”,

DATABASE::”Sales Shipment Line”,

SalesShptHeader.”No.”);

New Code

SalesShptLine.”Dimension Set ID”  := SalesLine.”Dimension Set ID”
Editing Dimensions from a Document
You can edit dimensions from a document. For example, you can edit a sales order line.

Old Code

Table 37, function ShowDimensions:

TESTFIELD(“Document No.”);

TESTFIELD(“Line No.”);

DocDim.SETRANGE(“Table ID”,DATABASE::”Sales Line”);

DocDim.SETRANGE(“Document Type”,”Document Type”);

DocDim.SETRANGE(“Document No.”,”Document No.”);

DocDim.SETRANGE(“Line No.”,”Line No.”);

DocDimensions.SETTABLEVIEW(DocDim);

DocDimensions.RUNMODAL;

New Code

Table 37, function ShowDimensions:

“Dimension ID” := DimSetEntry.EditDimensionSet( “Dimension ID”);
Showing Dimensions from Posted Entries
You can show dimensions from posted entries, such as sales shipment lines.

Old Code

Table 111, function ShowDimensions:

TESTFIELD(“No.”);

TESTFIELD(“Line No.”);

PostedDocDim.SETRANGE(“Table ID”,DATABASE::”Sales Shipment Line”);

PostedDocDim.SETRANGE(“Document No.”,”Document No.”);

PostedDocDim.SETRANGE(“Line No.”,”Line No.”);

PostedDocDimensions.SETTABLEVIEW(PostedDocDim);

PostedDocDimensions.RUNMODAL;

New Code

Table 111, function ShowDimensions:

DimSetEntry.ShowDimensionSet(“Dimension ID”);
Getting Default Dimensions for a Document
You can get default dimensions for a document, such as a sales order line.

Old Code

Table 37, function CreateDim()

SourceCodeSetup.GET;

TableID[1] := Type1;

No[1] := No1;

TableID[2] := Type2;

No[2] := No2;

TableID[3] := Type3;

No[3] := No3;

“Shortcut Dimension 1 Code” := ”;

“Shortcut Dimension 2 Code” := ”;

DimMgt.GetPreviousDocDefaultDim( DATABASE::”Sales Header”,”Document Type”,

“Document No.”,0, DATABASE::Customer, “Shortcut Dimension 1 Code”,

“Shortcut Dimension 2 Code”);

DimMgt.GetDefaultDim(TableID,No,SourceCodeSetup.Sales,

“Shortcut Dimension 1 Code”, “Shortcut Dimension 2 Code”);

IF “Line No.” <> 0 THEN

DimMgt.UpdateDocDefaultDim( DATABASE::”Sales Line”,”Document Type”,

“Document No.”,”Line No.”, “Shortcut Dimension 1 Code”, “Shortcut Dimension 2 Code”);

New Code

Table 37, function CreateDim()

SourceCodeSetup.GET;

TableID[1] := Type1;

No[1] := No1;

TableID[2] := Type2;

No[2] := No2;

TableID[3] := Type3;

No[3] := No3;

“Shortcut Dimension 1 Code” := ”;

“Shortcut Dimension 2 Code” := ”;

GetSalesHeader;

“Dimension ID” :=  DimMgt.GetDefaultDimID(

TableID,No,SourceCodeSetup.Sales, “Shortcut Dimension 1 Code”,

“Shortcut Dimension 2 Code”, SalesHeader.”Dimension ID”, DATABASE::”Sales Header”);

Configuring SQL Server Authentication in Microsoft Dynamics NAV 2016 – Part-3

To configure SQL Authentication on Microsoft NAV Server Instance using Microsoft Dynamics NAV 2016 Administration Shell

  • If you are modifying an existing Microsoft Dynamics NAV Server instance, run the Set-NAVServerConfiguration cmdlet.

Syntax & Parameter explaination:

Set-NAVServerConfiguration (cmdlet)

-KeyName <String>

(The configuration key name. Examine the CustomSettings.config file to determine the correct key name.)

[-Element <String> ]

(Specifies the navigation path from the root element to the appSettings section of the configuration document.)

[-ServerInstance] <String>

(Specifies the name of a Microsoft Dynamics NAV Server instance. The default instance name is DynamicsNAV90. You can specify either the full name of an instance such as MicrosoftDynamicsNavServer$myinstance or the short name such as myinstance.)

-DatabaseCredentials <PSCredential>

(The user name and password of the login account that the Microsoft Dynamics NAV Server instance will use to connect to the Microsoft Dynamics NAV database in SQL Server. This parameter configures the Microsoft Dynamics NAV Server instance to use SQL Server Authentication instead of Windows Authentication on the connection to the database. The login account must be a member of the db_owner role on the database.)

[-Force] (Forces the command to run without asking for user confirmation.)

[-KeyValue <String> ] (The configuration key value.)

[-Confirm] (Prompts you for confirmation before running the cmdlet.)

[-WhatIf] [ <CommonParameters>]

Use the DatabaseCredentials parameter to provide the login credentials of the database user that you want to use to access the application database.

Example:

C:\PS>Set-NAVServerConfiguration MyInstance -KeyName DatabaseServer -KeyValue DatabaseServer.Domain.Com

  • If you are creating a new Microsoft Dynamics NAV Server instance, run the New-NAVServerInstance cmdlet.

Syntax & Parameter explaination:

New-NAVServerInstance

[-ServerInstance] <String>

(Specifies the name of the Microsoft Dynamics NAV Server instance. The default instance name isDynamicsNAV90. You can specify either the full name of an instance, such as MicrosoftDynamicsNavServer$DynamicsNAV90, or the short name, such as DynamicsNAV90. You must use single-quotes around the instance name.)

-ManagementServicesPort <ServicePort>

(Specifies the TCP port that is used to manage the Microsoft Dynamics NAV Server instance. The Management Services port has no exceptions in the firewall, and will only be accessed from the local computer. The port is used by Windows PowerShell for access Microsoft Dynamics NAV Server management data.)

[-ClientServicesCredentialType <String> ]

(The type of client credential used for client authentication.Possible values are: Windows, Username, NavUserPassword and AccessControlService.)

[-ClientServicesPort <ServicePort> ]

(Specifies the listening TCP port for clients such as Microsoft Dynamics NAV Windows client and Microsoft Dynamics NAV Web client.)

[-DatabaseCredentials <PSCredential> ]

(The user name and password of the login account that the Microsoft Dynamics NAV Server instance will use to connect to the Microsoft Dynamics NAV database in SQL Server. This parameter configures the Microsoft Dynamics NAV Server instance to use SQL Server Authentication instead of Windows Authentication on the connection to the database. If the Microsoft Dynamics NAV Server instance is configured for multitenancy, then parameter configure SQL Authentication on the connection to the application database, not the tenant database. The login account must be a member of the db_owner role on the database.)

[-DatabaseInstance <DatabaseInstance> ]

(Specifies the SQL Server instance on which the Microsoft Dynamics NAV database is installed.)

[-DatabaseName <DatabaseName> ]

(Specifies the name of the Microsoft Dynamics NAV database.)

[-DatabaseServer <DatabaseServer> ]

(Specifies the name of the computer on which the SQL Server instance for the Microsoft Dynamics NAV database is installed.)

[-Force] (Forces the command to run without asking for user confirmation.)

[-Multitenant]

(Specifies the Microsoft Dynamics NAV Server instance to be a multitenant instance.)

[-ODataServicesPort <ServicePort> ]

(Specifies the listening HTTP port for Microsoft Dynamics NAV OData web services.)

[-ServiceAccount <ServiceAccount> ]

(Specifies the Windows-based computer account that the Microsoft Dynamics NAV Server instance must use to log on. The default value is NT AUTHORITY\NETWORK SERVICE. Only NetworkService and User values are supported. This parameter accepts values from the enum System.ServiceProcess.ServiceAccount.)

[-ServiceAccountCredential <PSCredential> ]

(Specifies a set of security credentials that you must use when configuring the service account.)

[-ServicesCertificateThumbprint <ClientServicesCertificateThumbprint> ]

(Specifies the certificate thumbprint for the x509 certificate that is going to be used for securing communication with the server. The certificate must be stored in the local machine store and in the personal sub-store in the certificate store. The private key of the certificate must be present and exchangeable. The certificate must be in .pfx format, not .cer format. The certificate can be either self-signed or issued by a trusted certification authority (CA).

When specifying a ServicesCertificateThumbprint, SOAP web services and OData web services become HTTPS.)

[-SOAPServicesPort <ServicePort> ]

(Specifies the listening HTTP port for Microsoft Dynamics NAV SOAP web services.)

[-Confirm] (Prompts you for confirmation before running the cmdlet.)

[-WhatIf] [ <CommonParameters>]

Use the DatabaseCredentials parameter to provide the login credentials of the database user that you want to use to access the application database.

Examples:

C:\PS>New-NAVServerInstance NewInstance -ManagementServicesPort 8099 -ClientServicesPort 8100 -SOAPServicesPort 8101 -ODataServicesPort 8102 –verbose

C:\PS>Get-Credential | New-NAVServerInstance NewInstance -ServiceAccount User -ManagementServicesPort 8099 -ClientServicesPort 8100 -SOAPServicesPort 8101 -ODataServicesPort 8102 –verbose

 

For Multitenant Environment

  1. Configure SQL Server Authentication with the application database as above.
  2. To configure SQL Authentication with the tenant database, run the Mount-NAVTenant

Syntax & Parameter explaination:

Mount-NAVTenant

[-AlternateId] <System.Collections.ObjectModel.ReadOnlyCollection[string]>

(Specifies the alternative IDs for the tenant, such as host names for the Microsoft Dynamics NAV Web client, SOAP web services, OData web services, or the Microsoft Dynamics NAV Windows client.

If you use alternative IDs for tenant resolution in the Microsoft Dynamics NAV Web client, you must also enable some of the UrlRewrite rules in the Web.Config file for the Microsoft Dynamics NAV Web Server components.)

[-AzureKeyVaultSettings] <Microsoft.Dynamics.Nav.Types.AzureKeyVaultSettings>

(Specifies the Azure key vault settings. This parameter is available only if the EncryptionProvider is set to AzureKeyVault.)

[-ServerInstance] <String>

(Specifies the Microsoft Dynamics NAV Server instance that you want to mount the tenant against, such as DynamicsNAV90. You can specify either the fully qualified name, such as ‘MyServer$DynamicsNAV90’, or the short name, such as ‘DynamicsNAV90’.)

[-DatabaseInstance] <System.String>

(Specifies the name of the SQL Server instance that hosts the database. You can also specify the instance in the DatabaseServer parameter, such as MyServer\MyInstance.)

[-DatabaseName] <System.String>

(Specifies the name of the Microsoft Dynamics NAV database that you want to mount against the Microsoft Dynamics NAV Server instance, such as ‘Demo Database NAV (9-0)’.)

[-DatabaseServer] <System.String>

(Specifies the name of the database server that hosts the Microsoft Dynamics NAV database that you want to mount against the Microsoft Dynamics NAV Server instance.)

[-DefaultCompany] <System.String>

(Specifies the name of the company that NAS services, OData web services, and SOAP web services use if no other company is specified.)

[-DefaultTimeZone] <System.TimeZoneInfo>

(Specifies the default time zone that is used by the NAS services, OData web services, and SOAP web services for this tenant.

You can set the parameter to UTC, ‘Server Time Zone’, or the ID of a Windows Time Zone.

UTC specifies that all business logic for services on the server instance runs in Coordinated Universal Time (UTC).

‘Server Time Zone’ specifies that services use the time zone of the computer that is running Microsoft Dynamics NAV Server instance.

ID of a Windows Time Zone specifies that services use a Windows time zone as defined in the system registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones. For example, Romance Standard Time is a valid Windows time zone value.

If this parameter is not specified, the value is taken from the ServicesDefaultTimeZone setting in the CustomSetting.config file for the Microsoft Dynamics NAV Server instance.)

[-NasServicesEnabled]

(Specifies to enable NAS services on the tenant. The default value is false.)

[-RunNasWithAdminRights]

(Specifies the NAS services to run with administrator rights. This grants the NAS service the same permissions as the SUPER permission set in Microsoft Dynamics NAV without having to add the Microsoft Dynamics NAV Server service account as a user. The default is false.)

[[-ApplicationDatabaseCredentials] <PSCredential> ]

(Specifies the user name and password of the login account that the Microsoft Dynamics NAV Server instance will use to access the application database in SQL Server. This parameter configures the Microsoft Dynamics NAV Server instance to use SQL Server Authentication instead of Windows Authentication on the connection to the application database.

The login account must be a member of the db_owner role on the database.

This parameter is only relevant when you set with the ApplicationDatabaseServer and ApplicationDatabaseName parameters )

[[-ApplicationDatabaseName] <System.String> ]

(Specifies the name of the application database to use with the tenant database.

This parameter is only relevant if the Microsoft Dynamics NAV Server instance is configured for multitenancy. This parameter, together with the ApplicationDatabaseServer parameter, enables you to mount a tenant to the same Microsoft Dynamics NAV Server instance as the application database without having to connect a running Microsoft Dynamics NAV Server instance.)

[[-ApplicationDatabaseServer] <System.String> ]

(Specifies the SQL Server name and instance, such as MyServer\MyInstance, that hosts the application database that you want to use with the tenant database,.

This parameter, together with the ApplicationDatabaseName parameter, enables you to mount a tenant to the same Microsoft Dynamics NAV Server instance as the application database without having to connect to a running Microsoft Dynamics NAV Server instance.)

[-AllowAppDatabaseWrite]

(Specifies if the tenant can write to the application database. The default value is false.)

[-DatabaseCredentials <PSCredential> ]

(Specifies the user name and password of the login account that the Microsoft Dynamics NAV Server instance will use to access the tenant database in SQL Server. This parameter configures the Microsoft Dynamics NAV Server instance to use SQL Server Authentication instead of Windows Authentication on the connection to the database.

The login account must be a member of the db_owner role on the database.)

[-EncryptionProvider <Microsoft.Dynamics.Nav.Types.EncryptionProvider> ]

(Specifies the name of the encryption provider.)

[-Force] (Forces the command to run without asking for user confirmation.)

[-OverwriteTenantIdInDatabase]

(Specifies if the Mount-NAVTenant cmdlet must overwrite the tenant ID in the database if the database has been mounted as a tenant earlier. If this is false, and the tenant database has previously been mounted with a different tenant ID, an exception is thrown.)

[-Confirm] (Prompts you for confirmation before running the cmdlet.)

[-WhatIf] [ <CommonParameters>]

Use the DatabaseCredentials parameter to provide the login credentials of the database user that you want to use to access the tenant database.

Examples:

PS C:\> Mount-NAVTenant DynamicsNAV90 -Id ‘Test’ -DatabaseName ‘Test_Database’

PS C:\> Mount-NAVTenant DynamicsNAV90 Test Test_Database

PS C:\> Mount-NAVTenant DynamicsNAV90 -Id ‘Test’ -DatabaseName ‘Test_Database’ -DatabaseCredentials (Get-Credential)

PS C:\> Get-NAVTenant Server1 | Mount-NAVTenant Server2

PS C:\> Get-NAVTenant Server1 | Dismount-NavTenant Server1 -Force | Mount-NAVTenant Server2

PS C:\> Mount-NAVTenant DynamicsNAV90 -Id ‘Test’ -DatabaseName ‘Test_Database’-AlternateId @( “test.mydomain.com”, “http://mydomain.sharepoint.com/sites/teamsite&#8221; )

PS C:\> Mount-NAVTenant -Id ‘Test’ -DatabaseName ‘Test_Database’ -DatabaseCredentials (Get-Credential) -ApplicationDatabaseServer ‘MySQLServer\NAV’ -ApplicationDatabaseName ‘MyNavAppDatabase’ -ApplicationDatabaseCredentials (Get-Credential) -KeyFilePath ‘C:\key\nav.key’ -KeyFilePassword (Get-Credential).Password

Configuring SQL Server Authentication in Microsoft Dynamics NAV 2016 – Part-2

Recall from my previous post Configuring SQL Server Authentication in Microsoft Dynamics NAV 2016 – Part-1, action is almost similar with few small differences when dealing with Multitenant Deployment.
Configure SQL Server Authentication on Microsoft NAV Server Instance in a Multitenant Deployment
How to configure a Microsoft Dynamics NAV database to use SQL Server Authentication with a Microsoft Dynamics NAV Server instance.

To configure a SQL Server Authentication on a Microsoft Dynamics NAV Server instance, you set up the server instance with the login credentials (user name and password) for the user accounts for the application and tenant databases in SQL Server.

To configure SQL Authentication on Microsoft NAV Server Instance using Microsoft Dynamics NAV Server Administration tool

  • Open the Microsoft Dynamics NAV Server Administration tool.
  • In the console tree, which is the left pane, expand the node for the computer that contains the Microsoft Dynamics NAV Server instance, and then select the Microsoft Dynamics NAV Server instance.
  • Configure SQL Server Authentication with the application database as follows:
    • In the Actions pane, choose Database Credentials.
    • On the Database Credentials page, choose the Edit button.
    • Set the Database Authentication Mode to SQL Server Authentication.
    • In the Database User Name field, type the login name for the database user that you want to use to access the Microsoft Dynamics NAV application database in SQL Server.
    • In the Password field, type the login password for the database user that you want to use to access the Microsoft Dynamics NAV database in SQL Server.
    • Choose the Save button, and then on the Enable Encryption on SQL Server Connections dialog box, choose the OK button.

Encryption keys are used to help secure the login credentials over the connection between the Microsoft Dynamics NAV Server instance and the Microsoft Dynamics NAV database in SQL Server.

  • On the Information dialog box about encryption, choose the OK button.

This dialog box is to inform you to enable encryption on SQL Server connections, which is disabled by default.

  • If you want to enable encryption on SQL Server connections, in the Action pane, choose Configuration, and then choose the Edit button. In the Database tab, select Enable Encryption on SQL Connections, choose the Save button, and then the OK button.
  • To configure SQL Server Authentication with the tenant database, mount the tenant to the Microsoft Dynamics NAV Server instance and specify the login credentials (user name and password) for the database user that you want to use to access the Microsoft Dynamics NAV tenant database in SQL Server.

If the tenant is already mounted to the Microsoft Dynamics NAV Server instance, you must dismount the tenant, and mount it again.

Restart the server instance.

Configuring SQL Server Authentication in Microsoft Dynamics NAV 2016 – Part-1

Microsoft Dynamics NAV 2016 supports SQL Server authentication between the Microsoft Dynamics NAV Server instance and the Microsoft Dynamics NAV database in SQL Server. Previously only Windows authentication was supported.

Set Up an Encryption Key

When using SQL Server authentication, Microsoft Dynamics NAV requires an encryption key to encrypt the credentials (user name and password) that the Microsoft Dynamics NAV Server instance uses to connect to the Microsoft Dynamics NAV database in SQL Server.

The encryption key must be installed on the computer where the Microsoft Dynamics NAV Server is installed and also in the database in SQL Server.

In a multitenant deployment, the encryption key must be installed in the application database.

To set up an encryption key, you can use one of the following methods:

  • You can create and import your own encryption key by using Microsoft Dynamics NAV 2016 Administration Shell cmdlets.
  • If you are configuring SQL Server authentication on a Microsoft Dynamics NAV Server instance for the first time, you can use the Microsoft Dynamics NAV Server Administration tool which can automatically create and install a system encryption key. If you decide to use this method, no action is required.

To create and import encryption key

  • In the Microsoft Dynamics NAV 2016 Administration Shell, run the New-NAVEncryptionkey

Create an encryption key and stores it in a file in a specified path on the computer or network.

Syntax

New-NAVEncryptionKey [-KeyPath] <String> [-Force] [-Password <SecureString> ] [-Confirm] [-WhatIf] [ <CommonParameters>]

The New-NAVEncryptionKey cmdlet enables you to specify a destination file for the key and specify a password to protect the file.

This creates a file that contains an encryption key. If you already have an encryption key file, you can skip this step.

Example: New-NAVEncryptionKey  -KeyPath “C:\UserData\SQLKey\MySQLKey” -Password (Get-Credential).Password

SQLServerAuthentication1

  • Run the Import-NAVEncryptionkey cmdlet to install the encryption key on the Microsoft Dynamics NAV Server instance and database.

Imports an encryption key from a file to a Microsoft Dynamics NAV Server instance and database in SQL Server.

Syntax

Import-NAVEncryptionKey [[-ServerInstance] <String> ] [-KeyPath] <String> -ApplicationDatabaseName <String> -ApplicationDatabaseServer <String> [-ApplicationDatabaseCredentials <PSCredential> ] [-Force] [-Password <SecureString> ] [-Confirm] [-WhatIf] [ <CommonParameters>]

Example: Import-NAVEncryptionKey -ServerInstance ‘DynamicsNAV90’ -KeyPath “C:\UserData\SQLKey\MySQLKey.key” -ApplicationDatabaseServer ‘INDEL-AXT5283VM’ -ApplicationDatabaseName ‘Demo Database NAV (9-0)’ -Password (Get-Credential).Password

SQLServerAuthentication2

You cannot import an encryption key on the Microsoft Dynamics NAV Server instance if an encryption key file already exists. You must first delete the encryption key from the computer where Microsoft Dynamics NAV Server is installed.

By default, encryption keys are stored in the C:\ProgramData\Microsoft\Microsoft Dynamics NAV\90\Server\Keys folder.

Configure SQL Authentication on the Database

This section describes how to configure a Microsoft Dynamics NAV database to use SQL Server Authentication with a Microsoft Dynamics NAV Server instance. You can complete the steps in this procedure by using SQL Server Management Studio or Transact-SQL.

Important : In a deployment where the Microsoft Dynamics NAV Server instance is configured as a multitenant server instance, you must complete the following procedure on the application database and tenant database.

To configure SQL Server Authentication on the database in SQL Server

  • Configure the SQL Server instance (Database Engine) that hosts the Microsoft Dynamics NAV database to use SQL Server Authentication.

To use SQL Server authentication, you configure the database instance to mixed authentication mode (SQL Server and Windows Authentication).

In the SQL Server instance, create a login that uses SQL Server authentication.
SQLServerAuthentication3

  • Map the login to a user in the Microsoft Dynamics NAV database, and add the user to the db_owner role of the Microsoft Dynamics NAV database.

SQLServerAuthentication4
Configure SQL Server Authentication on Microsoft NAV Server Instance (Non-Multitenant)
You configure the Microsoft Dynamics NAV Server instance with the login credentials (user name and password) of the user account in the Microsoft Dynamics NAV database in SQL Server that you want to use for authentication. You can do this using the Microsoft Dynamics NAV Server Administration tool or Microsoft Dynamics NAV 2016 Administration Shell.
To configure SQL Authentication on Microsoft NAV Server Instance using Microsoft Dynamics NAV Server Administration tool

  • Open the Microsoft Dynamics NAV Server Administration tool.
  • In the Actions pane, choose Database Credentials.
  • Set the Database Authentication Type to SQL Authentication.
  • In the Database User Name field, type the login name for the database user that you want to use to access the Microsoft Dynamics NAV database in SQL Server.

In the Password field, type the login password for the database user that you want to use to access the Microsoft Dynamics NAV database in SQL Server.
SQLServerAuthentication5

  • Choose the Save button, and then on the Enable Encryption on SQL Server Connections dialog box, choose the OK button.

SQLServerAuthentication6

Encryption keys are used to help secure the login credentials over the connection between the Microsoft Dynamics NAV Server instance and the Microsoft Dynamics NAV database in SQL Server.

  • On the Information dialog box about encryption, choose the OK button.

This dialog box is to inform you to enable encryption on SQL Server connections, which is disabled by default.
SQLServerAuthentication7

  • If you want to enable encryption on SQL Server connections, in the Action pane, choose Configuration, and then choose the Edit button. In the Database tab, select Enable Encryption on SQL Connections, choose the Save button, and then the OK button.

SQLServerAuthentication8

  • Restart the server instance.

Checkout my upcoming posts for more details on this.

Few Helpful PowerShell Commands which you can use for Upgrade Process in Navision 2016 – Part 2

You can use Windows PowerShell scripts to upgrade the latest version of Microsoft Dynamics NAV. Microsoft Dynamics NAV 2016 provides sample scripts that you can adapt for your deployment architecture.

Automating the Upgrade Process

When you upgrade to Microsoft Dynamics NAV 2016, you must first upgrade the application code, and then you upgrade the data.

In my earlier post I have explained this using PowerShell commands, you can find the link here: Helpful PowerShell Commands which you can use for Upgrade Process in Navision 2016

By using Windows PowerShell, you can automate both parts of the upgrade process. Also, you can use the same scripts to test each step in your upgrade process before you upgrade production databases.

You can combine this automated upgrade with a migration to multitenancy this makes maintenance easier for you.

The Sample Scripts for Code Upgrade

Microsoft Dynamics NAV includes sample scripts that illustrate how you can use Windows PowerShell cmdlets to upgrade your application to the latest version of Microsoft Dynamics NAV.

The sample scripts are located in the ApplicationMergeUtilities folder under the WindowsPowerShellScripts folder on the Microsoft Dynamics NAV product media.

However you can follow above post link steps explanation to get it done.

The Sample Scripts for Data Upgrade

Microsoft Dynamics NAV includes sample scripts that illustrate how you can automate the upgrade of data to the latest version of Microsoft Dynamics NAV.

The sample scripts are located in the Upgrade folder under the WindowsPowerShellScripts folder on the Microsoft Dynamics NAV product media. You can run the sample script using a partner license or a customer license.
PowerShell-3

To learn and follow MS suggested steps you can find details using this link. Automating the Upgrade Process using Windows PowerShell Scripts in Microsoft Dynamics NAV 2016

To run the sample script for the data upgrade of a Microsoft Dynamics NAV database, you must have a Microsoft Dynamics NAV 2013, Microsoft Dynamics NAV 2013 R2, or Microsoft Dynamics NAV 2015 database that is available on a SQL Server instance and is ready to be upgraded.

Here I present my version derived from above Steps:

To continue we will do some setup. Copy the Upgrade folder from above path and save as DataUpgradePSKit.

PowerShell-4

Create Folder OriginalScript and move the PS1 file on root to this folder although we don’t require for this exercise but you can safe copy for your reference. (Example, Set-PartnerSettings, Set-PowerShellEnvironment)

Create Backup folder, script will use to store backup of the database previous to start Upgrade process.

Create Upgrade Folder and place these files:

  • License File
  • New Merged Objects fob
  • Upgrade Toolkit / or your own prepared Upgrade Codeunits

PowerShell-5

  • Create ProcessLogs Folder, which will be used for recording log of Shell Script.

Here is the script which we will be using to perform our Data Upgrade process:

You can find this script here http://1drv.ms/1NyolVV or you can download from Menu of my Blog using Link Shared Files.

 

# Added below parameter values globally for ease of maintenance

# You just do correction on values here (as per your environment) and will be in effect for rest of below script

# No need to scan and change every occurrence for same value in different steps of the script.

# Select this section and Execute first so that these Variables value are available for rest of the script.

Import-Module ‘C:\Program Files\Microsoft Dynamics NAV\90\Service\NavAdminTool.ps1’

$NAVUpgrade_NAVServerInstance = “UpgradedDBfrom2013R2”

$NAVUpgrade_NAVServerServiceAccount = “NT AUTHORITY\NETWORK SERVICE”

$NAVUpgrade_FinSqlExeFile = “C:\Program Files (x86)\Microsoft Dynamics NAV\90\RoleTailored Client\finsql.exe”

$NAVUpgrade_IDEModulePath = “”

$NAVUpgrade_DatabaseServer = “INDEL-AXT5283VM”

$NAVUpgrade_DatabaseInstance = “”

$NAVUpgrade_DatabaseName = “Demo Database NAV (7-1)”

$NAVUpgrade_DatabaseToUpgradeBakFile = “C:\UserData\DataUpgradePSKit\Backup\DynamicsNAV70_BeforeUpgrade.bak”

$NAVUpgrade_NewVersionObjectsFobFilePath = “C:\UserData\DataUpgradePSKit\Upgrade\NewObjects.fob”

$NAVUpgrade_UpgradeToolkitObjectsFobFilePath = “C:\UserData\DataUpgradePSKit\Upgrade\Upgrade710900.FOB”

$NAVUpgrade_UpgradeObjectsFilter = “Version List=UPGTK9.00.00”

$NAVUpgrade_UpgradeLogsDirectory = “C:\UserData\DataUpgradePSKit\Upgrade\ProcessLogs”

#$NAVUpgrade_RapidStartPackageFile = ‘C:\UserData\DataUpgradePSKit\Upgrade\PackageSTCODES.rapidstart’

$NAVUpgrade_CurrentVersionLicenseFile = “C:\UserData\DataUpgradePSKit\Upgrade\DevLicense.flf”

$NAVUpgrade_PreviousVersionLicenseFilePath = “C:\UserData\DataUpgradePSKit\Upgrade\DevLicense.flf”

 

# Upgrade Steps:

Import-Module (Join-Path (Get-Location) ‘Cmdlets\NAVUpgradeCmdlets.psm1’) -DisableNameChecking

#1. Prepares the Windows PowerShell session by importing the required modules.

        # Import the NAV IDE Module.

Import-NAVIdeModule -IDEModuleSuggestedPath $NAVUpgrade_IDEModulePath -FinSqlExeFile $NAVUpgrade_FinSqlExeFile

Import-NAVManagementModule

Import-SqlPsModule

 

#2. Saves the current license from the Microsoft Dynamics NAV 2013, Microsoft Dynamics NAV 2013 R2, or Microsoft Dynamics NAV 2015 database.

# Backup current license from the application part of the database (table ‘$ndo$dbproperty’) , if it exists

        Export-NAVLicenseFromApplicationDatabase `

-DatabaseName $NAVUpgrade_DatabaseName `

-DatabaseServer $NAVUpgrade_DatabaseServer `

-DatabaseInstance $NAVUpgrade_DatabaseInstance `

-LicenseFilePath $NAVUpgrade_PreviousVersionLicenseFilePath

 

#3. Creates a backup of the Microsoft Dynamics NAV 2013, Microsoft Dynamics NAV 2013 R2, or Microsoft Dynamics NAV 2015 database, and then converts the database to Microsoft Dynamics NAV 2016.

        Backup-NAVSqlDatabase `

-DatabaseServer $NAVUpgrade_DatabaseServer `

-DatabaseInstance $NAVUpgrade_DatabaseInstance `

-DatabaseName $NAVUpgrade_DatabaseName `

-DatabaseBackupFilePath $NAVUpgrade_DatabaseToUpgradeBakFile

$NAVUpgrade_DatabaseSQLServerInstance = Get-SqlServerInstance -DatabaseServer $NAVUpgrade_DatabaseServer -DatabaseInstance $NAVUpgrade_DatabaseInstance

$NavServerInfo = New-Object PSObject

Add-Member -InputObject $NavServerInfo -MemberType NoteProperty -Name NavServerName -Value “$NAVUpgrade_DatabaseServer”

Add-Member -InputObject $NavServerInfo -MemberType NoteProperty -Name NavServerInstance -Value (Get-NAVServerConfigurationValue  -ServerInstance $NAVUpgrade_NAVServerInstance -ConfigKeyName “ServerInstance”)

Add-Member -InputObject $NavServerInfo -MemberType NoteProperty -Name NavServerManagementPort -Value (Get-NAVServerConfigurationValue -ServerInstance $NAVUpgrade_NAVServerInstance -ConfigKeyName “ManagementServicesPort”)

 

# Perform technical upgrade of the NAV database

        Invoke-NAVDatabaseConversion `

-DatabaseName $NAVUpgrade_DatabaseName `

-DatabaseServer $NAVUpgrade_DatabaseSQLServerInstance `

-LogPath $NAVUpgrade_UpgradeLogsDirectory\”Database Conversion”

 

#4. Connects the Microsoft Dynamics NAV 2016 Server instance to the converted database, imports the Microsoft Dynamics NAV 2016 license file, and then synchronizes the table schema.

 

# Connect the NAV Server to the NAV database

        Connect-NAVServerToNAVDatabase  `

-NAVServerInstance $NAVUpgrade_NAVServerInstance `

-NAVServerServiceAccount $NAVUpgrade_NAVServerServiceAccount `

-DatabaseServer $NAVUpgrade_DatabaseServer `

-DatabaseInstance $NAVUpgrade_DatabaseInstance `

-DatabaseName $NAVUpgrade_DatabaseName

# Import the new version license into the application database, and restart the server in order for the license to be loaded

        Import-NAVServerLicense -ServerInstance $NAVUpgrade_NAVServerInstance -LicenseFile $NAVUpgrade_CurrentVersionLicenseFile -Database NavDatabase

Set-NAVServerInstance -ServerInstance $NAVUpgrade_NAVServerInstance -Restart

# Synchronize the NAV database

        Sync-NAVTenant -ServerInstance $NAVUpgrade_NAVServerInstance -Mode Sync -Force

 

#5. Imports the application objects and upgrade toolkit objects from the specified .fob file, and then synchronizes the table schema again.

#   This updates the SQL Server database based on the new table schema that is defined by the imported application objects. Data that must be mapped to another table is saved in upgrade tables.

# Delete the tables from the previous version, using SynchronizeSchemaChanges Later.

# The new  objects we import will contain the new version of the tables.

        Delete-NAVApplicationObject `

-DatabaseName $NAVUpgrade_DatabaseName `

-DatabaseServer $NAVUpgrade_DatabaseSQLServerInstance `

-LogPath $NAVUpgrade_UpgradeLogsDirectory `

-Filter “Type=Table;ID=<2000000000” `

-SynchronizeSchemaChanges “No” `

-NavServerName $NavServerInfo.NavServerName `

-NavServerInstance $NAVServerInfo.NavServerInstance `

-NavServerManagementPort $NavServerInfo.NavServerManagementPort `

-Confirm:$false

# Import all the new objects and the upgrade objects, by delaying the schema synchronization

# If an $UpgradeToolkitObjects value has not been provided, then

#  the assumption is that the upgrade toolkit is within the same .FOB as the new objects

           if(!$UpgradeToolkitObjects)

{

# Import FOB file containing the new version of the application objects, including the upgrade toolkit

Import-NAVApplicationObject `

-Path $NAVUpgrade_NewVersionObjectsFobFilePath `

-DatabaseName $NAVUpgrade_DatabaseName `

-DatabaseServer $NAVUpgrade_DatabaseSQLServerInstance `

-LogPath $NAVUpgrade_UpgradeLogsDirectory `

-ImportAction “Overwrite” `

-SynchronizeSchemaChanges “No” `

-NavServerName $NavServerInfo.NavServerName `

-NavServerInstance $NAVServerInfo.NavServerInstance `

-NavServerManagementPort $NavServerInfo.NavServerManagementPort `

-Confirm:$false

}

else

{

 

# Import FOB file containing the new version of the application objects

Import-NAVApplicationObject `

-Path $NAVUpgrade_NewVersionObjectsFobFilePath `

-DatabaseName $NAVUpgrade_DatabaseName `

-DatabaseServer $NAVUpgrade_DatabaseSQLServerInstance `

-LogPath $NAVUpgrade_UpgradeLogsDirectory `

-ImportAction “Overwrite” `

-SynchronizeSchemaChanges “No” `

-Confirm:$false

 

# Import FOB file containing the upgrade codeunit and upgrade tables

Import-NAVApplicationObject `

-Path $NAVUpgrade_UpgradeToolkitObjectsFobFilePath `

-DatabaseName $NAVUpgrade_DatabaseName `

-DatabaseServer $NAVUpgrade_DatabaseSQLServerInstance `

-LogPath $NAVUpgrade_UpgradeLogsDirectory `

-ImportAction “Overwrite” `

-SynchronizeSchemaChanges “No” `

-Confirm:$false

}

# Synchronize the metadata changes to SQL

        Sync-NAVTenant -ServerInstance $NAVUpgrade_NAVServerInstance -Mode Sync -Force

#6. Calls the Start-NAVDataUpgrade cmdlet to verify the data upgrade preconditions and transfer data from the upgrade tables to the destination tables.

# Invoke the Data Upgrade process

        Invoke-NAVDataUpgrade -ServerInstance $NAVUpgrade_NAVServerInstance

 

#7. Deletes all obsolete tables and the upgrade toolkit objects.

# Delete Upgrade Toolkit objects

        Delete-NAVApplicationObject `

-DatabaseName $NAVUpgrade_DatabaseName `

-DatabaseServer $NAVUpgrade_DatabaseSQLServerInstance `

-LogPath $NAVUpgrade_UpgradeLogsDirectory `

-Filter “$NAVUpgrade_UpgradeObjectsFilter;ID=<2000000000” `

-SynchronizeSchemaChanges “Force” `

-NavServerName $NavServerInfo.NavServerName `

-NavServerInstance $NAVServerInfo.NavServerInstance `

-NavServerManagementPort $NavServerInfo.NavServerManagementPort `

-Confirm:$false

 

#8.       Initializes all companies in the upgraded database. If you specified a RapidStart package in the Set-PartnerSettings.ps1 file, the package is applied to all companies.

# Optionally, run RapidStart package import

        if($NAVUpgrade_RapidStartPackageFile)

{

Invoke-NAVRapidStartDataImport -ServerInstance      $NAVUpgrade_NAVServerInstance -RapidStartPackageFile $NAVUpgrade_RapidStartPackageFile

 

}

 

The sample script is intended to be run in the context of a Microsoft Dynamics NAV 2016 deployment, including the Microsoft Dynamics NAV Server instance.

The Microsoft Dynamics NAV Server instance cannot be multitenant. When the sample script runs successfully, the result is a Microsoft Dynamics NAV 2016 database that is connected to a Microsoft Dynamics NAV 2016 Server instance, and which uses a Microsoft Dynamics NAV 2016 license.

You may face some permission related issues, take help of you IT person if not sure about the nature of issue or use Administrator login.

I will come with more details in my next posts.

 

 

Previous Older Entries

Important

Most of the contents you find in this blog will be either inherited from MSDN or Navision Developer IT Pro Help. Some places images are also directly taken from these sites. Purpose is simple to try those stuffs and re-produce adding few things as per my understanding to make easy understanding for others and quick reference.

Here nothing under my own brand or authorship of the content. At any point of time we are just promoting Microsoft stuffs nothing personnel with same.

Hope stuffs used here will not violate any copyright agreement with them. In case by mistake or in-intestinally it happens and the Microsoft feels these should not be used Microsoft have full right to inform me about same and will be glad to take down any such content which may be violating the norms.

Purpose is to promote Navision and share with community.

FB Profile

Like FB Page

%d bloggers like this: