Canvas App Patching Multi-Select OptionSet Values to DataVerse Table

[AsaurdiffADTill]

Hello, 

I wanted to post this here because there is zero documentation on how to do this with MSoft and its extremely useful functionality when you don't want to be restricted to a ComboBox interaction in your Canvas apps (like I was 😁 ) 

Situation: 

I had a Dataverse table for my Canvas app with a Choice Column (OptionSet). I also wanted the users to be able to select multiple choices with this. Inside my application I did not want to utilize a Combobox because it doesn't display all the options visually for my users so I decided to use a Gallery. I had a Gallery of checkboxes and a gallery of buttons. I will explain the general things I did to keep it simple. 

First you must think of the OptionSet Values. These options are very difficult to work with and if you have before you know that it is best to pull them directly and not assume the values like you would with a Sharepoint list choice. So, use Choices([@databaseTable].OptionSet_Column) to store use choices. 

EX: 

Gallery Item: Choices([@DatabaseTable].OptionSet_Column)

inside the gallery we will use buttons for simplicity. 

Button Text: ThisItem.Value (will display the text value of the option)

Button OnSelect: (this is where the magic happens)

If( //Determine if the item is within the stored collection
  ThisItem.Value in chosenOptions.item,
  //if found first remove from collection
  Remove(
      chosenOptions,
      Lookup(chosenOptions,
      item = ThisItem.Value
      ),RemoveFlags.All
  );
  //Patch the altered collection to the database
  Patch(
      DatabaseTable,
      LookUp(
          DatabaseTable As DB, DB.Unique_Value = refRecord.Unique_Value
      ),
      {OptionSet_Column: Filter(Choices([@DatabaseTable].OptionSet_Column),Value in chosenOptions.item)}
  ),
  //If this is NOT already selected you will be adding to the collection stored
  Collect(
      chosenOptions,
      {// you will notice that i store the OptionSet "object" AND its Value. That is because it is easier to compare and 
        // evaluate against with the app. You can't compare ThisItem = OptionSet... it doesn't work ;( 
          Value: ThisItem,
          item: ThisItem.Value
      }
  );
  // Now you Patch the newly created (re-created) collection based against the Choices()
  Patch(
      DatabaseTable, 
      LookUp(
          DatabaseTable As DB, DB.Unique_Value = refRecord.Unique_Value),
          {OptionSet_Column: Filter(Choices([@DatabaseTable].OptionSet_Column),Value in chosenOptions.item)}
  )
);

Above is the code, for the button. But what matters is the Patch formula. You will see that i need to store my options so i can reference them on every selection. It basically re-writes the OptionSet table on every patch (which is what it does by design anyway). I use "Filter" because that will output a Table which is required entry for these Multi-Select OptionSets. 

I hope this helps anyone, I know i could have used this different way of looking at OptionSets when I was figuring this out. 

[Ethan_R]

Hi @AsaurdiffADTill ,

To patch your Combobox selected items in your optionset, you can try the following code:

When you patch selected options in your column then pass value like this:

Concat(ComboBox1.SelectedItems, Value, "; ")

Here Value is the ColumnName in my combobox, you might have different then do check that out. 

Once you have set proper data then you will patch multiple options in dataverse

Hope this helps

[AsaurdiffADTill]

That is good to know @Ethan_R, that they are literaly only ';' separated lists 🙂 . Although I was trying to avoid the combobox for visual purposes. 

I know that if i were to set the OnChange property to Patch(database,record,{Column: Combobox.SelectedItems}) it actually functions fine, but I wanted a bit more of a 'catered' experience for my application.