Copilot Studio and SharePoint lists: four routes compared

21 October 2026

Tags: Relational databasesAIDesktopCommand line

You added the facilities Requests list to a Copilot Studio agent, published it to Teams, and asked it how many urgent requests are open. It gave you a number well below what the filtered list view shows. The list has a little over 9,000 items, one for every job reported across the company’s offices since 2023, with a Building lookup into a second list, choice columns for Priority and Status, an Assigned To person and two dates. You built the agent so that regional managers could ask about their own buildings in Teams and stop asking you for an Excel export on Monday mornings.

Four routes come up when you search for a fix. The first is the one you already took, the list itself as knowledge, and for some agents it is the right one. After that you can give the agent a Power Automate flow that reads the list, move the data into Dataverse, or point the agent at a database copy of the list. Each of them works for some questions. We have spent a lot of time on the last, so read the others with that in mind; we have tried to say plainly where they are the better answer.

Copilot Studio SharePoint list knowledge source limits

Route one is the list as knowledge, and lists are a supported knowledge source. Microsoft Learn’s Add SharePoint as a knowledge source page, checked on 20 October 2026, says the selected lists are “ingested into Dataverse and indexed”, that each user signs in with their own SharePoint credentials, and that the connection reads current data. In practice the agent answers from a Dataverse copy of your list, through a search index. It recommends no more than 10 lists per agent and warns that lists with more than 35,000 rows affect quality and latency.

The line that explains your wrong count is on the Copilot Studio quotas and limits page, checked the same day: SharePoint list queries only return data from the first 2,048 rows. A list of 9,000 items is far inside the 35,000 ceiling, so nothing warns you at setup time. Every question still reads from a slice of roughly a quarter of the list, and the model counts what it was given.

The same page rules out lists with more than 12 lookup columns in the default view, and says you cannot pick a list view as the source. Nothing in either page describes a join between two lists, so a question that needs the Building lookup’s own columns, such as the lease end date, has no route through list knowledge.

None of that matters for “who is the facilities manager for Leeds?” or “what is the status of request 4821?”. One or two items answer those, and the list knowledge source is good at them. Managers mostly ask how many and which building, and those questions need every row in play.

The list is now native knowledge in both of Microsoft’s agent makers. Agent Builder, inside Microsoft 365 Copilot, takes a list too, with its own limits on its knowledge page, checked on 23 October 2026: one list per agent, up to 20,000 items, truncated beyond that. Roadmap item 561920 says this release supports neither lookup columns nor attachments, so the Building lookup is out of reach there as well.

Should you connect the agent to the list live instead of copying it?

You will hear this advice on podcasts and partner blogs: connect the agent to the data where it lives and stop migrating it. For a lot of agents we agree. A list of a few hundred contacts or policies, where one or two items answer each question, is what list knowledge was built for. Answers come from current values and are trimmed to the signed-in user’s SharePoint permissions. There is no extra service to run.

Two details belong next to that advice. Learn says the selected lists are ingested into Dataverse and indexed, so the live route keeps a copy too, one Microsoft manages and reads through search. And a copy you can search answers “which request is about the boiler in Leeds”, while a count needs a copy that something can add up. That is the question to ask of every route below.

Nobody has to migrate off the list for routes two to four either. Whoever logs a request keeps doing it in SharePoint, which stays the system of record. The flow reads it in place, and the Dataverse and SQL routes add a read-only copy for the agent beside it.

Power Automate flow as a Copilot Studio tool for list data

Route two also keeps the list where it is. You build an agent flow with the “When an agent calls the flow” trigger, add the SharePoint “Get items” action with an OData filter built from the flow’s inputs, trim the output with a Select action, and return it through “Respond to the agent”. In Copilot Studio you add the flow on the Tools page and write a description that tells the orchestrator when to call it.

For a single request, or the open requests for one building, this works well. The filter runs inside SharePoint and the flow hands back a short JSON array for the model to write its answer from. Index the columns the flow filters on, Status and Building here, and the same filter keeps working on a 9,000-item list.

Counting is where it starts to strain. The Get items action takes a filter, a sort order and a top count, and none of those counts or groups anything. To count urgent open requests the flow has to page through every match and take the length of the array. On the Low performance profile, Microsoft’s flow limits page caps paginated results at 5,000 items. Copilot Studio also requires a flow tool to respond within 100 seconds, and a long paging loop can run past that.

A question that spans two lists is harder again. “Which buildings with a lease ending before April have more than ten open requests?” needs the Buildings list as well, so the flow fetches both, loops over the buildings and counts matching requests inside the loop. You can build it, and it is slow to run and fiddly to change.

