Where Does the Data Live? Choosing Between Patch, Collect, and ClearCollect

Every Power Fx statement has a destination. The confusion between Patch, Collect, and ClearCollect is rarely about what the functions do — it is about where the result ends up. Once you pin that location, the right function picks itself.

This article introduces a simple warehouse inventory app. Over a few screens you will see the exact same data handled differently depending on whether it is being persisted to a database, staged for a batch job, or cached for performance. By the end the decision rule will feel automatic.

The Scenario: A Warehouse Stocktake

Let us build an inventory adjustment screen.

Data source: WarehouseStock (Dataverse or SharePoint list). Key columns: ProductID, ProductName, CurrentQty, AdjustedQty, LastCountDate. Local collections:

• colAdjustmentBatch — a scratchpad of pending stock changes.
• colProductCatalog — a cached copy of the product lookup table.

The user needs to search for a product, enter a new quantity, stage several of these adjustments, then submit the whole batch at once and refresh their local cache.

Patch — The Transactional Pen

Patch writes directly to a connected data source. Every call involves a network hop and a real transaction. Use it when a record must survive an app restart.

Creating a new stock record:

Patch(
WarehouseStock,
Defaults(WarehouseStock),
{
  ProductID: txtProductID.Text,
  ProductName: txtProductName.Text,
  CurrentQty: 0,
  LastCountDate: Now()
}
)

Updating an existing stock record directly:

Patch(
WarehouseStock,
galStockList.Selected,
{
  CurrentQty: Value(txtNewQty.Text),
  LastCountDate: Now()
}
)

The second argument is the key difference. Defaults(...) signals a create. An existing record signals an update.

Collect — The Scratchpad

Collect writes to a local table in the app's memory. No network, no persistence, instant feedback. It is the perfect tool for staging work before a final commit.

Adding a line item to the batch:

Collect(
colAdjustmentBatch,
{
  ProductID: galStockList.Selected.ProductID,
  ProductName: galStockList.Selected.ProductName,
  OldQty: galStockList.Selected.CurrentQty,
  NewQty: Value(txtNewQty.Text),
  AdjustedBy: User().FullName
}
)

Why not just Patch immediately? Network calls are expensive. If the user is making twenty adjustments, batching them into a forward-only commit is faster and far less prone to partial failures.

Committing the batch when the user hits "Submit":

ForAll(
colAdjustmentBatch,
Patch(
  WarehouseStock,
  Defaults(WarehouseStock),
  {
    ProductID: ProductID,
    AdjustedQty: NewQty,
    LastCountDate: Now()
  }
)
);
Clear(colAdjustmentBatch)

ClearCollect — The Camera Flash

ClearCollect empties a collection, then fills it. It is the perfect tool for refreshing a snapshot of your data source.

Caching the product catalog on app start:

ClearCollect(
colProductCatalog,
Filter(WarehouseStock, StockStatus = "Active")
)

Every drop-down and gallery on your adjustment screens now reads from colProductCatalog, not from WarehouseStock directly. This offloads query pressure from the data source and makes navigation feel instant.

Why not Collect for this? If you used Collect to refresh the cache, stale records would pile up alongside the new ones. ClearCollect guarantees the local table is a pristine mirror of the server query every time.

The Decision Rule

Here is the simple filter to run through when you reach for one of these functions:

1. Does this data have to survive a browser refresh?

   • Yes → Patch
   • No → ask the next question.

2. Am I adding items to a list, or replacing the whole picture?

   • Adding → Collect
   • Replacing → ClearCollect

Think of it like physical notes:

• Patch mails a letter. It leaves your possession permanently.
• Collect writes a sticky note and puts it on the desk.
• ClearCollect replaces the entire notepad with a fresh stack of paper.

Common Mistakes in the Inventory Context

The duplicate record glitch A worker reports that every adjustment creates two entries. The developer used Patch with a galStockList.Selected record, but the selected item was blank when the button fired. Patch received a blank second argument, treated it as a create, and wrote a new row.

Patch(
WarehouseStock,
galStockList.Selected,    // This can be blank!
{ CurrentQty: 100 }
)
// Results in a new row with only CurrentQty filled.

Fix: Guard the second argument or manage selection state explicitly with If(IsEmpty(...), Defaults(...), ...).

Unintended collection growth A developer cached WarehouseStock inside a timer using Collect. After ten minutes the collection held dozens of copies of the entire stock list, slowing the app to a crawl.

// Timer.OnTimerEnd
Collect(colProductCatalog, WarehouseStock)
// Every timer tick appends ALL rows again.

Fix: Use ClearCollect for any operation meant to replace the contents of a collection.

Security and Delegation Notes

• Security: Patch honours the data-source security model (column-level security, business rules). Collect and ClearCollect create data only in the app's memory. No data leaks to unauthorised users unless the formula itself removes a filter.
• Delegation: ClearCollect + Filter is still subject to the delegation limits of the Filter function. Power Apps fetches a maximum of 500 records by default (up to 2,000 if you raise the Data row limit setting in app settings). You cannot pull all rows of a large SharePoint list into a collection in one call. Use delegable comparisons (Status = "Active", Id > 1000) and consider paging patterns for very large datasets.
• Performance: A single Patch call is one round trip. A ForAll loop of Patch calls is N round trips. For high-latency connections, collect locally and submit through a single server-side action or Power Automate flow.

In Practice

Master the destination, and the function chooses itself. For your next Power Apps project, challenge yourself to categorise every data interaction into one of these three buckets before you write a single line of Power Fx. Your app will be faster, your code will be cleaner, and you will never accidentally mail a sticky note to the database again.

References

The discussion that inspired this article:

• PowerStack Editorial, ClearCollect, Collect, and Patch — when to use which

For further reading on Microsoft Learn:

• Patch function in Power Apps
• Collect and ClearCollect functions in Power Apps