Query Builder Coming soon
Build and test CAML and OData
Build a list query once and get it three ways: a CAML view, an OData query string and a PnPjs snippet. All three come from one expression, so AND, OR and brackets mean the same thing in each. Test it against the list and see exactly how SharePoint answers, including the list view threshold.
What it tells you
- Why does this CAML query return nothing, or get refused on a big list?
- What is the OData filter for this condition, with the quotes escaped properly?
- Is the column I am filtering on indexed?
How it runs
- Sign in. One sign-in when you click the tile. Any of the three credentials works.
- Choose a site. Then a list. Lists over 5,000 items are flagged.
- Build. Filter rows with brackets, the columns to show, the sort, the row limit and the scope.
- Test. Test CAML posts the view; Test OData reads the items endpoint. One request each, never more.
- Use it. Copy any of the three, copy as REST, save the query, or export the results to Excel.
How it works
The Query Builder only reads. It loads the site's lists with their item counts and the chosen list's columns, and every filter row, bracket, column choice, sort, row limit and folder scope feeds one expression from which the CAML, the OData query string and the PnPjs snippet are all written. A filter that cannot be built (unbalanced brackets, an operator that does not suit the column) is shown as an error and the tests are disabled until it is fixed.
Today plus or minus a number of days is left to SharePoint in the CAML, so a saved CAML query keeps meaning today. OData has no such token, so its date is worked out when the query is built and written as a fixed date. On a list over 5,000 items, the page warns when the first filter is not on an indexed column, because SharePoint will refuse that query.
Test CAML asks SharePoint to render the list with your CAML as the view; Test OData reads the list's items with your query string. Each sends exactly one request as you and is deliberately not routed through the threshold-safe reading the rest of ShareMaster uses, so you see SharePoint's real answer. A list view threshold refusal is explained as such with the indexing advice, never shown as an empty result. The grid shows a lookup or person value by its display text.
Saved queries are kept in a file on this computer and hold the query's definition (site, list, filters, columns, sort, limit and scope), not its results. Export results writes the rows from the last test and a second sheet with the site, list, CAML and OData that produced them.
What you need
- Read access to the list. The tests run as you, so you see what this sign-in can see.
What it will not do
- Each test reads one page of at most 5,000 rows. There is no paging loop, on purpose: the point is to see how SharePoint answers this exact query.
- Lookup and person columns are compared by item or user id.
- SharePoint's items API cannot filter a managed metadata column; the OData test is disabled for that and Test CAML still works.
- An OData address over 2,048 characters is refused by some proxies, so the OData test is disabled past it. The CAML test is a POST body and still works.