Dataverse Integration

Analogy categoryAnalogies

Why Dataverse Instead of SharePoint Lists

Dataverse is Microsoft's structured business data platform underlying Dynamics 365 and Power Apps model-driven apps, and it offers relational tables with true foreign-key relationships, enforced column types, business rules, and row-level security roles, in contrast to SharePoint lists, which are better suited to lightweight, document-centric scenarios. When a process needs referential integrity (an Order row that cannot exist without a valid Customer row), calculated or rollup columns computed by the platform itself, or fine-grained security based on business unit hierarchy, Dataverse is almost always the better foundation, and Power Automate's Dataverse connector triggers ('When a row is added, modified or deleted') support filtering by specific columns changed, not just any change to the row.

🏏

Cricket analogy: Enforced foreign-key relationships in Dataverse are like a scorecard system that refuses to log a wicket without a valid bowler and batsman already registered in the match database, unlike a loose spreadsheet that would accept any typo.

Triggers, Filtering, and the Common Data Service Actions

The 'When a row is added, modified or deleted' trigger can be scoped with a 'Filter rows' OData expression and, critically, a 'Filtering attributes' setting that restricts the trigger to fire only when specific columns change, which avoids the common problem of a flow re-running every time any unrelated field on a busy table gets touched. Actions like 'Add a new row', 'Update a row', and 'List rows' operate against the table's logical schema name (for example, 'account' or a custom table's 'cr123_myentity'), not its display name, and 'List rows' supports server-side OData $filter, $select, $orderby, and $top parameters directly in the action, which is significantly more efficient than retrieving all rows and filtering with Power Automate expressions afterward.

🏏

Cricket analogy: Filtering a trigger to specific column changes is like a scorer's alert system only pinging for a wicket or a boundary, not every single dot ball, cutting noise from a busy over.

Business Rules, Plugins, and Flow Interaction

Dataverse table-level business logic (business rules, classic workflows, and synchronous plugins) runs inside the platform's own save pipeline and executes before a Power Automate cloud flow's asynchronous trigger fires, which means by the time your flow's 'When a row is added or modified' trigger runs, any calculated columns, default values, or business-rule validations have already been applied to the row. This ordering matters when designing complex automation: use synchronous plugins or business rules for anything that must complete before the save transaction commits (like blocking an invalid save), and use Power Automate cloud flows for anything that can happen slightly after the save, like sending notifications or calling external APIs.

Working with Relationships and Choices

Dataverse lookup columns (representing many-to-one relationships) are written using an OData bind syntax in the 'Add a new row' or 'Update a row' actions, referencing the related table's set name and primary key GUID, while choice columns (formerly option sets) store an underlying integer value even though the UI shows a label, so a flow comparing a choice column's value needs to compare against the numeric option value or use the '(Value)' dynamic content Power Automate exposes for readability. Many-to-many relationships, by contrast, aren't exposed as simple columns at all; associating two rows requires the dedicated 'Associate rows' action (or a direct Web API call to the relationship's navigation property), since there's no single row that owns the relationship.

🏏

Cricket analogy: A lookup column's OData bind syntax pointing to a GUID is like a scorecard referencing a specific player by their unique BCCI registration number rather than just writing a name that could be ambiguous across two players sharing it.

text

// 'Add a new row' action body for a Dataverse 'account' row referencing a lookup
{
  "name": "Contoso Manufacturing",
  "primarycontactid@odata.bind": "/contacts(3d128f2e-...-9a1c)",
  "statuscode": 1
}

// List rows OData query parameters
$filter=statecode eq 0 and revenue gt 100000
$select=name,revenue,primarycontactid
$orderby=revenue desc
$top=25

Because Dataverse triggers run asynchronously after the save pipeline (business rules, plugins) completes, a Power Automate flow that also writes back to the same row can retrigger itself; always add 'Filtering attributes' scoped to only the columns your business logic actually cares about, and consider a Condition checking 'Modified By' against the flow's own service principal or connection identity to prevent infinite loops.

Dataverse provides relational integrity, enforced types, business rules, and row-level security beyond what SharePoint lists offer.
'Filtering attributes' on the Dataverse trigger restricts firing to specific column changes, avoiding noisy re-triggers on unrelated edits.
'List rows' supports server-side $filter, $select, $orderby, and $top, far more efficient than retrieving everything and filtering client-side.
Business rules and synchronous plugins run inside the save pipeline before an async cloud flow trigger fires.
Lookup columns are written via OData bind syntax referencing the related row's GUID; choice columns store an integer under a display label.
Many-to-many relationships require the dedicated 'Associate rows' action since no single row owns that kind of relationship.
Guard against self-triggering loops by scoping filtering attributes narrowly and checking Modified By against the flow's own identity.