Power Apps - Patch function

-


Power Apps, a part of the Microsoft Power Platform, empowers users to build custom business applications without the need for extensive coding knowledge. Among its many powerful functions, the Patch function stands out as a versatile tool for modifying data sources, such as SharePoint lists or databases. In this comprehensive guide, we'll delve into the intricacies of the Patch function, exploring its syntax, common use cases, and advanced techniques.

Understanding the Patch Function

The Patch function in Power Apps is used to modify or create records in a data source. Its syntax is as follows:

Patch(DataSource, Defaults(DataSource), ChangeRecord1 [, ChangeRecord2, …])

  • DataSource: The data source to be modified.
  • Defaults(DataSource): A reference to the default values of the data source.
  • ChangeRecord1, ChangeRecord2, …: One or more records containing the changes to be applied to the data source.


Basic Examples


  2. Updating a Record:


  3. Patch(MyDataSource, LookUp(MyDataSource, ID = 1), {Name: "Updated Name"})

  4. This example updates the record in MyDataSource where the ID is 1, changing the value of the Name field to "Updated Name".


  1. Creating a New Record:


  2. Patch(MyDataSource, Defaults(MyDataSource), {Name: "New Record"})

  3. This code creates a new record in MyDataSource with the Name field set to "New Record", using the default values for other fields.


Advanced Examples

  1. Conditional Updates:


  2. Patch(MyDataSource, LookUp(MyDataSource, ID = 1), {Name: If(TextInput1.Text <> "", TextInput1.Text, Defaults(MyDataSource).Name)})


  4. This example updates the Name field of the record where the ID is 1. If a TextInput control (TextInput1) is not empty, it sets the Name field to the value of the TextInput; otherwise, it retains the existing Name value.



  7. Batch Updates:


  8. Patch(MyDataSource, Defaults(MyDataSource), {Name: "Record 1"}, {Name: "Record 2"}, {Name: "Record 3"})


  10. Here, multiple records are created in MyDataSource with the Name fields set to "Record 1", "Record 2", and "Record 3", respectively.


  1. Deleting Records:


  2. Patch(MyDataSource, LookUp(MyDataSource, ID = 1), {IsDeleted: true})

  3. This code soft-deletes the record where the ID is 1 by setting the IsDeleted field to true.



Best Practices

  • Error Handling: Implement error handling mechanisms, such as using the Errors function, to handle potential errors during the Patch operation.


  • Performance Optimization: Minimize the number of Patch operations by batching updates whenever possible to optimize performance.


  • Security Considerations: Ensure that appropriate security measures are in place to prevent unauthorized access to sensitive data sources.


Merge records (outside of a data source)


Patch( { Name: "James", Score: 90 }, { Name: "Jim", Passed: true } )

Merges two records outside of a data source:
  • The values in the Name column of each record don't match. The result contains the value (Jim) in the record that's closer to the end of the argument list instead of the value (James) in the record that's closer to the start.
  • The first record contains a column (Score) that doesn't exist in the second record. The result contains that column with its value (90).
  • The second record contains a column (Passed) that doesn't exist in the first record. The result contains that column with its value (true).
Results:

{ Name: "Jim", Score: 90, Passed: true }


Use of As or ThisRecord

Using the As or ThisRecord keyword in the formula avoids ambiguous evaluation context.

In the example below, consider the first lookup in the If statement. (OrderID = A[@OrderID]) is expected to compare the OrderId in the lookup scope with the OrderId of collection A in the ForAll scope. In this case, you likely want A[@OrderId] to be resolved as a local parameter. But it is ambiguous.

