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?

[v-xida-msft]

Hi @david_castillo ,

Based on the error message that you mentioned, it seems to tell that the cr2ea_merchandise column recognized as a Text type column rather than a Multi-Select Option Set type column in your Patch function.

Please consider modify your Patch formula as below:

Patch(
          'YourCDSEntity',
           ...,
           {
              ...,
              Merchandise: [Merchandise.Poster, Merchandise.Banner, ...],
              ...
           }
)

Please type Merchandise rather than cr2ea_merchandise.

In addition, please also make sure you turned on the "Relation data, option sets, and other new features for CDS" option and "Improve data source experience and Common Data Service" option within Advanced settings of App settings of your app.

Best regards,

[david_castillo]

---

@v-xida-msft wrote:

Hi @david_castillo ,

Based on the error message that you mentioned, it seems to tell that the cr2ea_merchandise column recognized as a Text type column rather than a Multi-Select Option Set type column in your Patch function.

Please consider modify your Patch formula as below:

Patch(
          'YourCDSEntity',
           ...,
           {
              ...,
              Merchandise: [Merchandise.Poster, Merchandise.Banner, ...],
              ...
           }
)

Please type Merchandise rather than cr2ea_merchandise.

In addition, please also make sure you turned on the "Relation data, option sets, and other new features for CDS" option and "Improve data source experience and Common Data Service" option within Advanced settings of App settings of your app.

Best regards,

---

Hi @v-xida-msft,

I tried to modify now my code and updated the App Settings to turn the two options to On (I also had to remove and reconnect my CDS). My code now is the following:

Patch(
        'Smart Padala Cluster Meeting Registrations',
        Defaults('Smart Padala Cluster Meeting Registrations'),
        {
            'Card Number': CardNo.Text,
            MIN: MIN.Text,
            'Business Name': BizName.Text,
            'Authorized Representative': CO.Text,
            'Business Address': BizAdd.Text,
            Attendee: CDS_Attendee,
            Position: CDS_Position,
            'Facebook Username': CDS_FacebookUsername,
            'Date Attended': CDS_DateAttended,
            Merchandise: [Merchandise.Poster, Merchandise.Banner, Merchandise.'Tent Card', Merchandise.Sticker, Merchandise.'Padala Bible Inserts', Merchandise.'Transaction Slips', Merchandise.'Horizontal Banner', Merchandise.'Large Poster', Merchandise.'Merchandising Kit'],
            'Topics Trained': ['Topics Trained'.'AML Training','Topics Trained'.'DKYC Training','Topics Trained'.'PayMaya Negosyo','Topics Trained'.'SPU (New Agents)']
        }
    );

At first, there was no error encountered but when I simulated the submission and clicked on Submit, this error appeared:

Hope you can advise on this error as it didn't show anything that I can debug.

Thanks!

David

[david_castillo]

Hi @v-xida-msft 

After a few trial and errors I managed to submit the record correctly, but I'm facing an issue since I commented out the Position and Date Attended fields. On the data submission, it recognized the submissions directly:

As you can see, the Position and Date Attended is blank since these are the ones causing the error. here is the revised code:

If(
    (CDS_Position.Selected.Value exactin "Owner" && IsBlank(CDS_Attendee.Text)) || IsBlank(CDS_Position.SelectedItems.Value) || IsBlank(CDS_Attendee.Text) || IsBlank(CDS_FacebookUsername.Text) || IsBlank(CDS_Merchandise.SelectedItems.Value) || IsBlank(CDS_TopicsTrained.SelectedItems.Value),
    Set(
        recordsubmit,
        false
    ),
    Set(
        recordsubmit,
        true
    );
    Patch(
        'Smart Padala Cluster Meeting Registrations',
        Defaults('Smart Padala Cluster Meeting Registrations'),
        {
            'Card Number': CardNo.Text,
            MIN: MIN.Text,
            'Business Name': BizName.Text,
            'Authorized Representative': CO.Text,
            'Business Address': BizAdd.Text,
            Attendee: CDS_Attendee.Text,
            //Position: CDS_Position.Selected.Value,
            'Facebook Username': CDS_FacebookUsername.Text,
            //'Date Attended': CDS_DateAttended,
            Merchandise: [Merchandise.Poster, Merchandise.Banner, Merchandise.'Tent Card', Merchandise.Sticker, Merchandise.'Padala Bible Inserts', Merchandise.'Transaction Slips', Merchandise.'Horizontal Banner', Merchandise.'Large Poster', Merchandise.'Merchandising Kit'],
            'Topics Trained': ['Topics Trained'.'AML Training','Topics Trained'.'DKYC Training','Topics Trained'.'PayMaya Negosyo','Topics Trained'.'SPU (New Agents)']
        }
    );
    (Navigate(
        Form_Submitted,
        Fade
    ))
)

This is almost done! I just need to know what data I should pass in the Position Option Set (not multiple) and the Date Attended DateTime (Date only).

If I do the Position.Selected.Value, I get this error:

[v-xida-msft]

Hi @david_castillo ,

If you want to provide a value for the the Position Option Set (not multiple), please use the following formula:

 Patch(
        'Smart Padala Cluster Meeting Registrations',
        Defaults('Smart Padala Cluster Meeting Registrations'),
        {
            ...,
            Position: 'OptionSetNameBindToThePositionColumn'.Value1,
            ...
        }
    );

