Edit

Virtual tables for Tasks, Meetings, Files

  • Article

A new capability with this release is a set of Virtual tables. These enable developers to interact with Graph via OData APIs.

The Collaboration controls core solution includes a set of virtual tables, which can be used for programmatic access to the data created by the Collaboration controls.

Note

Currently, Collaboration controls are available only in public developer preview.

Tip

Virtual tables also known as virtual entities, enable the integration of data residing in external systems by seamlessly representing that data as tables in Microsoft Dataverse, without replication of data and often without custom coding.

The external system that is used by the Collaboration controls is Microsoft Graph. There are virtual tables for group calendar events, booking appointments, planner plans or tasks and SharePoint drives, folders, and files.

This article provides samples, which demonstrate how to access the virtual tables using the Dataverse REST API to perform CRUD (Create, Read, Update, and Delete) operations.

Tip

For more information on the Dataverse REST API, see use the Microsoft Dataverse Web API.

  • Virtual tables use the standard Dataverse Web API, which makes it easy to use the virtual tables to populate data in your application.
  • Virtual tables implement complex workflows required to support Collaboration controls and these execute within Microsoft data centers for optimum performance.
  • Virtual tables use the standard Dataverse logging and monitoring capabilities.

After you install the Collaboration controls, the virtual tables can be treated as another service to your application that can depend on.

Screenshot shows the Virtual tables overview.

Pre-requisites

To follow along with this article, you need:

  1. A Dataverse environment where the Collaboration controls have been installed.
  2. A user account in the Dataverse environment, which has the Collaboration controls User role assigned to it.
  3. A third-party tool, for example, Post man or some custom C# code that allows you to authenticate to Microsoft Dataverse instances and to compose and send Web API requests and view responses.

Tip

Microsoft provides information on how to configure a Postman environment that connects to your Dataverse instance and use Postman to perform operations with the Web API. See Use Postman with Microsoft Dataverse Web API.

Virtual tables sample scenario

The scenario described in this guide uses the Planner Plan and Task virtual tables. The scenario described is the same one that the Tasks Collaboration control uses. From a user perspective the scenario shows how a Planner Plan, and several Tasks are created and associated with a specific business record. The scenario goes on to show how to retrieve the tasks associated with the business record and how to read, update, and delete a specific planner task.

The following sequence diagram explains the interaction between the client, which could be the Tasks collaboration control, the Collaboration API and the Planner Plan and Task virtual tables.

The illustration shows the sequence diagram for virtual tables.

Virtual tables basic operations

This section describes the HTTP requests and responses for each step in the sample scenario.

Task 1: Retrieve the Group ID

Retrieve the Group ID used in settings for your Collaboration.

Note

The user you use to create the Plan in the subsequent tasks, must be a member of this group. If not you will get 403 Forbidden response.

Task 2: Begin a Collaboration session

A collaboration session is a record in the collaboration root table, which allows you to associate multiple collaborations, for example, tasks, events, appointments with a business record.

A collaboration session allows you to perform operations such as list of the calendar events associated with a business record, for example, an inspections application.

    HTTP/1.1 POST https://[Organization URI]/api/data/v9.0/m365_begincollaborationsession  

{
    "applicationName": "{{applicationName}}", 
    "collaborationRootEntityId": "{{collaborationRootEntityId}}", 
    "collaborationRootEntityName": "{{entityName}}" 
}
  • applicationName: Unique name for the application
  • collaborationRootEntityName: Name of the business record entity
  • collaborationRootEntityId: Primary key (ID) of the specific business record

Keep track of the collaborationRootId as it is needed in subsequent requests.

Task 3: Create a Planner Plan

Create a Planner Plan and associate it with the collaboration session created above with Group ID and collaborationRootId.

    HTTP/1.1 POST https://[Organization URI]/api/data/v9.0/m365_graphplannerplans  


