In this blog post, I will walk through the steps required to save details to an Option Set, LookUp type columns in a CDS entity. This is helpful for folks who are facing issues while patching an Option Set/ LookUp field data.
Problem Statement
How to save details in a LookUp/Option Set column using Patch from the Canvas Apps?
Solution
The way to save details in a LookUp/Option Set column differs from the other column types. For different column types different input schema matters and it should be passed in a proper way. The Choices() function can be used to pass appropriate schema to the column and save the details.
Data Model
Entity: Employee Details
Attributes:
Employee Name: Single Line of Text
Employee Email: Single Line of Text
Work Location: Single Select Option Set -> Office Location 1/ Office Location 2/ Remote
Skills: Multi Select Option Set -> Microsoft PowerApps/ Microsoft Azure/ Power Automate/ PowerBI/ Power Virtual Agents
Country: LookUp -> Countries Entity
Expression to patch details based on above schema
Patch(
'Employee Details',
Defaults('Employee Details'),
{
'Employee Name': TextInput1.Text,
'Employee Email': TextInput1_1.Text,
'Work Location': LookUp(
Choices('Employee Details'.'Work Location'),
Value = Dropdown1.Selected.Value
).Value,
Country: LookUp(
Choices('Employee Details'.Country),
Name = ComboBox1.Selected.Name
),
Skills: ComboBox1_1.SelectedItems
}
)
Explanation
Work Location:
LookUp(Choices('Employee Details'.'Work Location'),Value = Dropdown1.Selected.Value ).Value
LookUp function is used to filter the single record which matches the value selected in the drop-down control. This is an example implementation but a similar text control can be referenced instead of Dropdown1.Selected.Value to make it work with distinct inputs and scenarios.
Country:
LookUp(Choices('Employee Details'.Country),Name = ComboBox1.Selected.Name)
LookUp function is used to filter the record where the Name column matches the Value selected in the Combobox control. The formula works on the "Name" column as per above expression, but this can be changed to a different column (Make sure the value used to compare is in the expected format.)
Skills:
ComboBox1_1.SelectedItems
Here, the Items property of the ComboBox control is set to: Choices('Employee Details'.Skills), based on multiple selections in the combobox, the values will be saved based on the appropriate schema generated using Choices() function. In case, the input passed to the Combobox control is a static text, the expression can be modified to: Filter(Choices('Employee Details'.Skills),Value in ComboBox1_1.SelectedItems.Value)
Once the save button is clicked, the data is saved appropriately in the entity as shown in the image below:
This post shows how you can update LookUp, Option Set Values in a CDS entity from Canvas Apps using the Patch() function.