Integration With Dynamics 365 CE And Companies House API – Azure Logic App

ByRob Peledie

Integration With Dynamics 365 CE And Companies House API – Azure Logic App

Introduction and Background

Before I start the overview of this process, I think it’s worth noting that over the last few years, the options for integration, and to be fair, any sort of extension and development in Dynamics 365 has/is shifting from being a C# development only domain, or at least a coding exercise, to giving us the option to do this via an extensive toolset – Azure, Office 365 to mention a few.

So for this example – Integration of data from an external data source – I will be going through the process in what I consider the easiest and lowest overhead in terms of coding. Yes there are a number of different options for achieving this, and coding something is one of those, but my favourite expression at the moment is “Don’t crack a walnut with a sledgehammer”.

So a couple of assumptions in terms of your setup. You’ll need:

  • An Azure Account
  • Dynamics 365 Instance
  • A perfunctory understanding of API’s and JSON

The use case for this integration is to call out to the Companies House API and pull back some company data in to Dynamics 365. There is a lot of information stored there about Ltd Companies in the UK, and could be really useful.

The Companies House API is still in Beta……to be fair it’s been Beta for a couple of years, but seems fairly stable, and there is a wealth of documentation and a forum. You will need to create an account on the site in order to access and use the API’s.

You can do this, and access the documentation and Forum here: https://developer.companieshouse.gov.uk/api/docs/

The process is going to be something like this:

So one of the first things you’ll need to do once you have created an account on the Companies House website is register an application. This process then assigns you an API Key used for authentication. There a few options for type of API, and restriction of IP addresses etc, but for this example we’ll go for a REST API. Once this is completed, you’ll get an API key.

The next step is to setup you Azure Logic App. I’ll skip the bits around setting up Resource Groups etc. CLick on Create Resource, Integration then Logic App:

Add the details:

Once this is all done, you can start to build out your logic. When your resource is ready you can navigate to it. If you’re familiar with Microsoft Flow, you might recognise some similarities.

There’s a reason why Logic Apps and Flow look similar. Flow is built on top of Logic Apps. You can read more details about the similarities and differences here

Click on the Blank Logic App and add the trigger for Dynamics 365. In this example I am going to trigger the integration when a new Account is created, but it could also be when a record is updated and so on.

So the next stage is to use the HTTP connector set it up to point to the Companies House API. For authentication use Raw, and you’ll only need the value (no need for username and password)

You’ll also notice that the URI is appended with a Dynamics Field, in this case the Account Number field which needs to store the Company number, as this is the format for searching for the company. Obviously that means you need to know the company number!

Once the HTTP is set, you need to Parse the resulting JSON. For this you need the Parse JSON Operation. Here you will set the Schema for the returning JSON.

For the GET Company Schema use the following:

{
    "properties": {
        "accounts": {
            "properties": {
                "accounting_reference_date": {
                    "properties": {
                        "day": {
                            "type": "string"
                        },
                        "month": {
                            "type": "string"
                        }
                    },
                    "type": "object"
                },
                "last_accounts": {
                    "properties": {
                        "made_up_to": {
                            "type": "string"
                        },
                        "period_end_on": {
                            "type": "string"
                        },
                        "period_start_on": {
                            "type": "string"
                        },
                        "type": {
                            "type": "string"
                        }
                    },
                    "type": "object"
                },
                "next_accounts": {
                    "properties": {
                        "due_on": {
                            "type": "string"
                        },
                        "overdue": {
                            "type": "boolean"
                        },
                        "period_end_on": {
                            "type": "string"
                        },
                        "period_start_on": {
                            "type": "string"
                        }
                    },
                    "type": "object"
                },
                "next_due": {
                    "type": "string"
                },
                "next_made_up_to": {
                    "type": "string"
                },
                "overdue": {
                    "type": "boolean"
                }
            },
            "type": "object"
        },
        "can_file": {
            "type": "boolean"
        },
        "company_name": {
            "type": "string"
        },
        "company_number": {
            "type": "string"
        },
        "company_status": {
            "type": "string"
        },
        "confirmation_statement": {
            "properties": {
                "last_made_up_to": {
                    "type": "string"
                },
                "next_due": {
                    "type": "string"
                },
                "next_made_up_to": {
                    "type": "string"
                },
                "overdue": {
                    "type": "boolean"
                }
            },
            "type": "object"
        },
        "date_of_creation": {
            "type": "string"
        },
        "etag": {
            "type": "string"
        },
        "has_been_liquidated": {
            "type": "boolean"
        },
        "has_charges": {
            "type": "boolean"
        },
        "has_insolvency_history": {
            "type": "boolean"
        },
        "jurisdiction": {
            "type": "string"
        },
        "last_full_members_list_date": {
            "type": "string"
        },
        "links": {
            "properties": {
                "filing_history": {
                    "type": "string"
                },
                "officers": {
                    "type": "string"
                },
                "persons_with_significant_control": {
                    "type": "string"
                },
                "self": {
                    "type": "string"
                }
            },
            "type": "object"
        },
        "registered_office_address": {
            "properties": {
                "address_line_1": {
                    "type": "string"
                },
                "address_line_2": {
                    "type": "string"
                },
                "locality": {
                    "type": "string"
                },
                "postal_code": {
                    "type": "string"
                }
            },
            "type": "object"
        },
        "registered_office_is_in_dispute": {
            "type": "boolean"
        },
        "sic_codes": {
            "items": {
                "type": "string"
            },
            "type": "array"
        },
        "status": {
            "type": "string"
        },
        "type": {
            "type": "string"
        },
        "undeliverable_registered_office_address": {
            "type": "boolean"
        }
    },
    "type": "object"
}

