Let's Master on Data Manipulation: A Deep Dive into the Canvas App Patch Function
Updated: Oct 7
The Patch function is commonly used to create new records or update existing records in a data source
It can also be used to merge two or more records.
Bulk operation can also be done using it.
It's particularly useful when:
You want more control over which fields are updated and how data is transformed before updating.
You need to update multiple data sources in a single action.
You want to update or create records based on conditions, calculations, or complex logic.
Basic Syntax
Syntax to Create Record:
In this example, the Defaults function creates a new record with default values, and the Patch function fills in the specified field values.
Default with Patch Function
Default Function:
This function returns a record that contains the default values for the data source. If a column within the data source doesn't have a default value, that property won't be present.
You can combine the Defaults function with the Patch function to create a record.
Syntax: Defaults(DataSource)
Modify / Update using Patch Function
Syntax to Update Record:
In this example, the LookUp function is used to locate the employee with ID "123," and the Patch function updates the "Salary" field to 60000.
Merge using Patch Function
Syntax to Merge 2 Record:
It will not create/update record in data source, it will juts combine the records with later values overriding the earlier ones.
Bulk Operation using Patch Function
Modify or create a set of records in a data source
Patch can also be used to create or modify multiple records with a single call.
Instead of passing a single base record, a table of base records can be provided in the second argument. Change records are provided in a table as well, corresponding one-for-one with the base records. The number of records in each change table must be the same as the number of records in the base table.
When using Patch in this manner, the return value is also a table with each record corresponding one-for-one with the base and change records.
Error Handling
When you update a data source, one or more issues may arise. Use IfError and IsError with the return value from Patch to detect and respond to errors. You can also use the Errors function to identify and examine issues,
IfError
The IfError function tests values until it finds an error. If the function discovers an error, the function evaluates and returns a corresponding replacement value and stops further evaluation.
IsError
The IsError function tests for an error value.
The return value is a Boolean true or false.
Using IsError will prevent any further processing of the error.
IsError(1/0)
It will return true in the result.
Error
Use the Error function to create and report a custom error.
NOTE: The Patch function is a fundamental tool for working with data in Canvas Apps, and understanding its capabilities is essential for effective app development.
FAQs
How do I use the Patch function to update records?
To update records using the Patch function, you typically provide the target data source, the record you want to update, and the changes you want to make.
Can I use the Patch function to create new records?
Yes, you can use the Patch function to create new records by specifying a record that doesn't exist in the data source.
Can I use the Patch function with multiple records at once?
Yes, you can use the Patch function to update or create multiple records in a single call by using a ForAll or Collect loop. This is useful for bulk operations.
What happens if there's an error while patching data?
If an error occurs while using the Patch function, it can be handled using Error and Notify functions to display an error message to the user or perform other error-handling actions.
Does the Patch function support complex data sources like SharePoint lists or SQL databases?
Yes, the Patch function supports a wide range of data sources, including SharePoint lists, SQL Server databases, and custom connectors. You need to provide the appropriate connection and data source information in the function.
Are there any limitations or considerations when using the Patch function?
Yes, some limitations include data source-specific restrictions, delegation issues when working with large datasets, and the need to understand the schema of your data source for accurate updates. Be sure to consult the documentation and test thoroughly.
Can I use the Patch function in offline mode?
The Patch function may not work in offline mode for certain data sources, depending on the capabilities of the underlying data connector. It's essential to consider the offline capabilities of your chosen data source when designing your app.