{ 
    "m365_collaborationrootid": "{{collaborationRootId}}", 
    "m365_owner": "{{groupId}}", 
    "m365_title": "{{planTitle}}" 
}

  • collaborationRootId: Identifies the collaboration session we want to associate this plan with, use the value from task 2

  • groupId: Identifies the group who owns this plan, use the value from step 1

  • planTitle: Title for the plan

Keep track of them365_id as it is needed in subsequent requests.

Task 4: Create a Planner Task

Create a Planner Task with PlanId and collaborationRootId. you can create several Planner Tasks and associate them with the collaboration session created earlier.

    HTTP/1.1 POST https://[Organization URI]/api/data/v9.0/m365_graphplannertasks  

{ 
    "m365_collaborationrootid": "{{collaborationRootId}}", 
    "m365_planid": "{{planId}}", 
    "m365_title": "{{taskTitle}}", 
    "m365_duedatetime": "2022-05-04T08:00:00Z", 
    "m365_assignments": "{\"me\":{\"orderHint\":\" !\",\"@odata.type\":\"#microsoft.graph.plannerAssignment\"}}" 
}
  • collaborationRootId: Identifies the collaboration session we want to associate this plan with, use the value from task 2
  • planId: Identifies the plan this task will be assigned to, use the value from the previous step
  • taskTitle: Title for the task

Keep track of the m365_graphplannertaskid as it is needed in subsequent requests.

Note

The m365_graphplannertaskid is the primary key of the record in the Planner Task virtual table. All subsequent requests to the virtual table to interact with this record must use this primary key. This will be referred to as the plannerTaskId in subsequent steps in this document.

You should repeat this step to create multiple tasks in the plan.

Task 5: Retrieve Associated Planner Tasks

Retrieve Associated Planner Tasks with collaborationRootId associated with the collaboration session created previously.

    HTTP/1.1 GET https://[Organization URI]/api/data/v9.0/ m365_graphplannertasks?$filter=m365_collaborationrootid eq '{{collaborationRootId}}'&$select=m365_graphplannertaskid,m365_title,m365_createddatetime
  • $filter: Use the $filter system query to request records associated with the collaboration session (by specifying the ID of the collaboration root record).
  • $select: Use the $select system query option to request specific properties.

Keep track of the m365_id‘s as IDs will be needed in subsequent requests.

Task 6: Retrieve a Planner Task

Retrieve a Planner Task with PlannerTaskID to perform a Read operation on one of the planner tasks created earlier.

    HTTP/1.1 GET https://[Organization URI]/api/data/v9.0/m365_graphplannertasks({{plannerTaskId}})
  • plannerTaskId: The primary key for the planner task record is the m365_graphplannertaskid property.

Keep track of the @odata.etag property and them365_graphplannertaskid property as these are needed to perform update or delete operations.

Task 7: Update a Planner Task

Update a Planner Task with PlannerTask ID to perform an Update operation on one of the planner tasks created in the previous step. To update a planner task, execute the following request:

    HTTP/1.1 PATCH https://[Organization URI]/api/data/v9.0/m365_graphplannertasks({{plannerTaskId}})
  • Header: If-Match: {{@odata.etag}}

{
    "m365_title": "{{$planTitle}}" 
}

  • @odata.etag: Etag for the task, you must perform a read to retrieve the most up-to-date version.

  • planTitle: Updated title for the task

Task 8: Delete a Planner Task

Delete a Planner Task with PlannerTask ID to perform a Delete operation on one of the planner tasks created in the previous step. To delete a planner task, execute the following request:

    HTTP/1.1 DELETE https://[Organization URI]/api/data/v9.0/m365_graphplannertasks({{plannerTaskId}})
  • @odata.etag: Etag for the task, you must perform a read to retrieve the most up-to-date version.

Task 9: Update a Planner Task details

Update a Planner Task with PlannerTask ID to perform an update operation on one of the planner tasks created in the previous step.

    HTTP/1.1 PATCH https://[Organization URI]/api/data/v9.0/m365_graphplannertasks({{plannerTaskId}})

Header: If-Match: {{@odata.etag}}


