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!👍

One thought on “Send Attachments/Documents to Business Central with Power Automate 📎

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s