How to use Patch to clear a people picker field via an EditForm?

[Ken_V]

My power app a person (or people picker) field. The Patch function (triggered via a button on an EditForm) does add a name to or change a name on the SharePoint list. However, when the field is cleared or changed to from someone to blank, currently my Patch function does not clear said field.

List name: Clearance Review Tracker2
People picker field: Contracting Officer

Part of Patch Function that either adds or updates the Contracting Officer field.

If(
IsBlank(
LookUp(
'Clearance Review Tracker2',
PR = DataCardValue9.Text
)
),
SubmitForm(EditForm1),
Patch(
'Clearance Review Tracker2',
LookUp(
'Clearance Review Tracker2',
PR = DataCardValue9.Text
),
{
PIID: DataCardValue10.Text,

'Contracting Officer': {
'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
Claims: "i:0#.f|membership|" & Lower(DataCardValue15.Selected.Email),
Department: DataCardValue15.Selected.Department,
DisplayName: DataCardValue15.Selected.DisplayName,
Email: DataCardValue15.Selected.Email,
JobTitle: "",
Picture: ""
},

Discovered that the following does clear / or sets Contracting Officer to blank
'Contracting Officer' : Blank()

Thinking I need an If STMT that includes an IsBlank check.

Something along the lines of:
If(IsBlank(DataCardValue15.??),
'Contracting Officer' : Blank(),
'Contracting Officer': {
'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
Claims: "i:0#.f|membership|" & Lower(DataCardValue15.Selected.Email),
Department: DataCardValue15.Selected.Department,
DisplayName: DataCardValue15.Selected.DisplayName,
Email: DataCardValue15.Selected.Email,
JobTitle: "",
Picture: ""
}),

If an IF STMT is the way to go, I not sure how to put it into the Patch function per the button on my EditForm?
– OR – there another way to update a person field from someone to blank?

Something along the lines of:
If(IsBlank(DataCardValue15.??),
'Contracting Officer' : Blank(),
'Contracting Officer': {
'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
Claims: "i:0#.f|membership|" & Lower(DataCardValue15.Selected.Email),
Department: DataCardValue15.Selected.Department,
DisplayName: DataCardValue15.Selected.DisplayName,
Email: DataCardValue15.Selected.Email,
JobTitle: "",
Picture: ""
}),

If an IF STMT is the way to go, I not sure how to put it into the Patch function per the button on my EditForm?
– OR – there another way to update a person field from someone to blank?

[RandyHayes]

@Ken_V 

Is there a particular reason you are trying to use Patch and also a form?

All of what you are doing can be done in the SubmitForm action, as you have a lot of formula that you don't need.

Also, you cannot blank out a column data unless you have the Formula level error management feature turned on.

[Ken_V]

Mr. Hayes, thanks for replying. I’m new to Power Apps and I thought that Patch was the only way I could update date an existing SharePoint entry.

The “PR” field in my SP List is a key field that is a required field set to Unique.

Will SubmitForm work if “PR” is changed / updated to a non-existing value?

How should I implement SubmitForm?

Note I do have the Formula level error management feature turned on.

[RandyHayes]

@Ken_V 

Yes, Forms are there for new records as well as updating records.  

If you are editing a record, then whatever changes you make will be updated in the list with the form is Submitted.

You should have some button or icon that has an OnSelect action of : SubmitForm(EditForm1)

That is all you need!

Actually in terms of unique key fields - the ID column of your list is the primary key in terms of your app.  So, anything else is changeable. 

If you have special logic for the field, then it can easily be accounted for in the form prior to submit.  

It can be handled many ways.  One way is to not let the user change the PR value.  Another is showing an error message in the form that displays only if they change to an existing number and another is to disable the submit button if it is not unique.

However, your post seems to imply problems related to the Contracting Officer column.  Is that separate from the concern you have on the PR column?

Let's get you working with the form instead of a patch inside the form.  That is not only extra formula you don't need, but it can also cause issues with your form submit that you don't want.

[Ken_V]

Mr. Hayes, again thank you for the appreciated help. The SubmitForm(EditForm1) via OnSelect is working as you said, and I do like the having much, much less code that is simpler.

Although I am new to creating an App via Power Apps, I am not new to SP and I have created some MS Access that use SP as a back end, so do know that the SP ID column is the primary key. No issues w/the Contracting Officer field (or other person fields) since I switched to SubmitForm.

As for my “PR” field since I do not want to prevent users from correcting their own mistakes, I would to show “an error message in the form that displays only if they change to an existing number and . . . disable the submit button if it is not unique”

Right now the following non-friendly Sys error message is only shown @ the top of the screen when one attempts to duplicate "PR."

I would like to make that message more user friendly and specifically flag the “PR” field (per ErrorMesage6).

How can I make the above happen?

(Note I will not be able to reply until tomorrow due to other commitments.)

[RandyHayes · Accepted solution]

@Ken_V 

Very good!

Easiest thing to do is to add a label in your PR datacard (there already is a default error label there, but easiest to just leave it alone).  

So, if you add a label (let's call it lblDuplicatePR) and set the text to some error message like "Duplicate PR not allowed".

Then set the Visible property of the Label to :  LookUp(yourDataSource, PR=yourPRInputControl.Text, true)

This will make the Visible property true if the record with the same PR is found.

Now, in the Submit Button DisplayMode, set the property to:

   If(lblDuplicatePR.Visible, Disabled, Edit)

[Ken_V]

Mr. Hayes, sorry I was not able to reply sooner.  I want you to know I greatly 

appreciate your help. 

I did as you last said but I did it in the existing default ErrorMessage6 label – even though you specifically said to not used it.  It seemed to be no harder 

or easier to use the default error message label, so why did you recommend 

adding a label as opposed to using the default error message label? 

My data source (which is a SP List ‘Clearance Reivew Tracker2’) has about 25 fields.  A problem I encountered w/your solution was that since the 

LookUp(‘Clearance Review Tracker2’,PR=DataCardValue9.Text, true) was true 

when the other fields were being updated after initial submission, it would 
give users the Error message (and disable the submit button) even though 
they and not changed the PR field. 

Therefore, I used your input to create the following solution. 

In app’s Onstart property I added:  Set(PR_DuplicateMessage, false);  

I set ErrorMessage6 label’s Visible property to:  PR_DuplicateMessage 

In the PR’s DataCard i.e. DataCardValue9 I set OnSelect to:   

UpdateContext({localPR_BeforeChange: DataCardValue9.Text}) 

In the PR DataCard I added label “lblPR_InitialValue” w/the Text property set 
to:  localPR_BeforeChange 

Then set DataCardValue9’s OnChange property to:   

If(And(IsBlank(LookUp('Clearance Review 

Tracker2',PR=DataCardValue9.Text)),DataCardValue9.Text<>lblPR_InitialValue.Text), Set(PR_DuplicateMessage, false), Set(PR_DuplicateMessage, true)) 

In EditForm1’s submit button DisplayMode I added:  
If(lblPR_InitialValue.Visible, Disabled, Edit) 

“lblPR_InitialValue” is invisible. 

Only when a user is editing an existing entry and they change PR to a value that is not already on the SP List, then edit another field, then click back into PR and change it back to it’s original is the user given a false error message.