I’m writing this summary because it took me a bit of effort figure this out and I could see that a lot of people are trying to do this, but I couldn’t find the basics.
I’ve built a bespoke Microsoft Dataverse database for a PowerApps app and I want to access it from Zapier to SELECT/GET, INSERT/POST & UPDATE/PATCH.
The good news is that Zapiers API Request in Microsoft Dynamics 365 App does the job easily when you know how. We DON’T have a Dynamics 365 subscription, but I noticed that each DataVerse DBs Web API endpoint URL is a Dynamics.com URL so I tried it and it works.
Setup
For all three tasks the setup is basically the same.
- Use the built in Microsoft Dynamics 365 Zapier App
- Chose the API Request Event type
- Authenticate through it’s built in OAuth2 setup
- Put the DataVerse DBs Web API endpoint URL, plus you’ll need to add the “logical name” of the relevant table on the end of the API Endpoint URL (see example below). You can get the API Endpoint URL from your specific PowerApps Environment settings through the Developer Resources link.
- As several other people have pointed out, there are different Dataverse/Dynamics formats for field names and table names, you specifically need to use the “logical name” of the field and the same for the table as opposed to the schema name or the display name. The logical name is an all lowercase version of the “schema name”.
- I might have done a bit of overkill here, but these extra header settings were required.
SELECT/GET
To retrieve a specific record use $filter as the Query String Parameter and put the name of the field to filter on (where clause) in the value with the appropriate operator (eq is equals in this case, see references links below for other query options) and the value to pass in/filter on (in this case 1005) ie. myfieldname eq value.
INSERT/POST
This is a little more complex. It requires (as best as I can figure) the use of the JSON in the body field. This example is really simple, with the fieldname a colon and the value. Obviously make the HTTP Method POST
UPDATE/PATCH
This one is a little different again. It seems that in order to do an UPDATE that you MUST use the records GUID
to identify it by putting it onto the end of the URL inside round brackets after the table name and you can only update one record at a time. They you need to use JSON in the Body field again (as best as I can tell). Of course make the HTTP Method PATCH. It doesn’t seem to like PUT.
Triggers
Triggers for custom entities (tables) don’t seem to be in the list of the Dynamics 365 App if you add the App to your Zap in the normal way, however there’s also a way to get access to CREATE/NEW record Triggers/Webhooks in the Dynamics 365 app for custom entities which I haven’t finished testing. I also haven’t figured out UPDATE Triggers/Webhooks yet as it doesn’t seem to be available/built in. If anyone can tell me.
This interactive page helped me create the new record trigger in Zapier: https://zapier.com/apps/microsoft-dynamics-crm/integrations/webhook--code
Andrew,
Resources:
https://timhanewich.medium.com/dataverse-web-api-101-97e4099ff10b
https://learn.microsoft.com/en-us/power-apps/developer/data-platform/webapi/query-data-web-api
https://learn.microsoft.com/en-us/power-apps/developer/data-platform/webapi/create-entity-web-api
