Microsoft Flow, Twitter and Dynamics NAV

Standard

As C/AL is my number one language to code, I wanted since last summer to give it a try to Microsoft Flow. And as Twitter is one of the top 3 applications I use on my phone I wanted to see if I can get an MS Flow bring the tweets in my favorite environment, Dynamics NAV.

After a few trials and tweaks my flow brings twits in NAV:

Tweet Table

If you want to try it out this is what you need:

  • a Dynamics NAV instance with public IP. I used an azure machine loaded with Dynamics NAV 2017
    • web services for entities you want to create or update via MS flow
  • a Microsoft or work account to connect to flow.microsoft.com
  • a Twitter account

To allow MS Flow to talk to both Twitter and MS NAV we need to set up appropriate Connections in MS Flow:

Conn

The connection to NAV looks like this:

NAVConn

For usename and password create a NAV user and set a password. On the instance you want to connect set a credential type = NAVUserPassword.

For twitter connection user your twitter user id.

To support the Flow, I needed 2 tables and 2 pages:

  • a table Tweet and a page Tweets (exposed as web service) to support the tweets
  • a table Last Tweet to record the last tweet id coming into NAV and a page to be able to update this last tweet id, so the flow does not collect again the same tweets but only new tweets published after the last tweet brought to NAV

NAV tables

And this is what the flow is doing:

  1. Start with a recurrence action:Recurrence
  2. Get the last tweet id by using the NAV connection:Search Tweets
  3. Set up the search for tweets action:Search Tweets
  4. Insert a new record in the NAV Tweet table by using a NAV connection and mapping twitter fields to NAV fields:Net NAVTweet
  5. Update “Last Tweet” table with the last action in the flow:UpdateLastTweet

And this is how the flow looks like:

WholeFlow

The C/AL code is included here.

Thanks for reading. Enjoy Flowing!

 

 

 

 

Advertisements

NAV Upgrade Notes – First essential step for a successful NAV upgrade

Standard

The first step in my last NAV upgrade was to back up customized tables. Although having database backups throughout your upgrade process is a no brainer, my focus in this post is backing up locally tables that contain non-standard fields. Having the heavy customized tables readily available in the same database, somewhere in the range 50000-99999 it can prove useful.

For example, let’s say the table 21 Customer Ledger Entry in my customer NAV 2009 database has a few non-standard fields in the range 50k or some other range. My plan is to have an exact copy of this table with ID 50000, table that I can carry forward until the upgrade process reaches NAV 2017.

Having the original data allows me to:

  •  re-construct any field that might have gotten lost or overriden during the upgrade (through a Forced sync without an upgrade codeunit or some other faulty process) or
  • perform conversion validation like comparing the balance for each customer in the original table(now saved in the 50k range) with the value from the upgraded table.

To backup the table I start with opening the original table (21), copy all fields, create a new table in the 50k range (60007) and paste all the fields. In the new table we don’t need any business rules, therefore we can select all code lines and delete them.

I would do the same for all tables whose data I want to back up.

If your backup set includes like mine, hundreds of tables, I would create a new table(50000) where I am planning to keep all original-backed up table pairs. For example, I can have pairs like table 21 – table 60007 in the new table:

List Of Tables

Next we need to populate the new table 60007 with original data.

Create a new codeunit and traverse table 50000. For each row execute the following function:

CopyData

This method copies the value of “Normal” fields from the source table(21) into each correspondent field from target table(60007).

How to use CAPTIONCLASSTRANSLATE in Dynamics NAV

Standard

Not long ago I stumbled upon a question on the popular NAV forum dynamicsuser.net. The question was “What is the use of CAPTIONCLASSTRANSLATE command in NAV?”.
There was only one answer coming from one of the most influential experts in the NAV world, Luc Van Vugt, but his answer intrigued me even more. As msdn was not very helpful, I started digging into this function use.

First, I wanted to see where is used in standard NAV 2017 in the Cronus database. To find all occurrences of CAPTIONCLASSTRANSLATE in standard NAV I used the Object Manager produced by idyn , a NAV AddOn for managing C/AL code. For my readers that do not have access to this development tool, export all objects to a text file and search in the file for “CAPTIONCLASSTRANSLATE”.

std-nav-use

First occurrence was in report 13, VAT register:

rep13

The report is using the command CAPTIONCLASSTRANSLATE to populate the value of a control(VATEntryClosedCaption) with the caption of “VAT Entry”.Closed field.

Similarly, CAPTIONCLASSTRANSLATE is used in report 22 “No. Series Check”, report 35 “Document Entries”, report 122 “Reminder – Test”, report 123 “Finance Charge Memo – Test”, report 1403 “Bank Account Register”, report 5900 “Service Order”.