{ 

    "m365_title": "{{$planTitle}}", 
    "m365_details": "{\"@odata.etag\":\"{{details.etag}}\",\"description\":\"Updated Task Description\"}" 

}
  • @odata.etag: Etag for the task, you must perform a read to retrieve the most up to date version.
  • planTitle: Updated title for the task.
  • @details.etag: Etag for the task details, you must perform a read using the query $select query parameter to include the m365_details column to retrieve the most up to date version. This value is included in the m365_details column of the response. This value isn't the same as the @odata.etag because in the Planner backend, the Task and its details are stored separately.

Note

You can set the If-Match header to be '*' and then you'll not need to provide any etag values, but your changes will always overwrite the task and it’s details.

Virtual tables authorization

Following are the authorization steps required to make HTTP requests using the Virtual tables in the Collaboration controls solution.

Azure app registration

To acquire the correct bearer token, an app registration in Azure is required. For more information on app registrations, see register an app.

  1. Create an app registration in the Azure portal to authenticate.

  2. Browse to Certificates & secrets.

  3. Create a new client secret.

    Important

    Make sure to copy the secret value and store for later use. You will not be able to access it again after leaving the current page.

  4. Browse to API Permissions.

  5. Add the user_impersonation delegated permission from Dynamics CRM.

  6. Grant admin consent for this permission.

    Screenshot shows the Power Automate API permission.

  7. Browse to Manifest.

  8. Set the value of the following attributes to true:

    • oauth2AllowIdTokenImplicitFlow
    • oauth2AllowImplicitFlow

  9. Select Save.

    Screenshot shows the Power Automate manifest.

PowerApps environment permissions

After the app registration has been set up, you must set up an application user in PowerApps environment. This allows you to authenticate with the correct Dynamics scopes that were configured earlier.

  1. Open the Power Platform Admin Center.

  2. Browse to Environments > Your_Environment > Users > App Users List.

  3. Select New App User and select your Azure app registration.

  4. Select Edit Security Roles and assign the System Administrator role to the app user.

    1. The System Administrator role is applied to allow authentication for any users that have a lower security role. For example, Collaboration controls User.
    2. This can be restricted by applying a lower role to the application. For example, Collaboration controls Administrator.

    Screenshot shows the Power automate admin center.

Getting the bearer token

After completion of Azure app registration and PowerApps environment permissions, send the following HTTP request to get the Bearer token.

POST https://login.microsoftonline.com/<AZURE_APP_TENANT_ID>/oauth2/token
  • Content-Type: application/x-www-form-urlencoded
  • client_id: <AZURE_APP_CLIENT_ID>
  • &client_secret: <AZURE_APP_CLIENT_ID>
  • &resource: https://<RESOURCEURL>/
  • &username: <USERNAME>
  • &password: <PASSWORD>
  • &grant_type: Password

Important

Make sure to include the trailing forward slash on the resource parameter. If not you will get an error related to Graph scopes when calling the virtual table.

From the response payload, copy the value of the access_token property. You can then pass this Bearer token as the part of the authorization header when making requests to the Virtual tables.

Screenshot shows the Power automate authorization.

Virtual tables error handling

Virtual tables error handling describes common error scenarios and how the virtual tables respond.

Attempt to create a virtual record without a Collaboration session

A valid collaboration session is required for every request to create a virtual record. When a virtual record is created, the virtual table creates a collaboration map record, which includes the virtual record primary key, entity name and the external ID that is, Graph resource ID. This collaboration map is associated with a collaboration session, and this is how the Collaboration controls keep track of the collaborations associated with a business record.

    HTTP/1.1 POST https://[Organization URI]/api/data/v9.0/m365_graphplannertasks  


{ 
    "m365_planid": "{{planId}}", 
    "m365_title": "{{taskTitle}}", 
    "m365_duedatetime": "2022-05-04T08:00:00Z", 
    "m365_assignments": "{\"me\":{\"orderHint\":\" !\",\"@odata.type\":\"#microsoft.graph.plannerAssignment\"}}" 
}

The collaborationRootId property is missing from the request.