Someone will also ask about a column you never indexed, such as Category. Against a list past the 5,000-item threshold, SharePoint refuses that query with a threshold error, and unless the flow passes the error back, the agent tells the manager it found nothing.

Then there is the cost per question. Learn’s agent flows overview says every action an agent flow executes consumes Copilot Studio capacity, and that a run triggered by generative orchestration also consumes one autonomous action. A lookup flow runs a handful of actions. A counting flow runs its inner actions once per building it loops over, and you pay for that again every time a manager asks.

When a Power Automate flow is enough

Keep route two if the agent’s questions are lookups. A “where is my request” agent for staff is a good fit, and so is a tool that fetches one record so a topic can update it. Both keep SharePoint item permissions with no extra work. The same goes for lists that will stay in the hundreds of items, where paging through the whole list is cheap.

We would also keep the flow when the agent needs to write. SPSync is read-only against SharePoint, so if the agent must create or update a request, a flow with “Create item” or “Update item” is the tool for that part, whatever you use for reading.

Dataverse virtual table for a SharePoint list in Copilot Studio

Route three moves the data closer to Copilot Studio. Dataverse can show a SharePoint list as a virtual table through the virtual connector provider, and Copilot Studio can use Dataverse tables as knowledge, with synonyms and glossary entries so the model knows what a column means.

The two pieces do not meet. The Copilot Studio page Add Dataverse tables as a knowledge source, checked on 20 October 2026, says that only virtual tables associated with the Finance and Operations data provider can be selected, and that any other data provider is not supported. A SharePoint virtual table is fine in a model-driven app or a flow, and it cannot be agent knowledge. Microsoft’s virtual table pages add two more conditions. Creating one needs a Dataverse licence through Power Apps or Dynamics 365, and you cannot filter or sort on a virtual table’s lookup column.

The working version of route two is a copy into standard Dataverse tables. A dataflow or a sync tool keeps the copy current, and the Dataverse knowledge source takes up to 15 tables per source. SPSync can write that copy; the SharePoint to Dataverse page explains how lookups arrive as an id column and a display value column, with no Dataverse relationship between them.

What route three costs is Dataverse storage for a second copy of every row, plus the upkeep of whatever keeps it current. Where it stalls is the kind of question. Microsoft describes Dataverse knowledge as a retrieval-augmented technique over Dataverse search. Test your count questions against it before you rely on them, and expect to write glossary entries for every id column. The same page says Dataverse knowledge needs Dataverse search turned on in the environment, and that creating the search index incurs extra Dataverse capacity costs.

Some makers are asking an earlier question: Dataverse or a SharePoint list as the place the data lives. If the requests are captured in a Power App, the team holds Dataverse licences and the list is still young, a Dataverse table as the store skips the copy altogether. If people already raise and update requests in the list, moving them is a project of its own, and a copy for the agent is the smaller change.

Copilot Studio Azure SQL knowledge over a replicated list

Route four puts the list into a database and lets the agent query it. Copilot Studio’s real-time knowledge connectors include Azure SQL; the Learn page we checked on 20 October 2026 marks the feature as preview. Microsoft indexes only the table and column names, and each question runs against the database at runtime with the signed-in user’s own token. The agent writes the query and the database does the counting over every row.

That needs the list in Azure SQL and kept current, which is the part SPSync does. It reads SharePoint through Microsoft Graph on a schedule and writes typed tables. On our dev tenant, one run read 10,303 items from four lists, one of them a 10,000-item Service Requests list, and wrote them to SQL Server in 1m 52s with 115 Graph calls.

Replicate the Requests list and its lookups

In the desktop app, connect the facilities site and tick both Requests and Buildings. The lookup target has to come too, or the join has nothing to join to. Choose Azure SQL, paste a connection string, and check the schema preview, which shows each column, its type and which ones are lookups. From the command line it is one config file and one run:

spsync export-config --output facilities.yaml --pipeline-name facilities --prefix fm \
  --destination-kind mssql --connection-string-env FACILITIES_DB_URL \
  --dataset-name facilities --client-id $SPSYNC_CLIENT_ID \
  --site-url https://contoso.sharepoint.com/sites/facilities \
  --list Requests --list Buildings --create-reporting-views
spsync run facilities.yaml

After the first full load the facilities schema holds fm_requests with Building as building_id and building_value, fm_buildings with the city, region and lease end date, and the shared sp_users table that Assigned To points at. Every row carries sp_item_id, sp_modified and sp_deleted_at. Later runs read only what changed.

What the maker configures in Copilot Studio

