Trigger a Power Automate Flow from Business Central for a Selected Record

Intro

This is my first stop on the Power Platform learning train. As I mentioned in the first post, this seems like a significant moment to me. Over the last few versions users have had more control over the web client. Between the Personalise and Design options you can tweak a lot of the elements on a page.

If you wanted to introduce some logic you could do that through Power Automate. Trigger a flow in response to some event in Business Central, call some other service, post some data back to BC. Lovely, but all a little hidden away from the Business Central UI (with the exception of approvals which remains a confusing hybrid of Power Automate and old Business Central workflow). If you wanted to add some Power Automate into the mix and make it prominent in the UI you still needed to do some AL development.

Not any more.

Automate Menu

You might have noticed that there is an Automate menu pretty much everywhere now.

Let’s try clicking on Create a flow, see what happens. Power Automate is opened to a new flow with the Business Central trigger, “For a selected record (v3)”

You can see that there are few options for the trigger:

Environment: either specify a single environment that this flow can be used in or leave blank to appear in all envirnments
Company: specify a single company or leave blank for all
Page or table: to determine which page(s) the flow can be triggered from
Add some UI

Example: Translate Item Description

I want to add a flow to get the description of an item, translate into another language and update the record with the translation. I want the user to be able to choose the language that they want to translate into.

This is what the flow looks like:

I’ve added a text input to the trigger to allow the user to enter the language that they want to translate to. I’ve also specified that this flow applies to “TABLE27” – so the item list and item card pages.

From there on:

Get the item record (the “for a selected record” trigger outputs the SystemId of the record that the flow was run from)
Use a connection to Microsoft Translator (a free, throttled connection). Translate the item displayName (description) into the language entered by the user (the Language UI from the trigger is included as an output)
Update the item record with the output of the translation

Let’s test it with the “Facia Panel with display” item. I’ll enter “ja” for Japanese as the target language for translation. (You can also define a list of valid values for the user to choose from rather than having free entry).

Notes

Use the Trigger Outputs

If you want to be able to use this flow in any environment and company don’t hardcoded those values in the get/update record actions. Use the output from the trigger instead.

Constructing PATCH Request

I’m constructing the request for the update record action with this expression. This is the part that seems the least low-code to me, but maybe there is an easier way to achieve this.

json(concat('{"displayName": "', outputs('Translate_item_description')?['body'], '"}'))

Captions

The caption for the action is taken from the name of the flow.

The captions for the UI are set in the flow.

As an ISV we are always conscious of the languages that we need to support and captions that we need to translate. Unfortunately I couldn’t find anything about providing translations for these captions.

We do have the user id and name of the user that triggered the flow, so perhaps it is possible to retrieve the language of that user and adapt the UI but it definitely doesn’t look like something a consultant or citizen developer is going to do.

Personalise

When you personalise the page you can move the actions from the Automate action like other actions. I’ve dragged it into Home group here. Doesn’t look like you can choose an image for the promoted action though.

Thoughts

OK, so this is a trivial example, but even so I am impressed. You can add an action to specific pages, across all environments and their companies, with some UI, to integrate with external services without writing any AL code.

Power Automate handles most of the complexity of connecting to and authenticating with an external service for you.

For real use cases you might be more dependent on doing some API development in Business Central first to expose the correct data and bound actions, but that division makes sense to me. Handle the Business Central stuff in Business Central and handle the integration with other services in Power Platform.

How does that all hang together? How do you deploy an entire solution when that solution consists of an AL app, some Power Automate flows, maybe a Power App? What about source control? How do you manage credentials to external services when you deploy into another tenant? All good questions that I don’t have any answers to yet.

Getting Onboard with Power Platform as a Business Central Developer

Intro

One of the things that I wanted to come away with from Directions EMEA last week was a better overview of the Power Platform. What is it? What can we do with it? When should we use it? How does it fit into the overall solutions that we are designing for our Business Central customers?

If you’re anything like me then you’ve been aware that you need to get on the Power Platform train at some point – but you haven’t been quite sure how or when. You’ve been watching suspiciously from the platform wondering where this train even goes, how it gets there and how much a ticket costs. On closer inspection, it looks like the train is already full of consultants and citizen developers digitally transforming each other…*shudder*

Still, we can’t put it off forever. If Microsoft are to be believed then the vast majority of customisations in the future are going to be developed in low-code platforms.

