Business Central Data Dictionary ๐Ÿ“–

I’ve been asked about this topic a few times recently, by colleagues and customers – where do I get a Business Central data dictionary or database schema from? This is a very brief post on how I tackled this. My default answer has typically been Jet Reports because the sample reports come with one. You don’t need Jet Reports to have access to it. Just go to the website and download it – https://insightsoftware.com/sample-reports/data-dictionary/. It is saved in such a way that it is just a plain Excel file, and looks much like this:

This could fulfill your needs perfectly well, however you can’t refresh it, unless you have Jet Reports. What if you have custom tables or tables from appsource extensions? Is there another free method to get hold of the database tables and fields? For this we will use a Power BI report. Big caveat is that we will need to customise the system ever so slightly. There are two ways you can go here. Either create API pages (best practice) or create standard list page objects. I did both and oddly had much faster results from the none API pages – weird ๐Ÿ˜–. To the extent I built my below sample from the standard list pages:

Something along these lines could be the final result. The PBIX I have created is also available to try out if you don’t want to build the pages or use the Jet Report suggestion.
I have taken complete inspiration from the Jet Report example and created pages in the report based on theirs.
This is just a small subset of what could be generated from the these two system tables.

Code for the two pages (in both styles) and the PBIX I took the screen grabs from are available on GitHub here: https://github.com/JAng13sea/Blogs/tree/master/Data%20Dictionary

How is the Power BI option free? The entry level account is free: https://docs.microsoft.com/en-gb/dynamics365/business-central/admin-powerbi-setup and that is all I had to build the report.

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 CSV import with Power Automate ๐Ÿ“ฅ

There are plenty of things I try for blog posts that don’t make the cut. This was almost one of them. I’ll spare you the details on why exactly (until the end) so that we can keep focused on the good parts. My inspiration for this post came from watching this video: https://www.youtube.com/watch?v=2TIolfmtWQM, where a Excel script and a neat trick in Power Automate allow for a CSV file to be converted into an Excel table. The approach itself is very elegant and I’ve used the majority of it to my benefit. I recommend watching the video as I’ll skip over parts covered by it. The Excel script feature is truly awesome ๐Ÿ˜Ž

Let’s lay out a scenario where a supplier/vendor will send a CSV file with new items, with some basic details, so you can have them created for you in BC. My super simple CSV for this example:

Barcode,Name,UoM,Price,RRP
4545FB9797,Football,PCS,2.75,8
4545BB1324,Basketball,PCS,3.26,10.5
4545TB1344,Tennis Ball,Pack,0.95,3
4545SB9976,Squash Ball,Pack,0.78,3
4545RB4457,Rugby Ball,PCS,3.25,10.99

As mentioned I’m skipping some details, due to the recommended video, but once you have reached the Excel script stage you will want to add a few lines to cater for an extra row which is created and a caption change to one header value. Here is my script:

function main(workbook: ExcelScript.Workbook, lineCSV: string[]) {
  let selectedSheet = workbook.getActiveWorksheet();
  const alllines = lineCSV;
  let counter = 1;
  for (let line of alllines) {
    if (line.includes(",")) {
      let items = line.split(",");
      selectedSheet.getRange("A" + counter + ":E" + counter).setValues([[items[0], items[1], items[2], items[3], items[4]]]);
      counter++;
    }
  }
 
  workbook.addTable(selectedSheet.getRange("A1:E" + counter), true).setName("VendorItems");
  
//Delete Last blank line
 selectedSheet.getRange(counter+":"+counter).delete(ExcelScript.DeleteShiftDirection.up);
  //Retype the header otherwise the value is RRP\r
  selectedSheet.getRange("E1").setValue("RRP");
  }

From this point onward the video details are complete and it is time to think about extracting the data from the newly created Excel file to BC.

If you check over image 3 of the gallery you will notice that the “unitCost” and “unitPrice” values make use of an expression. This is to convert the data to the correct type. With it being CSV everything is text. This is an example of the expression to convert to decimals:

float(items('Apply_to_each_2')['RRP'])

All seems too simple right? Well yes in hindsight it sort of is. However, I will share the thing that tripped me up. The suggestion from the video is to use a compose action which is a carriage return. The end result of using that idea (with my data) is like this:

  "Barcode,Name,UoM,Price,RRP\r",
  "UVH72WFZ5EO1,facilisis,PCS,$25.33,55.93\r",
  "SVP51SRG4WY1,sodales,PCS,$85.55,50.16\r",
  "2RBS17JLI4CT,at,PCS,$71.72,29.32\r",
  "3YNT44PPY7LW,metus. In,PCS,$9.71,65.62\r",
  "4OGL41FFB6NB,fermentum convallis,PCS,$94.99,30.4\r",
  ""

Take note of the carriage returns which are the “\r” parts at the end of each row. Even after the data is converted into an Excel table those carriage returns remain in place. Remedy number 1 is to have the extra line to your script to rename the last column. Remedy number 2 is then the float() expression. I of course did not stumble across remedy number 2 that quickly, and tried some useless string manipulation ๐Ÿค. The performance of the import isn’t too bad. I tried a batch of 5000 records and it took 4.5mins to process. Baring in mind it is an automated process so those aren’t 4.5mins I lost in the space time continuum โณ

The final thing I would do with this scenario is to move the file you have imported to an archive folder. I think for my scenario to work better you would need a custom api page. I think the catalogue item table is probably a better place for the type of data I was working with ๐Ÿคท

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