Power Fx CRUD Operations: Patch, Filter, and LookUp

Reading Data: The "R" in CRUD

Before we can modify data, we need to be able to find and display it effectively. This is where Filter() and LookUp() come in.

Filtering for Multiple Records with Filter()

The Filter() function returns a table of records from your data source that satisfy a formula. Its syntax is Filter(DataSource, LogicalFormula).

Imagine you have a gallery connected to a 'Projects' table. To show only the projects that are "In Progress", you would set the gallery's Items property to:

Filter(Projects, Status = "In Progress")

You can also combine multiple conditions. For instance, to find all "In Progress" projects with a budget over $50,000:

Filter(Projects, Status = "In Progress" And Budget > 50000)

This is a highly common pattern for creating searchable galleries or lists in your apps.

A crucial reminder on delegation: As we discussed in the last lesson, always ensure your Filter conditions are delegable to the data source (especially for SharePoint and SQL) to avoid performance issues and incomplete data sets.

Finding a Single Record with LookUp()

When you only need to find the first record that matches a condition, LookUp() is more efficient than Filter(). It returns a single record, not a table. Its syntax is similar: LookUp(DataSource, LogicalFormula).

This is incredibly useful for retrieving the complete details of an item selected in a gallery to display them on an edit form. For example, to set the Item property of a form to display the details of the selected project from Gallery1:

LookUp(Projects, ProjectID = Gallery1.Selected.ProjectID)

Often, a simpler way is to directly use the selected item: Gallery1.Selected. LookUp becomes essential when you need to find a record based on criteria other than a gallery selection.

Creating and Updating Data: The "C" and "U" with Patch()

The Patch() function is one of the most powerful and versatile functions in Power Fx. It's used to both create new records and modify existing ones.

The basic syntax is: Patch(DataSource, BaseRecord, ChangeRecord)

• DataSource: The table you want to modify (e.g., Projects).
• BaseRecord: The record to modify. To create a new record, you use Defaults(DataSource).
• ChangeRecord: A record containing the new or updated values for the columns (e.g., { Title: "New Project", Status: "Not Started" }).

Creating a New Record

To create a new record, you use Defaults() to provide a blank template for Patch() to fill in.

The code for creating a basic record looks like this:

Patch(
    Projects,
    Defaults(Projects),
    {
        Title: "Project Alpha",
        Budget: 75000,
        Status: "Not Started"
    }
)

Updating an Existing Record

To update an existing record, the BaseRecord must identify the specific record you want to change. You typically do this using the selected item from a gallery (Gallery1.Selected) or by using LookUp() to find the record first.

// Update the status of the item selected in a gallery
Patch(
    Projects,
    Gallery1.Selected,
    {
        Status: "Completed"
    }
)

A Practical Pattern: The Combined "Save" Button

In most apps, you'll use the same form for both creating and editing. A common pattern is to have a single "Save" button that intelligently handles both cases. You can achieve this by checking if a variable holding the selected record is blank.

Patching Complex Dataverse Column Types

Your experience with Dynamics 365 means you're familiar with complex data types like Lookups, Choices, and Customer fields. Patching these in Power Apps requires a specific syntax for each type. This is a common area where developers get stuck, and mastering it is key for the PL-400 exam.

The following video is a practical masterclass on this topic. It shows how to build a form and write a single Patch function that handles all common Dataverse column types.

For a handy text reference, this article breaks down the Patch syntax for each complex Dataverse data type with clear code examples. It's an excellent resource to bookmark.

Deleting Data: The "D" in CRUD

To delete records, you use the Remove() or RemoveIf() functions.

• Remove(DataSource, RecordToRemove): Deletes a single, specific record. This is commonly used with a gallery's selected item.

  // Add to a trash can icon's OnSelect property in a gallery
  Remove(Projects, ThisItem)
  

• RemoveIf(DataSource, LogicalFormula): Deletes all records that match a condition. This is very powerful and should be used with care. It's analogous to SQL's DELETE FROM ... WHERE ....

  // Deletes all completed projects
  RemoveIf(Projects, Status = "Completed")
  

The Patch function video by Dhruvin Shah also includes a quick demonstration of the Remove function from 17:35 to 18:02.

Conclusion

You now have the fundamental Power Fx tools to build fully functional, data-driven applications. Mastering these functions is the bedrock of Canvas App development.

Key Takeaways:

• Read: Use Filter() to get a table of records and LookUp() to get the first matching record.
• Create: Use Patch() with Defaults(DataSource) to create a new record.
• Update: Use Patch() with a specific record reference (like Gallery.Selected) to modify an existing record.
• Complex Types: Pay close attention to the specific syntax required to patch complex Dataverse columns like Lookups and Choices.
• Delete: Use Remove() for single records and RemoveIf() for bulk deletion based on a condition.

Up Next:
So far, we have manipulated data that lives permanently in our data source. In the next lesson, "Manage app state and temporary data using collections and variables," we will explore how to create and manage data that exists only while the app is running. This is essential for improving performance, managing complex UI state, and preparing data before sending it to the data source.