Send Attachments/Documents to Business Central with Power Automate ๐Ÿ“Ž

“We have API’s now no need for physical copies!”

A while back I did a post on the attachments API for BC: https://joshanglesea.wordpress.com/2021/01/25/business-central-attachmentsapi/

I only talked about using it with Postman or another REST client of your choosing. What about using it with Power Automate instead? I will caveat the fact I hit a tonne of errors trying to get this to work with the latest API pages at the time of writing, v2.0. But we are all about the working version in this post!

Let me set the scene on how I have approached this topic. I want to take an email with an attachment – which is not an image and have it create a purchase invoice for the vendor emailing me. The purchase invoice will have a record of the attached document from the email. I will also store the attachment in OneDrive – could be deleted at the end if you wish.

My starting point is with this trigger. A shared mailbox is possible too.

I’ll break this post into chunks because the flow has a few steps to it. Challenge 1 – the vendor to create the invoice for. In my case I wanted to have a level of dynamism to how the document in BC knows what Vendor/Customer to use. In your case you might not find this possible and have to revert to a placeholder record. I’ve opted to take the first 3 characters of the email domain – for example if the e-mail address is Josh.Anglesea@somecompany.com the filter I will use is “Som” – yes we convert the 1st character to uppercase too. A fair few actions for this first challenge so check the image gallery and the embedded code.

I found this post helpful to solve this challenge: https://powerusers.microsoft.com/t5/Building-Flows/extract-s-string-between-two-characters/td-p/80830

//Index_of_@ 
add(int(indexOf(outputs('Email_From'),'@')),1)

//Index_of_.co
int(indexOf(outputs('Email_From'), '.co'))

//Vendor_Name_position
sub(outputs('Index_of_.co'),outputs('Index_of_@'))

//Vendor_Name_Filter_Value
substring(outputs('Email_From'), outputs('Index_of_@'),outputs('Vendor_Name_position'))

//Filter_in_Proper_Case
concat(toUpper(substring(outputs('Vendor_Name_Filter_Value'),0,1)),substring(outputs('Vendor_Name_Filter_Value'),1,2))

Challenge 2 – is avoiding pesky extra attachments on the e-mail. Because we are pulling this directly from an e-mail you can get all the images from the e-mail signature too ๐Ÿ™„ which means we will need to loop through until we get the typical file type we’re expecting. In my case I’ve said anything which is not a .png – change yours as you need to.

To be able to assess what the file type is we need to save it somewhere first. OneDrive was nice and easy here and the “Create File” action passes back the full file name. Simply a case of adding in a condition to check the property. You could have multiples here. You will then get two lanes – what happens if the file does not end with “.png” and what happens if the file does end with “.png”

Challenge 3 – get the file content from OneDrive and add it to BC. Yes this is the main part of the blog but it’s a challenge mainly because the BC connector for Power Automate does not play ball โšพ Instead I have used the HTTP connector. You will need to use this post to help with the authentication part of using the HTTP connector: https://joshanglesea.wordpress.com/2022/03/01/business-central-oauth2-for-power-automate-%f0%9f%9b%82/

I’ll use a gallery of images with comments to explain each of the subsequent steps

//Expression to get the first vendor found in BC with the earlier created filter:
outputs('Find_Vendor_to_Invoice')?['body/value']?[0]?['number']

//Parse JSON Schema for the POST action response
{
    "type": "object",
    "properties": {
        "@@odata.context": {
            "type": "string"
        },
        "@@odata.etag": {
            "type": "string"
        },
        "parentId": {
            "type": "string"
        },
        "id": {
            "type": "string"
        },
        "fileName": {
            "type": "string"
        },
        "byteSize": {
            "type": "integer"
        },
        "content@odata.mediaEditLink": {
            "type": "string"
        },
        "content@odata.mediaReadLink": {
            "type": "string"
        },
        "lastModifiedDateTime": {
            "type": "string"
        }
    }
}
I decided to wrap things up by deleting the file from OneDrive now that it will exist in BC too. Are you reading this and thinking “Hey! I got the standard BC connector to work for this!”. Please get in touch if so, or get the steps shared!๐Ÿ‘

Business Central OAuth2 for Power Automate ๐Ÿ›‚

