Batch Order Line Mastery with Power Apps in Business Central

In the realm of Microsoft Dynamics 365 Business Central, managing sales order lines can sometimes feel cumbersome, especially when dealing with bulk updates. If you've been nodding along in agreement, or if you've been left scratching your head after encountering errors with the Business Central Standard API v2.0 connector, then you've landed in the right place.

In our fast-paced digital world, both efficiency and automation are paramount to maintaining a competitive edge. However, when confronted with the challenges of updating multiple sales order lines, the task can appear daunting. But, fear not! With the integrative power of Microsoft Power Apps, there are effective and straightforward solutions at your fingertips.

Mastering Batch Sales Order Line Updates in Business Central with Power Apps Strategies

In this guide, we'll unravel the intricacies of batch updating in Business Central, spotlighting four distinct strategies that will redefine how you approach sales order line modifications. Whether you're new to Dynamics 365 and Power Apps or a seasoned user aiming for optimization, this blog will offer invaluable insights, techniques, and code snippets to elevate your sales order management game. Ready to dive into the world of efficient batch updates? Let's get started!

The Business Central Standard API v2.0 connector for the sales order lines, as well as some other Business Central tables require you to supply either the id or the document id on the call. This can make your batch update development efforts more challenging if you are used to using common approaches when retrieving the base record in a Patch. I define common approaches as using Lookup, Search, and Filter functions.

Let's first talk about what specifically does NOT work with the Standard API v2.0 connector when attempting to update sales order lines in a batch mode. Unlike updating just one sales order line at a time, attempting to update multiple line items by iterating through the lines using common approaches will cause the code to fail. For example, if you try to update the sales order lines using the following code you will get an error saying that you must specify the id or document Id to get the lines. Note: There are other issues with using a LookUp with Business Central data, but that will need to wait for another blog.

ForAll(
    Gallery1.AllItems,
    Patch(
        'salesOrderLines (v2.0)',
        LookUp(
            'salesOrderLines (v2.0)',
            ThisRecord.'Id' = 'Id'
        ),
        {Quantity: 1}
    )
)

What does work? Here are four simple alternate approaches to remedy the batch update dilemma:

1. Use a gallery record as the base record when calling the Patch. No need to do a Lookup, First, and/or Filter to get the base record. You use the ThisRecord from the Gallery control in a ForAll as the base when iterating through records. You must first bind the gallery to the salesOrderLines. In this example ALL the salesOrderLines will be set to a quantity of 1. This approach is good if you are using a gallery in your design.

ForAll(
    Gallery1.AllItems,
    Patch(
        'salesOrderLines (v2.0)',
        ThisRecord,
        {Quantity: 1}
    )
)

2. Use a cloned collection record as the base record when calling the Patch. In this approach you have cloned the salesOrderLines using a ClearCollect and called it SalesLines. You modify the SalesLines quantities. You then update the salesOrderLines based upon the Quantity value on each of the SalesLines records as you iterate. This approach is good if you have been manipulating and updating the SalesLines quantities before updating the SalesOrderLines.

ClearCollect(
    SalesLines,
    Filter(
        'salesOrderLines (v2.0)',
        'Document Id' = GUID("e31ae847-7d00-ee11-8f75-6045bdacccd6")
    )
);
// Modify the SalesLines Quantities Here
ForAll(
    SalesLines,
    Patch(
        'salesOrderLines (v2.0)',
        ThisRecord,
        {Quantity: ThisRecord.Quantity}
    )
)

3. Use a cloned collection record as the second merge record when calling the Patch. In this approach you are doing a merge between the salesOrderLines and the SalesLines. The later property values of the SalesLines will override the earlier values of the salesOrderLines. This is a good approach if you are updating multiple SalesLines columns.

ClearCollect(
    SalesLines,
    Filter(
        'salesOrderLines (v2.0)',
        'Document Id' = GUID("e31ae847-7d00-ee11-8f75-6045bdacccd6")
    )
);
// Modify the SalesLines Quantities + Other Columns Here
ForAll(
    SalesLines,
    Patch(
        'salesOrderLines (v2.0)',
        ThisRecord
    )
)

4. Use an Index to reference a base record when calling the Patch. In this approach we use an Index to reference a record in a collection that will be used as the base record. This is a good approach if you have added additional custom collections and want to use data from new columns in the process of updating the salesOrderLines.

In this example we first create a sub-set of the SalesLines called SalesLinesSimple. SalesLinesSimple only has the id column. We next add a factor column to SalesLinesSimple and call it SalesLinesSimplePlus. We then use a Sequence in the Forall loop that will serve as our Index to both the SalesOrderLines and SalesLinesSimplePlus collections. The end result is we Patch the salesOrderLines records with a new Quantity that has been modifed using the factor column in the SalesLinesSimplePlus collection.

ClearCollect(
    SalesLines,
    Filter(
        'salesOrderLines (v2.0)',
        'Document Id' = GUID("e31ae847-7d00-ee11-8f75-6045bdacccd6")
    )
);
ClearCollect(
    SalesLinesSimple,
    ForAll(
        SalesLines,
        {id: ThisRecord.Id}
    )
);
ClearCollect(
    SalesLinesSimplePlus,
    AddColumns(
        SalesLinesSimple,
        "factor",
        0.20
    )
);
ForAll(
    Sequence(CountRows(SalesLines)),
    Patch(
        'salesOrderLines (v2.0)',
        Index(
            SalesLines,
            Value
        ),
        {
            Quantity: Round(
                Index(
                    SalesLines,
                    Value
                ).Quantity + (Index(
                    SalesLinesSimplePlus,
                    Value
                ).factor * Index(
                    SalesLines,
                    Value
                ).Quantity),
                0
            )
        }
    )
);

As we wrap up our exploration into batch updating sales order lines in Microsoft Dynamics 365 Business Central using Power Apps, it's evident that innovation and efficiency come hand in hand. The standard API v2.0 connector may have its nuances, but with the right techniques, we can harness its full potential.

The four approaches we've delved into each offer unique advantages, allowing users to pick and choose based on their specific requirements. Whether you're aiming for streamlined code or seeking flexibility in manipulating data, there's a method tailored for you.

Remember, in the world of digital business, continuous learning and adaptability are the keys to success. By leveraging the synergy between Dynamics 365 and Power Apps, you're not just updating sales order lines—you're revolutionizing your business processes for the better.

Thank you for joining us on this informative journey. May your sales order management be as smooth and efficient as ever. Happy coding!