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

(0) Share

Report

Posted on by DD-02090909-0

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

Categories:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (3)

mmbr1606 14,605 Super User 2025 Season 2 on at

Like (0)

Report

hey @powerautouser20

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

thanks in advance

Was this reply helpful? Yes No
Verified answer

LaurensM 12,516 Moderator on at

Like (0)

Report
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!

Was this reply helpful? Yes No
DD-02090909-0 51 on at

Like (1)

Report

@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 !

Was this reply helpful? Yes No
DD-02090909-0 51 on at

Like (0)

Report

hi @mmbr1606 ,
I will note for future, thanks for the headsup

Was this reply helpful? Yes No
Verified answer

LaurensM 12,516 Moderator on at

Like (0)

Report
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!

Was this reply helpful? Yes No
DD-02090909-0 51 on at

Like (0)

Report

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

Was this reply helpful? Yes No
DD-02090909-0 51 on at

Like (0)

Report
@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!

Was this reply helpful? Yes No
LaurensM 12,516 Moderator on at

Like (0)

Report
@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!

Was this reply helpful? Yes No
DD-02090909-0 51 on at

Like (0)

Report

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

}
)

Was this reply helpful? Yes No
DD-02090909-0 51 on at

Like (0)

Report

Hi @LaurensM ,
I did some more experiments

My Gallery2 Items property has the colSPOListItems collection

So, I also tried the below patch formulae

1) Separate record in update record
Patch(
LookUp(List_FinalTracker3,UNIQUE_ID=Text(Gallery2.Selected.UNIQUE_ID)),

{CATEGORY:ComboBox1.Selected},
{EARLY_TERMINATION_YES_OR_NO: ComboBox2.Selected}

)

AND

2) Combined record in update record
Patch(
LookUp(List_FinalTracker3,UNIQUE_ID=Text(Gallery2.Selected.UNIQUE_ID)),

{CATEGORY:ComboBox1.Selected,
EARLY_TERMINATION_YES_OR_NO: ComboBox2.Selected}

)
Both methods above did not work to update the SharePoint list

Finally, to brute force test it, I tried the below Patch function, which also did not work! 😯
I am clueless at what is happening now ...

Patch(
LookUp(List_FinalTracker3,UNIQUE_ID=Text(17xxxxx6)),

{CATEGORY:{Value:"Early Termination confirmed", Id:7}},
{EARLY_TERMINATION_YES_OR_NO: {Value:"Unclear"}}

)

Was this reply helpful? Yes No