Following the article on patching complex column types in SharePoint, I thought I’d follow it up with a version for Dataverse. Transitioning from SharePoint lists to Dataverse tables is becoming more and more common, so understanding the differences in columns and how to patch them will be important to understand. The column types are the same on the surface, but the configuration & patch techniques are slightly different.
This article will cover how we patch to single choice, multiple choice, lookup and yes/no fields. There is no direct correlation for SharePoint’s ‘Person or Group’ column, but we can work around it.
To support the Power Fx code samples in this article, I’ve created a Dataverse table called Patch Complex DVS. If you’re new to Dataverse, the setup will appear different so let’s deep dive a bit.
Choice sets
Choice is a field type we can use in Dataverse tables:
When adding this column type, we get one of two options; do we want to sync with a global choice? Global choices are great if you want to use the same options across different solutions/tables in your environment. Local choices are purely local to the column & table you’re working with.
If we answer no to syncing with a global choice, we can add our bespoke entries in right away. This is very similar to what you’d get when setting up a SharePoint choice column:
If we select yes, we can select an existing choice or create a new one:
From a patching perspective, they both work in the same way and I’ll cover that later.
Choice columns
With the above in mind, I’ve created two choice columns; ChoiceSingle and ChoiceMulti (yes I know, wonderfully imaginative!). Both columns use the same global choice set, the only difference for the Choice Multi column is this option being ticked:
Lookup
Lookup columns in Dataverse are very similar to those in SharePoint. Add the column type, select the table you want to look up to. Here, I’m looking up to the Account table where I’ve added a couple of dummy entries:
Dummy entries:
Yes/No
Yes/no fields in SharePoint are boolean, the column wants true or false as a value. It’s the same in Dataverse, right?
It is – but it doesn’t look like it. Yes/No is actually a choice column of sorts, in that it has a default option set with yes or no as predefined values:
However, we can still patch boolean values to a Dataverse Yes/No column and they’ll still be accepted.
Person
There isn’t a Person or Group column type in Dataverse like there is in SharePoint. Instead, we can use another lookup column.
For the table to look up to, we’ll set the related table to look at the Microsoft Entra ID table (the table formerly known as AAD Users):
NOTE:
This will only be able to accept a single person. You cannot use this option to select and save multiple people.
For a multi-person people picker that’s compatible with Dataverse, we’ll have to do some creative workarounds. Look out for a dedicated article on this in early 2024.
Single choice
I’ve added a drop down control to my canvas app and renamed it to dpdChoiceSingleDV. For the column in Dataverse to recognise the value, I need to bind the Items property to the global (or local) choice set I created. In my scenario I’m using a global choice set called ComplexColumnsChoices, so the items property for the drop down is:
Choices(ComplexColumnsChoices)
The items property will look slightly different if using a local choice set, but same principle. It will instead show as the name of the column with the name of the table in brackets:
Choices('ChoiceSingleLocal (Patch Complex DVS)')
To patch a value from the drop down control to a Dataverse single choice column, our Power Fx needs to reference the control by name. We then need to add ‘.Selected.Value’ to ensure the specific value matches one of the defined choice set options.
Patch(
'Patch Complex DVS',
Defaults('Patch Complex DVS'),
{ChoiceSingle: dpdChoiceSingleDV.Selected.Value}
);
If we are not using a drop down control and want to hard code the value instead, we need to specify the pre-defined option from the choice set. This is done by naming the choice set in our Power Fx to access and choose the value we need. In the example code below, we are selecting the ‘Jedi’ option from the choice set:
Patch(
'Patch Complex DVS',
Defaults('Patch Complex DVS'),
{ChoiceSingle: ComplexColumnsChoices.Jedi}
);
In SharePoint, you can ignore the predefined values of a choice column and still submit your own. You cannot do this in Dataverse; it has to be one of the predefined values in the global or local choice set that’s bound to the column.
Multiple choice
As drop down controls in Power Apps do not accept multiple selections, I’ve added a combo box to my app instead. I’ve renamed the control to cmbChoiceMultiDV. The items property will need to reference the relevant choice set again. For me that’s my ComplexColumnsChoices global choice set, the same as for single choice:
Choices(ComplexColumnsChoices)
The code is very similar to single choice, except we are referring to SelectedItems – plural, as it’s multiple choice:
Patch(
'Patch Complex DVS',
Defaults('Patch Complex DVS'),
{ChoiceMulti: cmbChoiceMultiDV.SelectedItems}
);
Without using a control to reference, again we can submit hardcoded values but they can only be ones defined in the choice set. To send multiple values, we need to embed them within a Table function like so:
Patch(
'Patch Complex DVS',
Defaults('Patch Complex DVS'),
{
ChoiceMulti: Table(
{Value: ComplexColumnsChoices.'First order'},
{Value: ComplexColumnsChoices.Sith}
)
}
)
Alternatively, you can simply specify the values as an array, like so:
Patch(
'Patch Complex DVS',
Defaults('Patch Complex DVS'),
{
ChoiceMulti:
[
ComplexColumnsChoices.'First order',
ComplexColumnsChoices.Sith
]
}
);
Lookup
I’ve added a combo box to my app and renamed it to cmbLookupDV. As per earlier in this article, the lookup is using the Accounts table for its data. The items property of the combo box is bound to the relevant column in my dataverse table, which has an internal name of new_LookupValue.
Choices([@'Patch Complex DVS'].new_LookupValue)
The cmbLookupDV combo box can be referenced to get the record of the selected entry. We use ‘Selected’ here without any additional fields on the end, we need the whole record as that’s what the column expects:
Patch(
'Patch Complex DVS',
Defaults('Patch Complex DVS'),
{LookupValue: cmbLookupDV.Selected}
);
To update a Dataverse lookup field without a control to use, can find the relevant entry by doing the lookup manually. Again, we want to return the whole record found, just just a specific field from it. Here, we are looking up to the Accounts table manually to populate our lookup value. Make sure the value you’re using is unique and only returns a single record:
Patch(
'Patch Complex DVS',
Defaults('Patch Complex DVS'),
{
LookupValue: LookUp(
Accounts,
'Account Name' = "Account 3"
)
}
);
Person
I mentioned earlier that there is no ‘Person or Group’ field in Dataverse like there is in SharePoint. But we can cheat, by using a Lookup field and point it to the Microsoft Entra ID virtual table. As such, this will essentially be the same logic as above for the lookup column.
I’ve added a combo box to my app and renamed it to cmbPersonDV. The items property reflects the configuration of the UserLookupSingle column in my Dataverse table:
Choices([@'Patch Complex DVS'].new_UserLookupSingle)
The cmbPersonDV combo box can be referenced to get the record of the selected person. Again, we use ‘.Selected’ without any additional fields thereafter, as we need the whole record for the column to accept the value:
Patch(
'Patch Complex DVS',
Defaults('Patch Complex DVS'),
{UserLookupSingle: cmbPersonDV.Selected}
);
Without using a control, we can simply do a manual lookup to the Microsoft Entra ID table to find the relevant user. I’ll use email address for my lookup criteria, as that will always be unique to a user:
Patch(
'Patch Complex DVS',
Defaults('Patch Complex DVS'),
{
UserLookupSingle: LookUp(
'Microsoft Entra IDs',
Mail = "luke.skywalker@jedi.com"
)
}
);
You can perform such a Lookup using the Office 365 Users connector too:
Patch(
'Patch Complex DVS',
Defaults('Patch Complex DVS'),
{
UserLookupSingle: LookUp(
'Microsoft Entra IDs',
Mail = Office365Users.MyProfileV2().mail
)
}
);
Yes/No
I mentioned earlier in the article that Yes/No fields in Dataverse appear as an option/choice set. In the example, I’ll use a drop down box control, that I’ve renamed to dpdYesNoDV. The items property references the column name, and the table name in brackets:
Choices('YesNo (Patch Complex DVS)')
To patch the Yes/No column using the dpdYesNoDV control, we need to reference the control by name with the extension of ‘.Selected.Value’. This is what the column needs to accept the chosen value:
Patch(
'Patch Complex DVS',
Defaults('Patch Complex DVS'),
{YesNo: dpdYesNoDV.Selected.Value}
);
Without a control to reference, we must specify the choice set and the value we want to select:
Patch(
'Patch Complex DVS',
Defaults('Patch Complex DVS'),
{YesNo: 'YesNo (Patch Complex DVS)'.Yes}
);
However, if you prefer you can simply patch a boolean value instead. The Yes/No column in Dataverse will still recognise your choice as yes (true) or no (false):
Patch(
'Patch Complex DVS',
Defaults('Patch Complex DVS'),
{YesNo: true}
);
Thanks for reading! Hopefully there’s some useful methods & code snippets to help you patch some of the complex Dataverse columns with Power Apps.
If you liked this article and want to receive more helpful tips about Power Platform every week, don’t forget to subscribe 😊
Another good article. Always useful to refresh my memory every now and then. Thanks for sharing
Welcome! This was a post as much for my own reference as anything else as I need a refresher sometimes.
thanks – a big help
This is great information especially coming from developing in SP and now using Dataverse. Do you have an article or instructions on patching multi select fields using a combo box (inside the form) using a Form to Dataverse. I have a lookup field in a Dataverse table and cannot patch back to that field since it is a lookup field and only accepts one of the lookup fields not multiple.
Thannks
Hi, thanks for the feedback! I’m not sure that’s possible as Dataverse lookup columns only accept a single value. I’d maybe use a Choice column instead as you can accept those to allow multiple values. Alternatively, you can patch multi-select values from a combo box back to a text field – bit more work to do Power Fx wise but it’s doable.
Hi there
“For a multi-person people picker that’s compatible with Dataverse, we’ll have to do some creative workarounds. Look out for a dedicated article on this in early 2024.”. I’m just wondering if you have written this article yet. I’m wanting to move off Dataverse but SharePoint makes things so easy. (oh and multiselect lookup is dead simple in SP but not DV :()
Hi Daniel, not yet but will get to it! There are others in the community who have covered it too, essentially using a combo box as a people picker in a canvas app & writing information back to text fields in Dataverse.