Using REST/Json web services from NAV

One of my most popular blog entry is the one about Json.  I have also had some questions outside this website about this topic.

This week I got a task.  We need to communicate with a payment service that uses REST web services and Json file format.

posapi

I got a document describing the service.  Some methods use GET and some use POST.  Here is how I did this.

In the heart of it all I use Codeunit 1297, “Http Web Request Mgt.”.

getaccesstoken

Every time we talk to this POS API we send an Access Token.  If we don’t have the token in memory (single instance Codeunit), we need to get a new one.  That is what the above code does.

The ParameterMgt Codeunit is what I want to focus on.  You can see that I start by inserting my “Authorization Key” into the RequestBodyBlob.  As usual, I use the TempBlob.Blob to get and set my unstructured data.

setapirequest

The interesting part here is that I use an XMLPort to create the data I need to post to the Api.

apiauthenticatexml

A simple one in this example, but nothing says it can’t be complex.  Then I convert the Xml to Json with a single function.

converttojson

The last TRUE variable means the the Document Element will be skipped and the Json will look like it is supposed to.

apikey

The REST service response is Json.

token

And to read the Json response we take a look at the GetAccessToken function.

getaccesstokenfunction

Here I start by converting from Json to Xml.

convertfromjson

And make sure my Document Element name is “posApi”.

apiaccesstokenxml

And I have the result.

As you can see from the documentation some of the Json data is more complex.  This method will work nevertheless.

For more complex date I always create tables that matches the Json structure.  These table I use temporary through the whole process so the don’t need to be licensed tables.  Here is an example where this XMLPORT

getauthorization

will read this Json

getauthorizationjson

I suggest that with our current NAV this is the easiest way to handle REST web services and Json.

 

NAVTechDays 2016

Now, ten days after a succesful NAV TechDays 2016, it is time to write a short recap.

NAV TechDays 2016

As usual I met with a lot of old friends and managed to gain new ones.  Had a great time.  I hosted a workshops on extending the Data Exchange Framework on Thuesday and Wednesday.  It looks like I am the only one in my MVP group to have dug deep into that functionality.  Huge thanks to the people who attended.  I always learn a lot from you guys.

On Thursday morning I had my first NAV TechDays session.  I talked about migrating to events.  I had a blast!

Luc has the video on Mibuso, so if you missed it or just want to relive the moment, go ahead and watch or download.

You can also watch the slides right here.  I have notes on most of my slides to better explain what I was thinking.

On Friday morning Soren presented Source Control.  There he talked about an initiative that Kamil and me started – to make Source Control available for every NAV developer.  Soren jumped on board and we can expect more to join.  On that note I have created my GitHub account where everyone can download the stuff that I publish.

I have published the presentations and objects to GitHub for everyone to play with.  What you download and do with it is on your own responsibility.

p.s. the NAV TechDays photo album is right here to browse through.

My first Dynamics 365 Extension – step by step – fourth step

I got the following response from Microsoft

Hello Gunnar,

