Edit

Filtered record ownership by using code (preview)

[This article is prerelease documentation and is subject to change.]

Filtered record ownership provides row-level data access based on conditions that evaluate column values. This article explains how developers can configure record filters by using the Dataverse SDK for .NET or Web API.

You don't need to write code to use this feature. Before using the examples in this article, learn the important concepts and how to configure the feature with Power Apps in Filtered record ownership.

How filtered record ownership works

A record filter contains a FetchXML query that defines which rows a user can access. An entity record filter associates the record filter with the table that the query targets. A security role then grants access to the filtered rows for one or more operations.

The following tables store the configuration:

Table Purpose
recordfilter entity type
Record Filter table		 Stores the display name, unique name, and FetchXML query that defines a filter.
entityrecordfilter entity type
EntityRecordFilter table		 Associates a record filter with a Dataverse table.

Filtered record ownership doesn't give a row an owner. Rows in a filtered record ownership table can't be assigned or shared. Access is the union of the filters granted through all security roles assigned to the user or their teams. The system administrator security role has the system-provided All records filter.

Prerequisites

  • A user with the system administrator security role must configure the feature and security roles.
  • An unmanaged solution and its unique name. All solution components created by the examples are associated with this solution.
  • Know the table and column logical names used in the FetchXML query. The examples use a custom table named new_store with a new_city column.

Create a filtered record ownership table

Set the table OwnershipType property to the new Filtered member when you create the table. Filtered has the value 32 and specifies that the table doesn't have an owner and data access privileges are based on a filter. You can't change the ownership type after you create the table.

The following examples create the new_Store table and the new_City column used by the other examples in this article. Tables and columns are solution components, so the examples use the SolutionUniqueName optional parameter to associate them with an unmanaged solution when they're created.

Set EntityMetadata.OwnershipType to OwnershipTypes.Filtered in a CreateEntityRequest.

var createTableRequest = new CreateEntityRequest
{
    SolutionUniqueName = "<Solution Unique Name>",
    Entity = new EntityMetadata
    {
        SchemaName = "new_Store",
        DisplayName = new Label("Store", 1033),
        DisplayCollectionName = new Label("Stores", 1033),
        Description = new Label("Stores organized by city.", 1033),
        OwnershipType = OwnershipTypes.Filtered,
        IsActivity = false
    },
    PrimaryAttribute = new StringAttributeMetadata
    {
        SchemaName = "new_Name",
        DisplayName = new Label("Name", 1033),
        RequiredLevel = new AttributeRequiredLevelManagedProperty(
            AttributeRequiredLevel.None),
        MaxLength = 100,
        FormatName = StringFormatName.Text
    }
};

service.Execute(createTableRequest);

var createCityColumnRequest = new CreateAttributeRequest
{
    SolutionUniqueName = "<Solution Unique Name>",
    EntityName = "new_store",
    Attribute = new StringAttributeMetadata
    {
        SchemaName = "new_City",
        DisplayName = new Label("City", 1033),
        RequiredLevel = new AttributeRequiredLevelManagedProperty(
            AttributeRequiredLevel.None),
        MaxLength = 100,
        FormatName = StringFormatName.Text
    }
};

service.Execute(createCityColumnRequest);

For more information, see Create a custom table using the SDK for .NET.

Apply record filter to existing user or team and Organization record ownership tables

While the record filter is the only row-level data access for the new Filtered record ownership table, the following record filter can also be used in existing user/team and organization record ownership tables.

Create a record filter

The following examples create a record filter that selects rows where the new_city value is Redmond. The FetchXML root table must match the table you associate with the filter.

Use a CreateRequest so that you can set the SolutionUniqueName optional parameter when you create the recordfilter row.

string fetchXml = """
    <fetch>
      <entity name="new_store">
        <filter type="and">
          <condition attribute="new_city" operator="eq" value="Redmond" />
        </filter>
      </entity>
    </fetch>
    """;

Entity recordFilter = new("recordfilter")
{
    ["displayname"] = "Stores in Redmond",
    ["uniquename"] = "new_storesinredmond",
    ["fetchxml"] = fetchXml
};

  CreateRequest createRecordFilterRequest = new()
  {
    Target = recordFilter
  };
  createRecordFilterRequest["SolutionUniqueName"] = "<Solution Unique Name>";

  CreateResponse createRecordFilterResponse =
    (CreateResponse)service.Execute(createRecordFilterRequest);
  Guid recordFilterId = createRecordFilterResponse.id;

Associate the filter with a table

Create an entity record filter to associate the record filter with the table. Set objecttypecode to the table logical name and recordfilterid to the record filter you created in the previous step.

Entity entityRecordFilter = new("entityrecordfilter")
{
    ["name"] = "Stores in Redmond",
    ["objecttypecode"] = "new_store",
    ["recordfilterid"] = new EntityReference("recordfilter", recordFilterId)
};

  CreateRequest createEntityRecordFilterRequest = new()
  {
    Target = entityRecordFilter
  };
  createEntityRecordFilterRequest["SolutionUniqueName"] = "<Solution Unique Name>";

  CreateResponse createEntityRecordFilterResponse =
    (CreateResponse)service.Execute(createEntityRecordFilterRequest);
  Guid entityRecordFilterId = createEntityRecordFilterResponse.id;

Retrieve filters for a table

Retrieve the entity record filters for a table to inspect the active configuration. The following examples also return values from the related record filter.

QueryExpression query = new("entityrecordfilter")
{
    ColumnSet = new ColumnSet("name", "objecttypecode", "statecode"),
    Criteria = new FilterExpression(LogicalOperator.And)
    {
        Conditions =
        {
            new ConditionExpression("objecttypecode", ConditionOperator.Equal, "new_store"),
            new ConditionExpression("statecode", ConditionOperator.Equal, 0)
        }
    }
};

LinkEntity recordFilterLink = query.AddLink(
    "recordfilter",
    "recordfilterid",
    "recordfilterid");
recordFilterLink.EntityAlias = "recordfilter";
recordFilterLink.Columns = new ColumnSet("displayname", "uniquename", "fetchxml");

EntityCollection results = service.RetrieveMultiple(query);

Grant access with security roles

After creating the record filter and entity record filter, grant the filter for the required Create, Read, Write, Delete, Append, or Append to table privileges in a security role. Assign the role to users or teams. For the configuration steps, see Create and assign a security role.

Filter access from all roles is cumulative. Test with a nonadministrator account because system administrators receive the All records filter and don't experience the same row restrictions.

Manage filter records

Use the standard create, retrieve, update, and delete operations supported by the Record Filter table and EntityRecordFilter table. Deleting a record filter also deletes its associated entity record filters. Before changing or removing a filter, review every role that grants it to avoid unintended changes in data access.

Limitations

For current limitations, including behavior for linked tables and Dataverse Search, see Filtered record ownership limitations.

  • Last updated on