Dynamics 365 Finance & Operations: Integration APIs & Data Exchange Guide

Integration Patterns Overview

F&O supports four primary integration approaches:

1. Real-Time Request-Response (OData API)

• Synchronous HTTP calls; query or mutate a single record or small batch.
• Use case: Live dashboard, order lookup, customer validation.
• Latency: 100–500ms per request.
• Volume: 1–100 records per call.

2. Batch Operations (Data Management Framework)

• Asynchronous bulk import/export; schedules recurring jobs.
• Use case: End-of-day syncs, historical data migration, large file imports.
• Latency: Minutes to hours (depends on data volume and job queue).
• Volume: 100–millions of records per job.

3. Event-Driven Integration (Business Events & Power Automate)

• F&O emits events when critical actions occur (invoice posted, order created, shipment confirmed).
• External systems subscribe and react (e.g., “When invoice posted, send to accounting software”).
• Use case: Workflow automation, real-time downstream updates, audit trails.
• Latency: Near-real-time (milliseconds to seconds).

4. Custom Integration Layers

• X++ web services, Azure Functions, Logic Apps, or middleware platforms (Mulesoft, SOAPUI).
• Use case: Complex transformations, legacy system compatibility, hybrid cloud scenarios.
• Latency: Depends on implementation.

OData REST API Fundamentals

OData (Open Data Protocol) is a REST-based API standard. F&O exposes hundreds of entities via OData, allowing any HTTP client (JavaScript, Python, .NET, mobile app, Postman) to query and manipulate data.

OData Endpoint Format:

GET https://{environment}.dynamics.com/data/{entity}

Example: Query all customers:

GET https://mycompany.sandbox.dynamics.com/data/Customers

Query Operators:

• $filter – WHERE clause. Example: $filter=CustomerGroup eq ’VIP’
• $select – Column selection. Example: $select=CustomerId,Name,Email
• $orderby – Sort. Example: $orderby=Name desc
• $skip – Offset pagination. Example: $skip=100
• $top – Limit. Example: $top=50 (returns first 50 records after skip).
• $expand – Join related entities. Example: $expand=Orders($select=OrderId,Amount)

Example Query:

GET https://mycompany.sandbox.dynamics.com/data/Customers?$filter=CustomerGroup eq ’VIP’&$select=CustomerId,Name&$orderby=Name&$top=10

Response: JSON array of VIP customers, top 10, sorted by name.

Create, Update, Delete (CRUD):

• POST – Create new record. Send JSON payload with fields.
• PATCH – Update existing record. Send only fields to change.
• DELETE – Delete record. No payload.

Data Entities & Query Syntax

Not every table in F&O is exposed via OData. Microsoft provides a curated set of data entities—abstraction layers that group related tables and handle business logic.

Common Data Entities:

• Customers (CustTable)
• Vendors (VendTable)
• Sales Orders (SalesOrder)
• Purchase Orders (PurchaseOrder)
• Invoices (CustInvoiceJour)
• General Ledger Transactions (GeneralJournalEntry)
• Inventory On-Hand (InventOnHandEntity)
• Products (EcoResProduct)

To discover available entities, query the metadata endpoint:

GET https://mycompany.sandbox.dynamics.com/data/$metadata

This returns the full schema (XML or JSON) of all exposed entities, their fields, relationships, and constraints.

Query Example: Fetch Sales Orders for a Customer

GET https://mycompany.sandbox.dynamics.com/data/SalesOrders?$filter=CustomerId eq ’CUST-001’&$select=SalesOrderId,OrderAmount,CreatedDate&$expand=Lines($select=LineNum,ItemId,Quantity,Price)

This returns all orders for CUST-001, plus the nested order lines (items, quantities, prices).

Batch Operations with Data Management Framework

The Data Management Framework (DMF) is the enterprise-grade bulk integration tool. It’s designed for high-volume imports, exports, and recurring syncs.

DMF Components:

• Data Projects – Reusable import/export configurations. Define source file format, target entity, field mappings, and transformations.
• Staging Area – Intermediate tables where data lands before validation and import. Review, correct, or reject staging records before committing to production.
• Data Entities – 200+ pre-built entities for common objects (customers, orders, inventory). Can be extended with custom entities via X++ class.
• Job History – Audit trail of all imports/exports. Track success/failure, record counts, errors, and timestamps.

Typical DMF Workflow:

