Sharing data with multiple tenants

I am upgrading multiple companies to NAV 2016.  I really like to use the multi tenant setup and use it in most cases.

In NAV we have the option to make a table common with all companies.

NoDataPerCompany

This option has been available for all the versions of NAV that I can remember.

Using a multi tenant setup means that you have a dedicated database for each tenant and normally only one company for each tenant.  That makes this option completely useless.

I was running this same multi tenant setup in NAV 2013 R2 and there I solved this issue by modifying the table to be a linked table.

LinkedObject

To successfully setup a linked table I need to manually make sure that the table or view with the correct name and the correct layout is present in every tenant database.  That is a job for the SQL Server Management Studio (SSMS) and can’t be done within NAV.  Doing this also makes upgrades more difficult and can get in the way of a normal synchronization of metadata and tables.

Moving up to NAV 2016 I wanted to get out of this model and use the External SQL methods now available.

ExternalTable

With these properties we can select the table or view name as the ExternalName and the table or view schema as the ExternalSchema.  For all fields in the table we can define an ExternalName.  If that is not defined the normal NAV field name will be used.

FieldNames

This option basically opens the door from the NAV Server to any SQL table.  So, how do we get this to work?

I will show you how I moved from the Linked Table method to the External SQL method.  If you take another look at the properties available for an External SQL table you will see that the DataPerCompany property is not available.  So, an External SQL table is just a table definition for NAV to use and with C/AL code you can define where to find the external table.  This gives you the flexibility to have the same table with all companies and all tenants or select different by tenants and/or companies.

In Iceland we have a national registry.  That registry holds the registration details for every person and every company.  Some companies buy access to the data from the national registry and keep a local copy and are allowed to do a lookup from this data.  Since the data in this table is updated centrally but every company in every tenant wants to have access this is a good candidate for the External SQL table method.

I already had the table defined in NAV with needed data.  I was able to find that table with SSMS.

OriginalTable

By using this table I did not have to worry about the ExternalName for each column in my table definition since it already matched the NAV field names.

I found my application database and used the script engine in SSMS to script the database creation.  I updated the database name to create a central database for my centralized data.  I choose to use this method to make sure that the new database has the same collation as the NAV application database.

I scripted the National Register table creation and created the table in my centralized database.  Then combined the scripts from INSERT INTO and SELECT FROM to insert data into my centralized table.

Finally I made sure that the service user running the NAV service had access to my centralized database.  By doing this I can use a trusted connection between the NAV server and the SQL server.

Moving to NAV developement environment and into the table properties.

NationalRegisterExternalSQL

The ExternalName and ExternalSchema must match the table I created.  Look at the picture from the SSMS to see “FROM [dbo].[National Register]”.  There you can pick up what you need to specify in these properties.

When these changes are synchronized to my database NAV will remove the previous National Register table from the NAV database.  That requires a synchronization with force so be careful.

The actual connection to the centralized database must be done in C/AL.  More information is on this MSDN article.

To complete this solution I use several patterns.

I need a setup data to point me to the centralized database.  I like to look at this as an external service so I link the setup to the Service Connections, Waldo calls this the Discovery Event Pattern.  I create the following function in a Codeunit to register the service.

RegisterConnection

So, if the user has write access to the National Register Setup he will see this service listed in the service connections.

The link to an external database might require me to save a user name and a password.  To successfully do this I apply another pattern for password encryption.  I normally point people to the OCR service setup table and page to find out how to implement these password patterns.

I like to have the Enabled field on my setup tables.  When the service is enabled the user can’t modify the setup data and when trying to enable the service the setup data is verified.  Fields on the setup page are protected by using the EditableByNotEnabled variable.

EditableByNotEnabled

I don’t think you will find a pattern for this method but the setup table in other details follows the Singelton pattern.

NRSetup2

When the user tries to enable the service I do a series or error testing.  The error testing are done with the combination of the Error Message pattern and the TryFunction pattern.

TestSetup

Line 21 does the actual connection test with a TryFunction.

