Dynamics NAV with the Microsoft Flow SQL OnPremise Gateway

Soo… Microsoft pulled the Dynamics NAV Connector for Flow, that was not a win-win scenario for anybody, not a least customers who was using it or planing to use it. There are ways we can use the old connector, but certainly not optimal ways.

So I started looking at the SQL Server connector, it has a bonus feature, it works with the Flow On-Premise Gateway. The Gateway is a “proxy” of a sort that allows you to connect to Flow without exposing an OData endpoint to the internet.

 

First you must download and install the Gateway. Following the instructions here.

The gateway name is important if you never install more than one. And the recovery key is important for reinstall without having to redo all your flows.

 

When you’re done, you should have a gateway running like this:

Then we’re all done On-Premise, go to flow.microsoft.com and let’s create a new Flow. Select “My Flows” and click:

Search for triggers and find the SQL Server – WHen an item is created or modified

First, create the Connector:

The important part is the very first check box: Connect via on-premise data gateway, when checked a new option appears:

Select your gateway (Mine was called FF9 when I installed my gateway) and fill out the rest of the field as if you’re on-premise (witch you are, through the gateway).

Then you must select a table, this can be a bit tricky, because not all your tables will show up and there are tables for each company,

You can just use the Custom value option:

Type Company Name$Table Name, like this:

And click + New Step

We’ll just add a simple action for now:

Save and test, that’s it 🙂

There are a few things to consider when using the SQL connector:

1. Best for triggers, if you want to write data, use the normal Dynamics NAV/BC connector (that does not have triggers)
2. No access to flow fields, so no easy way of showing Balance on customers or other flowfields.
3. Field names with spaces, if you need to do OData filtering ($filter) on field names with spaces in it, you have to replace spaces with _x0020_, so “Name 2” becomes “Name_x0020_2”