Why does Copilot give a different ticket count from the list view?

Copilot grounds on search results over the list, and Copilot Studio's list knowledge only returns data from the first 2,048 rows. The model counts what it was given. The list view counts every row, so the two disagree once the list is larger than what retrieval returns.

Can Copilot Studio use Azure SQL as a knowledge source?

Yes. Azure SQL is one of the Power Platform real-time knowledge connectors in Copilot Studio, in preview at the time of writing. Microsoft indexes only table and column names, and each question runs against the database at runtime with the signed-in user's credentials.

Does the replica keep SharePoint item permissions?

No. SPSync copies list items and their columns, not item-level SharePoint permissions, so the database decides what an agent sees. Give the agent a read-only role on the schema and add row-level security if different people should see different tickets.

How far behind SharePoint is the replicated table?

A few minutes on a short schedule. Each run after the first reads only what changed, and a full reconcile runs weekly by default, configurable, as a QA check that the table matches the list.

Why Copilot on SharePoint lists cannot count your rows

10 October 2026

Tags: Relational databases AI Desktop Command line

Copilot Studio reads only the first 2,048 rows of a SharePoint list when it answers a question, and Copilot over a list counts whatever its search returned. Both facts are on Microsoft Learn, in the Copilot Studio quotas and limits article, and both explain a Monday morning like this one, when the head of IT service is preparing the weekly service review. The help desk runs on a SharePoint list called Tickets: just over 14,000 rows, with a Category lookup into a second list, Status and Priority choices, an Assigned To person column, a multi-choice Tags column, a Closed On date and a calculated Days Open column. She has a Copilot Studio agent pointed at that list, built so she and the team leads could stop exporting to Excel every week. She types the questions she asks every Monday: how many high-priority tickets are still open, the average time to close last month by category, and which engineer is holding the most open VPN tickets.

The first answer is a number that does not match the list view. Question two comes back as a few example tickets and a suggestion to export the list to Excel. The third names an engineer who left the team in the summer. None of these is a bug she can report: the agent is doing what it was built to do, and her job for it is a different one.

Why Copilot refuses or miscounts on a SharePoint list

Copilot over SharePoint, whether the Copilot pane on a list or a Copilot Studio agent with the list as knowledge, is a retrieval system. Your question becomes a search, the search returns the most relevant items, and the model writes an answer from them. That works well for “what is the escalation process for a broken laptop?”, where one or two items hold the answer.

A count needs every row that matches, and an average needs every row in the period. Retrieval returns a handful of the best matches, and the model counts what it was handed. If the search returned twenty open high-priority tickets, the model says twenty, with confidence, whether the real figure is twenty or two hundred. When it notices it cannot see enough rows, the honest response is the one she got: export to Excel.

A list request fails the same way: ask the agent for every open VPN ticket and it shows a handful, while the filtered list view shows two hundred. That short list, and an agent that says an item does not exist, are covered in Copilot Studio says it cannot find the item at row 3,000, with a test list that shows which rows your own agent can reach.

The Copilot Studio quotas page says as much: under SharePoint, it notes that agents cannot write and run code over structured files, so responses to analytical questions might not be optimal. The engineer who left in the summer is the same mechanism from another angle: older tickets with his name on them matched the word “VPN” better than the current ones.

The limits Microsoft documents for list knowledge

Microsoft publishes the limits on Microsoft Learn, in the Copilot Studio “Quotas and limits” article (SharePoint web app limits) and the “Add SharePoint as a knowledge source” article. As of October 2026 they say this about lists:

SharePoint list queries only return data from the first 2,048 rows.
An agent supports up to 15 lists, with up to 35,000 rows per list and no more than 120,000 rows in total. The knowledge article also advises using no more than 10 lists per agent and says lists over 35,000 rows affect quality and latency.
Lists with more than 12 lookup columns in the default view are not supported.
You cannot select a list view as a knowledge source.

The first line decides the Monday questions. A Tickets list of 14,000 rows is well inside the 35,000 ceiling, so the agent accepts it without complaint, and every query still reads from the first 2,048 rows. Any count across the whole list is a count over a slice, and the answer does not say so. Help-desk lists also gather lookups over the years, usually all in the default view, which brings the lookup limit into play.

Agent Builder in Microsoft 365 Copilot has its own limits. Learn’s Agent Builder knowledge page allows one list per agent, up to 20,000 items, and the roadmap entry for the release says lookup columns are not supported, so the Category lookup is the catch even though all 14,000 tickets fit. The row 3,000 post covers Agent Builder in full.

Is the 2,048 limit in Copilot Studio or in Power Automate?

Search for “copilot studio 2048 limit” and the results mix the Copilot Studio quotas page with Power Automate forum threads about flows, and the SharePoint Create item action, that mention the same number. Keep the two apart. The 2,048-row figure on the quotas page governs what an agent’s list knowledge reads when it answers a question. A flow you add to the agent as a tool has its own limits, covered in the workaround section below, and nothing you change in a flow moves the knowledge limit. If the agent answers from the list as knowledge, the quotas page is the one that applies.

