Auto populate user name only for Closed status

[Anonymous]

Greetings.

I have a SharePoint list that I'm using as a leads database and I'm having a small issue that I cannot figure out. When a rep selects "Closed" from the status dropdown, it autofills the closed date and closed by fields based on who's logged in. I'm using the following code to accomplish that.

If(DataCardValue13.Selected.Value="Closed"
,{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
Claims:Concatenate("i:0#.f|membership|",Office365Users.UserProfile(User().Email).Mail),
DisplayName:Office365Users.UserProfile(User().Email).DisplayName,
Email:Office365Users.UserProfile(User().Email).Mail},ThisItem.'Closed By')

When I started testing the form, I noticed that when I made a change to a list items, it wouldn't save back to SharePoint. I found a blog post (don't remember where) that suggested to add the following code in the Update property for the DataCard.

{Claims:"i:0#.f|membership|" & User().Email,Department:"",DisplayName:"",Email:"",JobTitle:"",Picture:""}

The problem of saving changes to a list item was fixed by adding the code above, but now I noticed that when I select "In Progress" from the status dropdown, it saves the users name in the Closed By field. How can that be fixed?

Any help would be greatly appreciated.

[Anonymous]

@RandyHayes That seemed to work! Would you mind explaining how that seemed to work and not the original formula I was using? Now, on to more testing...

[RandyHayes]

@Anonymous 
Good deal!  Sorry for so many iterations of typos!

Your original formula was:

If(DataCardValue13.Selected.Value="Closed", 
    {'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
     Claims:Concatenate("i:0#.f|membership|",Office365Users.UserProfile(User().Email).Mail),
     DisplayName:Office365Users.UserProfile(User().Email).DisplayName,
     Email:Office365Users.UserProfile(User().Email).Mail},

     ThisItem.'Closed By'
)

It is actually pretty much Identical (and my only alterations had to do with the removal of the odata.type and moving the expensive UserProfile action and User() functions out of the formula.

The issue in that formula is all of the calls to the User() function and the UserProfile actions are expensive.  Those have bad results when used in a record like this.  You have 3 calls to User() which take time to return and 3 calls to the UserProfile action. All very expensive and also prone to not return values "in time" for the formula to evaluate properly.  

With what I provided, we removed the need for the UserProfile action (as we don't need it at all), and we moved the expensive User() function into a Scoped With variable so that it only execute once and outside of the record.

I hope that explains it somewhat.

[Anonymous]

@RandyHayes It does thank you.

Just to confirm. I should use the following formula for the Default property

If(DataCardValue12.Selected.Value="Closed"
,{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
Claims:Concatenate("i:0#.f|membership|",Office365Users.UserProfile(User().Email).Mail),
DisplayName:Office365Users.UserProfile(User().Email).DisplayName,
Email:Office365Users.UserProfile(User().Email).Mail},ThisItem.'Closed By')

and this formula under the Update property

With({_user:User()}, If(DataCardValue12.Selected.Value="Closed", {Claims: "i:0#.f|membership|" & Lower(_user.Email), Department:"", DisplayName: _user.Email, Email:_user.Email, JobTitle:".", Picture:"."}, Self.Default))

[RandyHayes]

@Anonymous 

No, that first formula should not be used.  You mentioned the Default property...of what?  The Datacard or the Control in the Datacard?  Your Default property on the DataCard should be ThisItem.'Closed by' and the DefaultSelectedItems property on the control in the card should be Parent.Default (assuming that you have a combobox)

[Anonymous]

@RandyHayes Interesting. Do you mind taking a look at the original post? Just to make sure we're on the same page.

I was using the first formula for the Default  Closed By_DataCard1 property, and the second formula for the Update Closed By_DataCard1 property. Hope I didn't confuse you.

[RandyHayes]

@Anonymous 

To add...the reality is that (from a user interface perspective) your properties should be as follows:

DataCard Default Property:

ThisItem.'Closed By'

The Control (combobox) in your card DefaultSelectedItems property should be:

If(DataCardValue13.Selected.Value="Closed" && IsBlank(Parent.Default), 
    {'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
     Claims:Concatenate("i:0#.f|membership|",Office365Users.UserProfile(User().Email).Mail),
     DisplayName:Office365Users.UserProfile(User().Email).DisplayName,
     Email:Office365Users.UserProfile(User().Email).Mail},

     Parent.Default
)

And the Update property should be:

YourComboboxName.Selected

This will show the user the values.  Before we were just doing them on the Update.

Also, this accounts for the record already having a closed by person in the record and will not overwrite if there is one already.

[RandyHayes]

@Anonymous 

Yes, those formula you had in the original were incorrect in where they should be.  The last post I just sent should be the exact things you need.

[Anonymous]

@RandyHayes Ok thank you for clarifying. For some reason I kept inserting your suggestion in the Update property. Not sure why I thought that. I'm going to try what you just sent me to see if it works.

Any thoughts as to why it's "working" earlier when I said it was?

[RandyHayes]

@Anonymous 

Actually, the formula I was originally providing to you was aimed at the Update property.

It wasn't until after it was working for you that I realized this was a visible control in your form and it should really reflect the proper value of the record AND should also not change is someone else edited the record.

As it was, if a user (user x) went in and set the item as "closed", the Update would have properly used their information to update the person column in your list.  The problem was, if User A then edited the record and saw that User X was the person who closed it, and then Saved the record...not User A would be the person that closed it.

So, we solved both seeing the proper user and maintaining the proper user in the formulas and their places that I last sent.

[Anonymous]

@RandyHayes Ok so I wasn't crazy then...hahaha. That actually makes perfect sense. I'm going to make those changes and start testing. Thanks again. Now, on to testing. For real this time!