Associate and disassociate table rows using the Web API

Grein
08/10/2023

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.

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.

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

{
  "parentcustomerid_account@odata.bind": "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.

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

{
  "parentcustomerid_account@odata.bind": 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

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

Deila með

Associate and disassociate table rows using the Web API

Using single-valued navigation properties

Associate with a single-valued navigation property

Disassociate with a single-valued navigation property

Other methods

Using collection-valued navigation properties

Add a record to a collection

Remove a record from a collection

See also

Athugasemdir

Fleiri tilföng