Generally when writing a post I quickly explain the scenario and get straight to the solution. I need to break that rule here to avoid confusion. As the title suggests this post is about using OAuth2 with Power Automate – applies also to Azure Logic Apps. However, if you have Power Automate flows using the BC connector you are already covered. If you have custom connectors for BC data which use OAuth2 for authentication. You are also covered. So whatโ€™s the point of this blog you ask? If you use the HTTP connector in your flows to interact with BC data – you have opened the right blog post ๐Ÿ‘๐Ÿผ. Why does this post matter? Well at the date of writing, 01/03/22, basic authentication for BC web services is being deprecated (https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/upgrade/deprecated-features-platform). OAuth2 is the replacement. If you can move away from using the HTTP connector then please do. That way you can just natively pickup the correct authentication. I say there are two main reasons you wouldnโ€™t move things to the BC connector. If you have published a codeunit as a web service or you use query objects (which aren’t the API type). Lesser reasons might be that you donโ€™t have development resource or want to avoid cost of refactoring. After all Power BI get to use legacy web services so Power Automate should get to as well right? For PowerApps you need to use a custom connector or the BC connector. You might have Power Automate flows which run from PowerApps. In which case if they use the HTTP connector, same explanation as above. Woah! ๐Ÿ˜ฎ that was a wordy intro. If youโ€™re still with me letโ€™s get to the good bitโ€ฆ

I will admit early on I only came across my answer thanks to some brilliant blogs! I highly recommend reviewing Arend Janโ€™s posts in particular if you have little background on working/setting up OAuth2 for BC. Links:

The solution is to have a standalone flow which will retrieve the access token. You will use this flow as a child to you main PA flow. This pattern will work well as you don’t have to alter your existing PA flows using the HTTP connector that much. Let’s get the child flow out the way first as that’s the longer part.

Here is the text string from the compose action mentioned in the gallery of images. Replace the your_client_ID and your_client_secret parts to form the correct string:

concat('grant_type=',encodeUriComponent('client_credentials'),'&scope=',encodeUriComponent('https://api.businesscentral.dynamics.com/.default'),'&client_id=',encodeUriComponent('your_client_ID'),'&client_secret=',encodeUriComponent('your_client_secret'))

Now we have the child flow up and running let’s plumb it in to a parent flow ๐Ÿง‘โ€๐Ÿ”ง I will go through each step with images again.

It’s a fairly elegant way to slot in what is needed for the PA flows this might apply to. There are a number of my own previous blog posts which need this solution – like this one: https://joshanglesea.wordpress.com/2020/10/19/business-central-month-end/

To make life easy I’ve created a template of the child flow which you could import into your environment. There are setups that need dealing with before it will work though: https://github.com/JAng13sea/Blogs/tree/master/Template%20Get%20Access%20Token%20PA%20Flow

Business Central Multi Journal Line Dimension Update ๐Ÿ”ƒ

In Business Central we have a few areas where working with dimension updates takes place for many records at a time. For instance, when assigning default dimensions there is a method to apply the same values to many records. Or when working on a document, like a purchase order, you can set the dimensions on the header and BC asks to pass them to the lines. With a journal line though it is very much an individual way of working and a user relies heavily on default dimensions values being inherited from related records. Granted edit-in-excel can be seen as the remedy here as you can make updates to the shortcut dimension columns on mass. What about when it’s not a shortcut dimension? Or the user doesn’t have access to edit-in-excel (no O365 or the add-in is blocked)?

As the gif loops through notice that all dimensions can be chosen despite whether they are on the page or not (shortcut dimensions). If the line already has previous dimension values it keeps them and the new one’s are added.

The Selection page is based on the dimension set entry table so it handles the errors for you. For example if a user tries to add the same Dimension Code twice:

For the purpose of a compare and contrast to edit-in-Excel the user does not get the captions of the dimensions and no lookup. Not attempting to have a versus battle with edit-in-Excel, just stating the idea may have some merit.
Feels like it nicely compliments the “Journal Check” functionality by letting you action this for more than one line at a time too.

And what about if you update a default dimension and don’t want to add the Account No. again to validate the data. In my example the “DEPARTMENT” has been made code mandatory for this G/L Account:

Performing a test with 5000 journal lines proved to be faster than edit-in-Excel by some distance. I clocked about 3.5 mins to update just one dimension code. Which is completely fair given it is done outside of BC and then pushed back. Doing the same with my idea takes considerably less time for that volume of lines:

It is something I logged as an idea a while back but wasn’t sure how to go about creating for myself: Microsoft Idea  ยท Add dimension values to multiple journal lines at the same time (dynamics.com)

Give it a try for yourself or vote for the idea (if you like it ๐Ÿ™ˆ) in the hope it could be part of the standard product ๐Ÿ‘

https://github.com/JAng13sea/Blogs/tree/master/MultiDimLines

Business Central 2020 Wave 2 Enhanced Email Capabilities – How to setup

If you read over the latest changes shipped with version 17.1 you would have seen the enhanced email capabilities mentioned. This can be enabled early by checking out the feature management page:

