Queries

Queries are the read operations of your API. They define how data is retrieved from Dynamics 365 and exposed to consuming applications. FlowOn API supports three types of queries.

Query Types

Query Type	Description	Use Case
Single	Returns exactly one record	Get record by ID, get current user's profile
Multi	Returns multiple records (no pagination)	Get all active statuses, get team members
Paginated	Returns paged results with navigation	List all customers, search products

Single Query

A Single Query returns exactly one record based on the query criteria.

Configuration

Query: GetAccountById
Type: Single
Module: Sales

Configuration:
├── Entity: Account
├── Parameters:
│   └── accountId (GUID, required)
├── Filter: accountid = @accountId
├── Columns:
│   ├── accountid
│   ├── name
│   ├── telephone1
│   ├── emailaddress1
│   ├── primarycontactid (expand: fullname, emailaddress1)
│   └── ownerid (expand: fullname)
└── Authorization: All authenticated users

API Request

GET /api/sales/queries/account?AccountId={accountId}

API Response

Success (200 OK):

{
  "accountId": "a1b2c3d4-...",
  "name": "Contoso Ltd",
  "telephone1": "+1-555-0100",
  "emailAddress1": "info@contoso.com",
  "primaryContact": {
    "id": "contact-guid",
    "name": "Jane Doe"
  },
  "owner": {
    "id": "user-guid",
    "name": "John Smith"
  }
}

No Content (204): If no record matches the query criteria, the API returns 204 No Content with an empty response body.

---

Multi Query

A Multi Query returns multiple records without pagination. Best for bounded result sets.

Configuration

Query: GetAccountContacts
Type: Multi
Module: Sales

Configuration:
├── Entity: Contact
├── Parameters:
│   └── accountId (GUID, required)
├── Filter: parentcustomerid = @accountId AND statecode = 0
├── Order By: fullname ASC
├── Columns:
│   ├── contactid
│   ├── fullname
│   ├── emailaddress1
│   ├── telephone1
│   └── jobtitle
├── Max Results: 100
└── Authorization: All authenticated users

API Request

GET /api/sales/queries/account-contacts?AccountId={accountId}

API Response

Success (200 OK):

[
  {
    "contactId": "c1-guid",
    "fullName": "Alice Johnson",
    "emailAddress1": "alice@contoso.com",
    "telephone1": "+1-555-0101",
    "jobTitle": "CEO"
  },
  {
    "contactId": "c2-guid",
    "fullName": "Bob Williams",
    "emailAddress1": "bob@contoso.com",
    "telephone1": "+1-555-0102",
    "jobTitle": "CFO"
  }
]

No Content (204): If no records match the query criteria, returns 204 No Content.

When to Use Multi Query

Scenario	Recommendation
Results always bounded (e.g., max 50 team members)	✅ Use Multi Query
Results could grow unbounded	❌ Use Paginated Query
Need to load all records at once for UI	✅ Use Multi Query
Need to display with infinite scroll	❌ Use Paginated Query

---

Paginated Query

A Paginated Query returns results in pages, ideal for large datasets that need efficient loading and navigation.

Configuration

Query: SearchAccounts
Type: Paginated
Module: Sales

Configuration:
├── Entity: Account
├── Parameters:
│   ├── searchTerm (String, optional)
│   ├── categoryCode (Integer, optional)
│   └── ownerIdFilter (GUID, optional)
├── Filter: 
│   │  (name LIKE '%@searchTerm%' OR accountnumber LIKE '%@searchTerm%')
│   │  AND (@categoryCode IS NULL OR accountcategorycode = @categoryCode)
│   │  AND (@ownerIdFilter IS NULL OR ownerid = @ownerIdFilter)
│   │  AND statecode = 0
├── Order By: name ASC
├── Columns:
│   ├── accountid
│   ├── name
│   ├── accountnumber
│   ├── telephone1
│   ├── emailaddress1
│   └── accountcategorycode
├── Page Size: 20
├── Include Total Count: Yes
└── Authorization: All authenticated users

Pagination Parameters

Parameter	Type	Description
page	Integer	Page number (1-based)
pageSize	Integer	Records per page (max usually 100)
cursor	String	Cursor for cursor-based pagination

API Request

GET /api/sales/queries/search-accounts?searchTerm=contoso&page=1&pageSize=20

API Response

Success (200 OK):

{
  "cursor": "eyJwYWdlIjoyfQ==",
  "count": 20,
  "totalRecordCount": 47,
  "hasMoreRecords": true,
  "data": [
    {
      "accountId": "a1-guid",
      "name": "Contoso Corporation",
      "accountNumber": "ACC-001",
      "telephone1": "+1-555-0100",
      "emailAddress1": "info@contoso.com",
      "accountCategoryCode": 1
    },
    {
      "accountId": "a2-guid",
      "name": "Contoso Labs",
      "accountNumber": "ACC-002",
      "telephone1": "+1-555-0200",
      "emailAddress1": "labs@contoso.com",
      "accountCategoryCode": 2
    }
  ]
}

Paginated Response Structure

Field	Type	Description
cursor	String	Token for retrieving next page
count	Integer	Number of records in current page
totalRecordCount	Integer	Total records matching query (if enabled)
hasMoreRecords	Boolean	Whether more records exist
data	Array	The actual records

Cursor-Based Navigation

To get the next page, include the cursor from the previous response:

GET /api/sales/queries/search-accounts?cursor=eyJwYWdlIjoyfQ==

---

Query Configuration Options

Filter Expressions

Filters support standard comparison operators and can reference parameters:

Operator	Example
Equals	statecode = 0
Not Equals	statuscode != 2
Greater Than	creditlimit > 10000
Less Than	createdon < @cutoffDate
Like	name LIKE '%@searchTerm%'
In	categorycode IN (1, 2, 3)
Is Null	parentaccountid IS NULL
Is Not Null	primarycontactid IS NOT NULL

Column Expansion

For lookup fields, you can expand related entity fields:

Columns:
├── accountid
├── name
├── primarycontactid
│   └── Expand:
│       ├── fullname
│       ├── emailaddress1
│       └── jobtitle
└── ownerid
    └── Expand:
        └── fullname

Order By

Specify sort order for results:

Order By:
├── name ASC          (alphabetical by name)
├── createdon DESC    (newest first)
└── creditlimit DESC  (highest credit first)

Access Control

Queries support the same Authorization Policy and Pre-conditions as actions:

Access Control:
├── Authorization Policy:
│   ├── Type: Demand Any
│   └── Roles:
│       ├── Administrator
│       ├── SalesManager
│       └── SalesRep
│
└── Pre-conditions:
    └── Pre-condition 1:
        ├── Condition: @searchTerm IS NOT NULL OR @categoryCode IS NOT NULL
        ├── Error Code: 400
        └── Error Message: "At least one search parameter is required"

Query Best Practices

Performance

• Always include filters to limit result sets
• Use indexes on filtered columns (coordinate with DBA)
• Limit expanded relationships to necessary fields
• Set appropriate page sizes (20-50 for UI lists)
• Consider caching for frequently accessed, slowly-changing data

Security

• Use authorization policies to restrict sensitive queries
• Apply row-level filtering based on user context when needed
• Don't expose internal IDs unnecessarily
• Validate all input parameters

User Experience

• Include total count for paginated queries when feasible
• Return meaningful field names (use API Name mapping)
• Sort by most relevant field by default
• Support optional filtering for flexible searching