Mastering the 'With' Function in PowerApps: A Comprehensive Guide
Power Apps Mastery: Unleashing the Power of the With Function
Power Fx enables PowerApps developers to build efficient and robust business apps with an arsenal of handy functions. The With function is used to simplify complex formulas by assigning a name to an expression or value, allowing for reuse elsewhere in the same formula. This can enhance readability and maintainability and prevent PowerApps from recalculating named values multiple times, leading to performance improvements in some cases.
💡To get started, let's explore how the Power Fx With function can be utilized. Below are some examples that showcase its adaptability.
1. Calculations with Shared Logic
Perform complex calculations that share common logic:
📝 Note in this example: Width, Height, & Depth are Named Formulas.
With({Area: Width * Height}, UpdateContext({Perimeter: 2 * (Width + Height), Volume: Area * Depth}))
3. Grouping Operations on a Single Record
Group multiple operations related to a specific record:
We will reduce the stock quantity by 1, and then show a notification with the updated total stock quantity.
With({CurrentProduct: Lookup(Products, ProductID = selectedProductID)}, Patch(Products, CurrentProduct, {Stock: CurrentProduct.Stock - 1}); Notify(CurrentProduct.Stock,NotificationType.Information))
4. Date and Time Manipulation
Extract and manipulate date and time components:
With({Today: Now()}, UpdateContext({YYYY: Year(Today), MM: Month(Today), DD: Day(Today)}))
5. Conditional Formatting and Styling
Define conditional styling rules and apply them:
IsAdmin is a global variable defined based on the user logged in to the app. Then we can use the HeaderColor and ButtonColor as colors for various controls. We can also define more variables related to the app's accessibility features, e.g., show the sales reports in a sales management app.
With({Style: If(IsAdmin, Color.Blue, Color.Red)}, UpdateContext({HeaderColor: Style, ButtonColor: Style}))
6. Navigating Hierarchical Data
Navigate through hierarchical data structures:
Here we have a 1-N relationship between the Categories and Subcategories tables. I am retrieving the subcategories that are related to a specific category.
With({SelectedCategory: Lookup(Categories, CategoryID = selectedCategoryID)}, UpdateContext({Subcategories: Filter(Subcategories, ParentID = SelectedCategory.CategoryID)}))
Some more practical applications can be smoothly executed using the With function.
1. Simple Arithmetic
A basic example of using With is for arithmetic calculations:
Here, the result is given the value of 50 * 10, so you can easily reference it in the following expression, add 50 to the Result & return 550.
With({Result: 50 * 10}, Result + 50)
2. Working with Collections
Handling collections is a breeze with the With function:
This example filters the Products table to include only those items with a Price greater than 100, then counts the number of rows in the resulting filtered set.
With({MyProducts: Filter(Products, Price > 100)}, CountRows(MyProducts))
3. Nested With
Nesting With functions allows for sequential computations:
This is an example of nested calculations. It adds the sum of two text inputs, multiplies the result by 5, and divides by 100 to find the markup percentage.
With({Sum: Value(TextInput1.Text) + Value(TextInput2.Text)}, With({Markup: Sum * 5}, Markup / 2 ) )
4. Using With for Gallery Items
Assuming you have gallery control with items from a product list, you might want to calculate the total price for a specific category.
This will sum the price of all items in the "Electronics" category and then format the total price as currency.
With({TotalPrice: Sum(Filter(Gallery1.AllItems, Category = "Electronics"), Price)}, Text(TotalPrice, "$0.00"))
5. Date Handling
Here, With is used to define start and end dates, which are then used to filter the Orders table to include only those orders within the specified date range.
With({StartDate: DateValue("01-01-2022"), EndDate: DateValue("12-31-2022")}, Filter(Orders, OrderDate >= StartDate, OrderDate <= EndDate))
6. Displaying User Information
Assuming a user profile with multiple fields, you can use the With function to simplify displaying the information. In this example, the Lookup function is used to find the current user's profile in the Users table based on their email. The With function assigns the resulting record to CurrentUser, making concatenating the relevant fields easy into a displayable multi-line text.
With({CurrentUser: Lookup(Users, ID = User().Email)}, Concatenate("Name: ", CurrentUser.FullName,Char(10), "Email: ", CurrentUser.Email,Char(10), "Phone: ", CurrentUser.Phone))
7. Converting Duration String to Time
Even regular expressions can be neatly handled: Here's what's happening in this example:
- Regular Expression Matching: The Match function uses a regular expression to extract the hours, minutes, and seconds from the given duration string "PT5H30M08S".
- Using Named Capture Groups: The named capture groups hours, minutes, and seconds in the regular expression allow you to reference these extracted values directly.
- Converting to Time: Inside the With block, the Time function takes the extracted values and constructs a time value.
- Result: If you bind this formula to a label control, it will display "5:30 AM". You can use the Text function to format the results to see the seconds.
With(Match("PT2H1M39S", "PT(?:(?<hours>\d+)H)?(?:(?<minutes>\d+)M)?(?:(?<seconds>\d+)S)?"), Time(Value(hours), Value(minutes), Value(seconds)))
8. Creating a New Order and Associated Details
The With function is excellent for handling database-like structures. In this example, the With function is used to wrap a pair of operations related to creating a new order and adding details to that order.
Here's what is happening:
🚀 Creating a New Order: The Patch function creates a new order record in the Orders table with the status "New". The result of this operation is named and then used in the following steps.
🚀 Adding Order Details: Inside the With block, the ForAll function iterates over the NewOrderDetails table. A new record is added to the OrderDetails table for each row in this table.
🚀 Referencing Values: Within the Patch function for the OrderDetails, you are using values from the previous steps:
🚀 OrderID comes from the result of the first Patch, representing the primary key of the newly created order.
🚀 Quantity and ProductID come from the current row in the NewOrderDetails table being processed by ForAll.
With({NewOrder: Patch(Orders, Defaults(Orders), {OrderStatus: "New"})}, ForAll(NewOrderDetails, Patch(OrderDetails, Defaults(OrderDetails), {OrderNumber: NewOrder, Quantity: Quantity, ProductID: ProductID})))
**NOTE**: OrderNumber is a Lookup Column in the Child table NewOrderDetails
9. Comparing Two Records from Different Sources
Compare records from different sources effortlessly:
Here's what this code does:
🚀 Getting a Specific Order: Inside the With function, the Lookup function finds the order with ID "Order123" in the Orders table. The result is assigned to the variable SpecificOrder.
🚀 Comparing Columns: Next, inside the With block, an If function compares the columns of SpecificOrder with the currently selected item in Gallery1. In this case, it's comparing the CustomerID, OrderDate, and TotalAmount columns.
🚀 Returning a Result: Depending on the comparison result, the expression returns a string indicating whether the orders are identical.
🚀 You can use whatever function in the place of the true or false.
With({SpecificOrder: Lookup(Orders, OrderID = "Order123")}, If(SpecificOrder.CustomerID = Gallery1.Selected.CustomerID && SpecificOrder.OrderDate = Gallery1.Selected.OrderDate && SpecificOrder.TotalAmount = Gallery1.Selected.TotalAmount, true, false))
10. Evaluating Process Status and Emailing User
Suppose you have a Processes table that includes information about various ongoing processes, including a Status field and an AssignedTo field containing the email of the responsible user. You want to check a specific process with ProcessID "P123", evaluate its status, and send an email notification if the status has changed.
Here's a breakdown of what's happening:
🚀 Getting Process Record: The Lookup function finds the process ID "P123" record in the Processes table. The result is assigned to the variable ProcessRecord. (This can be a dynamic ProcessID)
🚀 Evaluating Status: The Switch function evaluates the Status field of ProcessRecord. Depending on the value, it triggers one of the corresponding email-sending functions.
🚀 Sending Emails: The Office365Outlook.SendEmail function sends emails to the user specified in the AssignedTo field of ProcessRecord. The email subject and body are constructed based on the process status.
📝 Note: To use the Office365.SendEmail function requires the Office 365 Outlook connector to properly be configured in your PowerApps.
With({ProcessRecord: Lookup(Processes, ProcessID = "P123")}, Switch(ProcessRecord.Status, "Completed", Office365.SendEmail(ProcessRecord.AssignedTo, "Process Completed", Concatenate("Process ", ProcessRecord.ProcessID, "has been completed"))))
11. Adding Checked Gallery Items to a Collection
Integrate checkbox controls within galleries for interactive features: (this can be other controls like toggle and radio controls)
Here's what's happening in this code:
🚀 Filtering Checked Items: Inside the With function, the Filter function creates a collection of items from the gallery where the checkbox control is checked (Checkbox1.Value = true). This collection is assigned to the variable CheckedItems.
🚀 Iterating Through Checked Items: The ForAll function iterates through the CheckedItems collection.
🚀 Adding to Collection: Inside the loop, the Collect function adds each checked item to a collection named CollectedProducts. You can specify the fields you want to include in the collection, such as ProductID, ProductName, and Price.
🚀 This code segment will result in the CollectedProducts collection containing all the records from the gallery that were checked.
With({CheckedItems: Filter(Gallery1.AllItems, Checkbox1.Value = true)}, ForAll(CheckedItems, Collect(CollectedProducts, {ProductID: ProductID, ProductName: ProductName, Price: Price})))
Conclusion
Utilizing the With function in Power Apps is a game-changing function that allows developers to be more adaptable, productive, and well-organized in their app's Power Fx formula development. Take advantage of the examples in this blog to elevate your coding techniques and revolutionize how you approach Power Apps development.
Comments
-
Mastering the 'With' Function in PowerApps: A Comprehensive Guide
A hugely underrated function, great article!
*This post is locked for comments