Please check the following blog for more details (check this blog carefully):

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

If you want to provide a value for the the Date Attended DateTime (Date only), please type the following formula:

Patch(
        'Smart Padala Cluster Meeting Registrations',
        Defaults('Smart Padala Cluster Meeting Registrations'),
        {
          ...
         'Date Attended': Now(),          /* or Type Today() function */
          ...
        }
    );

Please take a try with above solution, check if the issue is solved. If you have solved your problem, please go ahead to click "Accpet as Solution" to identify this thread as Solved.

Best regards,

[david_castillo]

---

@v-xida-msft wrote:

Hi @david_castillo ,

If you want to provide a value for the the Position Option Set (not multiple), please use the following formula:

 Patch(
        'Smart Padala Cluster Meeting Registrations',
        Defaults('Smart Padala Cluster Meeting Registrations'),
        {
            ...,
            Position: 'OptionSetNameBindToThePositionColumn'.Value1,
            ...
        }
    );

Please check the following blog for more details (check this blog carefully):

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

If you want to provide a value for the the Date Attended DateTime (Date only), please type the following formula:

Patch(
        'Smart Padala Cluster Meeting Registrations',
        Defaults('Smart Padala Cluster Meeting Registrations'),
        {
          ...
         'Date Attended': Now(),          /* or Type Today() function */
          ...
        }
    );

Please take a try with above solution, check if the issue is solved. If you have solved your problem, please go ahead to click "Accpet as Solution" to identify this thread as Solved.

Best regards,

---

I think my config is almost done! The Date Attended actually worked but I need the Position to be populated based on user input. Here is my screen:

Based on your input/code above, I'm only assigning a specific value which is, for example, "Owner"

What I need is to populate the Position entity column field based on the user input. I read the article but I don't think the Option Set explanation is based on what I want to achieve.

I think this is the last one then we can close this off! The name of my combo box is CDS_Position, I tried to do the following:

.....
 'Position': CDS_Position.Selected.Value,
....

But it returned the following error:

[v-xida-msft]

Hi @david_castillo ,

Could you please share a bit more about the Items property of the CDS_Position ComboBox?

On your side, please make sure the Items property of the CDS_Position ComboBox has been set to following:

Choices(OptionSetNameBindsToThePositionColumn)

Then modify your Patch formula as below:

Patch(
         ...,
         ..,
         {
           ...,
           Position:  CDS_Position.Selected.Value,          /* <-- Please do not wrap the Position column with single quotes. Type Position rather than 'Position' */
           ...
         }
)

Best regards,

[david_castillo]

Hi @v-xida-msft,

The name of my Option Set is Position:

The Items property of the CDS_Position ComboBox is set to the following:

Choices(Position)

I altered the code to remove the single quote as you mentioned:

Patch(
        ....
     ....
        {
....
            Position: CDS_Position.Selected.Value,
            '.....
        },

But still having the same error:

Thank you so much for the awesome help!

[v-xida-msft]

Hi @david_castillo ,

Currently, I could not reproduce your issue on my side. Please consider remove the Positon column from your Entity, then re-create a new one, then try your formula again, check if the issue is solved.

In addition, if the Edit form in your app is bind to your CDS Entity data source, you could also consider take a try to modify your formula as below (instead of providing value for each column one by one😞

Patch(
        'Smart Padala Cluster Meeting Registrations',
        Defaults('Smart Padala Cluster Meeting Registrations'),
        EditForm1.Updates
)

Note: The EditForm1 represents the Edit form in your app, where the user enter their data.

Best regards,

[david_castillo]

Hi @v-xida-msft 

I will try to resolve the single option set later. I double checked the code that we did earlier on the Multi-Select Option Set and while it is not throwing an error, it entered all the items of that Option Set. What I want to achieve is just to submit the items that the user has chosen. This is the code I did for SharePoint:

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

What this does is to get the value of each selected item. So for example I have the following Merch items:

Merch Items

• Banner
• Poster
• Tent Card
• ...
• Transaction Slips

And the user only chose Banner and Poster, the record should only reflect Banner and Poster. In our case, we did this for CDS:

Merchandise: [Merchandise.Poster, Merchandise.Banner, Merchandise.'Tent Card', Merchandise.Sticker, Merchandise.'Padala Bible Inserts', Merchandise.'Transaction Slips', Merchandise.'Horizontal Banner', Merchandise.'Large Poster', Merchandise.'Merchandising Kit']

In which we got all the values and submitted it to 1 record. How do I get the values for what the user only chose to be recorded in CDS?

I'm thinking of something like the following in what I did to SharePoint:

Merchandise: 
            ForAll(
                               CDS_Merchandise.SelectedItems,
                               {
                                  Value: CDS_Merchandise.Selected
                               }
                          )

But I'm having the following errors:

[yashag2255 · Accepted solution]

Hi @david_castillo 

Please follow the below configuration and this must allow you to save the multiselect optionset values. I carried out a test on my side and this works fine.

Combobox control to select options -> Items -> Choices(EntityName.MultiSelectColumnName)

Patch Query -> Patch(EntityName,Defaults(EntityName),{Name:"NameValue",MultiSelectColumnName: Combobox1.SelectedItems})

This way it will patch the selected values from combobox control to CDS entity.

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!