Best architecture for supplier-data agent

Use a Copilot Studio agent flow as a structured query tool and keep SharePoint as the datastore initially; knowledge-source retrieval is relevance-oriented and should not be used when every matching supplier must be returned.

The roughly 10-record behavior matches the documented Q&A case for Copilot Studio’s SharePoint connector and generative-answer node, where 10 records per response is described as a design limit ^1^. More generally, SharePoint knowledge search uses only the top three search results when generating a summary, so it is not an exhaustive database query mechanism ^2^.

Recommended architecture

User question
    ↓
Copilot Studio: interpret intent and collect typed criteria
    ↓
Agent flow tool: validate criteria and build an allowlisted query
    ↓
SharePoint Get items: server-side OData filter across supplier data
    ↓
Structured result: total count + requested page + continuation indicator
    ↓
Agent: display table/card and offer Next page or export

1. Consolidate supplier data where practical.
   • Prefer one Suppliers list with consistent columns such as SupplierName, Capabilities, Regions, Experience, ReferenceStatus, and Active.
   • Use related lists only for genuinely one-to-many data, such as multiple references or projects.
   • Add a stable supplier ID to every related row.
2. Create an agent flow with typed inputs, rather than accepting an LLM-generated OData string:
   • capability
   • region
   • minimumExperience
   • referenceRequired
   • activeOnly
   • pageNumber
   • pageSize
3. Validate inputs and map them to approved columns/operators. Do not let free-form user text become an executable query. This prevents invalid filters and keeps query behavior testable.
4. Use SharePoint’s Get items action with Filter Query. It supports server-side OData filtering, including equality, numeric comparisons, startswith, and substringOf. Its default item limit is 100, and the limit can be raised to 5,000—well above the stated 300–500 records ^3^. Example filter assembled from validated fields:

Active eq 1
and Region eq 'Europe'
and YearsExperience ge 5
and substringof('machining', Capabilities)

5. Return compact structured output, for example:

{
  "totalMatches": 27,
  "page": 1,
  "pageSize": 20,
  "hasMore": true,
  "suppliers": [
    { "id": 101, "name": "Supplier A", "region": "Europe" }
  ]
}

6. Add the published flow to the agent at Agents → your agent → Tools → Add a tool → Flow → Add and configure. Give it a precise description such as “Search all supplier records using structured filters; use this tool whenever the user asks for matching suppliers.” ^4^
7. Page the presentation, not the query accuracy. Compute the complete match count, but return manageable pages and offer Next page, Show all, or an exported CSV/link. Connector output is limited to 5 MB in public-cloud plans, so sending hundreds of wide records into one model response is not a reliable interface ^5^.

Natural-language translation

Copilot Studio can infer tool selection and collect tool inputs from the user’s request when the tool has clear names, descriptions, inputs, and outputs. However, exhaustive retrieval should still be performed by the flow—not by asking the model to identify matching rows itself.

For ambiguous terms, define explicit business mappings. For example:

• “experienced supplier” → YearsExperience >= 5
• “has references” → ReferenceStatus = Approved
• “local” → a defined set of regions

If a request cannot be represented by the approved filters, the agent should ask a clarification question instead of generating an unrestricted query.

When to move to Dataverse

Move to Dataverse if supplier information becomes a relational business application requiring:

• Multiple related tables and formal relationships.
• Dataverse security roles or record-level permissions.
• Governed processes, auditing, or broader Power Platform integration.

Dataverse supports retrieval of relevant rows and related records, while SharePoint is better suited to straightforward list-shaped team data ^6^. A flow can query Dataverse with List rows, selecting columns and applying OData filters; pagination supports more than 5,000 rows when needed ^7^.

For only 300–500 records, scale alone does not justify migration. Choose Dataverse for relationships, governance, and security—not to work around generative retrieval limits.

API, SQL, and MCP

• Azure SQL/API: appropriate when supplier data already belongs in an application database, requires complex joins, or must serve several systems. Expose a purpose-built search endpoint with typed filters and pagination.
• MCP: useful when an existing MCP server already exposes reusable supplier-query tools. It adds hosting, authentication, connector, and governance work; it is not necessary for this dataset size.
• Azure AI Search: useful for semantic search over long unstructured supplier documents, but combine it with a structured query service when complete row retrieval is required.

The practical choice is therefore SharePoint + agent flow now, with a normalized schema, allowlisted structured filters, pagination, and optional export. Migrate to Dataverse when relational modeling or governance requirements—not record count—make SharePoint unsuitable.

References

1. how to get all the data from sharepoint list using copilot studio - Microsoft Q&A
2. SharePoint knowledge sources don't return results in Copilot Studio
3. In-depth analysis into Get items and Get files SharePoint actions for flows in Power Automate
4. Call an agent flow from an agent
5. Quotas and limits
6. Dataverse data in Microsoft Copilot
7. Use lists of rows in flows