Working with Optionsets and Multi-select Optionsets in Canvas Apps using Common Data Service (Current) Connector

By | July 9, 2020

In the earlier post we have discussed working with Lookups and Polymorphic Lookups and this time we will focus on Optionsets and Multi-Select Optionsets.

OptionSet:

Using the CDS (Current) connector, when you include the optionset field on your canvas app, the drop-down items list is auto bound and you do not need to adjust any additional settings for this.

Let us first look at adding this to the Gallery. The field is available in the field list to select from and once you associate it with the label, the control source is auto set to –

Thisitem.’Preferred Method of Contact’ as shown in the screenshot below:

Canvas Apps

Preview to the left shows it is already working. It displays the optionset text and not the value behind that is stored in the database in CDS.

Now let us look at the edit form where we need to display this as a drop-down list with options to select from.

From the fields list for the Edit Form, click on Add field to add the Preferred Method of Contact field to the form and choose whether you want the control to be editable or view only from the two control type options available. We choose the edit option since this field is available for editing on our form.

Canvas Apps

The properties for the card and drop-down control are automatically bound to the following:

Canvas Apps

The Items property is auto set to Choices(‘Preferred Method of Contact (Contacts)). This auto binds the control with the options defined in the metadata for this field of the contacts entity. No further action required.

Since this is a single select optionset, the SelectMultiple property is set to false.

To set the default selected item of combo box to be the value of field on record, the following property is set on Card:

Canvas Apps

The update property is set to ensure that any change in the selection on the combo-box is updated here, so that the updated value is saved to the database.

DataCardValue12 – Is the combo-box control for this field.

Multi-OptionSet:

I just went and added a new multi-select optionset for the contact entity.

Canvas Apps

Note: After making any changes to the schema of the entity it is important to refresh the data source for the changes to be reflected.

However, in my case, inspite of the refresh, the data source does not select multi-select attributes for selection in the browse gallery field set. This is because –

ThisItem.Interests – returns a table and not a text that could be bound to a label.

To add this field to the Gallery listing, edit the gallery to add a label control and set the property of the label control as shown below:

Canvas Apps

Liked I said above, ThisItem.Interests returns a table, we need to read all values from this and create a comma separated string that can be displayed in the label.

Concat(ThisItem.Interests,Text(Value), “, “)

Concat function takes a table source as the first parameter and the expression for the second.

This loop through all values of the table and creates a comma separated string.

For reference, click here: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-concatenate

Now let us turn our focus to editing a multi-select in an edit form. We now need to display the available options in a combo-box for users to select appropriate values.

This field is available for selection in the field list for the Edit Form.

Canvas Apps

It auto detects this to be a multi-select optionset and sets the field properties appropriately to Edit Optionset Multi-select.

And in the preview you can see the default selected values – no additional changes or property settings required.

Canvas Apps

Both single select and multi-select optionsets are saved automatically with the SubmitForm().

However, if you would like to save them manually using the Patch(), here is how to set the value to optionset fields:

Patch(

Contacts,

Defaults(Contacts),

{

‘Last Name’: DataCardValue7.Text,

Email: DataCardValue8.Text,

Description: DataCardValue9.Text,

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

        Interests: DataCardValue6.SelectedItems,

‘Company Name’: DataCardValueCompany.Selected,

‘account lookup’: DataCardValue5.Selected,

Currency: DataCardValue17.Selected,

‘Managing Partner’: DataCardValue19.Selected

}

)

For single select optionset, we set it to

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

For multi-select optionset, we would pass

Interests: DataCardValue6.SelectedItems,

And now finally, let us look at a way to pass the values of both types of optionset to Power Automate Flow to save.

For both types of optionset, we need to pass the integer values of the options selected. So while it displays the Options Label, when passing to Power Automate Flow to update the optionset value, we need to find the integer value of the selected option.

There is no easy way to lookup the integer value of the option labels and therefore this approach though hardcoded works well where the app has to be designed for specific environment.

Create a Table Collection of all the option set values along with their labels.

Set(

InterestOptions,

Table(

{

name: “Technology”,

value: “102710000”

},

{

name: “Fashion”,

value: “102710001”

},

{

name: “Gadgets”,

value: “102710002”

},

{

name: “Movies”,

value: “102710003”

}

)

);

Set(

ContactMethods,

Table(

{

name: “Any”,

value: “1”

},

{

name: “Email”,

value: “2”

},

{

name: “Phone”,

value: “3”

},

{

name: “Fax”,

value: “4”

},

{

name: “Mail”,

value: “5”

}

)

);

I have added this on the Visible of the Details screen, to make sure this collection is available for lookup on the edit screen when I click the post button to save.

Now that we have the collection ready, we need to look up the value from this table collection and pass the numeric value to the power automate flow.

For Single Select OptionSet, lookup the label of Contact Method selected in the Table collection created above.

CallChildflowtoupdatecontact.Run(

BrowseGallery1.Selected.Contact,

DataCardValue7.Text,

DataCardValueCompany.Selected.Account,

DataCardValue5.Selected.Account,

DataCardValue19.Selected.Account,

DataCardValue8.Text,

LookUp(

        ContactMethods,

        name = Text(DataCardValue12.Selected.Value),

        value

    ),

InterestVal

);

For Multi-select, you need to pass the integer value of the selected options as a comma separated list. For this, it requires a couple of jumps to get there.

ForAll(

DataCardValue6.SelectedItems,

Collect(

SelInterestsName,

{

name: Value,

val: Value(LookUp(

InterestOptions,

name = Text(Value),

value

))

}

);

);

Set(InterestVal,Concat( SelInterestsName,Text(val),”,”));

First get the integer value of all selected options by using the ForAll loop to loop through all selected option text.

Collect(

SelInterestsName,

{

name: Value,

val: Value(LookUp(

InterestOptions,

name = Text(Value),

value

))

}

);

The Collect function allows you to create/add values to a collection variable. Here we create an object with name/value pair.

Name being the string which was the selected option label.

Val – We lookup the Interested Options hardcoded collection we created earlier to find the integer value of the matching option label.

And finally now that we have the collection with name/value pair, we now convert it to a comma separated list of values using the Concat().

Set(InterestVal,Concat( SelInterestsName,Text(val),”,”));

And then this is the string that we would be passing to Power Automate Flow.

CallChildflowtoupdatecontact.Run(

BrowseGallery1.Selected.Contact,

DataCardValue7.Text,

DataCardValueCompany.Selected.Account,

DataCardValue5.Selected.Account,

DataCardValue19.Selected.Account,

DataCardValue8.Text,

LookUp(

ContactMethods,

name = Text(DataCardValue12.Selected.Value),

value

),

InterestVal

);

Power Automate Flow is created for the PowerApps trigger and its design is as follows:

The Ask in PowerApps dynamic option is used to create parameters for the flow. The values for these parameters has been passed in the call above.

Canvas Apps

Note: When u first click on the optionset field in the action designer, it will only list the default options available for the picklist, so choose the add custom value option which will bring up the dynamic content window shown above for you.

And with this, we now have a way to work with OptionSets (Single and Multi-Select) using Canvas Apps, Patch function and finally Power Automate Flow.

Leave a Reply

Your email address will not be published. Required fields are marked *