cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Auto populate user name only for Closed status

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.

 

Screenshot 2021-01-28 074533.pngScreenshot 2021-01-28 074537.pngScreenshot 2021-01-28 074515.png

43 REPLIES 43
Anonymous
Not applicable

@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
Super User
Super User

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Anonymous
Not applicable

@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
Super User
Super User

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Anonymous
Not applicable

@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
Super User
Super User

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Anonymous
Not applicable

@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
Super User
Super User

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Anonymous
Not applicable

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

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

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