Power Apps SharePoint search: find records beyond 2,000 rows
An equipment search that misses an existing pump can trigger an unnecessary hire, a duplicate record or a wasted phone call. Build a search whose correctness survives a growing register.
27 September 2026 · Canvas apps + SharePoint Online · For UK operations teams and app makers
Equipment search test bench
Query: LEI · active only · prefix PUMP
Target: PUMP-1001 and PUMP-1002 found.
Source: EquipmentRegister → SharePoint filter → gallery.
Start with a search promise you can test
“Find active equipment at my selected site by the beginning of its asset code” is a precise requirement. “Search everything” leaves too much undefined.
Agree the match
This tutorial searches an asset-code prefix. PUMP finds PUMP-1001; 1001 does not. Put that instruction beside the search box and check that staff know the code on the equipment label.
Query the source
Delegation lets SharePoint evaluate a supported query. A nondelegable query can inspect only the local allowance: 500 rows by default, configurable up to 2,000. Increasing it postpones the failure; it does not prove completeness.
Prove the result
Use known matching and excluded records. A fast screen with no error message is insufficient evidence. The acceptance decision belongs to the operations owner as well as the app maker.
Technical basis: Microsoft’s delegation overview and StartsWith reference.
Build a small, deliberate test register
Use a development app and a disposable SharePoint list. You need permission to create the list and connect a canvas app, plus the appropriate Power Apps entitlement for your organisation.
Create the list and columns
Name the list EquipmentRegister. Keep the built-in Title column and use it for the asset code; make it required and enforce unique values. Create EquipmentName and SiteCode as required Single line of text columns. Add IsActive as Yes/No, default Yes.
Use these exact names when creating the columns. This keeps the formula mapping predictable. For an existing list, confirm its actual names and types rather than renaming production fields to follow this example.
| Title | EquipmentName | SiteCode | IsActive |
|---|---|---|---|
| PUMP-1001 | Transfer pump | LEI | Yes |
| PUMP-1002 | Washdown pump | LEI | Yes |
| PUMP-1003 | Transfer pump | MAN | Yes |
| PUMP-1004 | Retired pump | LEI | No |
| FAN-2001 | Ventilation fan | LEI | Yes |
In a blank canvas app, use Data → Add data → SharePoint, select the site and connect EquipmentRegister. This tutorial uses classic controls so the property names below are explicit. Formula separators assume an English authoring locale.
Wire the search directly to SharePoint
Add a classic text input, dropdown, toggle and a standard vertical gallery. Rename them before entering the formulas.
| Control | Property | Value or formula |
|---|---|---|
| txtAssetPrefix | Default | "" |
| txtAssetPrefix | HintText | "Asset code starts with, e.g. PUMP" |
| txtAssetPrefix | AccessibleLabel | "Search by the beginning of the asset code" |
| txtAssetPrefix | DelayOutput | true |
| ddSite | Items | ["LEI", "MAN"] |
| ddSite | Default | "LEI" |
| ddSite | AccessibleLabel | "Choose a site" |
| tglActiveOnly | Default | true |
| tglActiveOnly | AccessibleLabel | "Show active equipment only" |
| galEquipment | AccessibleLabel | "Equipment matching the selected site and asset prefix" |
| galEquipment | Selectable | false |
Replace galEquipment.Items in full
SortByColumns(
Filter(
EquipmentRegister,
SiteCode = ddSite.Selected.Value,
IsActive = true || tglActiveOnly.Value = false,
StartsWith(Title, TrimEnds(txtAssetPrefix.Text))
),
"Title",
SortOrder.Ascending
)The site, active-state and prefix conditions must all pass. Switching active-only off allows both active and inactive equipment. A blank prefix shows the selected site’s equipment subject to that toggle.
TrimEnds cleans the input once for the query; it does not transform each stored asset code. Keep the data-source columns as direct references. SharePoint documents support for these text and Boolean comparisons, Filter, text StartsWith and text SortByColumns.
Check the SharePoint connector’s type-specific delegation table. The same formula must be reassessed if you replace these columns with Choice, Lookup or other types.
Make each result recognisable
In the gallery template, set the title label’s Text to ThisItem.Title. Set a second label’s Text to:
ThisItem.EquipmentName & " | " & ThisItem.SiteCode &
If(ThisItem.IsActive, " | Active", " | Inactive")Keep a visible label beside the toggle reading “Active equipment only”. Add a visible search instruction: “Enter the beginning of the asset code. Clear the field to browse this site.” DelayOutput = true gives typing a half-second pause before the input updates.
Do not label galEquipment.AllItemsCount as the total number of matches: it counts loaded gallery items. This search screen deliberately makes no complete-count claim.
References: classic text input and gallery properties.
Make incomplete retrieval fail visibly
You do not need thousands of rows to expose a local-processing dependency. Lower the allowance in your development copy, then ask for more known matches than it can hold.
Run the one-row experiment
Record the current setting. In app Settings, find Data row limit and temporarily set it to 1. Reopen the development app preview so previous local data does not confuse the test. Select LEI, keep active-only on and search for PUMP. Both PUMP-1001 and PUMP-1002 should appear.
Microsoft recommends the one-row setting to expose nondelegable work. Inspect any delegation warning, then test the complete formula. Restore the recorded setting before release and repeat the checks.
Optional negative control: in a separate test button, use the formula below. Run it after setting the limit to 1, then temporarily set the gallery’s Items to colLocalProbe. This local load cannot supply both expected pumps. It demonstrates why copying the list into a collection is not the fix.
ClearCollect(colLocalProbe, EquipmentRegister)After the experiment, restore the full galEquipment.Items formula above and remove the test button. ClearCollect is nondelegable against a data source; the demonstration is intentionally unsuitable for production search.
| Site / active-only / prefix | Expected codes | What it checks |
|---|---|---|
| LEI / On / PUMP | PUMP-1001, PUMP-1002 | Two matches survive a local allowance of one. |
| LEI / Off / PUMP | PUMP-1001, PUMP-1002, PUMP-1004 | Inactive records enter only when requested. |
| MAN / On / PUMP | PUMP-1003 | The site selection changes the result. |
| LEI / On / blank | FAN-2001, PUMP-1001, PUMP-1002 | Browse and ascending code order work. |
| LEI / On / 1001 | No matches | The promise is prefix search, not contains search. |
| LEI / On / PUMP-1002 | PUMP-1002 | A known individual code can be retrieved. |
Keep three separate risks separate
A correct formula is part of a reliable search service. Source design, permissions and the meaning of a match still need their own decisions.
Delegation is not indexing
SharePoint’s list-view threshold, commonly 5,000 items for relevant operations, is a different constraint from Power Apps’ local row allowance. Plan selective filters and suitable indexes with the list owner, including the site and asset-code fields used here.
Test the actual combined query at expected scale. Indexing a column does not turn an unsupported Power Fx operation into a delegable one.
A dropdown is not access control
The site selector in this tutorial organises results. Do not treat it as a security boundary. Agree and test source permissions independently, including what each user can access outside the app.
Keep test equipment free of personal or confidential data. The operations owner should approve which audiences can browse which register.
Prefix is a product choice
If staff only know a word in the middle of a description, this design may not meet their need. Observe their task before replacing contains search with prefix search.
For richer search, assess a source and query design that supports the required matching rules. Recheck cost, licensing, permissions and performance for that design.
Source-design reference: Microsoft’s large-list threshold guidance. The release controls here are operating recommendations.
Release a behaviour people can rely on
Give the change a named owner, a short user demonstration and measurable acceptance criteria.
Own the data and the experience
The list steward owns unique asset codes, site-code consistency and active-state updates. The app maker owns the formula, regression evidence and a restorable previous app version. The operations owner accepts the match rules and signs off the result set.
Pilot with a colleague who did not build the app. Ask them to find an old pump, include inactive equipment and search by a full code. If they repeatedly try description fragments, revisit the search requirement.
Measure avoided rework
Track known-record search failures, duplicate equipment entries, help requests and time to locate a verified asset. Compare similar shifts and sites, and investigate “not found” reports before assuming the asset is absent.
Illustrative example: 40 searches a day with 30 seconds less rework each release 20 minutes a day. Over 20 working days, that is 6 hours 40 minutes of potential capacity. Measure the improvement; it is not a cash-saving claim.
Check your release evidence
Assess the actual app, not this illustration. Both critical checks must pass before the remaining readiness checks can support a release review.
Frequently asked questions
Will setting the data row limit to 2,000 fix missing records?
No. It increases the local allowance for nondelegable work. A matching record outside that allowance can still be missed. Review the whole query and test known results.
Does this search find a word anywhere in the description?
No. It searches the beginning of the asset code stored in Title. Agree that behaviour with users; a contains-search requirement needs a different, verified design.
Can I copy the list into a collection first?
Not as a completeness fix. ClearCollect against a data source is nondelegable and can load only an initial portion. Searching that collection cannot recover records it never received.
Does the site filter secure the data?
No. This site filter is a browsing feature. Design and test source permissions independently, including access outside the app.
Make your next app decision on complete results
Smart Statistics helps UK businesses build Power Apps, improve SharePoint data design and test the processes their teams depend on. Bring us the search that works in a demo but struggles in daily use.