Lookup, choice and person columns come out raw

SharePoint stores a lookup as a pointer. Through the APIs that a connector or a flow uses, the Category column on a ticket arrives as CategoryLookupId, a number that only means something inside the Categories list. Assigned To arrives as AssignedToLookupId, an integer from the site’s hidden user information list, which is neither an email address nor an Entra ID. The search index the agent reads holds the display text instead, which is friendlier, but it is only text. “Network” appears on the ticket and nothing connects it to the Categories list’s own columns, such as the target resolution time in hours that the category carries for SLA reporting.

A single choice column such as Priority is fine: it is text and it reads as text. The multi-choice Tags column is where meaning gets lost. In the index it is a run of words on the item, so “tickets tagged VPN” becomes a text match, and it also finds every ticket whose description mentions VPN in passing.

A calculated column such as Days Open has its own problem. SharePoint works the value out when the item is saved, so on a ticket left untouched for a fortnight it is a fortnight out of date, and the agent has no way to tell. The use-case page on grounding Copilot Studio on clean tables covers the same problem from the data side.

What does the Power Automate filter workaround cost to keep working?

The common workaround is a Power Automate flow added to the agent as a tool. The flow asks a model to turn the user’s question into a SharePoint OData filter, using a description of the list’s columns pasted into the prompt, runs Get items with that filter and returns the results. It works in a demo. Keeping it working is the cost.

The model has to write filters SharePoint will accept. Lookups need the related field’s path or the lookup id, person columns need a sub-property such as the user’s email, and a date filter needs SharePoint’s own date format. Each wrong guess is an empty result, which the agent reports as “no tickets found”.

The filter only fetches rows. SharePoint’s REST API has no GROUP BY and no AVG, so an average time to close means the flow pages through every closed ticket for the month and hands the lot to the model to do the arithmetic: the same counting problem, one step further down.

The list view threshold applies too. A filter on a column that is not indexed, against a list over 5,000 items, fails with the threshold error, so every new filterable column needs an index in list settings first.

The prompt is a second copy of the schema. When someone adds a column or renames a choice value from “P1” to “Critical”, the prompt goes stale without any warning. Every question is also a flow run, and the quotas page notes that flows an agent triggers draw on the Power Platform request allowance.

What it costs to keep a SharePoint export script running covers the same upkeep for flows that copy list data.

What a replicated SQL table changes

Copilot Studio already has a route for structured questions. Its real-time knowledge connectors, in preview at the time of writing, include Azure SQL. Microsoft indexes only the table and column names, and every question runs against the database at runtime with the signed-in user’s own credentials. The agent writes a query and the database does the counting.

That needs the list in a database, kept current. SPSync replicates SharePoint lists into Azure SQL, SQL Server or PostgreSQL on a schedule, read-only against SharePoint, with lookups, people and multi-value columns already turned into columns and tables a query can use.

Connect the site and pick both lists

In the desktop app, choose Connect a site, paste the help-desk site URL and tick Tickets and Categories. Tick the lookup target as well as the main list, so the category join has rows to join to. Choose Azure SQL as the destination, paste the connection string with Encrypt=yes, and check the schema preview: every column, its type, and which ones are lookups. Turn on “Create reporting views”. From the command line, the same setup is one config file and one run:

spsync export-config --output helpdesk.yaml --pipeline-name helpdesk --prefix hd \
  --destination-kind mssql --connection-string-env HELPDESK_DB_URL \
  --dataset-name helpdesk --client-id $SPSYNC_CLIENT_ID \
  --site-url https://contoso.sharepoint.com/sites/itservice \
  --list Tickets --list Categories --create-reporting-views
spsync run helpdesk.yaml

Run the first sync and look at the tables

The first run is a full load, with rows read and written per list on the run screen or in the terminal. Afterwards the helpdesk schema holds:

hd_tickets, one row per ticket. Category becomes category_id and category_value, Assigned To becomes assigned_to_user_id, and every row carries sp_item_id, sp_created, sp_modified and sp_deleted_at.
hd_categories, one row per category, including its resolution target column.
hd_tickets__tags, the multi-choice Tags column as a child table: sp_item_id, value and ordinal, one row per ticket and tag.
sp_users, shared by every list, with display_name, email and login.
v_hd_tickets, a reporting view with person columns resolved to display names and deleted tickets left out.

Days Open arrives holding the value stored at the last save. Compute it in SQL from sp_created and closed_on and it is correct when the question is asked.

Ask the Monday questions in SQL

The agent writes queries like the three below. Run them yourself first, so you know the right answers before you judge the agent’s.

-- How many high-priority tickets are still open?
SELECT COUNT(*) AS open_high
FROM helpdesk.hd_tickets
WHERE priority = 'High' AND status <> 'Closed' AND sp_deleted_at IS NULL;

-- Average days to close last month, by category, against the category's target
SELECT c.title AS category, c.target_hours,
       AVG(DATEDIFF(hour, t.sp_created, t.closed_on) / 24.0) AS avg_days_to_close,
       COUNT(*) AS closed
