CDS Patching Multi Select Option Sets

[david_castillo]

Hello!

I'm having issues in patching Multi-Select Option Sets to CDS. I'm doing this besides the native functionality because I need to patch two category records (Let's call them Data 1 and Data 2) to just one Record, and I can't do that natively with the Edit Form and Submit button to CDS.

In SharePoint, I remember that we have a code to patch multi-select dropdown boxes to SharePoint using the following code (for example I have a Position column in SharePoint):

    Merch: ForAll(
                               Merch.SelectedItems,
                               {
                                 '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                                  Value: Value
                               }
                          )

This is the same thing when patching the dropdown value (not multi-select):

Position:{
                                 '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                                  Value: 'Position-Drop'.SelectedText.Value
                               }

In CDS, I want to achieve the same thing since I'm transitioning CDS to some of our items in SharePoint for better security control and relational data. But when I try to do the same thing, obviously it will throw in an error since it references a SharePoint function rather than CDS.

What are the equivalent CDS patching option for Multi-Select and normal option sets?

[yashag2255]

Hi @david_castillo 

You can patch a multiselect option set as:

Patch(testentities,Defaults(testentities),{Name:"MultiTest",multiselect : [[@'Goal Type'].Decimal,[@'Goal Type'].Integer]})

Here, testentities is the name of my CDS entity and 'Goal Type' is the name of my option set with values Decimal and Integer. You can follow the same process to patch the records in your application.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[v-xida-msft]

Hi @david_castillo ,

Do you want to write values back to the Multi-Select Option Set type column in your CDS Entity using Patch function?

I have made a test on my side, please take a try with the following workaround:

Set the OnSelect property of the "Submit" button to following:

Patch(
      TaskLists, 
      BrowseGallery1.Selected, 
      {
         MultiStatus: [ApprovalStatus.Approved, ApprovalStatus.Pending, ApprovalStatus.'No Response']
      }
)

Note: The ApprovalStatus represents the Option Set which binds to my MutiStatus column in my TaskLists Entity.

On your side, you may need to type the following formula:

Patch(
      'YourCDSEntity', 
      Defaults('YourCDSEntity'),    /* <-- or specify the existing record you want to update  */
      {
         ...,
         MultiOptionSetColumn: [OptionSet.Value1, OptionSet.Value2, OptionSet.Value3],
         ...
      }
)

Note: The OptionSet represents the Option Set (in your CDS) which binds to your MultiOptionSetColumn in your Entity.

More details about setting values for Multi-Select and normal option sets column in CDS Entity, please check the following blog for more details:

https://powerapps.microsoft.com/en-us/blog/option-sets-and-many-to-many-relationships-for-canvas-app...

Best regards,

[david_castillo]

Hi @v-xida-msft,

I tried to replicate your recommendation and so far and I got these errors:

Few questions:

• Am I correct to assume that if an option set has space, I will just enclose it in quotations such as Tent Card will turn into 'Tent Card'?
• cr2ea_merchandise is my Field column in the entity and also my Option Set (see below).      

Did I reference the code correctly? For reference, here is my code:

Patch('Smart Padala Cluster Meeting Registrations',Defaults('Smart Padala Cluster Meeting Registrations'),{
    cr2ea_cardnumber:CardNo.Text,
    cr2ea_min:MIN.Text,
    cr2ea_businessname:BizName.Text,
    cr2ea_authorizedrepresentative:CO.Text,
    cr2ea_businessaddress:BizAdd.Text,
    cr2ea_attendee:CDS_Attendee.Text,
    cr2ea_facebookusername:CDS_FBUsername.Text,
    cr2ea_dateattended:CDS_DateAttended.SelectedDate,
    cr2ea_merchandise: [cr2ea_merchandise.Banner, cr2ea_merchandise.Poster, cr2ea_merchandise.'Tent Card']
})

I think the format above is ok except for the cr2ea_merchandise in which case I don't understand the code you mentioned.

[yashag2255]

Hi @david_castillo 

Were you able to check the suggestion I have provided?

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[david_castillo]

---

@yashag2255 wrote:

Hi @david_castillo 

Were you able to check the suggestion I have provided?

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

---

Hi @yashag2255,

I have tried to check your suggestion but I got stuck on your code on Decimal and Integer. My scenario is to record the data in one column in CDS containing the selected items I picked in Canvas:

In SharePoint, the expected value is entered into 1 column, sample below:

I have done this via doing this code for the Merch column:

Merch: ForAll(
                               Merch.SelectedItems,
                               {
                                 '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                                  Value: Value
                               }
                          

I'm not sure how to do this in CDS and based on your example, I think what you're doing is putting two values and patching them into one field - which is not the case I want to do. Mine is more of getting the selected items in Canvas and patch them in one CDS column

[yashag2255]

Hi @david_castillo 

Decimal and Integer are the choices in the Option Set, As I mentioned above, 'Goal Type' is the name of option set associated to the field and "Decimal" and "Integer" are the choices inside the option set.

In your case, you need to update that with [@'Option Set Name'].Poster and [@'Option Set Name'].Banner

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[v-xida-msft]

Hi @david_castillo ,

Based on the issue that you mentioned, I think you have some misunderstanding on the ApprovalStatus.Approved formula I provided in your Patch formula.

Firstly, the ApprovalStatus is not the Column name in my CDS Entity, it is the Option Set name. Please check the following screenshot for more details:

Within your Patch formula, when you reference values from the Option Set type, you should use the Option Set name rather than the column name. In other words, you should use the following formula:

OptionSetName.Value1

OptionSetName.Value2

...

rather than:

OptionSetColumnInEntity.Value1

Please modify your formula based on above solution, check if the issue is solved.

Best regards,

[david_castillo]

---

@yashag2255 wrote:

Hi @david_castillo 

Decimal and Integer are the choices in the Option Set, As I mentioned above, 'Goal Type' is the name of option set associated to the field and "Decimal" and "Integer" are the choices inside the option set.

In your case, you need to update that with [@'Option Set Name'].Poster and [@'Option Set Name'].Banner

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

---

Hi @yashag2255 would this mean that I'm patching the Decimal and Integer values? If yes, then that's not what I'm after. What I'm after is getting the selected items on that pick list/dropdown and patch those values, not a hard-coded/defined value.

Sorry in advance as CDS is ultimately new to me. Our org is used to SharePoint and it's a big transition to use CDS.

[david_castillo]

---

@v-xida-msft wrote:

Hi @david_castillo ,

Based on the issue that you mentioned, I think you have some misunderstanding on the ApprovalStatus.Approved formula I provided in your Patch formula.

Firstly, the ApprovalStatus is not the Column name in my CDS Entity, it is the Option Set name. Please check the following screenshot for more details:

Within your Patch formula, when you reference values from the Option Set type, you should use the Option Set name rather than the column name. In other words, you should use the following formula:

OptionSetName.Value1

OptionSetName.Value2

...

rather than:

OptionSetColumnInEntity.Value1

Please modify your formula based on above solution, check if the issue is solved.

Best regards,

---

Hi @v-xida-msft 

As with yashag, thank you for accommodating my requests as I'm entirely new to the concept of CDS. I tried what you mentioned, and I got this error:

My Option Set name (Multi-Option Set is Merchandise), and my Column Entity name is also Merchandise.