If all goes well, when you add the final Operation – Update Record – You should see all the returned data options:

Obviously the fields need to be available on the Dynamics 365 Accounts form.

Then….. Time to test!

Add a new Account in Dynamics and include the Company Number:

Once you save the record, the call is made to the Companies House APi, and returns any details it finds:

So this is very much a click and configure integration, but I think a nice use of the Logic App solution, and an easy integration with REST API’s.

ByRob Peledie

Show Image if field value equals…

Before I start, a massive shout out to Charles Osei .As part of the TDG team, we had a discussion, and his solution totally solved the requirement I had. Also another great example of how a collaborative group can work.

Had a requirement recently to show something a little more visual on a record form if a tick box was chosen…… something that would immediately show the user the status or conditions on a record.
In this example, were going to show Icons for Key Accounts and Sensitive Accounts at the top of the form if the corresponding fields are ticked.

Firstly, find your icons. For me I decided on a key for the Key Accounts, and an exclamation for the sensitive accounts, but I also added some text:

These need to be added to Dynamics as Web Resources.

Next add a section on your account form (I added a 3 column one) and add the two new icon web resources to it. Make sure you untick the box “Visible by Default” for each web resource.

You also need to add your radio buttons on the form – in my case one for Key Account and One for Sensitive.

Once these are all on, save and publish.

Next create a new web resource of type Script (JScript).

//Shows/hides keyaccount img webresouce based on a tickbox two option field

function alertpic (executionContext) 
{
    var formContext = executionContext.getFormContext();
	
    var keyaccount = formContext.getAttribute("uob_keyaccount").getValue();
	
    if (keyaccount === true) {
        formContext.getControl("WebResource_keyacc").setVisible(true);
    }
    else {
        formContext.getControl("WebResource_keyacc").setVisible(false);
    }
}

//Shows/hides sensacc img webresouce based on a tickbox two option field

function sensalert (executionContext) 
{
    var formContext = executionContext.getFormContext();
	
    var sensacc = formContext.getAttribute("uob_sensitiveaccount").getValue();
	
    if (sensacc === true) {
        formContext.getControl("WebResource_sensacc").setVisible(true);
    }
    else {
        formContext.getControl("WebResource_sensacc").setVisible(false);
    }
}

So the first variable sets the context (this will be passed as a parameter when you call the function).

The second variable is the tick box field.

Then depending on the value (True/False) it shows or hides the Icon.

Don’t forget to call the function from both Onload and Onchange (On the field)

The result:

ByRob Peledie

Using SSIS for Bulk Data Deletion in Dynamics 365

I had a situation recently that an instance (not one I managed I hasten to add!) had started to grow in storage, to the point that it was at nearly 200% capacity. I was asked to look at it. Turned out that Plugin Tracing had been switched on, and a plugin was throwing errors. Unfortunately there were now so many records in the Log file that it wouldn’t open (Usual generic unhelpful SQL Error).

