Update collection using UpdateIf on Choice column then Patch SharePoint List using Patch on Choice column

[powerautouser20]

Hi PA community!

Happy Sunday!

Within my PoweApp, in OnStart property of App -> I am creating a local collection using Power Automate flow after filtering large SharePoint list 

There are a  couple of choice columns in my SharePoint list i.e. a) Early_Termination_Yes_or_No where choices are Yes / No / Unclear 

and b) Category where there are 8 choices

The PowerAutomate response schema is like below for both above columns 

My collection (colSPOListItems) definition is below along with correct schema for above columns : 

Gallery items are set to colSPOListItems

Gallery OnSelect property set to define a variable Set(varListItem,ThisItem)

The workflow of my app is that users update their changes using combo-boxes for above choice columns into the collection.

When users press submit button, these changes made in the collection get updated into the SharePoint list using Patch function.

I have spent hours on this problem but unable to arrive at the solution.

Please can someone help?

Below are the errors I encounter

Thanks in advance!

1) Combo-box OnChange property formula error

2) Submit Button OnSelect property formula error

The yellow highlighted part should be made dynamic but I have just hard-coded some values to demonstrate the challenge I am facing

[mmbr1606]

hey @powerautouser20 

please copy paste your code here so we do not need to retype,

thanks in advance

[LaurensM · Accepted solution]

Hi @powerautouser20,

To Parse a choice column, you can use the following structure:

//Example of your ClearCollect colSPO... ForAll
{
    //Map fields to expected choice schema & define value as a text type
    EARLY_TERMINATION_YES_OR_NO: {Value: Text(Value.EARLY_TERMINATION_YES_OR_NO.Value)},
    CATEGORY: {Value: Text(Value.CATEGORY.Value)}
}

This will result in the expected SP choice column schema.

In order to Patch / Update this column, use the following structure:

//E.g. parameter in UpdateIf or Patch functions
{CATEGORY: {Value: "Text value here"}}

Please note that your UpdateIf condition may not work as expected. You may want to reference the Self.Selected property which is compared to the yes_no column:

UpdateIf(
    //Since this column is defined as a Text column above, the function is not needed
    EARLY_TERMINATION_YES_OR_NO.Value <> Self.Selected.Value,
    //Example using the new update parameter structure
    {EARLY_TERMINATION_YES_OR_NO: {Value: "No"}}
)

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

[powerautouser20]

@LaurensM ,

Thanks for the detailed reply.

I updated my collection definition as suggested by you

EARLY_TERMINATION_YES_OR_NO: {Value: Text(Value.EARLY_TERMINATION_YES_OR_NO.Value)},

CATEGORY: {Value: Text(Value.CATEGORY.Value)}

I updated the OnChange property of the combo-box buttons as suggested by you, sample below:

UpdateIf(colSPOListItems,

    EARLY_TERMINATION_YES_OR_NO.Value <> Self.Selected.Value,

    {EARLY_TERMINATION_YES_OR_NO:{Value:Self.Selected.Value}})

So far, so good!

I still get an error in the Patch formula which is bound to "SUBMIT" button OnSelect property, see code below

I used the structure described by you 

Patch(List_FinalTracker3,

{CATEGORY: {Value:ComboBox1.Selected.Value}}

)

Any ideas about this?

Is the error occurring due to the way CATEGORY is represented in SharePoint list ??

This is definitely much progress than today morning, appreciate your help !

[powerautouser20]

hi @mmbr1606 ,

I will note for future, thanks for the headsup

[LaurensM · Accepted solution]

Hi @powerautouser20,

Thanks for the additional information. Is category a LookUp column instead of a choice column by any chance?

To solve the error, let's add Id to our JSON parsing and patch parameter:

//ParseJSON
CATEGORY: {Value: Text(Value.CATEGORY.Value), Id: Value(Value.CATEGORY.Id)}

//Patch (expects Choices(ListName.CATEGORY) to be your Items property)
//(Or a table with the Value & Id column schema)
{CATEGORY: ComboBox1.Selected}

