Integration between did & dataverse for did codes & projects

[jenunn]

• I have searched for existing issues to avoid creating duplicates

TLDR

In order for Puzzlepart CRM & did to be in sync over the available DID codes in both systems, we want to implement an integration between the 2 systems.

In order to do this we have tried accessing the Cosmos DB using the connector "Azure Cosmos DB" connector in Power Automate.
This returned an error saying "Sql api is not supported for this database account
ActivityId: cc482c5c-39a6-4cf2-b811-15fb0d8f999c, Microsoft.Azure.Documents.Common/2.14.0"

We should instead use a direct call using graphQL request that needs to be transformed into a valid REST request because Power Automate does not have capabilities to use graphQL.

Description

• What is the feature?
  An integration between Dataverse & did for having did codes & projects up to speed in both systems
• Why should we add this feature?
  To be able to easily select did codes in Dataverse & did no matter which system the did codes & projects are created in.
• How does this improve the product?
  Easier user experience for users of did & Puzzlepart CRM

We could potentially also trigger an Azure Runbook which has a webhook we can trigger from a Power Automate that can handle the GraphQL queries towards did.

Potentially 3 automations:

1. Create a Power Automate Flow that triggers upon creation of a new did code or did project. This flow can then trigger an Azure runbook that can execute the GraphQL queries to the custom did API's.
   This will take care of the sync of creations from Dataverse to did.

2. Create a Power Automate Flow or Azure Runbook that triggers on a recurring interval that can retrieve data from both systems and make sure to update/create those items that is missing or is not in sync
   This will take care of the sync of data between both systems.

3. Create something that can trigger on new rows in Cosmos DB where a new did project or did code has been created from the did webapp and automatically sync this to Dataverse.
   This will take care of the sync of creations from did to Dataverse

[jenunn]

@joslange feel free to add your opinions and thoughts regarding virtual table, Fabric etc

[jenunn]

There is no possibility to use virtual tables with the "Preview feature: Azure Cosmos DB for NoSQL Data Provider" as the requirements for this is that the Azure Cosmos DB must be of type "SQL".

Did is using Azure Cosmos DB API for Mongo DB which is not SQL based.

[olemp]

@jenunn You should use the GraphQL API. Have you created the required flows? I could help out with setting up the actions :)

[jenunn]

@olemp, i have yet had the time to do this. We should decide with did team and the CRM product owner on which of the 3 scenarios we should go for as all 3 has different outcomes.

If we go for one of the scenarios where there is no sync both ways, we need to make sure that users can only create a did code/project in either platforms.

Are you able to ask the did team and @okms for a call on which scenario we should go for?

[joslange]

We can iframe a did-section on the project in CRM, but we still need the sync to have did keys in CRM.
For example, this URL kan be set dynamically in the iframe, but we need the projectkey:
https://did.xxx.com/projects/[projectkey]/timeEntries

(Time entries takes a while to load, so not sure it's the best user experience in an ifram)

[olemp]

@olemp, i have yet had the time to do this. We should decide with did team and the CRM product owner on which of the 3 scenarios we should go for as all 3 has different outcomes.

If we go for one of the scenarios where there is no sync both ways, we need to make sure that users can only create a did code/project in either platforms.

Are you able to ask the did team and @okms for a call on which scenario we should go for?

If you create some initial flows with CRM triggers I can help out with the actions for sending requests to the did API 🚀

[olemp]

We can iframe a did-section on the project in CRM, but we still need the sync to have did keys in CRM. For example, this URL kan be set dynamically in the iframe, but we need the projectkey: https://did.xxx.com/projects/[projectkey]/timeEntries

(Time entries takes a while to load, so not sure it's the best user experience in an ifram)

IFrame is not the way to go, we're not in 1999 anymore 🎸

[jenunn]

I think the best way moving forward using the GraphQL API with did is to do the same we did with the Xledger API for Cemasys.

Trigger an Azure Runbook when syncing from CRM to did.
Trigger an Azure Runbook when syncing from did to CRM.