Taking a quick look at organisation insights confirmed that this was consuming nearly 60gb of storage, and a quick record count (Using Record Counter in XrmToolBox) indication there was approaching 10,000,000 records.

Normally I would use the standard Bulk Delete option, however for a couple of reasons I didn’t think this was a viable option:

  1. It would take too long
  2. It kept falling over after 1000 deletes (probably due to the number of records, but I didn’t get to the bottom of it)

In the end I decided to go back to trusty old SSIS, and (again) the lovely KingswaySoft solution for Dynamics 365.

The steps are essentially:

  1. Create a CRM connection
  2. Retrieve what you want to delete (Using Dynamics Data Source Component)
  3. Delete

If you’ve used the SSIS Toolbox before, then you’ll know how to setup a connection.

To retrieve the data, I decided to go with FetchXML as I wanted to specify a date range, so the following was added to the Dynamics Source Data Flow:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="plugintracelog">
    <attribute name="plugintracelogid" />
    <filter type="and">
      <condition attribute="createdon" operator="on-or-before" value="2018-11-20" />
    </filter>
  </entity>
</fetch>

So the Fetch above would pull back any log files created before 20/11/2018.

Once this is done, connect the Source to the destination, and open up the Destination Module

Add your connection in Connection Manager.
In Action, choose Delete.
In Destination Entity, choose the Plugintracelog entity

I enabled multithread, and chose 32 threads.

Once that is all done, Save, Build and Run.

ByRob Peledie

JavaScript to show Form Notification

Just a quick one…..

Needed to show a notification on a form if an option set value = x

Used the following to get the text value, then show the Notification:

 

 

Add the function to an onload event, and the results:

ByRob Peledie

Anonymising Data in Dynamics 365 with SSIS

So I don’t want to get in to the whole GDPR discussion in this article, but with GDPR in mind, I think it’s important, and potentially reduces risk, to anonymise data in a non production environment.

Again, there are plenty of articles on whether it should be a process of anonymising or pseudonymisation, but for the purpose of this article, I am going to go through the steps of using SSIS for the purpose of Anonymising Dynamics 365 Data.

Firstly, a few assumptions…..

You have Visual Studio and SSIS setup. I’m personally using VS 2015 Pro Update 3, SSDT.

You will also need a couple of essential Dynamics tools from the lovely people at KingswaySoft

SSIS Integration Toolkit for Microsoft Dynamics 365

SSIS Productivity Pack

They provide a developer license model to get you started.

Once you have everything downloaded and setup, then you’re ready to go.

Create a new SSIS Project

In Connection Manager, create a new connection

 

 

 

 

 

 

 

Next, drag over the following components:

  • Dynamics CRM Source
  • Data Anonymizer
  • Dynamics CRM Destination

 

 

 

 

 

 

 

In the Dynamics Source Component, Enter the Connection you just created, the Entity (in this case Lead).

 

 

 

 

 

 

Click on the columns section, and choose which fields from Dynamics you want to anonymise…… In this case I chose things like FirstName, LastName, Email etc.

 

 

 

 

 

 

Connect the Dynamics CRM Source component to the Data Anonymiser component by dragging the connector.

Open the Data Anonymiser Component.

 

 

 

 

 

Here you will see the fields/columns you have decided to anonymise, and the option to choose the data type

So for example the email address will be anonymised with data of email type

 

 

 

 

Do this for all your fields/columns.

Once this is done, open the CRM Destination Component

 

 

 

 

 

 

Next click on the columns section and map the source to the destination

 

 

 

 

 

 

So to summarize….

  • CRM Connection
  • Choose Source entity and Fields
  • Choose Anonymise options
  • Set Destination Mapping

Once this is all done, save your solution and run it:

 

 

 

 

 

 

 

 

So in the instance above 274 Lead records were anonymised.

I hope you found this quick tutorial helpful.

By365Knowledge

Check Back Soon!

Thanks for checking out our new site!

Check back soon to see our updated blog posts!

I have decided to start again with the posts, as some of the old posts were for previous versions of Dynamics CRM/365 and not relevant.

 

Thanks,

365Knowledge Team