Power Apps currently interprets both the left-hand side OrderId and right-hand side A[@OrderId] as a field in the lookup scope. Therefore, lookup will always find the first row in [dbo].[Orders1] because the condition is always true (that is, any row's OrderId is equal to itself.)


ClearCollect( A, Filter( '[dbo].[Orders1]', OrderId = 8888888 ) ); ForAll( A, If( LookUp( '[dbo].[Orders1]', OrderId = A[@OrderId], "OK" ) = "OK", Patch( '[dbo].[Orders1]', LookUp( '[dbo].[Orders1]', OrderId = A[@OrderId] ), { OrderName: "val1" } ), Patch( '[dbo].[Orders1]', Defaults('[dbo].[Orders1]'), { OrderName: "val2" } ) ) )


Using As or ThisRecord


Whenever possible use the As operator or the ThisRecord to disambiguate the left-hand side. As is recommended for the above scenario.

When your formula uses multiple scopes with ForAll, Filter, and Lookup on the same data source or table, it is possible that the scope parameters may collide with a same field elsewhere. Therefore, it is recommended to use the As operator or ThisRecord to resolve the field name and avoid ambiguity.

For example, you can use the As operator to disambiguate in the example below.


ClearCollect( A, Filter( '[dbo].[Orders1]', OrderId = 8888888 ) ); ForAll( A, If( LookUp( '[dbo].[Orders1]' As B, B.OrderId = A[@OrderId], "OK" ) = "OK", Patch( '[dbo].[Orders1]', LookUp( '[dbo].[Orders1]' As C, C.OrderId = A[@OrderId] ), { OrderName: "val1" } ), Patch( '[dbo].[Orders1]', Defaults('[dbo].[Orders1]'), { OrderName: "val2" } ) ) )


Alternatively, you can use ThisRecord for the same purpose.


ClearCollect( A, Filter( '[dbo].[Orders1]', OrderId = 8888888 ) ); ForAll( A, If( LookUp( '[dbo].[Orders1]', ThisRecord.OrderId = A[@OrderId], "OK" ) = "OK", Patch( '[dbo].[Orders1]', LookUp( '[dbo].[Orders1]', ThisRecord.OrderId = A[@OrderId] ), { OrderName: "val1" } ), Patch( '[dbo].[Orders1]', Defaults('[dbo].[Orders1]'), { OrderName: "val2" } ) ) )


Conclusion

The Patch function in Power Apps is a powerful way to modify data sources with flexibility and precision. By mastering its syntax and exploring various examples, developers can leverage its capabilities to create dynamic and efficient applications tailored to their business needs.

Whether you're updating existing records, creating new ones, or implementing complex conditional logic, the Patch function empowers you to manipulate data with ease and confidence in your Power Apps projects.


Comments

Post a Comment

Popular posts from this blog

-
Image
Power Apps PDF Function: A Step-by-Step Guide The PDF function lets you create a PDF document from a screen or specific controls in your app. Imagine being able to generate certificates, invoices, or reports on the fly and send them directly via email or integrate them into a Power Automate flow. Sounds awesome, right? In this post, I'll walk you through how I used the PDF function to send a certificate as an email attachment from Power Apps only. I'll share the steps I took and some handy tips along the way. Let's dive in! Step 1: Enable the PDF Function Open your app in Power Apps Studio. Navigate to Settings . Go to Updates : Under the Experimental tab , find the PDF function and toggle it on. The PDF function is off by default, so the first thing you need to do is enable it: 2. Set Up Your Data Source I wanted to personalize the certificates, so I built a simple table (or you can use a gallery) to act as a database. In my case, I created a collection " colTrain...
Read more
-
Image
Tracking Power Apps Canvas Usage Using Power FX Introduction Monitoring user activity in Power Apps canvas apps can provide valuable insights for improving app performance and user experience. By implementing a custom usage tracking solution, you can capture details about who is using the app, on what device, and in which environment. This data helps in troubleshooting issues and making data-driven decisions to enhance the app over time. Why Track Usage? Understand user behavior: Knowing who uses your app and their device type can help you identify usage patterns and user needs​. For example, you might discover most users are on mobile, influencing your design decisions. Improve performance: If you log device types, you can detect if certain devices or form factors correlate with performance issues. This allows you to optimize the app for those scenarios. Make data-driven decisions: Usage data (e.g. frequency of launches, device breakdown) can guide feature improvements and prioriti...
Read more
-
Image
Tips and tricks  to enhance your Power Automate development experience 1. Using flow templates: Flow templates  in  Microsoft Power Automate  are prebuilt or custom templates that allow you to automate tasks or processes between connectors. Here’s how you can use flow templates: Browse and Explore Templates : Visit the  Microsoft Power Automate Templates page . You’ll find a wide range of templates categorized by use case, such as remote work, approval workflows, data collection, email automation, and more. Explore the templates to discover what’s possible and find one that suits your needs. Benefits of Flow Templates : Time-Saving : Templates provide a head start by offering preconfigured actions. Best Practices : Templates often follow best practices for specific scenarios. Community Contributions : By submitting your own templates, you contribute to the Power Automate community. Remember to explore templates and leverage them to streamline your flow developme...
Read more