Just doing this though doesn’t mean you can start to enjoy the new features straight away, there is some setup to handle. A lot of the required setup is described very well on the docs.microsoft.com site: https://docs.microsoft.com/en-us/dynamics365/business-central/admin-how-setup-email

However, that doesn’t always have all the steps in enough detail. The end goal of this functionality is to have dedicated email accounts for specific scenarios that need emailing from the system. Here is a configured list to illustrate where we want to end up:

All of the documents shown will now have the “From” address of accounts. This means a contact is more likely to reply directly to this group email rather than a direct contact ๐Ÿ‘

Before getting started in Business Central go to the Office 365 admin centre and add shared email accounts for each of the scenarios that can be covered. Out of the box this includes documents based on sales, purchase, service and internal messages like approvals notifications (can be extended to cover more – save that for another time). These type of mailboxes therefore make sense:

The majority of the connection setup is done through a wizard where you either pick the current user account so you can send emails as your logged in BC user or choose one of the above shared mailboxes. The current user method is very simple so I will cover the shared mailbox

Search for “Set up Email” and cycle through the wizard page to the one shown (page 2), choose the shared mailboxes option. Note that if you’re not a user with the SUPER permission assigned you will need a new permission set called EMAIL SETUP which is available in the main list.
Add the details of one of the shared mailboxes from the Office 365 admin centre. Finish the wizard and test the connection. Repeat this step for the other accounts that you need.
Once you have added all the accounts if you search for “Email Accounts” you can see all of the available ones to work with. Note that the legacy SMTP connection will be created for you already and it will be the “default”. There is an action to reassign the default available from this page as well as testing the accounts or composing a none document related email with that account.
The next area of interest in this page is under the “Navigate” tab where you can see email activity with that account from BC and which “Email Scenarios” are assigned to the email account.
Simply choose the documents you want to be sent from that email account
From that point onwards your chosen account will be used for that document. Any previous email dialog screens have been replaced with this one. If you have email layouts setup they will work too.
On the role centre you will notice a new activity cue. The draft cues means that users are asked to save as a draft or to discard the email – much like a regular email experience.

I’ve configured this in two separate tenants thus far (as of 09/12/20) and the experience has been fairly slick. The only issue I experienced was to do with an Exchange configuration where no Office 365 connector was in place. If you are repeatedly given a message in BC about not being able to access the “Set up Email” page jump over to the Exchange admin centre and see the “Connectors” which currently lives under the “Mail Flow” area:

Users are able to search for “Compose an Email” to access the email editor with no document scenario assignment:

Would have been nice if the “Attach File” option let you search documents stored in BC but that is currently not the case. Right now that will let you choose a file from a locally accessible location.

A much richer experience overall. Look forward to seeing what others do to enhance this.

PowerApps biz card reader to Business Central

This one comes with a premium as the PowerApps business card reader isn’t included in the license for BC. However, it’s a really good use of AI and something that could be utilised in the right scenario. Ironic that I’m blogging about something that won’t be getting much use right now. I haven’t acquired a new business card since March 2020. However, it feels as though the exchange of fancy pieces of card might be behind us and scanning something is a cleaner option, in more ways than one ๐Ÿ˜Š

A number of other online resources can explain the PowerApps build up so I will highlight the important parts. The goal here will be to take an image of a business card and have the details from the AI model sent to BC so a company or person contact can be created.

Add the business card reader from the AI builder and then add text input boxes which reference the properties of the business card reader. Use text input boxes as the data from the reader won’t always be perfect so some editing might be needed.

As you can see here the Company Name doesn’t quite work out so some manual adjustment is needed.

The next thing is to have the data passed and to do this I’ve used Power Automate. A button will be created in PowerApps that calls the flow that is needed. A few steps to the flow so I’ll break it into sections. Use a PowerApps trigger as the starting point:

In PowerApps we will build a JSON so add a Parse JSON and request the content from PowerApps. Note that it will most likely change the name as mine is above. A sample schema is needed:

{    “type”: “array”,    “items”: {        “type”: “object”,        “properties”: {            “Address1”: {                “type”: “string”            },            “AddressCity”: {                “type”: “string”            },            “CompanyName”: {                “type”: “string”            },            “Country”: {                “type”: “string”            },            “Email”: {                “type”: “string”            },            “FirstName”: {                “type”: “string”            },            “JobTitle”: {                “type”: “string”            },            “LastName”: {                “type”: “string”            },            “Mobile”: {                “type”: “string”            },            “OfficePhone”: {                “type”: “string”            },            “PostCode”: {                “type”: “string”            },            “Website”: {                “type”: “string”            }        },        “required”: [            “Address1”,            “AddressCity”,            “CompanyName”,            “Country”,            “Email”,            “FirstName”,            “JobTitle”,            “LastName”,            “Mobile”,            “OfficePhone”,            “PostCode”,            “Website”        ]    }}

