Saturday, May 16, 2026

Microsoft Teams Development & Extensibility Complete Guide

Microsoft Teams Development & Extensibility — Complete Guide

Tabs · Bots · Messaging Extensions · Adaptive Cards · Meeting Apps · Live Share · SSO · Teams Toolkit · Scenarios · Cheat Sheet


Table of Contents

  1. Core Concepts — Basics
  2. Tabs & Bots
  3. Messaging Extensions & Adaptive Cards
  4. Meeting Extensions & Live Share
  5. Teams Toolkit, SSO & Deployment
  6. Scenario-Based Questions
  7. Cheat Sheet — Quick Reference

1. Core Concepts — Basics

What are the extensibility points in Microsoft Teams?

Microsoft Teams offers multiple extensibility points for embedding custom apps directly into the Teams client.

Extensibility Point Description Use Case
Tabs Web pages embedded as tabs in channels, chats, or personal app Dashboards, project views, reports
Bots Conversational agents in chats, channels, or personal scope Notifications, workflows, Q&A
Messaging extensions Interact with apps from the compose box or message actions Search CRM, create tickets, insert cards
Meeting extensions In-meeting panels, stage sharing, pre/post-meeting tabs Polls, agendas, collaborative whiteboards
Adaptive Cards Rich interactive cards rendered natively in Teams Approvals, forms, notifications
Connectors / Webhooks Post messages from external systems into channels CI/CD alerts, monitoring notifications
Task modules (Dialogs) Modal pop-ups embedding web pages or Adaptive Cards Complex forms, confirmations, media
Link unfurling Rich card preview when a URL is pasted into Teams Internal app URL previews

Tip: All capabilities are packaged in a single Teams app with one manifest. One app can include a tab + bot + messaging extension — all deployable together.


What is the Teams App Manifest?

The Teams App Manifest (manifest.json) is a JSON file defining the app's identity, capabilities, permissions, and entry points. Packaged with icons into a .zip file for deployment.

{
  "$schema": "https://developer.microsoft.com/json-schemas/teams/v1.17/MicrosoftTeams.schema.json",
  "manifestVersion": "1.17",
  "version": "1.0.0",
  "id": "{{APP_GUID}}",
  "name": { "short": "My App", "full": "My Full App Name" },
  "description": { "short": "Short desc", "full": "Full description" },
  "icons": { "color": "color.png", "outline": "outline.png" },
  "accentColor": "#FFFFFF",
  "staticTabs": [...],
  "configurableTabs": [...],
  "bots": [...],
  "composeExtensions": [...],
  "permissions": ["identity", "messageTeamMembers"],
  "validDomains": ["yourapp.azurewebsites.net"],
  "webApplicationInfo": {
    "id": "{{ENTRA_APP_ID}}",
    "resource": "api://yourapp.azurewebsites.net/{{ENTRA_APP_ID}}"
  },
  "authorization": {
    "permissions": {
      "resourceSpecific": [
        { "name": "ChannelMessage.Read.Group", "type": "Application" }
      ]
    }
  }
}
App package structure:
myapp.zip
  ├── manifest.json
  ├── color.png      (192×192 px full-colour icon)
  └── outline.png    (32×32 px transparent outline icon)

What is the Teams JavaScript SDK?

The Microsoft Teams JavaScript SDK (@microsoft/teams-js) enables web content embedded in Teams to interact with the Teams host client.

import { app, authentication, pages } from '@microsoft/teams-js';

// MUST be called first — initialises host communication:
await app.initialize();

// Get Teams context — rich environment information:
const context = await app.getContext();
console.log(context.user.displayName);    // signed-in user
console.log(context.team.displayName);    // current team name
console.log(context.channel.id);          // current channel ID
console.log(context.app.locale);          // en-US, de-DE, etc.
console.log(context.app.theme);           // default | dark | contrast
console.log(context.meeting.id);          // meeting ID (if in meeting)
console.log(context.page.frameContext);   // content | sidePanel | meetingStage

// Register theme change handler:
app.registerOnThemeChangeHandler((theme) => {
  document.body.className = theme; // 'default' | 'dark' | 'contrast'
});

// Silent SSO token:
const token = await authentication.getAuthToken();
// Exchange on backend via OBO flow for Graph token

// Tab configuration (configurable tabs):
pages.config.setConfig({
  contentUrl: 'https://yourapp.com/tab/content?project=123',
  entityId: 'project-123',
  suggestedDisplayName: 'Project Dashboard'
});
pages.config.setValidityState(true); // enable Save button

2. Tabs & Bots

What are the types of Teams tabs?

1. Static tabs (personal scope):
→ Appear in the personal app left rail
→ Fixed URL — no configuration dialog
→ Each user sees their own personalised view
→ Use for: personal dashboards, task lists, profile views
→ Manifest: "staticTabs" array, scopes: ["personal"]

2. Configurable tabs (channel/group scope):
→ Added to Teams channels or group chats
→ Shows a configuration page when first added
→ Tab creator sets options — which project, report, or view
→ After config: URL fixed for that channel for all members
→ Use for: team dashboards, project views, shared resources
→ Manifest: "configurableTabs" array, scopes: ["team", "groupchat"]

3. Meeting tabs:
→ Tabs within a Teams meeting context
→ Pre-meeting, in-meeting side panel, post-meeting
→ Manifest: context = ["meetingSidePanel", "meetingStage", "meetingDetailsTab"]
// Static tab:
"staticTabs": [{
  "entityId": "personalDashboard",
  "name": "My Dashboard",
  "contentUrl": "https://yourapp.com/tab/personal",
  "websiteUrl": "https://yourapp.com",
  "scopes": ["personal"]
}]

// Configurable tab:
"configurableTabs": [{
  "configurationUrl": "https://yourapp.com/tab/config",
  "canUpdateConfiguration": true,
  "scopes": ["team", "groupchat"]
}]

What is a Teams Bot and how is it built?

Teams bots are conversational agents built on the Azure Bot Framework that process messages and events from Teams.

Architecture:
User in Teams → Teams service → Bot Framework Service → Your Bot Web App
                              ← Bot sends response ←

Bot registration steps:
1. Create Azure Bot resource (Bot Framework registration)
2. Generate App ID + Secret (or use Managed Identity)
3. Add Teams channel in Azure Bot configuration
4. Set messaging endpoint: https://yourbot.azurewebsites.net/api/messages
5. Register bot in manifest "bots" array
// Node.js — Teams Bot:
const { TeamsActivityHandler } = require('botbuilder');

class MyTeamsBot extends TeamsActivityHandler {
  constructor() {
    super();

    // Handle incoming messages:
    this.onMessage(async (context, next) => {
      const text = context.activity.text?.trim();
      if (text?.includes('hello')) {
        await context.sendActivity('Hello! How can I help you today? 👋');
      }
      await next();
    });

    // Handle member added to team/chat:
    this.onMembersAdded(async (context, next) => {
      for (const member of context.activity.membersAdded) {
        if (member.id !== context.activity.recipient.id) {
          await context.sendActivity(`Welcome ${member.name}! 🎉`);
        }
      }
      await next();
    });

    // Handle Teams-specific events:
    this.onTeamRenamed(async (context, next) => {
      await context.sendActivity(`Team renamed to: ${context.activity.channelData.team.name}`);
      await next();
    });
  }
}

Bot activity types in Teams:

Activity Trigger
onMessage User sends a message to bot
onMembersAdded User joins team/chat
onTeamRenamed Team is renamed
onChannelCreated New channel created
onMessageReaction User reacts to bot message
onMeetingStart Meeting begins
onMeetingEnd Meeting ends

What are proactive messages?

Proactive messages are sent by a bot without the user first messaging the bot — for notifications and alerts.

// Step 1: Store conversation reference when user first interacts:
this.onMessage(async (context, next) => {
  const ref = TurnContext.getConversationReference(context.activity);
  await storeConversationRef(context.activity.from.id, ref); // save to DB
  await next();
});

// Step 2: Send proactive message later (e.g., from a timer or webhook):
const ref = await loadConversationRef(userId);
await adapter.continueConversationAsync(
  process.env.MicrosoftAppId,
  ref,
  async (proactiveContext) => {
    await proactiveContext.sendActivity(
      MessageFactory.text('🔔 Your monthly report is ready!')
    );
  }
);

// Proactive message to a Teams channel:
const params = {
  isGroup: true,
  channelData: { channel: { id: channelId } },
  activity: MessageFactory.text('📢 Daily standup reminder!')
};
await TeamsInfo.sendMessageToTeamsChannel(context, params, appId, adapter);

Warning: You cannot proactively message a user who has never installed or interacted with your bot. Always handle missing conversation references gracefully.


3. Messaging Extensions & Adaptive Cards

What are messaging extensions?

Messaging extensions let users interact with external services from the Teams compose box or message actions menu.

1. Search commands (query type):
User types in compose box → app searches backend → returns list of cards
→ User selects card → inserts into conversation
→ Use for: searching CRM contacts, Jira tickets, KB articles

2. Action commands:
User right-clicks a message → More actions → your action
→ Opens task module dialog → user fills form → bot processes
→ Use for: create bug from message, file expense, log incident

3. Link unfurling:
User pastes a URL matching your domain
→ Teams calls your app → you return rich Adaptive Card preview
→ Use for: internal app URLs, ticket links, document previews
// Search command manifest:
"composeExtensions": [{
  "botId": "{{BOT_ID}}",
  "commands": [{
    "id": "searchTickets",
    "type": "query",
    "title": "Search Tickets",
    "description": "Search Jira tickets",
    "parameters": [{
      "name": "searchQuery",
      "title": "Search term",
      "description": "Ticket number or keyword"
    }]
  }]
}]
// Search command handler:
async handleTeamsMessagingExtensionQuery(context, query) {
  const searchTerm = query.parameters[0].value;
  const tickets = await searchJira(searchTerm);

  return {
    composeExtension: {
      type: 'result',
      attachmentLayout: 'list',
      attachments: tickets.map(ticket => ({
        contentType: 'application/vnd.microsoft.card.adaptive',
        content: buildAdaptiveCard(ticket),
        preview: {
          contentType: 'application/vnd.microsoft.card.thumbnail',
          content: { title: ticket.summary, text: ticket.key }
        }
      }))
    }
  };
}