We wanted to update you on your request to publish your app, G/L Source Names (Request ID# 835), on Microsoft AppSource. Based on our initial evaluation, your app has been moved to the Microsoft Dynamics NAV AppSource team for further evaluation. You will receive a follow up within 5-7 days or earlier with a status regarding your submission and next steps as relevant.

 Please let us know if you have any questions, or if there is anything we can do to help.

 Where you are in the process:

 appevaluationprocess

Request ID# 835

Thank you,
The Microsoft AppSource team

Soon after I got an Excel Template for my object request (microsoft-dynamics-365-for-financials-app-extension-questionnaire-and-object-range-request-form-october-2016).  Here posted for demonstration purposes, don’t use this for your actual process.

I did my object request and for this solution 50 objects is enough.

objectrequest

And this morning I got another email from Microsoft

Hi Gunnar,

50 objects have been assigned to “Kappi ehf.” (PSBC: 6433432) in the range: 70009200-70009249 for your app G/L Source Names.

You have most likely figured out by now that you need to be a Microsoft Partner.  Not a partner, jump to the partner website and sign up.

So, to PartnerSource Business Center to download a new development license.

licensekeyconfiguration

I like to start by downloading the “Permission Report Detailed (txt)” to verify that my objects are indeed in the license.

downloadkey

After verifying the below lines

I can download the license file and start my development process.  That process will be covered in later posts.

 

My first Dynamics 365 Extension – step by step – third step

I got a response from Microsoft from my first step.

Thank you for submitting your app for consideration to be listed on AppSource.

Once we have had the opportunity to review your app, you will receive a follow up within 1-2 weeks or earlier with a status regarding your submission and next steps as relevant.

Where you are in the process:

appsourceprocess

Request ID#: 835

Thank you,

The Microsoft AppSource team

This message from Microsoft is an important part of a program, service, or product that you or your company purchased or participate in.

From hereon I get out of sync with the steps on PartnerSource:

Step 3: Build your app

Find more information on how to build an extension in our learning plan on the Dynamics Learning Portal (DLP) (coming soon).

Refer to the Technical Validation Guidelines for Dynamics 365 for Financials (coming soon) to get insight into app technical requirements and recommendations.

To build the app I need to go through many steps.  You are building experience, not a solution.  Therefore we must make sure to have;

  • ToolTip for every field and every action
  • Correct value for ApplicationArea
  • Permission Set(s)
  • Friendly notifications
  • Assisted Setup
  • Help web page(s)
  • Help video(s)

I will go through this list in later blog posts.

Now for the main topic in this post.  Dynamics 365 requires all extension packages to be signed with a code signing certificate.  Microsoft says:

To sign an extension package

To help validate the authenticity of an extension package (the .navx file), we recommend that you have it signed. Code signing is a common practice for many applications. For more information about code signing, see Authenticode and Introduction to Code Signing in the MSDN Library.

  1. To sign an extension package, you need a computer that has the following:
    1. A code signing tool, such as SignTool or CodeSign.SignTool is part of the Windows Software Development Toolkit. For more information, see SignTool.
    2. Microsoft Dynamics NAV 2016 or later.
  2. Obtain a certificate that is enabled for the code signing purpose. You can have certificate as a file or installed in the certificate store of the computer.
  3. It is optional but we recommend that you use a time stamp when signing the .navx file.
  4. Sign the .navx file by using your signing tool.

So, if you want to publish to Microsoft AppSource get your self a valid code signing certificate.  There are number of vendors that can sell a code signing certificate.  Bing delivers this list.

I was allowed to quote one of Microsoft employees:

“I would only suggest using a self-signed certificate for testing and development purposes. If you plan to submit the extension as an app for Dynamics 365 for Financials, you will need to use a certificate from a trusted third party certification authority.

Every version of the extension doesn’t need to be signed using the same certificate. A new version could be signed with a different/new certificate.”

If you don’t already have the SignTool installed you can download it here for windows 7 and 8 and here for windows 10.  On Windows 10 all you need to select in the install process is the Windows Software Developement Kit.

sigtoolinsert

When the installation finishes you will find the SignTool.exe in the folders:

  • x86 -> C:\Program Files (x86)\Windows Kits\10\bin\x86
  • x64 -> C\Program Files (x86)\Windows Kits\10\bin\x64\

When I create the application package (navx file) I check for my code signing certificate and if not found I create one.  Now, of course this is done in PowerShell.

The first two lines are used to import modules and parameters from the AppPackageSettings file (below).  Lines four to nine create the application package (navx file), Lines eleven to seventeen check if the certificate selected in my AppPackageSettings is present, and if not it will be created in the certificate store and exported to a file.  For this to work this script has be executed in elevated mode (Run as Administrator).

Last line uses the SignTool to sign the application package (navx file).

My AppPackageSettings file looks like this

Don’t worry, I will share the whole script package when the time is right.

When I have the correct code signing certificate I update the two last lines in the settings file and point to my brand new certificate.

In next step we will start coding in C/AL – stay tuned.

 

My first Dynamics 365 Extension – step by step – second step

Now I need to be recognized as a developer.

Step 2: Complete Microsoft Development Center registration

Before you can publish your app to AppSource, you will need a Microsoft Development Center account.  A developer account lets you submit apps to Microsoft marketplaces, including the Windows Store, Office Store, Azure Marketplace, and Microsoft AppSource.

You can check to see if you already have access or register here:
https://developer.microsoft.com/en-us/store/register

NOTE:  You only need one developer account per company (not one per submission); a one-time registration fee applies.

This was a rather easy step, just needed to use my credit card.

I will go ahead and try the next step.  See you soon…

My first Dynamics 365 Extension – step by step – first step

You could say: “It is about time…”

And you would be right.  Finally, I am back to my blog.

My goal is to deliver my first app to Microsoft AppSource.  You can find a step by step instructions on PartnerSource.

I want to start with a very simple extension.  If you look closely at a general ledger entry in NAV and Dynamics 365 you will see that there are fields with information about the entry source.  For example, if you post a sales invoice then the customer will be the source.  If you post a purchase invoice then the vendor will be the source.  If you look at the balance account for a bank account you will see the actual bank account number as source.

My goal here is simple.  I want to add this information to the general ledger entry page.  Additionally I want to show the name for each customer, vendor or bank account.  After installation of this extension the user will be able to browse through his general ledger entries and see the source information for each entry.  Here is an example for CRONUS G/L Account No. 5610.

modifiedglentries

You can also see on the above screen shot that I have added a button to open the source card directly from the G/L Entry.

Today I will do the first step.

Step 1: Submit your app idea for pre-approval

We will review your app idea, provide any relevant feedback, and pre-approve.  Submit your idea here: Microsoft AppSource.

After pre-approval, you will receive a unique object range for your app along with additional information to guide you through the development, validation, and publishing of your app.

I will do my next blog post about step 2 and so forth…

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

 

Presenting the Data Exchange Framework

Earlier this month Arend-Jan contacted me about being a presenter on Dutch Dynamics Community NAV Event for March 2016.  Of course I was honored and after a moments thought I accepted.

The following presentation was repeated two times for close to 140 people in total.  I had a great time and am thankful for the opportunity.

I promised to share one Codeunit – that general mapping Codeunit – and here it is: Codeunit 60000.

If you download the presentation you can also read my notes.  They should help you better understand the whole story.