To resolve this issue, you must always provide a valid collaborationRootId property when creating a virtual record.

Attempt to read a virtual record without a Collaboration map

Virtual tables allow you to execute requests, which return collections of virtual records. We saw this earlier in this document where we requested all the planner tasks associated with a specific collaboration session. It's also possible to request all the planner tasks associated with a specific planner plan by using a $filter system query like this: $filter=m365_planid eq{{planId}}. One issue that happens if you use such a query is that records are returned for planner tasks, which aren't associated with a collaboration session that is, planner tasks that were created by a means other than using a Collaboration control. If you attempt to read, update, or delete such a record, the request fails because the virtual table can't find the associated collaboration map.

    HTTP/1.1 GET https://[Organization URI]/api/data/v9.0/m365_graphplannertasks({{plannerTaskId}})

The plannerTaskId property is associated with a planner task, which was created using the Planner web interface and so doesn't have a collaboration map record.

To resolve this issue, you must check the error message in the response and if it's set to the message shown above this means the virtual record isn't associated. To create an association for this record, you must call Associate Collaboration Map - REST API.

Attempt to read a virtual record and the Graph resource has been deleted

Related to the previous error, you need to handle the case where a Graph resource has been deleted but the client still has a reference to the deleted virtual record. This can happen if another user deleted the record. If you attempt to read, update, or delete such a record, the request fails because the virtual table can't retrieve the resource from Graph.

    HTTP/1.1 GET https://[Organization URI]/api/data/v9.0/m365_graphplannertasks({{plannerTaskId}})

The plannerTaskId property is associated with a planner task, which was deleted.

This case must be handled by any client code, which retrieves virtual records as another user can delete the associated Graph resource at any time.

Attempt to update a virtual record with an invalid @odata.etag

The @odata.etag property is used for data concurrency and to prevent the over writing of the same record if it has been updated by another user. When, a record is read the current etag is returned, and remains valid until the record is changed. The etag should be included in any update request and are checked before the operation completes. If the record was changed by another user since the current user read the record, then the current users update request fails.

If you perform two updates requests using the same @odata.etag, then the second request fails:

    HTTP/1.1 PATCH https://[Organization URI]/api/data/v9.0/m365_graphplannertasks({{plannerTaskId}})

Header: If-Match: {{@odata.etag}}

{
    "m365_title": "{{$planTitle}}" 
}

Querying for Associated Virtual Records

In Task 5 of above, described how to Retrieve Associated Planner Tasks. This operation is supported for all of the virtual tables. When executing this request, you must include a $filter query, which specifies the Collaboration Root ID as shown below:

    HTTP/1.1 GET https://[Organization URI]/api/data/v9.0/ m365_graphplannertasks?$filter=m365_collaborationrootid eq '{{collaborationRootId}}'&$select=m365_graphplannertaskid,m365_title,m365_createddatetime
  • Other filtering options can't be combined with this $filter query and if there they'll be ignored.
  • Other filtering must be performed directly on the response from the request.

Querying for Virtual records with required key attributes

When, Dataverse Web API is called to retrieve multiple records from the following virtual tables a mandatory key attribute is required. Graph Booking Appointments requires a valid m365_bookingbusinessid is included in the query. If the key attribute isn't provided, then the request fails as follows:

    HTTP/1.1 400 Bad Request 


{ 
  "error": { 
    "code": "0x80048d0b", 
    "message": "Key attribute is missing: 'm365_bookingbusinessid'.", 
    ….
  } 
}

To fix this problem, change the request to this format:

    HTTP/1.1 GET https://[Organization URI]/api/data/v9.0/ m365_graphbookingappointments?$filter=m365_bookingbusinessid eq '{{bookingBusinessId}}'

Creating virtual records and Graph access control

The virtual tables honor the access control specified for Microsoft Graph. The virtual tables won't permit operations that the user couldn't perform using the Microsoft Graph API. For example, if the user you use to create the Plan is Task 3 and isn't a member of group you use then you get 403 Forbidden responses.

See also