Power Apps: Consolidated Business Central Sales Orders

You use the Microsoft Dynamics 365 Business Central Standard APIs v2.0 connector when working with Microsoft Power Apps. You want to show a list of all sales order lines in Business Central with a quantity greater than 5. But, when you create a gallery in your canvas app and set the Data source for the gallery to sales order lines table you get an error. What is the problem?

The connector expects you to filter the sales order lines by the id and/or documentId. How are you going to supply an id or documentId for all sales order lines? The answer is you create a collection to solve your problem.

A Power Apps collection is an in memory store of data. Collections are great when you want to combine data or manipulate data to serve your purposes. Let's go ahead and solve our problem using a collection.

1. We use a clearcollect to create a temporary collection we call SalesLinesTemp. First, we will iterate on the sales orders. Then we will iterate on the associated sales order lines. Our link between the two document is the sales order id. When we iterate through the sales order lines we will also set a filter so that we only get sales order lines with a quantity greater than 5. We return in our SalesLinesTemp collection a value that is a table with the sales order number, customer number, item number, item description, requested delivery date, and quantity values for each sales order line and the associated sales order. Here is the code:

// Return a collection with both sales order and sales order line data
ClearCollect(
    SalesLinesTemp,
    ForAll(
        'salesOrders (v2.0)',
        ForAll(
            Filter(
                'salesOrderLines (v2.0)',
                'Document Id' = 'salesOrders (v2.0)'[@Id],
                Quantity > 5
            ),
            {
                orderNo: 'No.',
                customerNo: 'Bill-to Customer No.',
                itemNo: 'Line Object No.',
                itemDescription: Description,
                requestedDeliveryDate: 'Requested Delivery Date',
                quantity: Quantity
            }
        )
    )
);

2. In our second step we take the data we created in our table value and move it to a new collection we call SalesLines. We simply ungroup the SalesLinesTemp collection we created in step number one. Here is the code:

// Create a new collection by ungrouping the temp collection
ClearCollect(
    SalesLines,
    Ungroup(
        SalesLinesTemp,
        "Value"
    )
);

We now have the SalesLines collection as a result with the columns specified in step number one available to use in our gallery. Here is what the SalesLines collection looks like when inspecting the table data.

Business Central sales order lines in a Power Apps collection

Now, simply set the data source in your gallery to the SalesLines collection you just created and reference the new column names. You are good to go.