Business Central Complex Data: Power Apps 'With' Function

Leveraging the Power Apps "With" function offers a streamlined approach to manipulate and process data from Microsoft's Business Central, particularly when dealing with complex datasets such as those found in the Sales Order and Sales Order Lines tables. This function enables the evaluation of a formula within the context of a single record, providing a powerful tool for tasks such as aggregating sales totals from the Sales Order Lines.

Leverage Power App's "With" Function with Business Central Complex Data

By utilizing the "With" function, you can efficiently compute values for individual records, which is especially useful for summarizing sales amounts. Moreover, this function facilitates the creation of collections, which can then be utilized as data sources for tables displayed on a user interface. This capability not only enhances data manipulation efficiency but also simplifies the process of presenting and interacting with Business Central data in Power Apps.

In this detailed guide, we will explore the steps to construct a Power App that employs the "With" function to effectively summarize and analyze sales order data. This function is a cornerstone for those looking to streamline the processing of detailed information within their applications, particularly when dealing with the intricacies of sales orders.

The Example Code

The example code provided is a practical demonstration of how to leverage the Power Apps "With" function to aggregate and present sales order information.

Clear(SalesOrderSummary);
ForAll(
    'salesOrders (v2.0)' As OrderHeader,
    With(
        {
            _OrderDetail: Filter(
                'salesOrderLines (v2.0)',
                'Document Id' = OrderHeader.Id,
                'Amount Excluding Tax' > 0
            ),
            _CustomerName: LookUp(
                'customers (v2.0)',
                'No.' = OrderHeader.'Bill-to Customer No.',
                'Display Name'
            )
        },
        Collect(
            SalesOrderSummary,
            {
                orderNumber: OrderHeader.'No.',
                orderDate: OrderHeader.'Order Date',
                customerName: _CustomerName,
                recordCount: CountRows(_OrderDetail),
                totalAmount: If(
                    CountRows(_OrderDetail) = 0,
                    0,
                    Sum(
                        _OrderDetail,
                        'Amount Excluding Tax'
                    )
                )
            }
        )
    )
)

Here's a breakdown of what the code accomplishes:

1. Initialization of the SalesOrderSummary Collection: The Clear(SalesOrderSummary) function initializes the collection by clearing any existing data. This ensures that the collection is ready to store new summaries of sales order data.

2. Iterating Over Sales Orders: The ForAll function iterates over each record in the 'salesOrders (v2.0)' data source, aliasing each record as OrderHeader. This loop is where the bulk of processing occurs, allowing for operations on each sales order record.

3. Detail Filtering and Customer Name Lookup:

• Inside the With function, a temporary record is created with two fields: OrderDetail and CustomerName.

• _OrderDetail uses the Filter function to retrieve lines from 'salesOrderLines (v2.0)' that are associated with the current OrderHeader by matching Document Id and ensuring that the 'Amount Excluding Tax' is greater than 0.

• _CustomerName employs the LookUp function to find the customer's display name from the 'customers (v2.0)' data source based on the 'Bill-to Customer No.' from OrderHeader.

1. Aggregating Data into SalesOrderSummary:

• The Collect function then aggregates this information into the SalesOrderSummary collection. For each order, it includes the order number, order date, customer name, count of order detail records, and the total amount excluding tax.

• The total amount is calculated using the Sum function on _OrderDetail, specifically summing up the 'Amount Excluding Tax'. If there are no detail records, the total amount is set to 0.
  

This code exemplifies a powerful use case of the "With" function in Power Apps, enabling developers to efficiently summarize complex datasets by reducing the number of times data sources are accessed. By creating a detailed summary of sales orders, including the total amount for each order and the associated customer name, this approach enhances data visibility and usability within the app. Through this step-by-step explanation, you'll gain insights into crafting sophisticated data processing flows within your Power Apps applications, making your data work harder and smarter for you.

Creating the App

Here are the steps to create an app using the With function to process the sales order and sales order lines from Business Central:

1. Add a Clear function that will clear the SalesOrderSummary collection. We will be building this collection as we loop through the sales order headers.
   

Clear the SalesOrderSummary Collection

2. Add a ForAll function on the salesOrders and rename as OrderHeader. We will be looping through the sales order header records.

The Forall Function

3. Add the With function. All following functions will fall within the scope of this With.

The "With" Function

4. Create two values called OrderDetail and CustomerName. With the OrderDetail value we are creating a collection of sales order lines that are associated with the sales order header. With the CustomerName value we are getting the name of the customer on the sales order header.

OrderDetal and CustomerName Values

5. Next we are going to append the SalesOrderSummary collection with information we collected above in each loop of the sales order header. We will create a salesNumber and salesDate column from the OrderHeader, create the customerName from the CustomerName value, and we get the recordCount and totalAmount from the OrderDetail data.

Collect SalesOrderSummary Data

Using the SalesOrderSummary Collection

With the SalesOrderSummary collection formed, it can be showcased within a table on an app screen. Here, a button triggers the execution of the aforementioned code, demonstrating a practical application of dynamically presenting summarized sales order information within a Power Apps interface. This walkthrough not only clarifies the process of app creation using the "With" function but also underscores the function's role in enhancing data processing and presentation capabilities within Power Apps.

The Sales Order Presentation

Conclusion

In conclusion, the Power Apps "With" function emerges as a powerful ally in the realm of data processing and presentation, particularly for those navigating the complex datasets of Microsoft Business Central. Through the example provided, we've seen how this function can simplify the summarization of sales order information, enabling developers to create more efficient and user-friendly applications. The ability to streamline data manipulation, reduce redundant data source accesses, and enhance UI presentations underscores the "With" function's value in any Power Apps developer's toolkit.