Patch function to update item in Sharepoint List

[venka91]

Dear All, I am newbie in PowerApps. I have a onboarding form for employees. The basic details of employee will be filled in Form1.

When it comes to IT team for approval, IT team fills up the asset details in form.

Now,

in the IT team form, I have a combo box to pull the employee name. In addition I have textboxes for input Laptop Model, Serial Number.

I want to patch the details of asset into the employee record which is got selected using Combo box. I have been trying to do that, may be simple not finding how do I particularly select a record using patching function.

Any help appreciated.

[poweractivate]

@venka91 

From https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-patch

Modify or create a set of records in a data source

Patch( DataSource, BaseRecordsTable, ChangeRecordTable1 [, ChangeRecordTable2, … ] )

• DataSource – Required. The data source that contains the records that you want to modify or will contain the records that you want to create.
• BaseRecordTable – Required. A table of records to modify or create. If the record came from a data source, the record is found and modified. If the result of Defaults is used, a record is created.
• ChangeRecordTable(s) – Required. One or more tables of records that contain properties to modify for each record of the BaseRecordTable. Change records are processed in order from the beginning of the argument list to the end, with later property values overriding earlier ones.

[or for single record]

Patch( DataSource, BaseRecord, ChangeRecord1 [, ChangeRecord2, … ])

• DataSource – Required. The data source that contains the record that you want to modify or will contain the record that you want to create.
• BaseRecord – Required. The record to modify or create. If the record came from a data source, the record is found and modified. If the result of Defaults is used, a record is created.
• ChangeRecord(s) – Required. One or more records that contain properties to modify in the BaseRecord. Change records are processed in order from the beginning of the argument list to the end, with later property values overriding earlier ones.

---

Also refer to this:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

Usually for BaseRecordsTable you want to use something like Filter or Search for that argument to get a Table of records

Or for BaseRecord variant for when you know you want to modify just one record you use something like Lookup for that argument for just the one record.

See if it helps @venka91 

[venka91]

Thanks.

But I am using the code below, but it throws an error.

"Patch Employee Onboarding, Filter(Employee_Onboarding,Title = cmb_empname.Selected) ,{'Asset Model: 'cmb_asset.Selected}"

Employee Onboarding is my SharePoint List.

I am trying to filter the Record from SharePoint list based on the Value I Select in Combo Box.

Then I Will modify the SharePoint link with additional user inputs.

[poweractivate]

@venka91 

Here on this one what you provided is not the correct syntax:

Patch Employee Onboarding, Filter(Employee_Onboarding,Title = cmb_empname.Selected) ,{'Asset Model: 'cmb_asset.Selected}

 Please correct the syntax and if it gives an error after that, please include the error it gives.

Below is a suggested way to rewrite it in correct syntax (but pseudocode and not tested - it is up to you to test and adjust)

If it is written something like this:

Spoiler

Patch(EmployeeOnboardingDataSource
     ,
     LookUp(EmployeeOnboardingDataSource
          ,
          Title = cmb_empname.Selected
     )
     ,
     {AssetModel: cmb_asset.Selected}
)

then it might work. The use of LookUp instead of Filter in above example is intentional. For just using one Record and passing in a change Record, it must be LookUp, not Filter.

Because you use Employee_Onboarding,Title = cmb_empname.Selected we presume it is only one record so you should use the LookUp variant above. In the case there may be more than one match, you can try using the variant below instead:

For patching multiple records at once with a change in bulk  you must instead do this, and the other argument must be a Table and not a Record for the Filter or Search variant:

Spoiler

Patch(EmployeeOnboardingDataSource
     ,
     Filter(EmployeeOnboardingDataSource
          ,
          Title = cmb_empname.Selected
     )
     ,
     Table({AssetModel: "SomethingToChangeThisColumnValueWith"})
)

  (above is pseudocode and may need to be adjusted to your scenario)

[venka91]

Thanks for the input. I have attached screenshot. Pls check. the error is "invalid argument"

[poweractivate]

@venka91 

Is one of them or even both of them a ComboBox rather than a DropDown Control? If it is and you want just one Record, you should change the Control to DropDown. Because ComboBox may have zero, one, or more than one selection - if this is not what you want, it is better change it to a DropDown instead to explicitly only have zero or one selection only. Or if it must be keeping the ComboBox like it is now for some reason, you would do something like this instead in your Formula:

First(ComboBoxControl.SelectedItems)

to get just the first one, and use SelectedItems instead of Selected to be more precise (to use Selected without worrying about this, don't use a ComboBox, use a DropDown Control instead).

but it may be easier change it to DropDown control.

If you want to be able to select multiple people by ComboBox and bulk Patch, this is possible too, but you will need to use ComboBoxControl.SelectedItems and then you must use Filter, not Lookup in the other Formula and remember also to use a Table for the other argument too not a Record- or to work around it and use a Lookup even on a ComboBox, just use First(ComboBoxControl.SelectedItems)  to be more precise and get just one Record, the first record, even if multiple records are selected from ComboBox, it will ignore those.

Please also double check if the above is the same case with Asset Type as well? If so, the same as above applies and may be related to why you get this problem.

[venka91]

I changed it to List box and still I am stuck.

Probably, I am not able to filter a record from the sharepoint list, so I can patch it with additional field details.

I tried using Lookup as well. But it is also giving me the error and it shows the error "expects record".

[poweractivate]

@venka91 

Try just the simple DropDown (not ListBox, but DropDown) for both of the fields, and the LookUp version.

When you feel comfortable with just that - then try multiple record patching with the two ComboBox fields

See if it works @venka91  when you check over our replies again from that perspective

[venka91]

Hi there, I tried. Now I don't have any error. But however when I patch that, it is not saving into the list.

and after running the form, if I click on formula bar, it says, the formula has side effect and cannot be evaluated.

[v-xiaochen-msft]

Hi @venka91 ,

Happy holiday!

First of all, could you tell me: do you need to update one record or multiple records?

If you want to update a record, you could use the patch() function like this:

Patch(data source, A record that needs to be updated, { A column that needs to be updated : Value , Another column that needs to be updated : Value2,....,.... })

It should be noted that if you use the filter() function to filter records, even though there is only one eligible record, it is still table type data. You should use first(filter(xxx,aaa=bbb) ) instead of filter(xxx,aaa=bbb).

Secondly, could you tell me:

What’s data type of ‘Asset Type’ column? What’s data type of ‘Asset SN’ column? What’s data type of ‘Asset Model’ column?

What control is ‘cmb_empname’? What’s items property of this control?

What control is ‘cmb_asset’? What’s items property of this control?

In addition, I noticed that your formula uses the ‘cmb_asset’ control to assign values to ‘Asset Type’ column and ‘Asset Model’ column at the same time.

Is this your requirement or a mistake?

Finally, I suggest you use xxx.selected.(value or anything else) instead of  xxx.selected. Maybe this will solve your problem.

I hope you could provide more information. Looking forward to your reply.

Best Regards,

Wearsky