Auto-fill choice field based on users last selection

[b1t1]

Hi!

I have a customized form and trying to speed up the item creation process as much as possible for the users. I have a problem with a choice column in which they specify a location. They need to be able to choose location of course but 95% of the time while entering a new item the location will be the same for that given user that given day.

So I tried setting a variable on startup to save logged in users email and then, on DefaultSelectedItems property of choice column (datacardvalue), I tried finding that specific users last selection (on previous item/row) and put it as default. I can't get it to work properly.

App -> OnStartup (Does this work on customized form?)

Set(varUserEmail, User().Email)

datacardvalue6 -> DefaultSelectedItems

First(SortByColumns(Filter('TEST Temporary badges | Admission', 'Created By'.Email=varUserEmail),"ID", Descending)).Location

  

Notes:

- About 8-15 people will be editing the same list at the same time, creating their own items and editing items created by others. They all use different locations and sometimes the same location.

- The list will probably go way above 2000 items, so functions need to be delegatable

- Location data (the choices) are not available through office365user catalogue

 

 

Follow-up question: On Default property on hour and minute fields I have the Now()-function, but it only works if you refresh the webpage. It doesn't refresh when I click new item. How do I fix that? So time sets accordingly when pressing new item.

[b1t1]

Well, it "works" on the label but no text appears: 

It throws error on Location field. Yes, it's a single choice column. Shows as combobox in PowerApps.

I also don't want it to override the chosen value if I go back and edit an item (if I get this to work). Had that problem with the date and time picker, it overrides chosen value with my default "Now()" everytime I opened and old item. I fixed it with an If-statetement. But that don't seem to work with a LookUp-function, says If-function can't contain scope.

[WarrenBelz]

@b1t1 ,

If this does return a result

LookUp(
   Sort(
      'TEST Temporary badges | Admission',
      ID,
      Descending
   ),
   'Created By'.Email = varUserEmail
).Location.Value

there is something wrong in your data as it will return the value of the Location field in the last record created by the current user - I have confirmed this in a test here. Also ensure varUserEmail has a value when testing (or just user User().Email).
Also your error is because you have taken {Value: . . .} away from the DefaultSelectedItems and now you have a Text value for a Table requirement. You need either Value in both places or neither (now I know it is a Choice field which was not evident previously).

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[b1t1]

I think I might've found the problem. The User().Email returns email as: ID@companyname.com but when hovering over user name in 'Created by' column in the list the email is shown as name.lastname@companyname.com. 

No matter what column I choose in your formula, it doesn't return an error, but result is always blank, as if the filter removes all rows from the lookup. It makes sense if its filtering by ID@companyname.com and only find emails like name.lastname@companyname.com.

But how the hell do I fix it? User() only has options like email, Fullname and picture. 'Created by' has options DisplayName, Department, Email (which doesnt match) etc.

[WarrenBelz]

@b1t1 ,

There is something wrong with your AD settings if User().Email does not return the current user's email address and 'Created by'.Email returns something different as this should be the email of the logged-in user (who logs in with their email address). Put this on a Label and see what you get displayed

First(Office365Users.SearchUser({searchTerm:User().Email,Top:999})).Mail

[b1t1]

I get an error using that. Looks like Office365Users doesn't exist? I get no suggestions for followups when writing it on it's own either.

Well the e-mails I described look different but they are treated as the same e-mail. For example, IDs are generated from the names. So you can send an e-mail to either Adam.Smith@company.com or ASMITH1@company.com , both work and the same user gets the email no matter which address you send to.

[WarrenBelz · Accepted solution]

Hi @b1t1 ,

Firstly, you need to add Office365Users in your data sources - can you please do this and report what is on the Label. I suspect you have alias emails in your AD, but the issue you have is that you can only retrieve the primary email using either User().Email or the code I sent.

To your second question, Now() is not a "ticking clock" - it is a snapshot at the time it was set by an event (in your case the screen opening with sets the Default of the control). You could try setting a Variable as Now() and then have this as your control Default, but you still need to set this in whatever event/trigger you need it to update.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[b1t1]

Thank you! Managed to solve it after adding Office365Users and using your formula. I got the correct e-mail to the variable by using Office365Users.MyProfile(v2).Mail instead of User().Email. 🙂

Also made variables for hours and minutes initializing the variable (using now()) on the forms visible property, hoping it updates everytime I create a new item.

[b1t1]

A new problem arose when editing an existing record.

I have three fields only visible when editing and set as to auto-fill the location and status when selecting "Return date". Problem is I get "Network error when using Patch function. An error occurred on the server." when trying to save record after only selecting return date (so location and status is auto-filled).

Error is circumvented by simply manually editing the return location field before saving. How can I get the form to accept the auto-filled location? It seems identical to manually selecting, I can't find the reason for patching error.

I have a similar "auto-fill" functon for the first location field and that works correctly, the status field dependent on the same return date field works as well.

This code seems to generate an invalid location and the error. Just manually selecting the same location in this field after it is auto-populated allows the record to be saved, and I have no idea why.

If(SharePointForm1.Mode=FormMode.Edit And ThisItem.'Return location'.Value=Blank() And IsBlank(DataCardValue7.SelectedDate) = false,
{
   Value:
   LookUp(
      Sort(
         'TEST Temporary badges | Admission',
         ID,
         SortOrder.Descending
      ),
      'Created By'.Email = varUserEmail 
   ).'Return location'
},
Parent.Default)

This works correctly and can be saved:

If(SharePointForm1.Mode = 0 And (IsBlank(DataCardValue7.SelectedDate)) = false, {Value: "Returned"}, Parent.Default)

   

[WarrenBelz]

Hi @b1t1 ,

This is a more concise version  of what you have - please see if it works 

{   
   Value:
   If(
      SharePointForm1.Mode = FormMode.Edit And 
      ThisItem.'Return location'.Value = Blank() And 
      Value(DataCardValue7.SelectedDate) > 1,
      LookUp(
         Sort(
            'TEST Temporary badges | Admission',
            ID,
            SortOrder.Descending
         ),
         'Created By'.Email = varUserEmail 
      ).'Return location',
      ThisItem.'Return location'.Value
   }
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[b1t1]

Hi! This almost worked, I had to correct a bracket and add a ".value" to avoid errors. After this I could save every time however, the pre-filled location always was the same, previously it always fetched my last used return location when I selected a return date, but with your formula the pre-filled location was identical even if I saved a bunch of different locations.

The adjusted code: 

{   
   Value:
   If(
      SharePointForm1.Mode = FormMode.Edit And 
      ThisItem.'Return location'.Value = Blank() And 
      Value(DataCardValue7.SelectedDate) > 1,
      LookUp(
         Sort(
            'TEST Temporary badges | Admission',
            ID,
            SortOrder.Descending
         ),
         'Created By'.Email = varUserEmail 
      ).'Return location'.Value,
      ThisItem.'Return location'.Value
   )
}