Working with Complex Data Types in Canvas Apps using Common Data Service (Current) Connector – Lookups & Polymorphic Lookups

A couple of years ago I had visited this topic but back then it was using the Dynamics 365 connector which is now deprecated. New connector changed ways – time to write again more for a personal reference and glad if it helps others out there too.

First – let’s look at adding a lookup field to Gallery for display purposes.

When you associate an entity to the gallery, you are provided the options to choose the fields that you want to be displayed, Lookup fields are not available in the list though. To add them, you will need to bind it to the field manually. Now since lookups actually reference to another entity record, you also need to provide the field from that entity that you need to display. It does not automatically choose the primary field.

And the best part with Canvas Apps is you get the preview right away to confirm if you are doing it right!

The Title is bound to Full name – text field

Body has been tied to Currency – since it is a reference field, we set it as

ThisItem.Currency.’Currency Name’

Subtitle = I would like to display the Parent Customer of this contact.

Parent Customer although is a Polymorphic type of Lookup which means, it references more than 1 table record types. The customer attribute type cannot be read similar to the simple lookup. For this you need to refer the value as follows

If( IsType( ThisItem.’Company Name’, [@Accounts] ),

AsType( ThisItem.’Company Name’, [@Accounts] ).’Account Name’ ,

AsType( ThisItem.’Company Name’, [@Contacts] ).’Full Name’ )

The IsType function is used to identify the table that the current value of that field refers to. It could either refer to Accounts or Contacts.

AsType function is a way to type cast or convert the value to a specific type, in this case either account record type or contact record type.

Once you have type cast it, it starts behaving like the simple lookup reference and you can get the list of fields of the said record type.

Note: @Accounts and @Contacts here refer to the data sources added for the Accounts and Contacts table in the app as shown below

Next let us move to the display form to place both simple and polymorphic lookups on the form.

Place the detail form on the canvas and choose the fields from the list as shown below

Here the simple lookup fields are listed and once selected, they auto display the value without any additional settings or property manipulation.

Polymorphic Lookups though are still unavailable on this list and they need to be added manually to the form.

Choose Add Custom Card from the ellipsis as shown below

This adds an empty section for you to design your card.

Choose the card and edits its property to set the control source

Datafield = The attribute you would like to bind this card to.

Display Name = Label for the card

Default = The default value to be displayed for this control.

Note since this is a Polymorphic lookup, we typecast it to the appropriate table based on the value set in this field for the current item.

If( IsType( ThisItem.’Company Name’, [@Accounts] ),

AsType( ThisItem.’Company Name’, [@Accounts] ),

AsType( ThisItem.’Company Name’, [@Contacts] ))

Since parentcustomerid field is a reference type of field, we are not setting it to

AsType( ThisItem.’Company Name’, [@Accounts] ).’Account Name’

As we did while setting the text value in the gallery item.

Now we will add controls for the field label and field text.

Shown below are the properties set for the label control

We have set the Text to be picked up from the Display name of the parent control. The parent control here refers to the data card we added.

Now let’s add a label control to display the value/name of the parent customer.

Here similar to the label text set in the gallery we refer the actual fields’ names of the respective record types that we need to be displayed here.

If( IsType( ThisItem.’Company Name’, [@Accounts] ),

AsType( ThisItem.’Company Name’, [@Accounts] ).’Account Name’ ,

AsType( ThisItem.’Company Name’, [@Contacts] ).’Full Name’ )

Let us move on to the Edit form for these lookup fields.

Choose the lookup field from the list, simple lookups do show up in the field selection

Once placed, all good – no more changes required. Since it is an edit form, the control is editable, it places a drop-down control. It also binds the drop-down to list the values of the referencing table in the drop-down

From the field, list includes the polymorphic lookup field – Company name to add an empty data card.

Change the Default property as below

AsType(ThisItem.’Company Name’,Accounts)

Note, since this is editable control instead of a label to display the name, we will be adding a drop-down control to provide the user with the list of accounts to choose from to set the company name and therefore, the default here is being set to account reference

Make sure to set the Update property to

DataCardValueCompany.Selected

This ensures selection made in the drop-down is updated here to be saved appropriately.

Let’s now add the label and drop-down control to the card

Parent.DisplayName will set the label to the text that was set on the DataCard.

Add a combobox for the value, we need to give them the account list for selection. We now have the ability to provide a view for listing and I choose the Active Accounts view.

To bind the combobox to account set for the selected contact, we need to set the DefaultSelectedItems property.

Switch to the Advanced tab and set it as follows

The Canvas App Framework has the SubmitForm function which will autosave all the values of the controls placed on the details form

SubmitForm(EditForm1)

Now just in case you would like to manually save the data and cannot use the SubmitForm function, you will need to use the Patch function.

Patch(

Contacts,

Defaults(Contacts),

{

‘Last Name’: DataCardValue7.Text,

Email: DataCardValue8.Text,

Description: DataCardValue9.Text,

‘Preferred Method of Contact’: DataCardValue12.Selected.Value,

‘Company Name’: DataCardValueCompany.Selected,

‘account lookup’: DataCardValue5.Selected,

Currency: DataCardValue17.Selected,

‘Managing Partner’: DataCardValue19.Selected

}

)

The above code will create a new contact record with the specified values. For all the lookups (simple, polymorphic) we are using the selected property of the combobox.

If instead, you wanted to call a flow to update the record, you can create a flow with the Power Apps trigger

In this flow, the values for each of the fields are to be passed as a parameter from the calling Power Apps Canvas App as shown below

Since the flow is expecting Guid values for lookups, we pass it as

DataCardValueCompany.Selected.Account

The account here returns the accountid guid value.

Conclusion

Hopefully, this helps with displaying, editing, saving lookup values using Power Apps and Flows.