A more intriguing way of using the command CAPTIONCLASSTRANSLATE is found in the other objects  as following:

employeelistreport

As we can see above, CAPTIONCLASSTRANSLATE receives a string parameter consisting of three sub-strings separated by two commas.

CAPTIONCLASSTRANSLATE(‘i,j,k’)

By investigating the code in Codeunit 42 “CaptionManagement” and Codeunit 57 “Document Totals” I could draw the following conclusions:

If i = 1 then CAPTIONCLASSTRANSLATE will output Dimension codes.

If j = 1 then CAPTIONCLASSTRANSLATE will output Global Dimensions code.

If k = 1 (i=1,j=1) then CAPTIONCLASSTRANSLATE will output “Global Dimension 1 Code”

If k = 2 (i=1,j=1) then CAPTIONCLASSTRANSLATE will output “Global Dimension 2 Code”

If i = 1 and j = 2 then CAPTIONCLASSTRANSLATE will output Shortcut Dimension Codes(for k=1..8).

dimensioncodes

For printing field captions that have (LCY) in caption use i = 101.

For example to display Amount(LCY) use CAPTIONCLASSTRANSLATE like below:

lcy

To display captions of amount fields that contain the text “Incl. VAT” or “Excl. VAT” use CAPTIONCLASSTRANSLATE with i =2 and j = 0 (to display Excl. VAT) or j = 1 (to display Incl. VAT) as in the examples below:

vat

All examples of CAPTIONCLASSTRANSLATE found in NAV standard were in reports and used to display captions in the table headers or group totals in report layouts.

 

 

 

NAV 2017: Wizard pages now supported in Web Client

Standard

As of October 2016, NAV 2017 is the latest version of the popular ERP system. To complement the brand new features, NAV team worked on enhancing current features.

A list of the features not supported in NAV 2016 is located here.  Among them, NavigatePage was not supported in NAV 2016 Web Client, but it is now supported in NAV 2017 Web Client.

Let’s delve into creating a simple (yet fit for starting your own) NavigatePage and check it in the Web Client. Additionally, I will suggest a few cases when a NavigatePage is a good candidate to collect data from users and perform some processing in one screen.

The NavigatePage assumes the existence of the following objects (included in the demo):

  • Table 90001 “Generic Entity” with the following fields:
    • field_1 (Code10)
    • field_2(Code10)
    • field_3(Code10)

PK: field_1,field_2,field_3

  • Table 90002 “Generic Entity Comment” with the following fields:
    • field_1 (Code10)
    • field_2(Code10)
    • field_3(Code10)
    • Entry No.(integer)
    • Comment(Text80)

PK: field_1,field_2,field_3,Entry No.

  • Page 90001 “Generic Entity Card”
    • An action to launch the NavigatePage will be located on this page

NavigatePage details:

navigatepage

And the actions:

navigatepageactions

Note: To make the actions appear as buttons on the toolbar and the groups as tabs set each action’s InFooterBar property to Yes.

This simple NavigatePage will collect data(the comment) from the user in Step1 and when the user clicks Next will insert a comment and make visible Step2.

When user clicks Next, the system will:

  • validate the fields in Step1
  • Hide the fields in Step1 group
  • Perform Wizard action (generate the comment)
  • Show Step2 group

When the user clicks on Finish the page closes.

A few snapshots of the NavigatePage as it appears on the Web Client:

genericentitycardpage

Launching Comment – Wizard action:

genericentitycommentwizzard

Click on Next will move the wizard into Step 2:

wizzardsummarystep

Standard NAV has a few NavigatePage pages. Check them out:

  • 5077 -> Create Interact
  • 5097 -> Create To-do
  • 5126 -> Create opportunity

If you have processes in which the users need to go through a few pages to enter data and create different entities, then NavigatePages are a great candidate for improving user efficiency and experience.

I used recently NavigatePages in two instances. Once, when I needed an unified screen to allow users to attach files (word docs, excel files, pdfs) in a Property Management AddOn. The documents were collected and attached to an email sent to the owner at month-end.

Another example was more recent when, with one NavigatePage, I was able to create records similar to a fixed asset, collect mandatory data for a purchase invoice and generate the purchase invoice for that record. Of course you need methods that will take care of generating the purchase invoice, its lines, general ledgers, vendor ledgers, dimensions. Moreover, assertiveness is needed to not end up with half-performed processes … But it’s possible to run your processes through a wizard page with an overall improved user experience and efficiency.

Sample code available here.