While I'm working on a more exciting article, I wanted to share with you one quick tip on using the Dataverse Web API I found the hard way. So today I will show you how to get the Dataverse Web API URL for any table (even the intersect / Many-to-Many relationship table!).
Hope you enjoy it!
Introduction
Querying Dataverse tables is all about OData. This standard is present in other Microsoft technologies as well (e.g., using the SharePoint Online Connector). Now, have you had trouble finding the URL to query a Table? I have. Particularly in a project where I had to migrate a lot of data from another software vendor to custom tables in the Dataverse.
To save you some time, use this table of examples to find how to query a Dataverse table:
Entity | ​Logical Name | How it ends | What you need to add | Dataverse Web API query example (using the "set name") |
|---|---|---|---|---|
Table | ab_projects | With an 's' | 'es' | |
Table | ab_employee | Without an 's' | 's' | |
Many to Many Relationship table name | ab_projects_ab_employee | N/A | 'set' |
Finding the "Set Name" of a Custom / Standard Table
So, when working programmatically with the Dataverse using the Web API or Power Automate, you will need to find that table's "Set Name". It can be found directly from the editor:
Let's stop for a moment and appreciate the quick links that Microsoft hands over here (we didn't have that not so long ago):
What's the Table's definition (aka Metadata)?
The columns you create, relationship between tables and other configurations you make using the editor affect directly the "Metadata" of a table. You can read more about it here:
Using the link "API link to table definition", we'll access the definition of the Table, where we will be able to locate our Set Name, along with some interesting stuff:
Finding the "Set Name" of an Intersect Table (Many-to-Many relationship)
Trying to get the Set Name to query an intersect table is a little bit more difficult.
What's an intersect table?
When you setup a Many-to-Many relationship, Dataverse creates a new Table behind the scenes to track the relationship between the two tables:
This doesn't happen with Many-to-One or One-to-Many because Dataverse will store the ID of the "One" side in the rows of the "Many" side, which is enough to keep track of the relationship between tables.
Option 1: From the Portal + a trick
Just follow these three steps:
From the editor, open the relationship panel
Copy the Relationship table name (exactly, this is the table name of the intersect table)
Add a "set" at the end
Option 2: Querying the Entity Definition (metadata)
If you reproduced the examples yourself, you'll probably have noticed something very interesting when we accessed the table's metadata:
… you jumped to a new tab with a similar URL to:
Now, you just need to replace your equivalent of "ab_projects" with "ab_projects_ab_employee" (the Relationship table name of the Many-to-Many relationship):
https://xxxxxxx.crm4.dynamics.com/api/data/v9.2/EntityDefinitions(LogicalName='ab_projects_ab_employee')
When it loads, just search for the EntitySetName property there:
Wrapping up
We've seen how to get the "Set Name" for any Dataverse table (including an Intersect Table). This will come handy when you try to use the Dataverse Web API or the Dataverse Power Automate connector.
I hope this article was useful to you! Let me know in the comments if you are interested in more of this stuff!
See you around,
Andres