Embedding Canvas Apps in Model-Driven Forms: Retrieving Related Table Columns of a Related Record

Posted on by Alex

In the following, I will describe key aspects of using the ModelDrivenFormIntegration control when embedding Power Apps Canvas apps into model-driven apps that need to retrieve related data of the current record (e.g., service contracts linked to a customer asset in a case). This needs to be implemented via the First([@ModelDrivenFormIntegration].Data).ItemId) formula.

If you are interested only in how I solve the challenge, jump directly to the paragraph Access related table columns of a related table record of the current form via the ModelDrivenFormIntegration

Project Scenario

A client needed a way to rapidly build an integration between Dynamics 365 Customer Engagement apps & SAP. Due to time and budget limitations, after considering multiple solutions we proposed the Power App Canvas Apps Embedded into model-driven apps approach via the ModelDrivenFormIntegration.

Approach

After a spirited debate over various options, I decided to utilize the Dataverse Virtual Tables and build canvas apps that would seamlessly integrate with the existing Dynamics 365 CE apps. By replicating the SAP data into a Microsoft SQL Database, which then served as the data source for building multiple Virtual Tables in the Dataverse. In this way we had a perfect balance between performance, usability, and licensing considerations. Our goal: to display SAP-related data across multiple Dynamics 365 form tabs, including accounts, opportunities, and cases etc. We needed the flexibility of showing different columns on the virtual tables and the Power Canvas FX offers such flexibility instead of limitation of virtual tables lookup fields.

Tip: If you use Virtual Tables, working with Lookup Fields is not so straightforward and it seems that is not working as described (or at least not for me and some members in the community) (at time of writing of this article 27 July 2024). This was a limitation that helped me decide quickly towards the embedded canvas apps scenario instead of showing virtual tables on forms. After discussing with Microsoft Support, it seems that lookup fields should work if you define both text and GUID columns into your virtual tables. This was not an approach to follow in our case because we had only a one-way synchronization SAP -> MS SQL DB -> Dataverse (Virtual Table).

Creating the Virtual Tables

Creating the Virtual Tables was a straightforward task, the only issue that I encountered during creation were caused by some duplicated fields that were created unmanaged during the recreation of the virtual tables (due to some changes in the MS SQL DB structure).

Tips:

  • Create the Virtual Tables directly in a solution from the Maker Portal and not the classic Solution Explorer
  • Transport the Virtual Tables in a managed solution to the different environments.
  • Create the Connection to your datasource (in my case Microsoft SQL) in target environment before importing the solution
  • In case you do not see any data in the virtual tables (Maker Portal), chec the Plug-in Trace Logs (Dynamics 365 Settings) – in my case there were some virtual table creation issues (duplicate fields that were unmanaged. By removing them, the problem got solved)

Building the Embedded Power Apps Canvas apps

Building for instance a Canvas App that displays the data from a virtual table (e.g. SAP Sales Quotes) connected to the Account form as defined in the DataSource property of the ModelDrivenFormIntegration component (e.g. Accounts table) is straightforward and easy task that can be solved by using the Filter formula on the Gallery Items property

Filter(
        'Sales Quotes', 
        'Potential Customer' = [@ModelDrivenFormIntegration].Item.'ERP Number'

    )

The formula filters the ‘Sales Quotes’ virtual table to return only those records where the ‘Potential Customer’ field matches the ‘ERP Number’ of the current account in the embedded canvas app.

The Challenge:

Access related table columns of a related table record of the current form via the ModelDrivenFormIntegration

The challenge emerged when I needed to filter records based on another related table of the lookup record added to the form.

More precisely, the user who openes a Case form needs to see the SAP Service Contracts that are linked/related to the Customer Asset which is related to the Case on the “Customr Asset” lookup field. Interesting to solve, right?

This required filtering based on the “ERP Equipment Number” field on the Customer Asset.

Being successful with the filtering formula turned into a bit of a trial-and-error journey. Before giving up, I did what every developer should do (with the last efforts), read the Microsoft’s official documentation, (ideally early in the development process). When I found my scenario documented, it threw a curveball: “The ModelDrivenFormIntegration control doesn’t provide a value for columns of a related table. For example, when the ModelDrivenFormIntegration control is connected to the Accounts table, using ModelDrivenFormIntegration.Item.’Primary Contact’.’Full Name’ won’t return a value.” With this newfound knowledge, I finally managed, creating the following formula for the Gallery Items Property:

Explaining the formula

Even after writing it is it hard to follow, so I will do my best to explain it properly 🙂

Filter(
    'SAP Customer Assets',
    'Customer Asset' = LookUp(
        Cases,
        Case = GUID(First([@ModelDrivenFormIntegration].Data).ItemId)
    ).'Customer Asset'.'ERP Equipment Number'

)

The formula is used to filter in a gallery only those ‘SAP Customer Assets’ virtual table by finding the correct GUID of the Customer Asset that is added on the Case form (Lookup field)

  • The Filter function returns records from ‘SAP Customer Assets’ where the ‘Customer Asset’ field matches the GUID value obtained from the inside LookUp function.
  • This LookUp function searches for a specific record in the Cases table where the ‘Case’ field matches the GUID-converted ‘ItemId’ from the first record in the ModelDrivenFormIntegration context data. It then retrieves the ‘ERP Equipment Number’ of the related ‘Customer Asset’.
  • The Filter function checks each record in ‘SAP Customer Assets’ to see if its ‘Customer Asset’ field matches this ‘ERP Equipment Number’. This process ensures that only the ‘SAP Customer Assets’ records associated with the specific case identified by the ‘ItemId’ from the form integration data are included in the result.

Detailed Step-by-Step Execution

  1. Retrieve the ItemId:
    First([@ModelDrivenFormIntegration].Data).ItemId retrieves the ItemId of the first record from the ModelDrivenFormIntegration context data, which in in my scenario is the internal ID that the ModelDrivenFormIntegration control is generating for the current case opened in the form
    (Note: ItemId is empty at authoring time but will have a value at runtime)
  2. Convert ItemId to GUID:
    GUID(First([@ModelDrivenFormIntegration].Data).ItemId) converts the ItemId to a GUID so that we can work with it in Power FX formulas.
  3. LookUp Matching Case:
    LookUp(Cases, Case = GUID(First([@ModelDrivenFormIntegration].Data).ItemId)) iterates over the Cases table to find the record where the Case field matches the GUID-converted ItemId.
  4. Retrieve ERP Equipment Number
    Once the Lookup identifies the current case, by using the dot notation in LookUp(…).’Customer Asset’.’ERP Equipment Number’ extracts the ERP Equipment Number from the matched record’s Customer Asset.
  5. Filter SAP Customer Assets:
    Filter(‘SAP Customer Assets’, ‘Customer Asset’ = …) iterates over each record in the SAP Customer Assets table. For each record, it checks if the Customer Asset field matches the ERP Equipment Number obtained from the LookUp function and displays the matched items in the gallery.

Conclusion

When you develop embedded canvas apps, keep in mind that GUID(First(ModelDrivenFormIntegration.Data).ItemId)).<Lookup Field>.<Column Name> is a crucial expression when embedding canvas apps, as it allows access to the unique internal ID of the current form record within the context of a model-driven form. This ensures precise data operations and interactions based on the specific record’s identifier.