What is Issue?

While working on Canvas Apps in PowerApps, how to deal with Dataverse Tables with N:N relationships ?

The Reason-

In PowerApps, while working with multiple Dataverse tables it is easy to write expressions/formulas with simple columns as well as 1:N (1-to-Many) relationships between tables. But when there are Dataverse tables with N:N (Many-to-Many) relationships, the intersecting table is not available to use directly.

Solution-

Let’s first understand Dataverse tables with N:N (Many-to-Many) relationship.

Suppose, we have 2 tables “Contact” and “Software Service”.

“Software Service” table have lookup “Service Owner” that creates 1:N (1-to-Many) relationship between “Contact” and “Software Service” tables.

“Software Service” also needs multiple “Service Engineers”, So I have created N:N (Many-to-Many) relationship between “Software Service” and “Contact” tables.

Now I have scenario, In Canvas App I have to show list “Software Services” associated with “Contact” as either “Service Owner” or “Service Engineer”. And when it is “Service Engineer”, display his name in “Service Engineer” Column in the list else the “Service Engineer” Column will be blank.

Suppose, “Service 1” is having “Prashant Parkale” (contact record) as added “Service Owner” and contact records “Iron Man” and “Spider Man” added as “Service Engineers”.

If I open “Prashant Parkale” contact record, It should list “Service 1” record with “Service Engineer” column as blank.

If I open “Iron Man” or “Spider Man” contact record, it should list “Service 1” record with “Service Engineer” column as respective contact record name.

Let’s create Canvas App to embed in Contact form.

• Create Canvas App with “ModelDrivenFormIntegration” enabled to get current contact record context.
• Define contactRecord variable in Formula in App.

contactRecord=LookUp(Contacts,Contact= [@ModelDrivenFormIntegration].Item.Contact);

• Add Gallery control in App Screen
• Add below formula Items of Gallery control

Filter(
    'Software Services',
    Or(
        'Service Owner'.Contact = contactRecord.Contact,
        'Software Service' in Distinct(
            contactRecord.'Software Services (gen_SoftwareService_Contact_Contact)',
            'Software Service'
        )
    )
)

• ‘Service Owner’.Contact = contactRecord.Contact will filter out “Software Service” Records where current contact record is added as “Service Owner”
• contactRecord.’Software Services (gen_SoftwareService_Contact_Contact)’ is retrieving all the “Software Service” records related to current contact record with N:N (Many-to-Many) relationship gen_SoftwareService_Contact_Contact. This I am using as “Service Engineers”. But the fields are not directly accessible in the relationship. So utilizing the Distinct formula to list out all the “Software Service” Id to compare in Filter formula.
• Please note- when you have to access “Software Service” N:N(Many-to-Many) records for Contact record it can be accessible as contactRecord.’Software Services (gen_SoftwareService_Contact_Contact)’ and if you have to access all contact N:N (Many-to-Many) records for “Software Service” record it can be accessible as serviceRecord.Contacts.
• Above formula will list out all the “Software Service” records when current contact record when it is added as Service Owner or Service Engineer.

• Now another requirement to display “Service Engineer” Name for “Software Service” when contact record is associated as “Service Engineer” in N:N (Many-to-Many) relationship.
• Add below formula in Text for “lbl_serviceEngineer” label added in Gallery control

If(
    contactRecord.Contact in Distinct(
        ThisItem.Contacts,
        Contact
    ),
    contactRecord.'Full Name',
    Blank()
)

• As explained in Gallery control Item formula, ThisItem.Contacts represents the all N:N (Many-to-Many) “Contact” records related to ThisItem, where ThisItem represent current “Software Service” record.
• If current opened contact record is listed in all associated “Contacts” to “Software Service” (ThisItem), then show current contact record Full Name, else show blank.
• In all above formulas, I have used Distinct formula to overcome inaccessibility of related record fields as well as any delegation errors in formula.

In above scenario, we learnt how to access related tables and use related table columns in regular formulas (using Distinct) when tables are in N:N (Many-to-Many) relationships.

Hope this will help…
Enjoy MS CRM!!!

Follow on Facebook- FB: MSCRM16Tech