1. Create a data project (e.g., “Daily Customer Import”).
2. Upload source file (CSV, XLSX, or JSON) to blob storage or local.
3. Map source columns to F&O fields.
4. Configure transformations (e.g., convert “US” to country code “USA”).
5. Preview staging data; validate for errors.
6. Execute import (async). F&O queues the job, processes in background.
7. Monitor job status and error logs.
8. On success, data is committed to production tables.

DMF Advantages over OData:

• Handles large files without timeout (OData request-response has ~2-minute timeout).
• Automatic retry and error recovery.
• Staging area allows review before commit (transactional safety).
• Supports custom business logic via plugins and transformations.
• Detailed error logs help troubleshoot data quality issues.

Recurring Integrations & Scheduling

F&O allows you to schedule DMF jobs on a recurring basis (hourly, daily, weekly, or custom schedule).

Recurring Integration Setup:

• Create a data project (as above).
• Publish the project to the Recurring Integrations portal.
• Configure schedule (e.g., “Every day at 2 AM UTC”).
• Set source (e.g., Azure Blob Storage, SFTP, or API endpoint).
• Enable notifications (email on success/failure).

The system automatically pulls the latest file, stages it, and imports. If the import fails, it retries (configurable retry policy) and notifies stakeholders.

Best Practices:

• Schedule during off-peak hours to avoid contention with operational traffic.
• Implement idempotency: if a record already exists, update it (don’t duplicate).
• Validate source data before import (schema, required fields, data types).
• Monitor job history for failures; investigate and resolve root causes quickly.

Authentication & Authorization

F&O uses Azure Active Directory (Azure AD) for all authentication. Integration scenarios fall into two categories:

1. User-Based (Interactive) Authentication

• A user signs in via Azure AD; F&O validates the user and role permissions.
• Example: Power Automate flow with a user’s account context.
• Tokens expire after 1 hour; user must re-authenticate.

2. Application-Based (Service-to-Service) Authentication

• An application (Azure Function, Logic App, external service) authenticates using client credentials (client ID + client secret).
• No user involved; automation runs on behalf of the application identity.
• Application must have appropriate permissions in Azure AD and F&O security roles.

OAuth2 Client Credentials Flow:

1. Register application in Azure AD. Obtain client ID and generate client secret.
2. Application requests token: POST https://login.microsoftonline.com/{tenant}/oauth2/v2.0/token
3. Azure AD returns access token (JWT) valid for 1 hour.
4. Application sends token in API request: Authorization: Bearer {token}
5. F&O validates token and processes request.

Assigning F&O Permissions:

• Service principal (application) is created in F&O as a user account.
• Assign security roles that match required permissions (e.g., “Sales Order Processor” role for importing orders).
• Test permissions before deploying to production.

Business Events & Event-Driven Integration

Business Events allow F&O to emit notifications when important business actions occur. External systems subscribe and react in real-time.

Pre-Built Business Events (100+ available):

• Customer created, updated, deleted.
• Sales order created, confirmed, invoiced.
• Purchase order received.
• Invoice posted to GL.
• Shipment confirmed.
• Payment received.
• Inventory transfer completed.

Fire-Receive Pattern:

• Fire – F&O emits the event (e.g., “Invoice Posted”).
• Receive – External system receives notification and takes action (e.g., send invoice to accounting system).

Event Payload (Example):

{
  "id": "event-12345",
  "eventType": "SalesOrderCreated",
  "timestamp": "2026-03-19T10:30:00Z",
  "data": {
    "SalesOrderId": "SO-001",
    "CustomerId": "CUST-A",
    "Amount": 5000.00,
    "Status": "Created"
  }
}

Delivery Mechanisms:

• Power Automate – Trigger flow on business event; call external API, send email, or update CRM.
• Azure Event Grid – F&O publishes events to Event Grid; subscribers (Functions, Logic Apps, webhooks) consume.
• Webhooks – Custom HTTP endpoints registered to receive events.

Power Automate Integration

Power Automate (formerly Flow) is Microsoft’s no-code/low-code automation platform. The D365 connector provides 1000+ pre-built actions and triggers.

Common Power Automate Patterns:

• Event-Triggered Workflow – When sales order is created in F&O, automatically create task in Project Operations and send email to salesperson.
• Scheduled Job – Every day at 3 PM, query F&O for overdue invoices and export to Excel, send to CFO.
• Multi-System Sync – When customer is created in Salesforce, automatically create customer account in F&O (bidirectional).
• Approval Workflow – When purchase order exceeds $50k, route to manager for approval; if approved, send to F&O; if rejected, notify requester.

Connector Actions (Example):

