cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ARAlmac
Helper I
Helper I

Patch Choice Values to Collection and SharePoint

Hi PowerApps Community,

 

I  am following Matthew Devaney's blog on excel style editing here:

https://matthewdevaney.com/power-apps-excel-style-editable-table-part-1/

 

I have come to patching all the changes to the collection and SP List.

App On Start:

ClearCollect(colUpdates,{
ID: 1,
'Overall Performance Rating': "A",
'Additional % Increase or Decrease to Standard' : "1",
'Proposed Payroll Salary Increase': 1,
'Proposed Payroll Salary':"1",
Approved: "A"
});
Clear(colUpdates);

OnSelect (Save Icon):

// Create a collection to store updated values
ForAll(
    Filter(
        Gallery_ManStream.AllItems,
        tog_IsChanged.Value
    ) As ChangedRows,
    Patch(colUpdates,
    Defaults(colUpdates), {
        ID: ChangedRows.ID,   
    'Overall Performance Rating':  ChangedRows.DropdownGal_OPR_Stream.Selected.Value,
    'Additional % Increase or Decrease to Standard' : Value(ChangedRows.txtAdd.Text),
    'Proposed Payroll Salary Increase': Value(ChangedRows.txtProposedSalaryIncrease.Text),
    'Proposed Payroll Salary': Value(ChangedRows.txtProposedSalary.Text),
    Approved: ChangedRows.Dropdown_ManagerApproved_Stream.Selected.Value
    })
);

// Update SharePoint with new values
Patch(Data, colUpdates);
Clear(colUpdates);

 

I am receiving this error:
invalid argument type table expected record value instead

ARAlmac_0-1617290196148.png

The "Overall Performance Rating" and "Approved" fields are Choice fields which I think could be my problem.

In a Collection set up - is there anything that is needed to let the collection know its a choice field? 
In my patch function, I changed the code to have the:

Approved: {
'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Value: ChangedRows.Dropdown_ManagerApproved_Stream.Selected.Value
}

I then received : the type of this argument "Approved" does not match type "Text". Found type "Record".

 

Any help would be great!

1 ACCEPTED SOLUTION

Accepted Solutions
v-xiaochen-msft
Community Support
Community Support

Hi @ARAlmac ,

 

I don't think you need to use a collection for your needs.

You could use this formula:

ForAll(
Filter(

        Gallery_ManStream.AllItems,

        tog_IsChanged.Value

    ) As ChangedRows,
        Patch(your datasource,
             LookUp(your datasource, ID= ChangedRows.ID),{
             'Overall Performance Rating': {Value: ChangedRows.DropdownGal_OPR_Stream.Selected.Value},

    'Additional % Increase or Decrease to Standard' : Value(ChangedRows.txtAdd.Text),

    'Proposed Payroll Salary Increase': Value(ChangedRows.txtProposedSalaryIncrease.Text),

    'Proposed Payroll Salary': Value(ChangedRows.txtProposedSalary.Text),

    Approved: {Value: ChangedRows.Dropdown_ManagerApproved_Stream.Selected.Value}

})
)

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

View solution in original post

7 REPLIES 7
v-xiaochen-msft
Community Support
Community Support

Hi @ARAlmac ,

 

Since you are using a sharedpoint list instead of an excel sheet, there are some differences between them.

 

1\ Please do not manually create the column names of the sharepoint list in the collection.( If there are spaces or special characters in the column names, errors are prone to occur)

2\ You could not edit the ID column of the list, it is read-only, it is automatically generated. So, you don’t need to create an ID column in your collection.

 

I did a test for you.

1\ This is my test list ‘LIST104’.

v-xiaochen-msft_0-1617349125093.png

 

/* Proposed Payroll Salary Increase is a Title column. I don’t know which column you renamed the "Title" column to.

  Additional % Increase or Decrease to Standard is a Number column.

  Proposed Payroll Salary is a Number column.

  Overall Performance Rating is a Choice column.

  Approved is a Choice column.

*/

 

2\ Add a button control and set its onselect property to:

ClearCollect(colUpdates,ShowColumns(LIST104,"Title","Additional_x0025_IncreaseorDecre","ProposedPayrollSalary","OverallPerformanceRating","Approved"));Clear(colUpdates)

// The column names in your ShowColumns function may be different from mine

 

3\ Add a button control and set its onselect property to:

ForAll(

    Filter(

        Gallery_ManStream.AllItems,

        tog_IsChanged.Value

    ) As ChangedRows,

    Patch(colUpdates,

    Defaults(colUpdates), { 

    'Overall Performance Rating': {Value: ChangedRows.DropdownGal_OPR_Stream.Selected.Value},

    'Additional % Increase or Decrease to Standard' : Value(ChangedRows.txtAdd.Text),

    'Proposed Payroll Salary Increase': Value(ChangedRows.txtProposedSalaryIncrease.Text),

    'Proposed Payroll Salary': Value(ChangedRows.txtProposedSalary.Text),

    Approved: {Value: ChangedRows.Dropdown_ManagerApproved_Stream.Selected.Value}

    })

)

 

4\ Add a button control and set its onselect property to:

Patch(LIST104,colUpdates)

 

5\ The result is as follows:

v-xiaochen-msft_1-1617349125095.png

 

 

Best Regards,

Wearsky

If my post helps, then please consider Accept it as the solution to help others. Thanks.

Hi @v-xiaochen-msft ,

 

Thanks for the reply!

 

Since it is updates do you not need the ID? otherwise it will just create a new record?

 

I also think Matthew's blog is working with a SharePoint list, the design of the Gallery is just "excel style".

v-xiaochen-msft
Community Support
Community Support

Hi @ARAlmac ,

 

In my example, what I did was create records instead of updating records.

You may need an ID if you need to update the record instead of creating a record.

No "ID" is required to create a record (the record has not been created yet, so there is no ID).

What is your ultimate goal?

 

Best Regards,
Wearsky

@v-xiaochen-msft ,
So it is a salary review app where we have a SharePoint list of data.

This user will come in and change a few record values and then there is one save button that saves and updates the changes. No new records added, just updates.

For example, John Smith is earning 50000 and has been given a 5% increase so the value needs to change to 52500.

v-xiaochen-msft
Community Support
Community Support

Hi @ARAlmac ,

 

I don't think you need to use a collection for your needs.

You could use this formula:

ForAll(
Filter(

        Gallery_ManStream.AllItems,

        tog_IsChanged.Value

    ) As ChangedRows,
        Patch(your datasource,
             LookUp(your datasource, ID= ChangedRows.ID),{
             'Overall Performance Rating': {Value: ChangedRows.DropdownGal_OPR_Stream.Selected.Value},

    'Additional % Increase or Decrease to Standard' : Value(ChangedRows.txtAdd.Text),

    'Proposed Payroll Salary Increase': Value(ChangedRows.txtProposedSalaryIncrease.Text),

    'Proposed Payroll Salary': Value(ChangedRows.txtProposedSalary.Text),

    Approved: {Value: ChangedRows.Dropdown_ManagerApproved_Stream.Selected.Value}

})
)

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

View solution in original post

@v-xiaochen-msft  ah right okay!

 

Does that patch still work if multiple records are changed? 

 

I think the collection was to collect the rows that have been changed and their IDs and then Patch the patch updates all them?

v-xiaochen-msft
Community Support
Community Support

Hi @ARAlmac ,

 

My formula is used to patch() multiple records at once.

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,373)