Associate and disassociate table rows using the Web API
You can associate individual records in table rows with other records using relationships that exist between the table definitions. In OData, the relationships are expressed as navigation properties.
You can discover which navigation properties exist in the $metadata service document. See Web API Navigation Properties. For existing Dataverse tables, see the Web API EntityType Reference, for each entity type, see the listed single-valued and collection-valued navigation properties.
The following table describes the three types of relationships between tables in Dataverse.
| Type | Description | Example |
|---|---|---|
| One-to-Many | One record can have many records associated with it. | An account record can have many contact records in the contact_customer_accounts collection-valued navigation property. |
| Many-to-One | Many records can be associated with one record. Many-to-One is the mirror image of a One-to-Many relationship. There's just one relationship. |
Multiple contact records can be associated to a single account record using the parentcustomerid_account single-valued navigation property. |
| Many-to-Many | Many records can be associated with many records. | Each security role (role) might include references to the definition of a systemuser. Both of these tables have a systemuserroles_association collection-valued navigation property. |
Using single-valued navigation properties
For existing records on the many side of a one-to-many or many-to-one relationship, you can associate the record by setting a Uri reference to the other record. The easiest and most common way to do this is to append the @odata.bind annotation to the name of the single-valued navigation property and then setting the value as the Uri to the other record in a PATCH request.
Associate with a single-valued navigation property
For example, to associate a contact record to an account using the parentcustomerid_account single-valued navigation property:
Request:
PATCH [Organization Uri]/api/data/v9.2/contacts(cf9eaaef-f718-ed11-b83e-00224837179f) HTTP/1.1
If-Match: *
OData-MaxVersion: 4.0
OData-Version: 4.0
If-None-Match: null
Accept: application/json
{
"[email protected]": "accounts(ce9eaaef-f718-ed11-b83e-00224837179f)"
}
Response:
HTTP/1.1 204 NoContent
OData-Version: 4.0
OData-EntityId: [Organization Uri]/api/data/v9.2/contacts(cf9eaaef-f718-ed11-b83e-00224837179f)
As described in Associate table rows on create, new records can also be associated with existing records in the same way.
Disassociate with a single-valued navigation property
If you want to disassociate, you can simply set the value to null.
Request:
PATCH [Organization Uri]/api/data/v9.2/contacts(cf9eaaef-f718-ed11-b83e-00224837179f) HTTP/1.1
If-Match: *
OData-MaxVersion: 4.0
OData-Version: 4.0
If-None-Match: null
Accept: application/json
{
"[email protected]": null
}
Response:
HTTP/1.1 204 NoContent
OData-Version: 4.0
OData-EntityId: [Organization Uri]/api/data/v9.2/contacts(cf9eaaef-f718-ed11-b83e-00224837179f)
When disassociating in this manner, you don't need to include the @odata.bind annotation. You can simply use the name of the single-valued navigation property:
Request:
PATCH [Organization Uri]/api/data/v9.2/contacts(cf9eaaef-f718-ed11-b83e-00224837179f) HTTP/1.1
If-Match: *
OData-MaxVersion: 4.0
OData-Version: 4.0
If-None-Match: null
Accept: application/json
{
"parentcustomerid_account": null
}
Response:
HTTP/1.1 204 NoContent
OData-Version: 4.0
OData-EntityId: [Organization Uri]/api/data/v9.2/contacts(cf9eaaef-f718-ed11-b83e-00224837179f)
More information: Basic update
Other methods
There are other ways to achieve the same results described previously with single-valued navigation properties.
You can use the following PUT request to set the value of the parentcustomerid_account single-valued navigation property:
Request:
PUT [Organization Uri]/api/data/v9.2/contacts(cf9eaaef-f718-ed11-b83e-00224837179f)/parentcustomerid_account/$ref HTTP/1.1
OData-MaxVersion: 4.0
OData-Version: 4.0
If-None-Match: null
Accept: application/json
{
"@odata.id": "[Organization URI]/api/data/v9.2/accounts(ce9eaaef-f718-ed11-b83e-00224837179f)"
}
Response:
HTTP/1.1 204 NoContent
OData-Version: 4.0
Note
Note: You must use an absolute URL when setting the value for @odata.id.
To remove the reference, you can also use this DELETE request:
Request:
DELETE [Organization Uri]/api/data/v9.2/contacts(cf9eaaef-f718-ed11-b83e-00224837179f)/parentcustomerid_account/$ref HTTP/1.1
OData-MaxVersion: 4.0
OData-Version: 4.0
If-None-Match: null
Accept: application/json
Response:
HTTP/1.1 204 NoContent
OData-Version: 4.0
Using collection-valued navigation properties
With OData, both sides of a many-to-many relationship have collection-valued navigation properties. For one-to-many and many-to-one relationships, the table one the 'One' side has a collection-valued navigation property. There's no difference how you work with any of these types of relationships while using collection-valued navigation properties. This section describes how to work with collection-valued navigation properties with any type of relationship.
Add a record to a collection
The following example shows how to add a contact record to the account contact_customer_accounts collection, which is part of a one-to-many relationship.
Request:
POST [Organization Uri]/api/data/v9.2/accounts(ce9eaaef-f718-ed11-b83e-00224837179f)/contact_customer_accounts/$ref HTTP/1.1
OData-MaxVersion: 4.0
OData-Version: 4.0
If-None-Match: null
Accept: application/json
{
"@odata.id": "[Organization URI]/api/data/v9.2/contacts(cf9eaaef-f718-ed11-b83e-00224837179f)"
}
Response:
HTTP/1.1 204 NoContent
OData-Version: 4.0
The following example shows how to add a role record to the systemuser systemuserroles_association collection, which is a many-to-many relationship.
Request:
POST [Organization Uri]/api/data/v9.2/systemusers(34dcbaf5-f718-ed11-b83e-00224837179f)/systemuserroles_association/$ref HTTP/1.1
OData-MaxVersion: 4.0
OData-Version: 4.0
If-None-Match: null
Accept: application/json
{
"@odata.id": "[Organization URI]/api/data/v9.2/roles(886b280c-6396-4d56-a0a3-2c1b0a50ceb0)"
}
Response:
HTTP/1.1 204 NoContent
OData-Version: 4.0
Remove a record from a collection
The following example shows how to remove a contact record to the account contact_customer_accounts collection where the contact contactid value is cf9eaaef-f718-ed11-b83e-00224837179f.
Request:
DELETE [Organization Uri]/api/data/v9.2/accounts(ce9eaaef-f718-ed11-b83e-00224837179f)/contact_customer_accounts(cf9eaaef-f718-ed11-b83e-00224837179f)/$ref HTTP/1.1
OData-MaxVersion: 4.0
OData-Version: 4.0
If-None-Match: null
Accept: application/json
Response:
HTTP/1.1 204 NoContent
OData-Version: 4.0
The following request also works:
Request:
DELETE [Organization Uri]/api/data/v9.2/accounts(ce9eaaef-f718-ed11-b83e-00224837179f)/contact_customer_accounts/$ref?$id=[Organization URI]/api/data/v9.2/contacts(cf9eaaef-f718-ed11-b83e-00224837179f) HTTP/1.1
OData-MaxVersion: 4.0
OData-Version: 4.0
If-None-Match: null
Accept: application/json
Response:
HTTP/1.1 204 NoContent
OData-Version: 4.0
See also
Web API Basic Operations Sample (C#)
Web API Basic Operations Sample (Client-side JavaScript)
Perform operations using the Web API
Compose Http requests and handle errors
Query Data using the Web API
Create a table row using the Web API
Retrieve a table row using the Web API
Update and delete table rows using the Web API
Use Web API functions
Use Web API actions
Execute batch operations using the Web API
Impersonate another user using the Web API
Perform conditional operations using the Web API