If you're not connecting Dynamics 365 to other apps, you're driving a Ferrari in first gear. Seriously, think about it. You've got this powerhouse of an app suite – yet, without stretching its capabilities through integration to other data sources, you're merely scratching the surface of what can be done. Let’s shift into higher gears and unravel the mystery of "how to connect other apps to Dynamics 365."
Unlocking Dynamics 365's Potential: Why Integration Matters
A system is just as good as the data that you put in it. At its core, integrating Dynamics 365 within the broader ecosystem of your IT infrastructure is not just an enhancement; it’s a necessity for any technologically mature organization. Without interconnected applications and other data sources, Dynamics 365 can end up isolated, its potential underutilized.
The simple exchange of data can be a game changer. It will enable a cohesive, automated environment where silos are dismantled, and real-time data availability becomes the norm, not the exception. Dynamics integration enables access to the right data in the location that is more convenient for the end-user. This might sound like common sense, but it is actually key to staying ahead.
Paths to Dynamics 365 Integration Mastery
You're probably wondering, "How do I make this magic happen?" Well, you’ve got two paths.
- For coders: If you're a wizard with code, then APIs are your playground. Utilizing Web APIs, OData, and SOAP endpoints, you can architect complex integrations that connect Dynamics 365 to a vast array of systems and applications. You can use API calls to enable real-time data synchronization and offer a seamless user experience across platforms.
- For Non-Coders: If you are not a coder or you don’t have time to master a new API? No sweat. Middleware integration platforms are your friend here. These platforms, acting as bridges, streamline the integration process, offering pre-built connectors and a visually intuitive mapping interface for those less inclined to engage directly with APIs.
Spotlight on Connect Bridge as a Middleware Solution
For a clearer example, let’s explore how Connect Bridge acts as a middleware. It functions as an integration enabler, allowing you to interact with Dynamics 365 through the familiar lens of SQL statements. This abstraction simplifies complex integration challenges. This does not mean that all integrations using Connect Bridge as middleware are the same.
This is because you again have two paths available:
- Code the integration in your preferred programming language
You can code the integration in the programming language of your choice (but still using SQL rather than the Dynamics 365 API) - Set up a Linked Server for minimal coding
Detailed instructions on how to achieve a Dynamics connection on a Linked Server are in the following video, and we will look into this option further in the Hands-on section below.
Hands-on: Connect to Dynamics 365 with a Linked Server
In the video, you can see that a Linked Server was created in SSMS and that was linked to Connect Bridge so that we could access the data as if we were accessing Dynamics 365 database.
We also saw that the T-SQL queries to perform CRUD operations on the data can be as simple as
DELETE c
FROM D365CE...Contact c
INNER JOIN Contact_DEL d
ON CONVERT(varchar(255), c.ContactID) = d.OnlineID;
This is an example query for deleting contacts in Dynamics 365. It is simple because the Connect Bridge middleware hides the complexity, allowing you to work with T-SQL alone. Please note that the T-SQL queries are being converted to Microsoft Dynamics 365 API calls behind the scenes – your Dynamics database integrity is safe!
When you look back at it, the example query follows the typical pattern for a DELETE with a JOIN on a Linked Server, but let’s analyze it:
- DELETE c: This specifies that rows will be deleted from the table aliased as "c".
- FROM D365CE...Contact c: This defines the source table for deletion. "D365CE" is likely the Linked Server name, and "Contact" is the table name on that server.
- INNER JOIN Contact_DEL d ON CONVERT(varchar(255), c.contactid) = d.OnlineID: This joins the local table "Contact_DEL" with the Linked Server table based on converted values (potentially for data type mismatch).
As shown in the video, the query for inserting contacts in Dynamics 365 is not complicated either. The example used is
INSERT INTO D365CE...Contact(LastName, FirstName, EmailAddress1)
SELECT LastName, FirstName, Email
FROM Contact
WHERE OnlineID IS NULL OR OnlineID ='';
In this snippet, you see how simple it can be. You are inserting what you get as a result of a SELECT query – just your regular INSERT with a subquery. In this case, the only thing worth noting is that the procedure is iterative - the WHERE clause is used to guarantee you only take the local contacts that were not previously handled in other iterations.
Are you wondering how you know which fields to use in these queries? That is a great question, and the answer is in Connect Bridge Management Studio. This tool looks quite similar to SSMS - and it is not by chance!
Using it you can explore what looks like Dynamics 365 data model. In fact, you see the model as exposed by Connect Bridge, but these are the fields you can use in your queries. You can also test your queries within that interface, if you want.
A final note on how you can achieve effective synchronization of data between the local "Contact" table and the Dynamics 365 Contact table on the Linked Server. This is more difficult because you don’t know if you need an INSERT or an UPDATE. This can be elegantly solved with a MERGE T-SQL query.
MERGE Contact as target
USING (SELECT ContactiID FirstName, LastName, EmailAddress1
FROM D365CE...Contact
WHERE statuscode = 1 AND Modified = 0
ON (target.OnlineID = CONVERT(varchar(255), source.ContactID))
WHEN MATCHED THEN
UPDATE SET FirstName = source.FirstName, LastName = source.LastName,
Email = source.EmailAddress1, Synced = 1
WHEN NOT MATCHED THEN
INSERT (FirstName, LastName, Email, OnlineID, Synced, LastSynced,
LastUpdated)
VALUES (source.FirstName, source.LastName, source.EmailAddress1,
source.ContactID, 1, GETDATE(), GETDATE())
OUTPUT inserted.FirstName, inserted.LastName, inserted.Email, inserted.OnlineID, inserted.Synced, inserted.LastSynced, inserted.LastUpdated
Now that you've seen how Connect Bridge simplifies data exchange with Dynamics 365, consider exploring the two implementation options in more detail: coded integration or a Linked Server setup. You can:
- ask for your free personalized demo with our technical experts, or
- request a free trial and play with this in your environment.
I actually recommend that you do both. Start with the consultation and then experiment to see if this works for your integration idea and your environment.
Speaking of your environment: When considering integration targets for Dynamics 365, prioritize platforms that will amplify your operational efficiency, data intelligence, and customer engagement. The best choices will be the ones where the data exchange makes sense given your ecosystem, but here are a few options you can consider: SQL databases, SharePoint, Exchange / Office 365, Power BI, and Gmail.
Integrate. Transform. Thrive.
Integrating Dynamics 365 with other existing apps is not just about leveraging technology; it’s about pioneering a transformation in your business's operations. So, gear up, shift out of first gear, and propel your Dynamics 365 integration efforts into the fast lane.
You don’t need a super-long project or to know the intricacies of API specifics to do this. Connect Bridge allows tech professionals to craft robust integrations without touching the API directly. See how Connect Bridge can transform your Dynamics 365 in a free consultation, including a personalized demo from our experts.
About the Author
By Ana Neto, technical advisor at Connecting Software.
“I have been a software engineer since 1997, with a more recent love for writing and public speaking. Do you have any questions or comments about this article? I would love to have your feedback, leave a comment below!"