Next up an “Apply to each” section is required where the body of the JSON is used. In our case there will be one value at a time but a JSON can handle multiples. A series of HTTP triggers will follow along:

  1. A GET to determine if the “Company Name” value can be found in BC already. For this to happen I have published page 5050 as a web service and particular ODATA filtering is needed ?$filter=Type%20eq%20%27Company%27%20and%20Company_Name%20eq%20%27′<CompanyName>’%27
  2. A POST for a Company Contact for the occasions where this detail isn’t available yet. The displayed “Headers” will be needed and the “Body” will be made up of a mix of static values and those from the PowerApps JSON:

3. A final POST to handle the creation of a person contact. This will be repeated so use the “Copy to Clipboard” feature to save time having to type it out again.

A “Condition” has been added off the back of using the GET command and the returned “Body” is checked to see if the Company Name from the PowerApps JSON exists in the JSON of the GET Company HTTP. This will result in the “Yes” command or the “No” commands taking place. Once this is saved then it is ready for hooking up to PowerApps.

Create a new button in PowerApps and use the “Action” part of the ribbon to call on Power Automate. Choose the flow that was devised from the earlier steps. This will add one line to the formula area of PowerApps. Use the shift key and enter to add some additional lines above that inserted line. Some earlier steps are needed before the detail for Power Automate is ready. I’ll break this into sections too:

A Collection will be built up of all the values from the Biz card reader. Refer to the online documentation to understand the PowerApps formulas further:

ClearCollect(BizCardContact,{CompanyName: CompanyName.Text,FirstName:Concatenate(firstname.Text,” “,Lastname.Text),LastName: Lastname.Text,Email:Email.Text,Address1: BusinessCardReader1.AddressStreet,AddressCity: BusinessCardReader1.AddressCity,PostCode: BusinessCardReader1.AddressPostalCode, Country: BusinessCardReader1.AddressCountry,JobTitle: JobTitle.Text,OfficePhone: OfficePhone.Text,Mobile:MobilePhone.Text,Website: Website.Text});

A JSON is required in Power Automate and it can be built in PowerApps using the collection from the formula above:

Set(ContactJSON,JSON(BizCardContact));

Last thing is to pass the JSON to Power Automate:

‘Copyof-BizCardReadertoBC’.Run(ContactJSON)

In full flight you will get one of two results, two contacts or just a person contact. Note the green ticks in the top right of each window to show what has been executed:

Business Central Gen. Jrnl. Imports with Data Exchange Definitions

Have you seen this button on the general journal page and thought that’s useful?

Some of you might not have seen it though as the button only appears if you complete some setup in the General Ledger Setup page:

The above field shows a list of Data Exchange Definitions for the type of “Payroll Import”. So it is fit for purpose but only usable for payroll – and more importantly just one import file structure. For demos I have often mocked up a Data Exchange Definition as a payroll import type and just passed over a CSV file. This falls into my one of many ways a journal can have external data added to it. This is fine for one off demos sessions but how can this idea be taken seriously in a project/production scenario? The answer lies in how is the current “Payroll Import” button working and can we change it for our situation? My end goal here would be to have the user pick from a range of different data exchange definitions which work for different CSV files. In the past customers may of had bespoke import routines passing data through the excel buffer. If they can compromise on having the file converted to CSV this could be a worthwhile solution!

Over in the base app the “Payroll Import” action button looks like this:

So it is only ever looking at that setup page to know which data exchange definition to use. It also has it’s own codeunit to run the process. So the component parts are small and we can adjust them with ease for our end goal.

Very simply a page extension is needed for the general journal and you can go with something like below to add the type of page action we want. In my case I have opted to use the “Generic Import” type on the data exchange definition – which I have set as a global variable on the page.

Codeunit wise I have done a complete copy of the base app and then renamed the function names. Very easy indeed. The process itself works fine so no need to change anything it is more about what you offer the user when they make that button selection.

The end result is a dynamic journal import selection. The user can now maintain and update a data exchange definition or add new one’s as new journal import requirements come along! ๐Ÿ‘๐Ÿ˜

Don’t know much about data exchange definitions? Check this out: https://docs.microsoft.com/en-gb/dynamics365/business-central/across-how-to-set-up-data-exchange-definitions

Code available here: https://github.com/JAng13sea/DED-Jrnl-Import