Now, how to connect to the centralized data?

In my setup table I store the database server name and the database name within that server.  With this data I create the connection string.

RegisterUserConnection

The table connection must have a unique id.  I like to create a function to return variables that are linked to the functionality – not using the text constants.

GetConnectionName

This combines what I need to do.  With the correct connection string C/AL registers the connection to my centralized database and set that connection as a default connection for the user.  When NAV needs to use the data from the National Register C/AL must register the connection.

CheckOrRegister

Adding a call to this Codeunit from every page and every function that uses the National Register.

PageInit

Now back to my TryFunction, I can simply check if I can do a FINDFIRST without an error.

TryLookup

 

 

 

Using the new FilterPage in NAV 2016

I was a little surprised to not find any information online on the new FilterPage type in Dynamics NAV 2016.

As a part of the new Workflow feature Microsoft built a new generic feature to ask the user for a filter on any record.

Workflow

Pressing the Assist-Edit button will open the Dynamic Filter Page.

DynamicFilterPage

This view is the same view a NAV users is familiar with when starting reports and batches.

Now to show how to use this new feature.  The best way to show is usually with an example.

Go to the Chart of Accounts.  Then from the ribbon select G/L Balance by Dimension.  Select a setup similar to the screenshot below and press Show Matrix on the ribbon.

GLByDimension

Now you are in a page where you can’t filter anything.  You will see all G/L Accounts within the G/L Account Filter selected earlier and all Accounting Periods in columns according to the Matix Options.  Yes, you have all the normal filter options on the page but none of them work.

OriginalMatrix

So lets see how to use the Dynamic FilterPage to give the user a better experience of this feature.

The first challenge; I want a single month comparison in the columns.  Lets compare amounts for January by year.

To do this we need to make a few modifications to Page 408.

Add the global text variable PeriodTableView.

Page408AddNewGlobal

When the user changes what to show as columns we need to make sure that the PeriodTableView is empty.

Page408ClearPeriodTableView

When the column captions are generated the new PeriodTableView should be used.

Page408AddSetView

Same changes needs to be applied to the NextRec function.

Two new functions needs to be added to ask the user for the filter.

Page408NewFunctions

And finally, get these functions available for the user.

CallingPageView

The result is that the user can now press the Assist-Edit button and enter a filter for every column option.

Page408AccountingPeriod

To attain our goal, lets filter on the month we want to see.

FilterOnJanuary

And the result Matrix looks like this.

MatrixForJanuary

We could add a filter page to the Matrix Page to be able to filter on the G/L Accounts using the same methods and we could add a functionality to add filter on the lines similar to what we did for the columns, but I am not going though that now.

The modified Page 408 is attached.  Good luck.

Page408

 

Asynchronous web services in NAV

Asynchronous methods are very useful for web services.  To be able to start a process with one method and then check the status with another opens a lot of possibilities.

There are two known patterns in NAV that support asynchronous methods;  one is to use the STARTSESSION function to execute the business logic in another thread, the other is to create a job queue entry and let NAS handle the task.

I was talking to a client this morning and suggested using the asynchronous web services for his tasks.  I decided to write some code and test the functionality to see if my theory was working.

The first thing I need is a table for the requests.  This table contains the identification for the queue, the status and other details.

ProcessQueueTable

A single web service method is used to create a new queue entry.

AsyncWebService

I like to use the new TryFunction for my web services.  Note that when the queue has been inserted I fire an event with the newly created queue entry.  This means that I can extend this web service with events without having to modify the web service signature.

Each process needs a dedicated Codeunit.  That Codeunit is built on top of the queue record and thereby using the parameter table pattern.

TheAsyncJob2

Note that this Codeunit is also using the TryFunction and taking care of the rollback if that function fails.

The process Codeunit catches the integration event and checks the process code before starting then selected task.

ProcessStarter2

So, if I ask the web service to start a job called StartAsyncAdjustCostItemEntriesProcess, NAV will start a new session to execute the adjust cost for item entries.  The process that creates the queue and starts a new session is very quick so the response from the web service is almost instant.