Good news for me was that there was more Power Platform content at Directions than you could shake two sticks at. I still have some questions about how to manage Power Platform development as part of our Business Central solutions, but I’ve come away more convinced that it is going to be an important part in the future and an inclination to get on board.

Series

I haven’t been blogging for a while because, life, but I thought it might be interesting to blog my way through my Power Platform learning curve. Hopefully you’ll find something useful but at the very least you’ll be able to laugh at my fumbling attempts to make sense of it all.

Flow from Business Central

I’m going to start with something that was demoed by Microsoft in one of the Directions keynotes that struck me as very significant. We can trigger a Power Automate flow from an action on a page in Business Central.

But couldn’t we do that before? Yes, we’ve been able to trigger a flow with an HTTP trigger and call the URL from an action on a page, for example. What’s different now is that you can add a new action to specific tables and/or pages in Business Central, with some UI, to trigger a flow for a given record without any AL development.

Maybe you’re thrilled by the possibilities that this opens up. Finally you are not so dependent on developers to get stuff done. Or maybe you are horrified that anyone can add an action to a page without building an extension, adding the code to source control, running a pipeline or any tests.

I’ll show some examples and explore the possibilities next time…

Execute JavaScript with WebPageViewer for Business Central

TL;DR

The WebPageViewer add-on has an overload to accept some JavaScript. You can use that to execute arbitrary script locally. WebPageViewer.SetContent(HTML: Text; JavaScript: Text);

JSON Formatting

This post starts with me wanting to format some JSON with line breaks for the user to read. It’s the response from an Azure Function which integrates with a local SQL server (interesting subject, maybe for another time). The result from SQL server is serialized into a (potentially very long) JSON string and this is the string that I want to present in a more human-readable format.

Sometimes I converge on a solution through a series of ideas, each of which slightly less bad than the previous. This was one of those times. If you don’t care about the train of thought then the solution I settled on was to use the JavaScript parameter of the WebPageViewer’s SetContent method.

If you’re still here then here are the stations that the train of thought pulled into, starting with the worst.

Requirement

Have some control on my page for the user to view the JSON returned from the Azure Function, formatted with line breaks.

1. Format at Source

Why not just add the line breaks when I am serializing the results in the C# of my Azure Functions? That way I don’t need to change anything in AL.

No, that’s dumb. That would make every response from the function larger than it needs to be just for the rare occasions when a human might want to read it. Don’t do that.

2. Call an Azure Function to Format the Result

I could have a second Azure Function to accept the unformatted result and return the formatted version. I could have a Function App which runs node.js and return the result in a couple of lines of code.

Wait, that’s absurd. Call another Azure Function just to execute two lines of JavaScript? And store the Uri for that function somewhere? In a setup table? Hard-coded? In a key vault? Seems somewhat over-engineered.

3. Create a User Control

Hang on. I’m being thick. We can execute whatever JavaScript we want in a user control. I can create a control with a textarea, or just a div, create a function to accept the unformatted JSON, format it and set the content of the div. No need to send the JSON outside of BC.

Closer, and if you want more control over how the JSON looks on screen probably the best bet. But, is it really necessary to create a user control just to execute some JavaScript? Still seems like too much work for what is only a very simple problem.

4. Use WebPageViewer

The WebPageViewer has a SetContent method (which I’ve written about before) which can accept HTML and JavaScript.

If you pass some script it will be executed when the page control is loaded. Perfect for what I need. I can just use the JSON.parse and JSON.stringify functions to read and then re-format my JSON text. I’m also wrapping it in pre tags and removing any single quotes in the text to format (because they will screw the JavaScript and I can’t be bothered to handle them properly).

The AL code ends up looks like this:

local procedure SetResult(NewResult: Text)
var
    JS: Text;
begin
    NewResult := NewResult.Replace('''', '');
    JS := StrSubstNo('document.write(''<pre>'' + JSON.stringify(JSON.parse(''%1''), '''', 2) + ''</pre>'');', NewResult);
    CurrPage.ResultsCtrl.SetContent('', JS);
end;

If you’re not using 26 single quotes in three lines of code then you’re not doing it right 😉

Tip: Editing RapidStart Configuration Package Files

TL;DR

Extract the package with 7-Zip
Open the extracted file in a VS Code / Notepad++ / text-editor-of-choice
Edit the xml as required
Use 7-Zip to compress in gzip format

Editing Config Packages

