Blog
Dataverse - How to build an edit form to set the parent account for a contact record
July 24. 2023
A task that canvas apps builders struggle with, is how to set the parent account for contact record. The account lookup field doesn't appear in the form editor and doesn't appear to be editable in the data grid view. This post describes the behaviour walks through how to build an edit form in a canvas app that enables the user to set the parent account on a contact record.
When working with Dataverse, the Account lookup in the standard Contact table may appear to work in an unexpected way.
From the Dataverse designer, the Contact table clearly contains an Account column. However, this is greyed out and not editable, as highlighted below.
A problem that canvas app makers can struggle to work out is how to associate an account with a contact. This issue is also relevant for users building apps with Power Pages because the contact table plays a key role in authentication.
This post describes the problem in more detail and walks through how to associate an account record with a contact.
Demonstration - Cannot add 'account' lookup on Contact form
To expand on why canvas app builders struggle with this issue, let's add a form to an app and attempt to add an account lookup.
The closest option we have is 'Accounts', as highlighted beneath.
It soon becomes clear that this isn't correct because the detected data type is 'Complex' and the default control type is set to 'view option set multi-select'. The only other control type available is 'Attachment', which isn't appropriate.
Understanding the relationship between Contacts and Accounts
The initial attempt above failed to create a combo box to set the parent account. This is because there are multiple linkages between the Contact and Accounts table, and the Accounts field that we selected above was not the correct field for setting the parent account.
In addition to each contact having a parent account, each account can have a 'primary contact' and multiple accounts can share the same primary contact (in other words, there's a one-many relationship between account and contact tables, and a separate one-many relationship between the contact and account tables).
To illustrate this visually, the screenshot below illustrates an account record in a model driven app and the right hand section shows the UI for setting the primary contact.
.The 'Accounts' field that we attempted to use represents the 'primary contact' relationship between the contact and accounts tables.
Why is the account lookup in the Contact table different to other lookups?
Before we move on, it's very important to understand that the account lookup in the contact table is different to other lookups.
The best way to visualise this is to open a contact record in a model-driven app and to set the parent 'Account Name'. As highlighted beneath, the combo box enables the user to search for and to associate a contact record with a parent record from either the account or contact tables.
For further reading, the online documentation describes how the contact table in greater detail.
What's the field name for the account lookup in the Contact table?
So far, we know that 'Accounts' is not the correct field to set the parent account (or parent contact). Therefore, what's the field name for the the account/contact lookup?
To discover this, let's investigate the 'Account Name' lookup from a model driven app. From the screenshot beneath, we see that 'Account Name' is the 'friendly' name for the 'Company Name' field. Therefore, 'Company Name' is the field we use to set the parent account (or contact).
If we delve a little deeper and look at the column details in the table designer, we see that parentcustomerid is the logical name for the 'Company Name' field.
Whilst we're exploring the fields in the Contact table, it's also important to note that the Contact table also includes 2 additional columns - accountid and parentcontactid. These are columns that Dataverse uses to handle the polymorphic relationship and are not columns that we should work with directly (they are read-only columns).
App builders may attempt to set a parent account by referencing accountid and this is the reason for the failure.
How to create an edit form to set the parent account for a contact record
Now we understand that 'Company Name' is the field we should use to set the parent account for a contact record, we have all the pieces we need to build an edit form.Returning to our form, we can add the add the 'Company Name' field. The control type is an 'Edit reference' control (shown below), which is effectively an empty card.
Within this card, we can insert a combo box control and set the Items property to the Accounts table (we'll name this cboAccounts) . Next, set the 'select multiple' property to false and set the DefaultSelectedItems property to the following formula.
If(IsType(ThisItem.'Company Name',Accounts),
AsType(ThisItem.'Company Name', Accounts)
)
The final part is to set the Update property of the card to the following:
cboAccount.Selected
At this point, we can play our app and use the combo box to set the account for the contact.
Gotchas - Why app builders fail to build forms that set polymorphic lookups
We've now completed a edit form that allows a user to set a parent account from a contact record. This working demonstrate enables us to highlight the common reason why app builders fail to create forms that set polymorphic lookups.
In our working example, notice how the DataField property is set to _parentcustomerid_value. Notice how this is different to the logical name of the field which is parentcustomerid, as highlighted in an earlier screenshot in this post.
It's reasonable to expect that a DataField setting of parentcustomerid would correctly update the field, however this isn't the case. With this setting, SubmitForm fails to update the field and no error message will be given. In the majority of cases where updating polymorphic lookups don't work, the error lies in the DataField name.
For reference, if we were to update the parent account for a contact record through patch, the field name we use here is the 'friendly' name (ie, 'Company Name'), as opposed to any of the logical names.
Patch(Accounts,
Gallery1.Selected,
{'Company Name': cboAccount.Selected})
)
Final caveat - adapting the account lookup on the edit form to cope with contact records
The caveat to the example in this post is that it's designed to associate contact records with account records only.
Due to the polymorphic nature of the 'Company Name' field, it's possible for a user to open an existing record that's associated with a parent contact record.
In this situation, we might not want to lose the existing parent contact record. Therefore, we can adapt our form to disable the account lookup card when it loads an existing contact record that's associated with a parent contact.
This is the syntax that we would add to the display mode property of the card looks like this:
If ((Parent.Mode = FormMode.New) || (Parent.Mode = FormMode.Edit && (IsType(ThisItem.'Company Name', Accounts) || IsBlank(ThisItem.'Company Name') )) , Parent.DisplayMode, DisplayMode.Disabled )
This formula disables the card if the form is edit mode and the 'Company Name' isn't an account record or hasn't been set.
Finally, the logical extension of the process would be to extend the card to allow users to set either a parent contact or a parent account. This post hopefully provides a good basis on which to build this example further. For additional guidance on how to do this, the documentation provides a good example of building UI with a radio control that enables users to switch between accounts and contacts.
Conclusion
When building canvas apps based on the Dataverse contact table, it isn't obvious how to create an edit form that sets the parent account for a contact record.
This post described the polymorphic nature of the account field and walked through how to build an edit form to set the parent account.