FROM helpdesk.hd_tickets AS t
JOIN helpdesk.hd_categories AS c ON c.sp_item_id = t.category_id
WHERE t.closed_on >= '2026-09-01' AND t.closed_on < '2026-10-01'
  AND t.sp_deleted_at IS NULL
GROUP BY c.title, c.target_hours
ORDER BY avg_days_to_close DESC;

-- Who holds the most open tickets tagged VPN?
SELECT u.display_name, COUNT(*) AS open_vpn
FROM helpdesk.hd_tickets AS t
JOIN helpdesk.hd_tickets__tags AS tg ON tg.sp_item_id = t.sp_item_id
JOIN helpdesk.sp_users AS u ON u.id = t.assigned_to_user_id
WHERE tg.value = 'VPN' AND t.status <> 'Closed' AND t.sp_deleted_at IS NULL
GROUP BY u.display_name
ORDER BY open_vpn DESC;

Each one runs over all 14,000 rows. The VPN question matches the tag itself, so a ticket that only mentions VPN in its description stays out of the count. The engineer who left only appears if open tickets are still assigned to him.

Point the agent at the tables

In Copilot Studio, open the agent, choose Add knowledge, and pick Azure SQL from the real-time connectors (under Advanced if it is not in the featured list). Sign in, select the tables or views, and write a description that says what a ticket is and what the status values mean. Microsoft indexes only table and column names, so those names are most of what the agent knows. A view of your own with plain column names, such as category, assigned_to and days_to_close, often gets better answers than the raw tables.

For SQL Server in your own data centre or PostgreSQL, which are not on Copilot Studio’s real-time connector list, give a custom agent a read-only SQL tool: an Azure AI Foundry agent with a query function, or any agent framework that can call one. The replica is the same either way.

How do you keep the table current and bounded?

Currency comes from the schedule. After the first full load, each run reads only what changed in SharePoint since the last one, so a short interval stays cheap and the table is usually a few minutes behind the list. A full reconcile, weekly by default, configurable, compares the whole list with the table as a QA check, and you can run one on demand with the command-line tool after a Microsoft 365 outage.

Deletes are kept. When someone deletes a ticket in SharePoint, the row stays in hd_tickets with sp_deleted_at set, so “what was closed or deleted last week?” has an answer. Active-ticket queries filter on sp_deleted_at IS NULL, which the reporting view already does for you.

Bounding what the agent sees is the database’s job, because the replica holds list items and their columns and carries no SharePoint item permissions. Create a role that can read the help-desk schema and nothing else:

-- Azure SQL or SQL Server
CREATE ROLE helpdesk_agent_reader;
GRANT SELECT ON SCHEMA::helpdesk TO helpdesk_agent_reader;

-- PostgreSQL: also cover tables a later sync adds
CREATE ROLE helpdesk_agent_reader NOLOGIN;
GRANT USAGE ON SCHEMA helpdesk TO helpdesk_agent_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA helpdesk TO helpdesk_agent_reader;
ALTER DEFAULT PRIVILEGES FOR ROLE spsync_writer IN SCHEMA helpdesk
  GRANT SELECT ON TABLES TO helpdesk_agent_reader;

Who goes in the role depends on the route. The Copilot Studio connector runs each question as the signed-in user, so add the users, or an Entra group created with CREATE USER [Help Desk Leads] FROM EXTERNAL PROVIDER, to helpdesk_agent_reader. A custom agent with its own SQL tool connects with its own login, so that login goes in the role.

On SQL Server a schema-level grant covers tables added later. On PostgreSQL the default privileges line does the same job for tables created by the sync account, named spsync_writer here. If team leads should only see their own categories, add row-level security on top: a security policy with a predicate function on SQL Server, or CREATE POLICY on PostgreSQL. Because the connector runs as each user, those policies apply per person. SPSync itself never writes to SharePoint, so a badly formed query cannot change a ticket.

When is a SharePoint list as knowledge still fine?

For a lot of agents the list connection is the right choice. A policy, FAQ or contacts list of a few hundred items, where one or two items answer every question, is a good fit for retrieval. “Who is the escalation contact for printers?” is a lookup, and Copilot Studio handles it well.

The list connection also trims results to each user’s SharePoint permissions with no extra setup, and Microsoft describes it as real-time. If your list relies on item-level permissions that would be hard to model as row-level security, or the questions need the last minute of changes, keep the list as knowledge.

The line sits at aggregation. Once people ask how many, how long on average, who has the most, or what changed since last week, they need every row, typed, with the joins resolved, and a retrieval system cannot provide that. You can run both side by side: the list as knowledge for the lookup questions and the replica for the numbers.

If the Tickets list also feeds a Power BI report that times out, the same replica fixes that: see Why Power BI refreshes on SharePoint lists slow down. For Managed Instance networking, see Claims data from SharePoint in Azure SQL or Managed Instance. The same lists can go to several destinations, one replication each; if the one you need is missing, tell us. Licences are per installation and per destination family: see licensing.

Why Copilot cannot count the rows in your SharePoint list