Should the combobox not contain the Choices() function as items property, you will have to provide the record schema manually within the Patch function:

//Primary column is the main column selected within the 'Select a column' dropdown (see column settings in SP)
{CATEGORY: {Value: ComboBox1.Selected.PrimaryColumn, Id: ComboBox1.Selected.ID}}

I hope this helps!

[powerautouser20]

My bad ... Category is indeed a LookUp column, let me try your latest suggestion!

[powerautouser20]

@LaurensM ,

The ComboBox1 Items property is set at Choices(ListName.CATEGORY)

Similarly, the ComboBox2 Items property is set at Choices(ListName.EARLY_TERMINATION_YES_OR_NO)

The OnChange properties for the Combo-boxes are below:

ComboBox1: 

UpdateIf(colSPOListItems,

    CATEGORY.Value <> Self.Selected.Value,

    {CATEGORY: ComboBox1.Selected})

ComboBox2 : 

UpdateIf(colSPOListItems,

    EARLY_TERMINATION_YES_OR_NO.Value <> Self.Selected.Value,

    {EARLY_TERMINATION_YES_OR_NO:{Value:Self.Selected.Value}})

I am using below in my Patch formula, as suggested by you

{CATEGORY: ComboBox1.Selected}

All the 'compile' errors have disappeared ! 

Now I am getting a run-time error, please see below screenshot

The 'OnSelect' property of my Submit button has the below code: 

Patch(List_FinalTracker3,

{CATEGORY: ComboBox1.Selected},

{EARLY_TERMINATION_YES_OR_NO: {Value:ComboBox2.Selected.Value}}

)

If I try your alternate suggestion within Patch formula i.e. 

{CATEGORY: {Value: ComboBox1.Selected.PrimaryColumn, Id: ComboBox1.Selected.ID}}

then I get below error

Any suggestion to get rid of the runtime error?

Thanks in advance!

[LaurensM]

@powerautouser20,

Does the error remain when using the following Patch structure:

Patch(
    List_FinalTracker3,
    //New record creation patch
    Defaults(List_FinalTracker3),
    //Mention both columns within the same update parameter
    {
        CATEGORY: ComboBox1.Selected,
        EARLY_TERMINATION_YES_OR_NO: ComboBox2.Selected
    }
)

I hope this helps!

[powerautouser20]

Hi @LaurensM ,

Thank you - the run-time error is gone.

However, there is no update to the Data Source (List_FinalTracker3)

There is no new record / existing record is not updated

The collection gets updated

To verify this, I created two text labels referencing the collection values for CATEGORY, EARLY_TERMINATION_YES_OR_NO

Formulae used 

Label_CATEGORY --> "CATEGORY_Value_from_collection: " &  LookUp(colSPOListItems,UNIQUE_ID=Text(17xxxxx6),CATEGORY.Value)

Label_EARLY_TERMINATION --> 

"EARLY_TERMINATION_YES_OR_NO_Value_from_collection: " & LookUp(colSPOListItems,UNIQUE_ID=Text(17xxxxx6),EARLY_TERMINATION_YES_OR_NO.Value)

Below is a 'BEFORE' screenshot i.e. when I refresh the App by pressing App -> Run OnStart

The collection values match those with my list (filtered for UNIQUE_ID: 17xxxxx6)

AFTER screenshot(s) upon clicking SUBMIT button

AFTER SharePoint list screenshot (filtered for UNIQUE_ID: 17xxxxx6)

Moreover, in my process, new records should not be created i.e. only existing records to be updated

I think we are getting there - one step at a time!

Thanks for your support

Please tell me if you know what's happening with the Patch function, reproduced below (latest one I am using)

Patch(

    List_FinalTracker3,

    //New record creation patch

    Defaults(List_FinalTracker3),

    //Mention both columns within the same update parameter

    {

        CATEGORY: ComboBox1.Selected,

        EARLY_TERMINATION_YES_OR_NO: ComboBox2.Selected

    }

)