Then we wait for a moment and ask the web service about the status for the newly created queue.

CheckQueueStatus

Again using the TryFunction to make sure that my web service will have a proper response to the query.

It is quite easy to extend this module by catching the OnNewQueueInserted event for each process code you build support for.

A sample C# code that I used to test this looks like this

CSharpDemoCode2

As you can imagine we can put anything into the ProcessData variable.  By using Base64 encoding anything can be converted to a text variable.

I hope this will turn on some lights and you will be able to use this in your daily work.

Attached is a zip file with the NAV objects and the C# project.

NAVAsyncWebService

 

 

 

 

Twenty different Menu Suites

In an effort to give better support to NAV Extensions Microsoft have added support for ten more MenuSuite objects.

MenuSuites

I have given my opinion to Microsoft on the MenuSuite design and how it could be improved.  I guess everything can be improved in one way or another.

To quote Microsoft: “This simple update doesn’t pretend to solve the underlying problem described. It mitigates by doubling the number of add-ons to 20, already accounted for in existing licenses. We are aware that this is not the long term solution, but it is a quick way to mitigate some partners’ problems with MenuSuites and Add-ons.” 

NAV 2016 Data Exchange – file import

As promised it is time to pick more things that I have improved for NAV 2016.  Still on the Data Exchange, and looking at file import.  We can import a text file; fixed and delimited, with different encoding.

By using Codeunit 1240 for the External Data Handling action we are able to get any text file into the Data Exchange framework.  It will simply prompt the user for a local file name.

Microsoft shipped Codeunit 1241 to read a flat file.  I decided to change that a bit with two enhancements.  First, I wanted to be able to read a delimited file and second, I wanted to be able to use different encoding.

The Codeunit name is “Fixed File Import” and just the name change show the difference.  My version is named “Fixed/Delimited File Import”.

There are settings in the Data Exchange that I wanted to be able to support.

FileDetails

The standard Codeunit only reads the MSDOS File Encoding with this code

Changed

to read the four different text encoding that are supported by the Data Exchange Setup.

The PharseLine section in the standard Codeunit has this simple loop to read the fixed file.

Extending this and adding two functions gives me the ability to import both fixed and variable text file.

More to come.  Stay tuned…

 

Inspired by a Microsoft Developer

Yes it happens.

When reading the C/AL code in NAV written by other developers you normally pick up smart way to do things.  Yesterday I got one.

In my solutions I have been using a function to check if a string is numeric and another function to extract the numeric value from a string.

IsNumeric

All good and well.  Working fine so far.  But if you can write each function with a single line, would that not be better ?

NewIsNumeric

Thanks for the inspiration Microsoft.

Conference season as an MVP

It is always a good feeling when the notification email from the Microsoft MVP Award is received.  Today I got mine and hence the third year as a Dynamics NAV MVP begins.  This year will start with the busy conference season.

On Monday, October the 5th I will do my first session on MVP Best Practices Track at Directions EMEA.  The same session will be repeated on Monday, October the 26th at Directions USA.  Joining me as a co-speaker for these sessions is Soren Klemensen.

Thinking NAV - Thinking Differently

The session title is Document Handling.  Of course the session will all be about NAV 2016.

We will not be showing the new application features, that part belongs to Microsoft.  However, most of the new NAV features introduced in NAV releases bring new and exciting technologies and we will be talking about how these new technologies can be used.

The demos we will show will all be about document handling.  Both electronic documents, XML or JSon and scanned or imported documents.  We will utilize the enhancements in the Data Exchange functionality and talk about how we see the new technologies used in best practices.

As a bonus for me I will be joining Mark Brummel in his session on Web Services.

Following Directions are Luc’s NAV Tech Days.  There I will join Soren for a full day workshop where coding best practices will be the topic.

This will all be shared on Dynamics.is and on Objects4NAV.com following the sessions and formal release of NAV 2016.

Looking forward to the next two months.