What are Adaptive Cards?

Adaptive Cards are platform-agnostic JSON-defined UI components rendered natively in Teams (and Outlook, Windows).

{
  "type": "AdaptiveCard",
  "version": "1.5",
  "body": [
    {
      "type": "TextBlock",
      "text": "🎫 Support Ticket #4521",
      "weight": "Bolder",
      "size": "Medium"
    },
    {
      "type": "FactSet",
      "facts": [
        { "title": "Priority", "value": "High" },
        { "title": "Assigned To", "value": "Alice Johnson" },
        { "title": "Status", "value": "Open" }
      ]
    },
    {
      "type": "Input.Text",
      "id": "comment",
      "placeholder": "Add a comment...",
      "isMultiline": true
    }
  ],
  "actions": [
    {
      "type": "Action.Execute",
      "title": "✅ Approve",
      "verb": "approve",
      "data": { "ticketId": "4521" }
    },
    {
      "type": "Action.Execute",
      "title": "❌ Reject",
      "style": "destructive",
      "verb": "reject",
      "data": { "ticketId": "4521" }
    },
    {
      "type": "Action.OpenUrl",
      "title": "View in Portal",
      "url": "https://helpdesk.contoso.com/ticket/4521"
    }
  ]
}

Adaptive Card elements:

Category Elements
Text TextBlock, RichTextBlock
Inputs Input.Text, Input.Number, Input.Date, Input.Time, Input.Toggle, Input.ChoiceSet
Layout Container, ColumnSet, Column, ActionSet, Table
Media Image, Media
Actions Action.Submit, Action.Execute, Action.OpenUrl, Action.ShowCard

Tip: Action.Execute (Universal Actions) updates the card in place after user interaction — bot handles the verb and returns an updated card replacing the original. Eliminates the "stale card" problem.


What are Task Modules (Dialogs)?

Task Modules are modal pop-up windows in Teams embedding either a web page or Adaptive Card.

// Bot opens a task module when card action is clicked:
async handleTeamsTaskModuleFetch(context, taskModuleRequest) {
  const taskInfo = taskModuleRequest.data;

  return {
    task: {
      type: 'continue',
      value: {
        title: 'Create Support Ticket',
        height: 600,
        width: 800,
        url: `https://yourapp.com/create-ticket?msgId=${taskInfo.messageId}`
        // OR: card: { type: 'AdaptiveCard', ... }
      }
    }
  };
}

// Handle task module submission:
async handleTeamsTaskModuleSubmit(context, taskModuleRequest) {
  const { title, priority, description } = taskModuleRequest.data;
  const ticket = await createTicket({ title, priority, description });

  return {
    task: {
      type: 'message',
      value: `✅ Ticket #${ticket.id} created successfully!`
    }
  };
}

Use cases:

  • Multi-step approval workflows with rich forms
  • Complex data entry that doesn't fit in a card
  • Confirming destructive actions before execution
  • Rich media display (video player, image gallery)

4. Meeting Extensions & Live Share

What are Teams meeting extensibility features?

1. In-meeting side panel:
→ Tab appearing on right side of active meeting
→ Users interact without leaving the meeting view
→ Use for: live polls, Q&A, collaborative notes, agendas

2. Meeting stage (Share to Stage):
→ App content shared to the full meeting screen
→ All participants see the same view simultaneously
→ Use for: whiteboards, shared documents, presentations

3. Pre-meeting tab:
→ Tab in meeting details before meeting starts
→ Use for: agenda setting, pre-meeting polls, document prep

4. Post-meeting tab:
→ Tab accessible after meeting ends
→ Use for: meeting summaries, action items, follow-up

5. Meeting bot:
→ Bot participating in meeting context
→ Can receive meeting events, transcribe, send to meeting chat
// Meeting tab manifest:
"configurableTabs": [{
  "configurationUrl": "https://yourapp.com/config",
  "canUpdateConfiguration": true,
  "scopes": ["groupchat"],
  "context": [
    "meetingChatTab",
    "meetingSidePanel",
    "meetingStage",
    "meetingDetailsTab"
  ]
}]
// Get meeting context in tab:
const context = await app.getContext();
const meetingId = context.meeting?.id;
const frameContext = context.page.frameContext;
// frameContext values: 'content' | 'sidePanel' | 'meetingStage'

// Share app to meeting stage (from side panel):
import { meeting } from '@microsoft/teams-js';
await meeting.shareAppContentToStage(
  (err) => { if (err) console.error(err); },
  'https://yourapp.com/stage-view'
);

What is the Live Share SDK?

Live Share enables real-time synchronised collaborative experiences during Teams meetings — no backend infrastructure needed for synchronisation.

import { LiveShareClient, LiveState, LivePresence } from '@microsoft/live-share';
import { LiveShareHost } from '@microsoft/teams-js';

// Connect to the meeting's Live Share session:
const host = LiveShareHost.create();
const client = new LiveShareClient(host);

const schema = {
  initialObjects: {
    currentSlide: LiveState,
    presence: LivePresence,
  }
};

const { container } = await client.joinContainer(schema);
const { currentSlide, presence } = container.initialObjects;

// Initialise shared state:
await currentSlide.initialize(0);

// Listen for remote state changes:
currentSlide.on('stateChanged', (state, local) => {
  if (!local) {
    updateSlideView(state.data); // another participant changed slide
  }
});

// Change state (broadcasts to all participants):
await currentSlide.set(newSlideIndex);

// Track participant presence:
await presence.initialize({ name: 'Alice', cursor: { x: 0, y: 0 } });
presence.on('presenceChanged', (userPresence, local) => {
  updateCursor(userPresence); // show all participants' cursors
});

Live Share use cases:

Scenario SDK Feature
Synchronised video playback LiveState (position + playing state)
Collaborative whiteboard LivePresence (cursors) + Fluid SharedMap (strokes)
Live quiz/polling LiveState (current question + answers)
Shared slide presentation LiveState (current slide index)
Real-time code review LivePresence (highlights) + Fluid SharedString

Tip: Live Share uses Azure Fluid Relay under the hood — but developers don't provision any Azure infrastructure. The Teams meeting session automatically provides the relay service.


5. Teams Toolkit, SSO & Deployment

What is Teams Toolkit?

Teams Toolkit is a VS Code extension (and CLI) simplifying building, debugging, and deploying Teams apps.