• List records (customers, orders).
• Get record details.
• Create record.
• Update record.
• Delete record.
• Run action (e.g., post journal, confirm PO).

Custom X++ Web Services

X++ web services are legacy integration points (pre-OData). They allow you to expose custom business logic via SOAP or JSON endpoints.

Why Consider Custom Services:

• Highly specific business logic not covered by standard entities.
• Complex transformations or multi-step processes.
• Legacy system compatibility (older systems may not support OData).

Why Avoid (Prefer OData When Possible):

• Maintenance burden: Custom code must be updated during F&O upgrades.
• Security: Custom code is harder to audit than standard APIs.
• Performance: OData is optimized and cached; custom code may not be.

API Rate Limits & Throttling

F&O enforces rate limits to protect system stability:

Published Limits:

• OData Requests – 3000 requests per 60 seconds, per tenant.
• Per-User Limits – Individual users are also throttled; limits are shared across all apps they use.

When You Hit the Limit:

• API returns HTTP 429 (Too Many Requests).
• Response header includes Retry-After (e.g., “Retry-After: 10”, meaning retry in 10 seconds).

Mitigation Strategies:

• Batch Requests – Use DMF or batch API calls instead of individual requests.
• Exponential Backoff – Retry after 1s, then 2s, then 4s, etc. Most SDKs do this automatically.
• Pagination – Use $top/$skip to fetch smaller chunks; spread requests over time.
• Caching – Cache frequently-accessed data (products, customers) locally; refresh periodically.
• Async Processing – Use Power Automate or Azure Functions with queues to serialize requests.

Common Integration Patterns

Pattern	Technology	Use Case	Pros	Cons
Real-Time Query	OData API	Dashboard, lookup, validation	Fast, simple, no infrastructure	Small volumes, request timeout ~2 min
Bulk Import	DMF + Recurring Integration	End-of-day sync, migration	Handles large files, staged review, retry logic	Latency (minutes to hours), async
Event-Driven	Business Events + Power Automate	Workflow automation, downstream updates	Real-time, no polling, scalable	Complexity, requires Power Automate license
Middleware	Azure Data Factory, Logic Apps, Mulesoft	Complex transformations, legacy compatibility	Flexible, handles any source/target	Higher cost, operational overhead

Security & Best Practices

• Use Service Principals – Create Azure AD service principals for integrations. Never use personal user accounts.
• Principle of Least Privilege – Assign only the minimum security roles required. Don’t grant System Administrator to integration accounts.
• Rotate Credentials – Client secrets expire after 2 years. Rotate before expiry or use certificate-based auth.
• Monitor API Usage – Enable activity logging. Track who called which APIs, when, and from where.
• Secure Secrets – Store client IDs and secrets in Azure Key Vault, not in code or config files.
• Implement Retry Logic – Don’t hammer the API on transient failures; use exponential backoff and circuit breakers.
• Validate Data – Always validate incoming data (schema, required fields, data types) before inserting into F&O.
• Use HTTPS Only – All API calls must use TLS 1.2 or higher. Never send credentials over HTTP.
• Audit Trails – Enable change tracking and audit logging to see who modified records and when.

Frequently Asked Questions

How do I know if an entity is available via OData?

Query the $metadata endpoint: GET https://mycompany.sandbox.dynamics.com/data/$metadata. This returns the full schema of all exposed entities. Alternatively, check the Microsoft documentation or browse the OData browser in the LCS Environment monitoring portal.

Can I use OData to call a custom business logic action?

Yes. If you’ve created a custom action (X++ class decorated as OData action), it will appear in the metadata. Call it as POST https://mycompany.dynamics.com/data/CustomAction with appropriate parameters.

What’s the maximum payload size for an OData request?

Default is 1 MB. For larger uploads, use DMF or split the request into multiple API calls. Contact Microsoft Support to request an increase (not recommended; DMF is safer).

How do I troubleshoot a failing DMF job?

Check the Job History list and error log. Look for staging area records marked “Error” or “Warning”. Review validation rules, transformations, and data format. Common issues: missing required fields, invalid lookup values, duplicate records, or data type mismatches.

Can Business Events fire for historical records (e.g., old invoices)?

No. Business Events fire only for new or modified records during normal operation. To process historical data, use OData queries or DMF export.

How do I test an integration before going live?

Use a sandbox environment. Configure the integration against sandbox, run test data, verify outputs in both F&O and the external system. Validate end-to-end workflows (create order in external system, confirm import in F&O, verify GL postings, etc.). Load test with production-like data volume.