Open the agent, choose Add knowledge, and pick Azure SQL from the real-time connectors (look under Advanced if it is not in the featured list). Sign in, select the server and database, tick the tables or views, and give the source a name and a description that says what a request is and what each status means. Copilot Studio shows the source as In progress while it indexes the metadata, then Ready.

Since column names are most of what the model sees, a view with plain names does better than the raw tables. We would give the agent one view, open_requests, with building, city, region, priority, category, reported_on and assigned_to, and the Buildings table alongside it.

A count, a filter and a cross-list question

Run these yourself before you test the agent, so you know the right answers when you check its replies. They use the raw tables, so the joins are visible; against the view they get shorter.

-- How many urgent requests are open?
SELECT COUNT(*) AS open_urgent
FROM facilities.fm_requests
WHERE priority = 'Urgent' AND status <> 'Closed' AND sp_deleted_at IS NULL;

-- Open heating requests in Leeds reported more than a fortnight ago
SELECT r.sp_item_id, r.title, b.title AS building, r.reported_on
FROM facilities.fm_requests AS r
JOIN facilities.fm_buildings AS b ON b.sp_item_id = r.building_id
WHERE r.category = 'Heating' AND r.status <> 'Closed' AND b.city = 'Leeds'
  AND r.reported_on < DATEADD(day, -14, CAST(GETDATE() AS date))
  AND r.sp_deleted_at IS NULL;

-- Buildings whose lease ends before April with more than ten open requests
SELECT b.title AS building, b.lease_end, COUNT(*) AS open_requests
FROM facilities.fm_requests AS r
JOIN facilities.fm_buildings AS b ON b.sp_item_id = r.building_id
WHERE r.status <> 'Closed' AND r.sp_deleted_at IS NULL
  AND b.lease_end < '2027-04-01'
GROUP BY b.title, b.lease_end
HAVING COUNT(*) > 10
ORDER BY open_requests DESC;

All three run over the whole list. The Leeds filter cannot hit the list view threshold, because SharePoint is not the one answering it. The lease question needed a loop in the flow; here it is one join with a GROUP BY and a HAVING clause.

Some questions are about meaning. “Find requests that mention a burning smell” is a search problem, and an index with embeddings handles it better than SQL. SPSync also writes to Azure AI Search with embeddings generated on sync, and the RAG use case covers that setup. One agent can use the database and the index side by side.

Our map of the Microsoft AI stack for SharePoint lists sets these routes beside Copilot connectors, Work IQ, Agent Builder and MCP.

How do you bound what the agent can read?

The replica holds list items and their columns. It carries no SharePoint item permissions, so the database decides who sees what. Because the Azure SQL connector runs each question as the signed-in user, you grant access to people or Entra groups:

CREATE ROLE facilities_agent_reader;
GRANT SELECT ON SCHEMA::facilities TO facilities_agent_reader;
CREATE USER [Facilities Managers] FROM EXTERNAL PROVIDER;
ALTER ROLE facilities_agent_reader ADD MEMBER [Facilities Managers];

If each regional manager should see only their own region, add a row-level security policy on Azure SQL with a predicate on region. Because the connector runs as each user, that policy applies per person. Nothing the agent sends can change SharePoint, since SPSync has no write path back to the list.

Freshness comes from the schedule. On the default five-minute schedule the tables trail the list by a few minutes. A full reconcile, weekly by default and configurable, compares every row with the list as a QA check. You can also run a reconcile by hand after a Microsoft 365 outage. Deleted requests keep their row with sp_deleted_at set, so last month’s figures do not shift when someone tidies the list.

SharePoint list vs Dataverse vs SQL for a Copilot Studio agent

The same 9,000-item Requests list, taken through each route:

Which route should a Copilot Studio maker pick?

Start from the questions people will type. When they name one request or one building, the list as knowledge or a flow tool is less to build, and SharePoint permissions come with it at no extra effort. A team that already runs on Dataverse, with the licences in place, can keep a standard table copy next to the agent, provided the count questions pass testing. Once managers ask which building has the most open jobs, they need every row, and of the four routes only the database query reads every row for each question. You can also keep the list as knowledge for lookups and add the database beside it for the numbers.

The Copilot Studio grounding use case covers the same pattern from the data side. The agent data layer use case describes one SQL copy that every agent reads, and AI agents and tools over SharePoint covers custom agents that query the replica through their own SQL tool. For the Copilot in Microsoft 365 side of this problem, see why Copilot cannot count the rows in your SharePoint list.

SPSync writes to many destinations, and the same lists can go to Azure SQL for the agent and somewhere else for reporting, one replication each. If the destination you need is not listed, tell us. Licences are per installation and per destination family; see licensing.