Key capabilities:

  1. Project scaffolding: templates for tab (React/Blazor), bot (Node.js/C#), messaging extension, notification bot, workflow bot, SPFx tab, AI chat bot.
  2. Local development: auto-starts dev tunnel to expose local server — no manual ngrok setup.
  3. Manifest management: variable substitution ({{TEAMS_APP_ID}}, {{BOT_ID}}) across environments (local, dev, prod).
  4. Provisioning: one-command Azure resource creation — App Service, Bot, Entra app registration all wired together.
  5. Deployment: deploy to Azure App Service or Azure Functions from VS Code.
  6. Teams Developer Portal integration: publish to org catalogue or generate sideload package.
# teamsapp.yml (Teams Toolkit v5 pipeline):
provision:
  - uses: aadApp/create           # create Entra app registration
  - uses: botFramework/create     # create Azure Bot
  - uses: azureAppService/zipDeploy # provision App Service
  - uses: teamsApp/create         # create Teams app in developer portal

deploy:
  - uses: cli/runNpmCommand
    with: { args: "run build" }
  - uses: azureAppService/zipDeploy

publish:
  - uses: teamsApp/publish        # publish to org catalogue

How does SSO work in a Teams Tab?

Teams SSO lets tabs silently obtain a token for the signed-in Teams user — no login prompt.

SSO flow:
1. Tab calls:  authentication.getAuthToken()
2. Teams host retrieves ID token from Entra ID silently
   (audience = your Entra app ID, NOT Microsoft Graph)
3. Tab sends this token to its backend server
4. Backend exchanges via On-Behalf-Of (OBO) flow:
   POST https://login.microsoftonline.com/{tenantId}/oauth2/v2.0/token
   {
     grant_type: 'urn:ietf:params:oauth:grant-type:jwt-bearer',
     client_id:  '{{ENTRA_APP_ID}}',
     client_secret: '{{CLIENT_SECRET}}',
     assertion:  '{teams_sso_token}',        // token from step 3
     scope: 'https://graph.microsoft.com/User.Read',
     requested_token_use: 'on_behalf_of'
   }
5. Entra ID returns a Graph access token
6. Backend calls Microsoft Graph with the Graph token
7. Backend returns data to the tab

SSO prerequisites:

// 1. Entra app registration: expose an API
//    Application ID URI: api://{hostname}/{entraAppId}
//    Scope: access_as_user
//    Authorised clients: Teams desktop (5e3ce6c0-...), Teams web (1fec8e78-...)

// 2. manifest.json webApplicationInfo:
"webApplicationInfo": {
  "id": "{{ENTRA_APP_ID}}",
  "resource": "api://yourapp.azurewebsites.net/{{ENTRA_APP_ID}}"
}

// 3. Tab code:
const token = await authentication.getAuthToken();
const graphData = await fetch('/api/userProfile', {
  headers: { Authorization: `Bearer ${token}` }
});

Critical: The SSO token from Teams is NOT a Graph token. It is an ID token for your app — it must be exchanged via the OBO flow on the backend to get a real Graph token. This two-step exchange is the most tested SSO concept in.


What is Resource-Specific Consent (RSC)?

RSC allows Teams apps to request permissions scoped to a specific team or chat — without tenant-wide admin consent.

RSC vs tenant-wide permissions:
Tenant-wide (Graph application permission):
  User.Read.All → read ALL users in tenant
  Requires: Global Admin consent
  Scope: entire tenant

RSC permission:
  TeamMember.Read.Group → read members of THIS team only
  Requires: team owner consent during app install
  Scope: only the specific team where app is installed

Team RSC permissions:
  TeamSettings.Read.Group      → read team settings
  TeamMember.Read.Group        → read team members
  ChannelMessage.Read.Group    → read channel messages
  ChannelMessage.Send.Group    → send to channels
  File.Read.Group              → read team's SharePoint files

Chat RSC permissions:
  ChatSettings.Read.Chat       → read chat settings
  ChatMember.Read.Chat         → read chat members
  ChatMessage.Send.Chat        → send to this chat
// manifest.json:
"authorization": {
  "permissions": {
    "resourceSpecific": [
      { "name": "ChannelMessage.Read.Group", "type": "Application" },
      { "name": "TeamMember.Read.Group", "type": "Application" }
    ]
  }
}

How do you deploy and govern Teams apps in an enterprise?

1. Build package: manifest.json + icons → .zip via Teams Toolkit

2. Publish to org catalogue:
   Teams Admin Centre → Teams apps → Manage apps → Upload an app

3. App permission policies:
   Control which apps users can install:
   → Microsoft apps | Third-party apps | Custom org apps
   Create policy restricting users to approved apps only

4. App setup policies:
   → Pin apps to Teams left rail for all users (or specific groups)
   → Auto-install apps without user action
   → Use for: company-wide tools, mandatory apps

5. Admin consent for permissions:
   → Graph permissions: admin grants consent in Teams admin centre
   → RSC permissions: team owners consent during install

6. CI/CD pipeline using Teams Toolkit CLI:
npm install @microsoft/teamsfx-cli
teamsfx provision --env production
teamsfx deploy --env production
teamsfx publish --env production

7. Teams Developer Portal:
   developer.microsoft.com/en-us/microsoft-365/teams
   → Manage apps, validate manifests, analytics

6. Scenario-Based Questions

Scenario: Build a Teams app to raise support tickets from a message.

  1. Messaging extension — action command: add "Create Ticket" to message actions menu (right-click → More actions → Create Ticket).

  2. Task module: clicking "Create Ticket" opens a modal with a form — title pre-filled from message text, priority dropdown, description field.

  3. Bot handles submission: handleTeamsTaskModuleSubmit receives form data → calls helpdesk API → creates ticket.

  4. Adaptive Card confirmation: bot sends card to conversation confirming creation with ticket number, assignee, priority, and portal link.

  5. Proactive updates: when ticket status changes (resolved, escalated) → use stored conversation reference to proactively notify the user.

  6. Universal Actions (Action.Execute): card includes "Add Comment" and "Escalate" — bot handles verb and returns updated card in place without creating a new message.

Tip: This combines four Teams features: messaging extension + task module + bot + Adaptive Card Universal Actions. Knowing how these connect is the clearest signal of real Teams development experience.


Scenario: Deploy and govern Teams apps in an enterprise.

  1. Packaging: Teams Toolkit manages per-environment manifests with variable substitution.
  2. Org catalogue: upload to Teams Admin Centre → available to all users without sideloading.
  3. Permission policies: restrict users to org-approved apps only; block consumer/third-party apps on specific groups.
  4. Setup policies: pin the app to the left rail for all users, auto-install in targeted departments via M365 group-based assignment.
  5. Consent: Graph permissions → admin consent in Teams Admin Centre. RSC permissions → team owners consent on install.
  6. CI/CD: Azure DevOps pipeline using teamsfx CLI — provision → deploy → publish as a single automated pipeline.
  7. Monitoring: Teams Developer Portal analytics — active users, install counts, errors. Application Insights for backend telemetry.

Scenario: Build a notification bot sending daily standup reminders.

  1. Bot type: notification bot (Teams Toolkit template). Azure Functions timer trigger — cron: 0 0 9 * * 1-5 (9am Mon–Fri).

  2. Installation: when bot is installed in a team → store conversation reference (teamId + channelId + serviceUrl) in Azure Table Storage.

  3. Adaptive Card reminder with Input.Text fields for yesterday/today/blockers + Action.Submit button.

  4. Response aggregation: when user submits → bot collects all submissions. After timeout or full response → posts summary card to channel.

  5. Deployment: Azure Function App (Consumption plan) + Azure Bot Service + Teams app package deployed via Teams Toolkit CI/CD.


Scenario: Teams Tab shows a blank screen in Teams but works in the browser. How do you debug?

  1. X-Frame-Options header (most common cause): check if your server returns X-Frame-Options: DENY or SAMEORIGIN. Teams loads tabs in iframes — these headers block embedding. Replace with CSP:

    Content-Security-Policy: frame-ancestors teams.microsoft.com *.teams.microsoft.com *.skype.com
    
  2. HTTPS required: Teams only loads HTTPS URLs. Local HTTP = blank screen. Use Teams Toolkit's dev tunnel or ngrok.

  3. Missing app.initialize(): must be called before any Teams JS SDK calls. Without it, SDK cannot communicate with the Teams host.

  4. Domain not in validDomains: ALL domains loaded by your tab (CDN, API, fonts) must be in validDomains in the manifest. Unlisted domains are silently blocked.

  5. Cookie restrictions: Teams desktop webview blocks third-party cookies. Use localStorage instead of session cookies. Use MSAL token caching in memory.

  6. Debug in Teams client: right-click the tab → Inspect Element → DevTools. Check Console for JS errors and Network tab for blocked requests.

Tip: Always check the X-Frame-Options response header first — it's the single most common cause of blank Teams tabs. Check in browser Network tab before anything else.


7. Cheat Sheet — Quick Reference

Teams Extensibility Quick Map

Need to...                                          → Use
Embed a web page in Teams channel/chat              → Configurable Tab
Build a personal app experience                     → Static Tab
Respond to messages conversationally                → Bot
Send notifications to users/channels               → Bot + Proactive Messages
Let users search external data in compose box      → Messaging Extension (Search)
Create records from Teams messages                  → Messaging Extension (Action) + Task Module
Show rich interactive cards                         → Adaptive Cards
Real-time collaboration during meetings             → Live Share SDK
Share app to full meeting screen                    → Meeting Stage
In-meeting sidebar experience                       → In-meeting Side Panel Tab
Post messages from external systems                 → Incoming Webhooks
Show rich preview when URL is pasted               → Link Unfurling
Authenticate with user's identity silently          → Teams SSO + OBO flow
Access specific team data without admin consent     → RSC permissions

Manifest Sections Quick Reference

{
  "staticTabs": [],          // personal scope tabs
  "configurableTabs": [],    // channel/chat/meeting tabs
  "bots": [],                // bot configuration
  "composeExtensions": [],   // messaging extensions
  "permissions": [],         // identity, messageTeamMembers
  "validDomains": [],        // ALL domains your app loads
  "webApplicationInfo": {},  // Entra app ID for SSO
  "authorization": {         // RSC permissions
    "permissions": {
      "resourceSpecific": []
    }
  }
}

Adaptive Card Actions

Action.Submit:    sends form data to bot (classic — creates new activity)
Action.Execute:   Universal Actions — server updates card in-place (preferred)
Action.OpenUrl:   opens URL in browser
Action.ShowCard:  reveals a nested card inline

Universal Action flow (Action.Execute):
User clicks → Teams sends invoke to bot
Bot handles verb → returns updated AdaptiveCard body
Teams replaces the card in place — no new message created

SSO Flow Summary

Tab → authentication.getAuthToken() → Teams ID token (audience = app)
                                            ↓
Backend receives Teams token → OBO flow to Entra ID
  POST /oauth2/v2.0/token
  grant_type: jwt-bearer
  assertion: {teams_id_token}
  scope: https://graph.microsoft.com/User.Read
                                            ↓
Backend receives Graph access token → call Microsoft Graph
                                            ↓
Return Graph data to tab

Key: Teams SSO token ≠ Graph token
     Must exchange via OBO flow on the server side

Bot Message Types

sendActivity(text)                → plain text message
sendActivity(MessageFactory.attachment(card)) → Adaptive Card
sendActivity(MessageFactory.carousel([...]))  → carousel of cards
sendActivity(MessageFactory.list([...]))      → list of cards
sendActivity(MessageFactory.suggestedActions(text, ['Yes','No'])) → quick replies

Proactive message flow:
1. Store TurnContext.getConversationReference() when user first messages
2. Later: adapter.continueConversationAsync(appId, ref, handler)
3. In handler: context.sendActivity(...)

RSC vs Tenant-Wide Permissions

RSC (Resource-Specific Consent):
→ Consent: team owner (during app install)
→ Scope: only that specific team/chat
→ No Global Admin needed
→ Examples: TeamMember.Read.Group, ChannelMessage.Read.Group

Tenant-wide (Graph application permissions):
→ Consent: Global Admin only
→ Scope: entire tenant
→ Examples: User.Read.All, Mail.Read (ALL mailboxes)
→ High risk — use RSC instead where possible

Top 10 Tips

  1. Know all 7 extensibility points — tabs, bots, messaging extensions, meeting apps, Adaptive Cards, webhooks, task modules. Be able to explain each and name a use case for each.

  2. Teams SSO token ≠ Graph token — the SSO token from authentication.getAuthToken() can only be used as an OBO assertion. It must be exchanged on the backend for a real Graph token. This two-step exchange is the most tested SSO concept.

  3. X-Frame-Options: DENY = blank tab — the single most common cause of blank Teams tabs. Always check this response header first when debugging a tab that works in browser but not in Teams.

  4. app.initialize() is mandatory — it must be the first Teams JS SDK call in every tab. Without it, all subsequent SDK calls fail silently.

  5. Action.Execute updates cards in place — Universal Actions allow the bot to return an updated card replacing the original, without creating a new message. Far better UX than Action.Submit which creates a new bot reply.

  6. Proactive messages require a stored conversation reference — you cannot proactively message a user without a stored reference from a previous interaction. Always capture and store conversation references when users first interact.

  7. RSC over tenant-wide permissions where possible — RSC requires only team owner consent and scopes to one team. Tenant-wide application permissions require Global Admin consent and access all tenant data. Least privilege principle.

  8. validDomains blocks silently — any domain not in validDomains in the manifest is silently blocked by Teams. Include ALL domains your tab loads — CDN, API endpoints, auth endpoints.

  9. Live Share = no backend synchronisation infrastructure — it uses Azure Fluid Relay transparently via the Teams meeting session. For any "real-time meeting collaboration" question, Live Share is the answer.

  10. Teams Toolkit manages the full lifecycle — scaffolding, local dev tunnelling, manifest variable substitution, Azure provisioning, deployment, and publishing. Knowing the teamsapp.yml lifecycle pipeline (provision → deploy → publish) shows production maturity.



Build a Conversation Transcript Viewer Power App for Copilot Studio

Copilot Studio stores every bot conversation as a JSON payload inside the ConversationTranscripts Dataverse table. That raw JSON contains every user message, every bot reply, timestamps, session metadata, and even GPT trace data — but there is no built-in UI to browse it meaningfully. In this guide, we build a fully functional 3-screen Power Apps canvas app that lets you filter sessions, browse turns, and read full conversation detail — all driven directly from Dataverse with no intermediate API.

📋 Table of Contents

  1. What Is the ConversationTranscripts Table?
  2. App Architecture Overview
  3. Collections and Global Variables
  4. Screen 1 — Session List
  5. Screen 2 — Conversation Detail
  6. Screen 3 — Turn Detail
  7. Parsing the JSON Activities Array
  8. Matching User Prompts to Bot Responses
  9. Common Issues and Fixes
  10. Full Formula Reference

1. What Is the ConversationTranscripts Table?

Every time a user has a conversation with a Copilot Studio bot, a new record is created in the ConversationTranscripts Dataverse table. Each record contains a Content column that holds the full conversation as a JSON string. The JSON has an activities array — each activity is either a message, event, or trace.

Key columns used in this app:

ColumnTypePurpose
NameTextUnique record identifier (SessionId)
ContentText (JSON)Full activities array for the session
ConversationStartTimeDateTimeWhen the session started
bot_conversationtranscriptLookupThe bot that handled the session
conversationtranscriptTextSession GUID used as display identifier

A Turn = one complete user message + bot reply. A session with TurnCount = 0 means the user opened the bot but sent no message — nothing worth displaying.


2. App Architecture Overview

The app uses a 3-screen navigation model — session list → conversation detail → turn detail. Navigation flows forward with ScreenTransition.Cover and back with ScreenTransition.UnCover, giving a native app feel.

ScreenNameShows
Screen 1scrSessionListAll sessions, filtered by agent/date/search
Screen 2scrConversationDetailAll turns (Q&A pairs) for the selected session
Screen 3scrTurnDetailFull text of selected user prompt + bot response

3. Collections and Global Variables

NameTypePurpose
colSessionsCollectionSession list built from filtered Dataverse records
colCurrentConversationCollectionParsed turns for the selected session
gSelectedSessionRecord variableSession clicked on Screen 1
gSelectedTurnRecord variableTurn clicked on Screen 2

colSessions schema:

{
  SessionId:             Text,
  AgentName:             Text,
  ConversationStartTime: DateTime,
  Session:               Text,
  FirstPrompt:           Text,
  TurnCount:             Number
}

colCurrentConversation schema:

{
  UserPrompt:        Text,
  PromptTimestamp:   DateTime,
  BotResponse:       Text,
  ResponseTimestamp: DateTime
}

4. Screen 1 — Session List

Screen 1 has three filter controls (agent dropdown, start date, end date), a search box, and a Load Sessions button that populates colSessions.


Figure 1 — Screen 1: Session list with agent filter, date range, and keyword search

Conversation Transcripts Viewer
All Agents ▾ 01/05/2026 📅 15/05/2026 📅 Load Sessions Search by user prompt...
How do I check the warranty status of my SSD?
Support Bot  |  10 May 2026 09:14  |  Turns: 3
What is the difference between SLC and MLC NAND flash?
Support Bot  |  11 May 2026 11:30  |  Turns: 2
My drive is not showing up in Disk Management, what should I do?
Support Bot  |  12 May 2026 14:05  |  Turns: 4
Is the XD700 series compatible with PCIe Gen 5 slots?
Support Bot  |  13 May 2026 16:22  |  Turns: 2
How do I update firmware using the storage management tool?
Support Bot  |  14 May 2026 08:47  |  Turns: 1

Agent Dropdown Items:

=Table(
    {Value: "All"},
    Distinct(ConversationTranscripts, 'bot_conversationtranscript'.Name)
)

btnLoad OnSelect:

=Clear(colSessions);
ForAll(
    Filter(
        ConversationTranscripts,
        (ddAgent.Selected.Value = "All" ||
         'bot_conversationtranscript'.Name = ddAgent.Selected.Value) &&
        ConversationStartTime >= dpStart.SelectedDate &&
        ConversationStartTime <= DateAdd(dpEnd.SelectedDate, 1, TimeUnit.Days)
    ) As T,
    With({parsed: ParseJSON(T.Content)},
        With({firstUserMsg: First(Filter(Table(parsed.activities),
            Text(ThisRecord.Value.type) = "message" &&
            Text(ThisRecord.Value.from.role) = "1"))},
            Collect(colSessions, {
                SessionId:             T.Name,
                AgentName:             T.'bot_conversationtranscript'.Name,
                ConversationStartTime: T.ConversationStartTime,
                Session:               T.conversationtranscript,
                FirstPrompt:           Text(firstUserMsg.Value.text),
                TurnCount:             CountRows(Filter(Table(parsed.activities),
                                           Text(ThisRecord.Value.type) = "message" &&
                                           Text(ThisRecord.Value.from.role) = "1"))
            })
        )
    )
)

galSessions Items:

=SortByColumns(
    Filter(
        colSessions,
        TurnCount > 0 &&
        (IsBlank(txtSearch.Text) || txtSearch.Text in FirstPrompt)
    ),
    "ConversationStartTime",
    SortOrder.Descending
)

💡 Why TurnCount > 0? Sessions where the user opened the bot but typed nothing still create a Dataverse record with zero user message activities. Filtering these out keeps the list meaningful.

galSessions OnSelect:

=Set(gSelectedSession, ThisItem);
If(
    ThisItem.TurnCount > 0,
    Clear(colCurrentConversation);
    With({parsed: ParseJSON(LookUp(ConversationTranscripts,
        Name = gSelectedSession.SessionId).Content)},
        ForAll(
            Filter(Table(parsed.activities),
                Text(ThisRecord.Value.type) = "message" &&
                Text(ThisRecord.Value.from.role) = "1") As UserMsg,
            Collect(colCurrentConversation, {
                UserPrompt:        Text(UserMsg.Value.text),
                PromptTimestamp:   DateAdd(DateTime(1970,1,1,0,0,0),
                                       Value(UserMsg.Value.timestamp), TimeUnit.Seconds),
                BotResponse:       Text(First(Filter(Table(parsed.activities),
                                       Text(ThisRecord.Value.type) = "message" &&
                                       Text(ThisRecord.Value.from.role) = "0" &&
                                       Text(ThisRecord.Value.replyToId) = Text(UserMsg.Value.id)
                                   )).Value.text),
                ResponseTimestamp: DateAdd(DateTime(1970,1,1,0,0,0),
                                       Value(First(Filter(Table(parsed.activities),
                                           Text(ThisRecord.Value.type) = "message" &&
                                           Text(ThisRecord.Value.from.role) = "0" &&
                                           Text(ThisRecord.Value.replyToId) = Text(UserMsg.Value.id)
                                       )).Value.timestamp), TimeUnit.Seconds)
            })
        )
    );
    Navigate(scrConversationDetail, ScreenTransition.Cover)
)

5. Screen 2 — Conversation Detail

Screen 2 shows all turns for the selected session sorted by timestamp, with a search box to find specific turns by keyword.


Figure 2 — Screen 2: All Q&A turns for the selected session, with turn-level search

← Back Support Bot — Conversation Detail 12 May 2026  |  4 turns
Search by user prompt...
👤 My drive is not showing up in Disk Management, what should I do?
🤖 First, check that the drive is properly seated in its slot and all cables are securely connected. Then open Device Manager to check if the drive appears with a warning...
👤 It does appear in Device Manager but shows an error code 43.
🤖 Error code 43 typically means Windows has stopped the device due to a reported problem. Try uninstalling the device driver and restarting your PC...
👤 I reinstalled the driver but the issue persists. Should I update the firmware?
🤖 Yes, updating the firmware is a good next step. Download the Storage Management Tool from the support page and follow the firmware update wizard...
👤 The firmware update completed but the drive still shows the same error.
🤖 If the issue persists after a firmware update, the drive may need to be replaced under warranty. Please contact customer support with your drive serial number...

galConversation Items:

=SortByColumns(
    Filter(
        colCurrentConversation,
        !IsBlank(ThisRecord.UserPrompt) &&
        !IsBlank(ThisRecord.BotResponse) &&
        (IsBlank(txtSearchTurn.Text) || txtSearchTurn.Text in ThisRecord.UserPrompt)
    ),
    "PromptTimestamp",
    SortOrder.Ascending
)

galConversation OnSelect:

=Set(gSelectedTurn, ThisItem);
Navigate(scrTurnDetail, ScreenTransition.Cover)

💡 Tip: The three conditions in the gallery Items formula work together — blank turns are excluded, and search is applied only when the user types something. When the search box is empty, IsBlank(txtSearchTurn.Text) returns true and all valid turns are shown.


6. Screen 3 — Turn Detail

Screen 3 displays the full text of a single selected turn — user prompt and bot response — using read-only multiline text inputs. This solves the gallery truncation problem for long bot responses.


Figure 3 — Screen 3: Full user prompt and bot response for the selected turn

← Back Support Bot — Turn Detail 12 May 2026
👤 User Prompt
My drive is not showing up in Disk Management, what should I do?
🤖 Bot Response
First, check that the drive is properly seated in its slot and all cables are securely connected.

Then open Device Manager (Win + X → Device Manager) to see if the drive appears with a warning icon. If it does, right-click and select Update Driver.

If the drive does not appear at all, try connecting it to a different port or testing it on another machine to rule out a hardware fault. You can also run the Storage Management Tool to diagnose drive health.
🕐 Prompt: 12 May 2026 02:05 PM 🕐 Response: 12 May 2026 02:06 PM 🤖 Agent: Support Bot 🆔 Session: f3a91c02-7b4e-41d2...

ControlPropertyValue
txtUserPromptDefault=gSelectedTurn.UserPrompt
txtUserPromptDisplayMode=DisplayMode.View
txtUserPromptMode=TextMode.MultiLine
txtBotResponseDefault=gSelectedTurn.BotResponse
txtBotResponseDisplayMode=DisplayMode.View
txtBotResponseMode=TextMode.MultiLine
lblPromptTimestampText="Prompt: " & Text(gSelectedTurn.PromptTimestamp, "dd mmm yyyy hh:mm AM/PM")
lblResponseTimestampText="Response: " & Text(gSelectedTurn.ResponseTimestamp, "dd mmm yyyy hh:mm AM/PM")
lblAgentNameText="Agent: " & gSelectedSession.AgentName
btnBack3OnSelect=Navigate(scrConversationDetail, ScreenTransition.UnCover)

7. Parsing the JSON Activities Array

The Content column is a raw JSON string. Power Fx's ParseJSON() function converts it to an untyped object. Wrapping it in Table() converts the activities array into a Power Apps table with a Value column — each row representing one activity.

With({parsed: ParseJSON(Transcript.Content)},
    Table(parsed.activities)
)

The from.role field identifies who sent each message:

role valueMeaning
1User message
0Bot message

⚠️ Important: All values from ParseJSON are untyped. Always wrap field access in Text() before comparing:

// Correct
Text(ThisRecord.Value.from.role) = "1"

// Incorrect — silently fails
ThisRecord.Value.from.role = 1

8. Matching User Prompts to Bot Responses

Each bot response activity has a replyToId field containing the id of the user message it responds to. This is the key to correctly pairing prompts and responses.

BotResponse: Text(
    First(
        Filter(
            Table(parsed.activities),
            Text(ThisRecord.Value.type) = "message" &&
            Text(ThisRecord.Value.from.role) = "0" &&
            Text(ThisRecord.Value.replyToId) = Text(UserMsg.Value.id)
        )
    ).Value.text
)

⚠️ Critical scoping rule: Always reference parsed.activities directly inside the bot response Filter — never use an intermediate variable like allMessages. Power Apps ForAll closures can cause stale variable references across transcript records, resulting in bot responses from a different session appearing for the wrong user prompt.


9. Common Issues and Fixes

IssueCauseFix
Bot response shows text from a different session Intermediate variable leaks across ForAll iterations Reference Table(parsed.activities) directly in all filters
TurnCount = 0 sessions appear in list Gallery Items formula missing turn filter Add TurnCount > 0 to gallery Items Filter
Timestamps show 1970 or wrong date JSON timestamp is Unix epoch in seconds Use DateAdd(DateTime(1970,1,1,0,0,0), Value(...), TimeUnit.Seconds)
ParseJSON comparison returns no rows Comparing untyped value without Text() wrapper Always use Text(ThisRecord.Value.field) = "value"
Empty BotResponse for some turns Session abandoned before bot replied; no matching replyToId Expected behavior — filter with !IsBlank(ThisRecord.BotResponse)
Gallery shows all sessions despite filter Delegation limit exceeded on Dataverse Narrow the date range to stay within delegation limits

10. Full Formula Reference

Screen 1 — Gallery Items:

=SortByColumns(
    Filter(
        colSessions,
        TurnCount > 0 &&
        (IsBlank(txtSearch.Text) || txtSearch.Text in FirstPrompt)
    ),
    "ConversationStartTime",
    SortOrder.Descending
)

Screen 2 — Gallery Items:

=SortByColumns(
    Filter(
        colCurrentConversation,
        !IsBlank(ThisRecord.UserPrompt) &&
        !IsBlank(ThisRecord.BotResponse) &&
        (IsBlank(txtSearchTurn.Text) || txtSearchTurn.Text in ThisRecord.UserPrompt)
    ),
    "PromptTimestamp",
    SortOrder.Ascending
)

Epoch to DateTime conversion:

=DateAdd(
    DateTime(1970, 1, 1, 0, 0, 0),
    Value(activity.timestamp),
    TimeUnit.Seconds
)

Summary: The ConversationTranscripts Dataverse table is a rich source of Copilot Studio conversation data, but it requires JSON parsing to be useful. By combining Dataverse delegation for filtering, ParseJSON + Table() for activity parsing, and replyToId matching for turn pairing, you can build a production-ready 3-screen conversation viewer entirely in Power Apps with no code, no flows, and no external APIs. The scoping rule — always reference parsed.activities directly rather than through intermediate variables — is the most important detail to get right when working across multiple transcript records.

Labels: Copilot Studio, Dataverse, Power Apps, Power Fx, Power Platform, Canvas App

Friday, May 15, 2026

Add Co-Owner to a Power Automate Flow Using PowerShell

Power Automate flows are often built by one person but need to be managed by a team. Adding a co-owner gives another user full edit access — they can modify, run, share, and delete the flow. In this guide, we walk through how to add a co-owner to any Power Automate flow using PowerShell and the Power Apps Administration module.

1. What is a Co-Owner in Power Automate?

A co-owner in Power Automate has the same permissions as the original flow owner. They can edit and modify the flow, turn it on or off, view run history, add or remove other owners, and delete the flow.

This is different from a Run-only user, who can only trigger the flow but cannot edit or manage it.

2. Prerequisites

Before running the script, make sure you have the following:

  • Power Platform Admin or Environment Admin role assigned to your account
  • PowerShell 5.1 or later (PowerShell 7+ recommended)
  • Internet access to connect to Microsoft services
  • The Flow ID and Environment ID from the flow's browser URL
💡 Tip: The Flow ID and Environment ID are visible directly in the browser URL when you open the flow details page:

https://make.powerautomate.com/environments/{EnvironmentId}/flows/{FlowId}/details

3. Install Required PowerShell Modules

Run the following commands in PowerShell as Administrator:

# Power Apps Administration module
Install-Module -Name Microsoft.PowerApps.Administration.PowerShell -Force -AllowClobber

# Az module (to retrieve your Azure AD Object ID)
Install-Module -Name Az -Force -AllowClobber

4. Connect to Power Platform

Authenticate to both Power Apps and Azure. A browser window will open for interactive login — sign in with your Microsoft 365 / Azure AD account.

# Sign in to Power Platform
Add-PowerAppsAccount

# Sign in to Azure (to retrieve your Object ID)
Connect-AzAccount

5. Get Your Azure AD Object ID

Every user in Azure Active Directory has a unique Object ID. This is what the API uses to identify who to assign as co-owner.

# Get the Object ID of the currently signed-in user
$myObjectId = (Get-AzADUser -SignedIn).Id
Write-Host "Your Object ID: $myObjectId"

To add someone else as co-owner, look up by their email address instead:

# Get Object ID by UPN (email)
$myObjectId = (Get-AzADUser -UserPrincipalName "user@yourdomain.com").Id

6. Add Co-Owner Using PowerShell

Use Set-AdminFlowOwnerRole to assign the co-owner role. Set RoleName to CanEdit for full co-owner access, or CanView for read-only access.

$EnvironmentName = "default-xxxxxxxxxxxxxxxxxxxxxxxxxxxx"
$FlowId          = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

Set-AdminFlowOwnerRole `
    -EnvironmentName   $EnvironmentName `
    -FlowName          $FlowId `
    -RoleName          "CanEdit" `
    -PrincipalType     "User" `
    -PrincipalObjectId $myObjectId

Write-Host "Co-owner added successfully!" -ForegroundColor Green

7. Verify the Co-Owner Was Added

After running the script, confirm the role was assigned correctly by listing all current owners:

Get-AdminFlowOwnerRole `
    -EnvironmentName $EnvironmentName `
    -FlowName        $FlowId

You should see an entry with your Object ID and RoleName: CanEdit in the output.

8. Full Script

# ============================================================
# Add Co-Owner to Power Automate Flow via PowerShell
# Modules required: Microsoft.PowerApps.Administration.PowerShell, Az
# ============================================================

# Step 1: Install modules (run once as Administrator)
# Install-Module -Name Microsoft.PowerApps.Administration.PowerShell -Force
# Install-Module -Name Az -Force

# Step 2: Authenticate
Add-PowerAppsAccount
Connect-AzAccount

# Step 3: Get your Object ID
$myObjectId = (Get-AzADUser -SignedIn).Id
Write-Host "Object ID: $myObjectId"

# Step 4: Set flow details (replace with your actual values)
$EnvironmentName = "default-xxxxxxxxxxxxxxxxxxxxxxxxxxxx"
$FlowId          = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

# Step 5: Add as Co-Owner
Set-AdminFlowOwnerRole `
    -EnvironmentName   $EnvironmentName `
    -FlowName          $FlowId `
    -RoleName          "CanEdit" `
    -PrincipalType     "User" `
    -PrincipalObjectId $myObjectId

Write-Host "Done! Co-owner added successfully." -ForegroundColor Green

# Step 6: Verify
Get-AdminFlowOwnerRole `
    -EnvironmentName $EnvironmentName `
    -FlowName        $FlowId

9. Common Errors & Fixes

Error Cause Fix
Get-AzureADCurrentUser not recognized Old AzureAD module not installed Use Get-AzADUser -SignedIn from the Az module
Forbidden / 403 error Account lacks admin rights Ensure you have Environment Admin or Power Platform Admin role
Flow not found / 404 Wrong Flow ID or not authenticated Re-run Add-PowerAppsAccount and verify the Flow ID in the URL
Unapproved verbs WARNING Module internal naming convention This is harmless — the command still executes correctly

Summary: Using the Microsoft.PowerApps.Administration.PowerShell module, you can programmatically manage flow ownership without needing UI access. This is especially useful for admins managing flows at scale, or for recovering access when the original owner has left the organization.

Thursday, May 7, 2026

Liquid code in Power Pages - FetchXML

FetchXML is the native query language of Microsoft Dataverse. If you're building anything serious on the Power Platform — flows, plugins, portals, or canvas apps — you will hit a wall with OData eventually. This guide is the syllabus I wish existed when I started: every feature, every operator, real patterns you'll actually use in production.

1. What is FetchXML

FetchXML is an XML-based query language built specifically for Microsoft Dataverse (formerly Common Data Service / CDS). It's the closest thing Dataverse has to SQL — you write structured XML describing which records you want, and Dataverse translates it internally into the right database query.

It predates OData in the Dynamics ecosystem and remains more powerful for complex scenarios: multi-level joins, aggregates, cross-entity filtering, and paging over large datasets.

When to use FetchXML vs the alternatives

  • FetchXML — complex joins, aggregates, paging over 5,000 records, cross-entity conditions, working inside plugins or C# code
  • OData ($filter) — simple single-entity queries via Web API, quick REST calls, Power Automate "List Rows" without complex joins
  • LINQ (early-bound C#) — type-safe queries in plugin/tool code, good for simple lookups, limited aggregate support

Where FetchXML runs

  • Power Automate — "List rows" action on Dataverse connector → FetchXML input
  • Power Apps — via Collect with a Dataverse table or inside canvas app formulas using the connector
  • Plugins / C#IOrganizationService.RetrieveMultiple(new FetchExpression(fetchXml))
  • Web APIGET /api/data/v9.2/entity?fetchXml=<encoded xml>
  • XrmToolBox — FetchXML Builder plugin for visual building and live testing
  • Power Pages / Portals — FetchXML-based data lists and liquid templates
<!-- Minimal valid FetchXML -->
<fetch>
  <entity name="account">
    <attribute name="name" />
    <attribute name="emailaddress1" />
  </entity>
</fetch>

2. Basic Structure

Every FetchXML query follows the same skeleton. Learn this once and everything else is layers on top.

The fetch element

AttributeValuesWhat it does
topintegerLimits result count (like SQL TOP). Omit for full result set.
distincttrue / falseRemoves duplicate rows. Essential when using outer joins.
no-locktrue / falseAdds NOLOCK hint. Use for read-heavy reporting queries.
aggregatetrue / falseSwitches the query to aggregate mode (section 6).
pageintegerPage number for paging (section 7).
countintegerPage size for paging. Max 5000.

entity, attribute, order

  • <entity name="logical_name"> — the table you're querying. Always use the logical name (lowercase, prefixed).
  • <attribute name="field_name" /> — columns to return. Omit completely to return all columns (not recommended for production).
  • <order attribute="field_name" descending="false" /> — sort order. Stack multiple for multi-column sort.
<fetch top="50" distinct="true" no-lock="true">
  <entity name="contact">
    <attribute name="fullname" />
    <attribute name="emailaddress1" />
    <attribute name="createdon" />
    <order attribute="createdon" descending="true" />
    <order attribute="fullname" descending="false" />
  </entity>
</fetch>
Tip: Never omit all <attribute> elements in production queries. Selecting all columns is a silent performance killer — you end up fetching columns you never use across potentially thousands of rows.

3. Filtering — <filter> and <condition>

Filters are where FetchXML earns its keep. The <filter> element wraps one or more <condition> elements and can be nested arbitrarily deep.

condition anatomy

<condition attribute="fieldname" operator="eq" value="somevalue" />

Complete Operator Reference

OperatorSQL EquivalentNotes
eq= valueExact match
ne!= valueNot equal
gt> valueGreater than
lt< valueLess than
ge>= valueGreater than or equal
le<= valueLess than or equal
likeLIKE '%val%'Use % wildcard. Leading % kills index — avoid.
not-likeNOT LIKEInverse of like
inIN (v1,v2,v3)Uses <value> child elements
not-inNOT INUses <value> child elements
betweenBETWEEN v1 AND v2Uses two <value> child elements
nullIS NULLNo value attribute needed
not-nullIS NOT NULLNo value attribute needed
eq-useridCurrent user IDNo value needed. Dynamic current user.
ne-useridNot current user
eq-businessidCurrent BU ID
today= CAST(GETDATE() AS DATE)Date fields only
tomorrowTomorrow's date
yesterdayYesterday's date
this-weekCurrent calendar week
this-monthCurrent calendar month
this-yearCurrent year
last-seven-daysRolling 7 days back
last-x-daysRolling N days backRequires value="N"
next-x-daysRolling N days forwardRequires value="N"
last-x-monthsRolling N months backRequires value="N"
next-x-monthsRolling N months forward
onExact dateDate fields, value = YYYY-MM-DD
on-or-before<= date
on-or-after>= date
begins-withLIKE 'val%'Index-safe unlike leading wildcard like
ends-withLIKE '%val'Avoid on large tables
contain-valuesMulti-select option setUses <value> child elements
not-contain-valuesMulti-select exclusion

Examples

<!-- IN operator -->
<condition attribute="statuscode" operator="in">
  <value>1</value>
  <value>2</value>
  <value>290560001</value>
</condition>

<!-- BETWEEN operator -->
<condition attribute="createdon" operator="between">
  <value>2024-01-01</value>
  <value>2024-12-31</value>
</condition>

<!-- Dynamic date operators -->
<condition attribute="modifiedon" operator="last-x-days" value="30" />

<!-- Null check -->
<condition attribute="emailaddress1" operator="null" />

<!-- Current user -->
<condition attribute="ownerid" operator="eq-userid" />

<!-- Like with trailing wildcard only (index-safe) -->
<condition attribute="name" operator="like" value="Contoso%" />

4. AND vs OR Logic

By default, all conditions inside a <filter> are ANDed together. Set type="or" to switch to OR. Nest filters to build complex boolean trees.

<!-- Default AND: active accounts in the US -->
<filter>
  <condition attribute="statecode" operator="eq" value="0" />
  <condition attribute="address1_country" operator="eq" value="United States" />
</filter>
<!-- OR: either status applies -->
<filter type="or">
  <condition attribute="statuscode" operator="eq" value="1" />
  <condition attribute="statuscode" operator="eq" value="290560001" />
</filter>
<!-- Mixed AND + OR nesting:
     (statecode = 0) AND (country = 'US' OR country = 'Canada') -->
<filter type="and">
  <condition attribute="statecode" operator="eq" value="0" />
  <filter type="or">
    <condition attribute="address1_country" operator="eq" value="United States" />
    <condition attribute="address1_country" operator="eq" value="Canada" />
  </filter>
</filter>
<!-- Real pattern: projects that are completed OR cancelled OR have no tasks -->
<fetch>
  <entity name="project">
    <attribute name="name" />
    <attribute name="statuscode" />
    <filter type="and">
      <condition attribute="ownerid" operator="eq-userid" />
      <filter type="or">
        <condition attribute="statuscode" operator="eq" value="290560002" />
        <condition attribute="statuscode" operator="eq" value="290560003" />
        <condition entityname="tsk" attribute="taskid" operator="null" />
      </filter>
    </filter>
    <link-entity name="task" from="regardingobjectid" to="projectid"
                 link-type="outer" alias="tsk">
      <attribute name="taskid" />
    </link-entity>
  </entity>
</fetch>
Watch out: The entityname attribute on a <condition> lets you filter on a joined entity's columns from the parent filter. This only works with outer joins and is how you implement "NOT EXISTS" patterns. The value of entityname must match the alias on the <link-entity>.

5. Link-Entities (Joins)

FetchXML joins are called link-entities. They work like SQL JOINs and can be nested multiple levels deep.

link-type values

link-typeSQL EquivalentWhen to use
innerINNER JOINOnly return parent records that have a matching child. Default if omitted.
outerLEFT OUTER JOINReturn parent records even when no child exists. Required for "not exists" patterns.

Basic inner join

<!-- Contacts and their parent Account name -->
<fetch>
  <entity name="contact">
    <attribute name="fullname" />
    <attribute name="emailaddress1" />
    <link-entity name="account" from="accountid" to="parentcustomerid"
                 link-type="inner" alias="acc">
      <attribute name="name" alias="accountname" />
      <attribute name="telephone1" alias="accountphone" />
    </link-entity>
  </entity>
</fetch>

Multi-level joins

<!-- Three levels deep: Task → Stage → Project -->
<fetch>
  <entity name="task">
    <attribute name="subject" />
    <attribute name="statuscode" />
    <link-entity name="stage" from="stageid" to="stageid"
                 link-type="inner" alias="stg">
      <attribute name="name" alias="stagename" />
      <link-entity name="project" from="projectid" to="projectid"
                   link-type="inner" alias="proj">
        <attribute name="name" alias="projectname" />
        <attribute name="statuscode" alias="projectstatus" />
      </link-entity>
    </link-entity>
  </entity>
</fetch>

Filtering on a joined entity

<!-- Contacts whose account is in a specific industry -->
<fetch>
  <entity name="contact">
    <attribute name="fullname" />
    <link-entity name="account" from="accountid" to="parentcustomerid"
                 link-type="inner" alias="acc">
      <attribute name="name" alias="accountname" />
      <filter>
        <condition attribute="industrycode" operator="eq" value="7" />
      </filter>
    </link-entity>
  </entity>
</fetch>

Cross-entity condition (entityname attribute)

This pattern filters on a linked entity's column from the parent entity's filter block. Most commonly used for "NOT EXISTS" — find parent records where no child row matches.

<!-- Accounts with NO contacts -->
<fetch distinct="true">
  <entity name="account">
    <attribute name="name" />
    <filter>
      <condition entityname="con" attribute="contactid" operator="null" />
    </filter>
    <link-entity name="contact" from="parentcustomerid" to="accountid"
                 link-type="outer" alias="con">
      <attribute name="contactid" />
    </link-entity>
  </entity>
</fetch>
Rule: Cross-entity conditions (entityname=) always require the join to be link-type="outer". An inner join would already exclude the non-matching rows before the NULL check runs.

6. Aggregate Queries

Set aggregate="true" on <fetch> to switch to aggregate mode. In this mode, every <attribute> must declare an aggregate function or be a groupby column.

Aggregate functions

FunctionDescription
countCount of all rows (including nulls)
countcolumnCount of non-null values in a column
sumSum of numeric column
avgAverage of numeric column
minMinimum value
maxMaximum value
<!-- Count contacts per account -->
<fetch aggregate="true">
  <entity name="contact">
    <attribute name="contactid" aggregate="count" alias="contactcount" />
    <attribute name="parentcustomerid" groupby="true" alias="accountid" />
  </entity>
</fetch>
<!-- Sum and average of estimated revenue grouped by industry -->
<fetch aggregate="true">
  <entity name="opportunity">
    <attribute name="estimatedvalue" aggregate="sum" alias="totalrevenue" />
    <attribute name="estimatedvalue" aggregate="avg" alias="avgrevenue" />
    <attribute name="opportunityid" aggregate="count" alias="dealcount" />
    <attribute name="industrycode" groupby="true" alias="industry" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>
<!-- Distinct count: unique companies with open cases -->
<fetch aggregate="true">
  <entity name="incident">
    <attribute name="customerid" aggregate="countcolumn" distinct="true" alias="uniquecustomers" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

Grouping by date parts

<!-- Cases created per month -->
<fetch aggregate="true">
  <entity name="incident">
    <attribute name="incidentid" aggregate="count" alias="casecount" />
    <attribute name="createdon" groupby="true" dategrouping="month" alias="createmonth" />
    <attribute name="createdon" groupby="true" dategrouping="year" alias="createyear" />
    <order alias="createyear" descending="false" />
    <order alias="createmonth" descending="false" />
  </entity>
</fetch>
Note: Aggregate FetchXML results are capped at 50,000 groups. For reporting over very large datasets, consider Azure Synapse Link or Dataverse long-term data export instead.

7. Paging

Dataverse returns a maximum of 5,000 records per request. For anything larger you must page through results using either simple page numbering or paging cookies.

Simple page/count attributes

<!-- Page 2, 100 records per page -->
<fetch page="2" count="100">
  <entity name="contact">
    <attribute name="fullname" />
    <attribute name="createdon" />
    <order attribute="createdon" descending="true" />
  </entity>
</fetch>

Paging cookies (correct approach for large datasets)

Simple page numbering degrades at scale because the database still scans from the beginning every time. Paging cookies pass a server-side bookmark that picks up exactly where the previous page ended — far more efficient.

The response from Dataverse includes a @Microsoft.Dynamics.CRM.fetchxmlpagingcookie annotation. Decode it and inject it into the next request:

<!-- First request: no cookie -->
<fetch page="1" count="5000">
  <entity name="contact">
    <attribute name="fullname" />
    <order attribute="contactid" descending="false" />
  </entity>
</fetch>

<!-- Subsequent requests: inject the decoded cookie -->
<fetch page="2" count="5000"
  paging-cookie="&lt;cookie page=&quot;1&quot;&gt;&lt;contactid ... /&gt;&lt;/cookie&gt;">
  <entity name="contact">
    <attribute name="fullname" />
    <order attribute="contactid" descending="false" />
  </entity>
</fetch>
Important: Always include an <order> on a unique field (like the primary key) when paging. Without a deterministic sort order, you can get duplicate or skipped records across pages.

Paging in C# (plugin or tool)

// C# paging loop example
string fetchXml = @"<fetch count='5000'>
  <entity name='contact'>
    <attribute name='fullname' />
    <order attribute='contactid' />
  </entity>
</fetch>";

var allRecords = new List<Entity>();
int pageNumber = 1;
string pagingCookie = null;

while (true)
{
    string pagedFetch = PatchFetchXmlPage(fetchXml, pageNumber, pagingCookie);
    var result = service.RetrieveMultiple(new FetchExpression(pagedFetch));
    allRecords.AddRange(result.Entities);

    if (!result.MoreRecords) break;

    pagingCookie = result.PagingCookie;
    pageNumber++;
}

8. FetchXML in Power Automate

The Dataverse connector's List rows action accepts a FetchXML query directly. This unlocks joins, aggregates, and paging that you simply cannot do with OData filter expressions.

Basic setup

  1. Add action: Microsoft Dataverse → List rows
  2. Set Table Name to your entity
  3. Expand Advanced options
  4. Paste your FetchXML into the Fetch Xml Query field
<fetch top="500" no-lock="true">
  <entity name="contact">
    <attribute name="fullname" />
    <attribute name="emailaddress1" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

Injecting dynamic values safely

In Power Automate, FetchXML is a string. You can inject dynamic expressions using @{expression} syntax. The critical rule: never inject user-controlled input directly — always validate or use GUIDs from trigger/action outputs.

<!-- Safe: injecting a trigger GUID value -->
<fetch>
  <entity name="task">
    <attribute name="subject" />
    <attribute name="statuscode" />
    <filter>
      <condition attribute="regardingobjectid" operator="eq"
        value="@{triggerOutputs()?['body/accountid']}" />
      <condition attribute="createdon" operator="last-x-days"
        value="@{variables('DaysBack')}" />
    </filter>
  </entity>
</fetch>

Dynamic IN clause from an array variable

<!-- Step 1: Build <value> tags using Select action -->
<!-- Select "from": array of IDs, "map": concat('<value>', item(), '</value>') -->

<!-- Step 2: Join them and inject into FetchXML -->
<fetch>
  <entity name="project">
    <attribute name="name" />
    <filter>
      <condition attribute="projectid" operator="in">
        @{join(body('Select_ID_values'), '')}
      </condition>
    </filter>
  </entity>
</fetch>

Handling paging in Power Automate

The List Rows action does not auto-page. Use a Do Until loop: check the response for @odata.nextLink or use the paging cookie from the response headers to fetch the next batch.

Tip: For flows processing large volumes, consider moving paging logic to an Azure Function or plugin. Power Automate's 30-day / 30-second action limits make deep paging expensive.

9. FetchXML in Plugins / C#

Inside a Dataverse plugin or any code using IOrganizationService, pass your FetchXML to RetrieveMultiple via a FetchExpression.

Basic retrieval

string fetchXml = @"
<fetch top='50'>
  <entity name='contact'>
    <attribute name='fullname' />
    <attribute name='emailaddress1' />
    <filter>
      <condition attribute='statecode' operator='eq' value='0' />
    </filter>
  </entity>
</fetch>";

EntityCollection results = service.RetrieveMultiple(new FetchExpression(fetchXml));

foreach (Entity entity in results.Entities)
{
    string name = entity.GetAttributeValue<string>("fullname");
    string email = entity.GetAttributeValue<string>("emailaddress1");
}

Reading aliased join attributes

When you alias an attribute from a joined entity, it comes back in the Entity as an AliasedValue.

// FetchXML has: <attribute name="name" alias="accountname" /> on the link-entity
var aliasedValue = entity.GetAttributeValue<AliasedValue>("accountname");
string accountName = aliasedValue?.Value as string;

Reading aggregate results

// FetchXML aggregate result: alias="contactcount"
var aliasedCount = entity.GetAttributeValue<AliasedValue>("contactcount");
int count = Convert.ToInt32(aliasedCount?.Value);

Building FetchXML dynamically in C#

// Use XElement for safe construction — no string concatenation
var fetch = new XElement("fetch", new XAttribute("top", 100),
    new XElement("entity", new XAttribute("name", "contact"),
        new XElement("attribute", new XAttribute("name", "fullname")),
        new XElement("filter",
            new XElement("condition",
                new XAttribute("attribute", "statecode"),
                new XAttribute("operator", "eq"),
                new XAttribute("value", "0")
            )
        )
    )
);

string fetchXml = fetch.ToString();
var results = service.RetrieveMultiple(new FetchExpression(fetchXml));

10. FetchXML via Web API

You can execute FetchXML directly against the Dataverse Web API by passing it as a URL query parameter. This is useful for integrations, testing from Postman, or server-side code calling the API directly.

Endpoint pattern

GET https://<org>.api.crm.dynamics.com/api/data/v9.2/<entity_plural>?fetchXml=<url-encoded-xml>

URL-encode your FetchXML

<!-- Raw FetchXML -->
<fetch top="10">
  <entity name="contact">
    <attribute name="fullname" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

<!-- URL encoded (key characters) -->
<    →  %3C
>    →  %3E
"    →  %22
/    →  %2F
=    →  %3D

Full example in JavaScript

const fetchXml = `<fetch top="10">
  <entity name="contact">
    <attribute name="fullname" />
    <filter><condition attribute="statecode" operator="eq" value="0" /></filter>
  </entity>
</fetch>`;

const encoded = encodeURIComponent(fetchXml);
const url = `${orgUrl}/api/data/v9.2/contacts?fetchXml=${encoded}`;

const response = await fetch(url, {
  headers: {
    'Authorization': `Bearer ${accessToken}`,
    'OData-MaxVersion': '4.0',
    'OData-Version': '4.0',
    'Accept': 'application/json'
  }
});

const data = await response.json();
console.log(data.value); // array of records

Request formatted values

To get the display label of option sets (formatted values) in the response, add a Prefer header:

Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"

Formatted values then appear in the response as fieldname@OData.Community.Display.V1.FormattedValue.

11. XrmToolBox: FetchXML Builder

FetchXML Builder (by Jonas Rapp) is the fastest way to build, test, and debug FetchXML queries against a live environment. Every serious Dataverse developer should have it installed.

Key features to use

  • Visual query builder — drag-and-drop entities and fields, set filters via dropdowns. Good starting point even if you'll hand-edit the XML.
  • Execute and preview — run the query and see results instantly against your connected environment. No need to deploy a flow or plugin to test.
  • View as SQL — converts your FetchXML to approximate T-SQL. Useful for understanding what's happening under the hood and for writing the equivalent SQL for reporting.
  • Copy as code — exports the query as ready-to-use C#, JavaScript, or Power Automate-compatible string.
  • Aggregate mode toggle — switches the builder into aggregate mode with a checkbox.
  • Paging cookie test — lets you page through results directly in the tool.

Workflow tip

  1. Build the query visually in FetchXML Builder
  2. Switch to XML view and refine edge cases (nested filters, cross-entity conditions)
  3. Execute and validate results against real data
  4. Copy the final XML into your flow, plugin, or API call
Tip: FetchXML Builder also supports the no-lock hint and shows row counts per page. Use it to benchmark query performance before committing to production code.

12. Common Real-World Patterns

Pattern 1: Find records with no related records (NOT EXISTS)

<!-- Accounts with zero contacts -->
<fetch distinct="true">
  <entity name="account">
    <attribute name="name" />
    <attribute name="accountid" />
    <filter>
      <condition entityname="con" attribute="contactid" operator="null" />
    </filter>
    <link-entity name="contact" from="parentcustomerid" to="accountid"
                 link-type="outer" alias="con">
      <attribute name="contactid" />
    </link-entity>
  </entity>
</fetch>

Pattern 2: Get the latest record per group

<!-- Most recent case per customer -->
<fetch aggregate="true">
  <entity name="incident">
    <attribute name="customerid" groupby="true" alias="customerid" />
    <attribute name="createdon" aggregate="max" alias="latestcase" />
  </entity>
</fetch>

Pattern 3: Multi-condition OR across linked entities

<!-- Projects that are complete OR cancelled OR have stages with no tasks -->
<fetch distinct="true" no-lock="true">
  <entity name="project">
    <attribute name="name" />
    <attribute name="statuscode" />
    <filter type="and">
      <condition attribute="retailerid" operator="eq"
        value="abf8e77a-845b-f011-bec1-6045bda966da" />
      <filter type="or">
        <condition attribute="statuscode" operator="eq" value="290560003" />
        <condition attribute="statuscode" operator="eq" value="290560002" />
        <condition entityname="tsk" attribute="taskid" operator="null" />
      </filter>
    </filter>
    <link-entity name="stage" from="projectid" to="projectid"
                 link-type="outer" alias="stg">
      <link-entity name="task" from="stageid" to="stageid"
                   link-type="outer" alias="tsk">
        <attribute name="taskid" />
      </link-entity>
    </link-entity>
  </entity>
</fetch>

Pattern 4: Hierarchy query — tasks for a specific project via stages

<!-- All tasks under a project by traversing Project → Stage → Task -->
<fetch>
  <entity name="task">
    <attribute name="subject" />
    <attribute name="statuscode" />
    <attribute name="ownerid" />
    <link-entity name="stage" from="stageid" to="stageid"
                 link-type="inner" alias="stg">
      <attribute name="name" alias="stagename" />
      <link-entity name="project" from="projectid" to="projectid"
                   link-type="inner" alias="proj">
        <attribute name="name" alias="projectname" />
        <filter>
          <condition attribute="projectid" operator="eq"
            value="3fa85f64-5717-4562-b3fc-2c963f66afa6" />
        </filter>
      </link-entity>
    </link-entity>
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

Pattern 5: Active records modified in the last N days, owned by current user

<fetch top="200">
  <entity name="opportunity">
    <attribute name="name" />
    <attribute name="estimatedvalue" />
    <attribute name="modifiedon" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
      <condition attribute="ownerid" operator="eq-userid" />
      <condition attribute="modifiedon" operator="last-x-days" value="14" />
    </filter>
    <order attribute="modifiedon" descending="true" />
  </entity>
</fetch>

Pattern 6: Count of records per status with a join condition

<!-- Task count per project, only for active projects -->
<fetch aggregate="true">
  <entity name="task">
    <attribute name="taskid" aggregate="count" alias="taskcount" />
    <link-entity name="stage" from="stageid" to="stageid"
                 link-type="inner" alias="stg">
      <link-entity name="project" from="projectid" to="projectid"
                   link-type="inner" alias="proj">
        <attribute name="projectid" groupby="true" alias="projectid" />
        <attribute name="name" groupby="true" alias="projectname" />
        <filter>
          <condition attribute="statecode" operator="eq" value="0" />
        </filter>
      </link-entity>
    </link-entity>
  </entity>
</fetch>

13. Performance Tips

1. Never use a leading wildcard in LIKE

<!-- BAD: forces a full table scan -->
<condition attribute="name" operator="like" value="%contoso%" />

<!-- GOOD: index-seekable -->
<condition attribute="name" operator="like" value="contoso%" />
<!-- Or use begins-with explicitly -->
<condition attribute="name" operator="begins-with" value="contoso" />

2. Always use no-lock on read-only reporting queries

<fetch no-lock="true">
  <!-- Adds NOLOCK hint. Prevents blocking on busy tables.
       Only use for reporting — not for business-critical transactional reads. -->
</fetch>

3. Select only the columns you need

<!-- BAD: returns all columns -->
<entity name="contact">
  <!-- no attribute elements = SELECT * -->
</entity>

<!-- GOOD: return only what you use -->
<entity name="contact">
  <attribute name="fullname" />
  <attribute name="emailaddress1" />
</entity>

4. Filter as early as possible — push filters to the lowest join level

<!-- Filter on the join entity directly, not at the top -->
<link-entity name="account" from="accountid" to="parentcustomerid"
             link-type="inner" alias="acc">
  <filter>
    <!-- This runs during the join, not after -->
    <condition attribute="statecode" operator="eq" value="0" />
  </filter>
</link-entity>

5. Use top and paging — never retrieve all records speculatively

<!-- Always bound your queries in automation flows -->
<fetch top="5000" no-lock="true">
  <entity name="contact">
    <attribute name="fullname" />
  </entity>
</fetch>

6. Avoid deeply nested joins unless necessary

Each additional join level multiplies the SQL complexity. If you're going 4+ levels deep, consider whether a Dataverse Custom API or pre-computed rollup field could reduce query depth.

7. Use distinct only when you have to

distinct="true" forces a DISTINCT on the SQL side which means sorting the entire result set. Only use it when outer joins actually produce duplicates that matter for your output.

8. Index-backed fields filter faster

Dataverse automatically indexes primary keys, lookup fields, and some system fields like statecode, statuscode, ownerid, createdon, and modifiedon. Filtering on custom text fields without enabling a custom index will always be slower.

14. FetchXML vs OData

CapabilityFetchXMLOData ($filter)
Simple single-entity queries✅ (simpler syntax)
Multi-level joins❌ (expand limited to 1 level)
Outer joins / NOT EXISTS
Aggregate functions (sum, count, avg)
Group by with date parts
Dynamic date operators (last-x-days, this-week)
Cross-entity conditions (entityname=)
Paging with cookies✅ (@odata.nextLink)
Readable / writeable by non-developers❌ (verbose XML)✅ (concise URL params)
Works in Power Automate List Rows
Works in Canvas App formula barLimited
Direct URL testing (Postman, browser)⚠️ (URL-encode required)✅ (natural params)
Formatted value annotations✅ (via API header)✅ (via API header)
Tool support (FetchXML Builder)✅ (excellent)
NOLOCK hint

Decision rule

  • Use OData for simple filters on a single table, quick API tests, Canvas App formulas.
  • Use FetchXML for anything involving joins, aggregates, dynamic dates, NOT EXISTS, or paging beyond page 1 of a large dataset.
  • In plugins and C# code, prefer FetchXML — it's more predictable than LINQ and gives you full control over what the database executes.

Quick Reference Cheat Sheet

fetch attributes

AttributePurpose
top="N"Limit result count
distinct="true"Remove duplicate rows
no-lock="true"NOLOCK hint for read queries
aggregate="true"Enable aggregate mode
page="N"Page number
count="N"Page size (max 5000)
paging-cookie="..."Server-side cursor for efficient paging

Comparison operators

OperatorMeaning
eq / neEqual / Not equal
gt / ltGreater than / Less than
ge / leGreater or equal / Less or equal
like / not-likeString pattern match (% wildcard)
begins-withIndex-safe prefix match
in / not-inSet membership (use <value> children)
betweenRange (two <value> children)
null / not-nullIS NULL / IS NOT NULL

Dynamic date operators

OperatorNotes
today / yesterday / tomorrowNo value needed
this-week / this-month / this-yearNo value needed
last-seven-daysNo value needed
last-x-days / next-x-daysvalue="N" required
last-x-months / next-x-monthsvalue="N" required
on / on-or-before / on-or-aftervalue = YYYY-MM-DD

Aggregate functions

FunctionUsage
countRow count including nulls
countcolumnNon-null value count
sumNumeric sum
avgNumeric average
min / maxMin / Max value
groupby="true"Group by this column (no aggregate function)
dategroupingValues: day, week, month, quarter, year

link-entity

AttributeValues / Notes
nameLogical name of the entity to join
fromField on the child entity
toField on the parent entity
link-typeinner (default) or outer
aliasRequired for accessing attributes and cross-entity conditions

Cross-entity condition (NOT EXISTS)

<filter>
  <condition entityname="alias" attribute="pkfield" operator="null" />
</filter>
<link-entity ... link-type="outer" alias="alias">
  <attribute name="pkfield" />
</link-entity>

Skeleton query

<fetch top="500" distinct="false" no-lock="true">
  <entity name="entity_logical_name">
    <attribute name="field1" />
    <attribute name="field2" />
    <order attribute="field1" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
    <link-entity name="related_entity" from="fk_field" to="pk_field"
                 link-type="inner" alias="rel">
      <attribute name="name" alias="relatedname" />
    </link-entity>
  </entity>
</fetch>

Featured Post

Microsoft Teams Development & Extensibility Complete Guide

Microsoft Teams Development & Extensibility — Complete Guide Tabs · Bots · Messaging Extensions · Adaptive Cards · Meeting Apps · Live ...

Popular posts