Sometimes you might want to edit a config package file without having to import and export a modified copy from BC. In my case I wanted to remove the Social Listening Setup table from the package. Microsoft have made this table obsolete and BC throws an error if I try to import the package with this table present. (Probably not a bad idea – stopping listening to socials).

Fortunately, a rapidstart file is just a compressed xml file. Extract the rapidstart file with 7-Zip and then open the extracted file in a text editor. The format of the file is pretty straight forward. Each table is represented with an XYZList node where XYZ is the name of the table which the table-level settings followed by one or more XYZ nodes with the data.

Here are two records for the Payment Terms table.

<PaymentTermsList>
  <TableID>3</TableID>
  <PageID>4</PageID>
  <SkipTableTriggers>1</SkipTableTriggers>
  <PaymentTerms>
    <Code PrimaryKey="1" ProcessingOrder="1">10 DAYS</Code>
    <DueDateCalculation ProcessingOrder="2">&lt;10D&gt;</DueDateCalculation>
    <DiscountDateCalculation ProcessingOrder="3">
    </DiscountDateCalculation>
    <Discount ProcessingOrder="4">0</Discount>
    <Description ProcessingOrder="5">Net 10 days</Description>
    <CalcPmtDisconCrMemos ProcessingOrder="6">0</CalcPmtDisconCrMemos>
    <LastModifiedDateTime ProcessingOrder="7">
    </LastModifiedDateTime>
    <Id ProcessingOrder="8">{6BD87497-B233-EB11-8E89-E8FD151D8C93}</Id>
  </PaymentTerms>
  <PaymentTerms>
    <Code>14 DAYS</Code>
    <DueDateCalculation>&lt;14D&gt;</DueDateCalculation>
    <DiscountDateCalculation>
    </DiscountDateCalculation>
    <Discount>0</Discount>
    <Description>Net 14 days</Description>
    <CalcPmtDisconCrMemos>0</CalcPmtDisconCrMemos>
    <LastModifiedDateTime>
    </LastModifiedDateTime>
    <Id>{6DD87497-B233-EB11-8E89-E8FD151D8C93}</Id>
  </PaymentTerms>
</PaymentTermsList>

All I need to do is find the offending Social Listening Setup node in my file and remove it. Here it is:

Once you are finished editing you can use 7-Zip to compress the file again with the gzip method and import.

Tip: Test for Tables Missing from Permission Sets

In PowerShell:

$tablesInPermissionSets = @()

$permissionSets = gci . -Recurse -Filter '*.al' | ? {(gc $_.FullName).Item(0).startsWith('permissionset')}
 $permissionSets | % {
    $content = gc $_.FullName -Raw
    [Regex]::Matches($content, '(?<=tabledata ).*(?= =)') | % {
        $tablesInPermissionSets += $_.Value
    }
 }

$tablesInTables = @()

$tables = gci . -Recurse -Filter '*.al' | Where-Object {(Get-Content $_.FullName).Item(0).StartsWith('table ')}
 $tables | % {
    $content = gc $_.FullName -Raw
    [Regex]::Matches($content, "(?<=table \d+ ).*(?=$([Environment]::NewLine))") | % {
        $tablesInTables += $_.Value
    }
 }

$missingTables = ""

Compare-Object $tablesInTables $tablesInPermissionSets | ? SideIndicator -eq '<=' | % {
    $missingTables += $_.InputObject + [Environment]::NewLine
}

if ('' -ne $missingTables) {
    throw "Missing table permissions: $missingTables"
}

In English:

Find all the files in the current folder, and child folders, with a filename ending in .al and which have a first line starting with “permissionset”
Build a collection of the tabledata objects that are referenced in those permission sets
Find all the files in the current folder, and child folders, with a filename ending in .al and which have a first line starting with “table ” (with a space to avoid matching “tableextension”)
Build a collection of the names of the tables
Use Compare-Object to compare the collections and find names which appear in the list of tables but not in tabledata permissions
Build an error message of missing table permissions
Throw the error

PowerShell Profile:

Like most small PowerShell scripts that I write, I’ve just added it to my PowerShell profile. Run code $profile in a PowerShell prompt to open the profile file in VS Code.

function Test-Permissions() {
  #...all of the above code
}

Maybe there is already a VS Code extension that checks for this? It would make sense, but I’m pretty minimalist with the extensions that I have installed anyway. I run it from the terminal in VS Code.