Pre-populate form based on user (if existing)

[WG3]

I have a SharePoint list called "Subscribers" with a number of fields

• Subscriber - Person type
• Matching - Multi-select choice field
• Preferred Location - Multi-select choice field
• Active - Multi-select choice field

Now I would like to build an PowerApp which allows a user to modify it's subscription. Users are not supposed to see who else is subscribed. Now I would like to have a form which pre-populate the 4 fields based on the user. I created a PowerApp from the SharePoint list.

The form is then populated using the formula 

If(IsBlank(SharePointIntegration.Selected) || IsEmpty(SharePointIntegration.Selected),First([@Subscribers2]),SharePointIntegration.Selected)

When I understand this correctly, it pre-populates based on the selection. However as mentioned I just want to have the form pre-populate by the user entry.

Now I tried the solution from https://powerusers.microsoft.com/t5/Building-Power-Apps/Pre-populate-form-data-based-on-current-user... however it does not seem to work correctly. 

Who can help :-)?

LookUp(
    SortByColumns(mySPList, "Created", SortOrder.Descending),
    'User Name' = User().FullName
)

// Tried the following and varations
LookUp(
    SortByColumns(Subscribers, "Created", SortOrder.Descending),
    'Subscriber' = User().FullName
)

LookUp(
    SortByColumns(Subscribers, "Created", SortOrder.Descending),
    'Subscriber' = User().Email
)

[RusselThomas]

Hi @WG3 ,

First thing I notice is that you're performing a lookup, which finds a specific record in the source based on some condition - if it's possible that the condition could be true for more than one record, then it will return the first record it finds that matches that condition, in which case the sortorder of the data set might be relevant.

So in your examples above, I'm assuming 'Subscriber' exists multiple times in the list, so you want to look up the last created entry where 'Subscriber'=User().Email

If each 'Subscriber' only has one record in the list, then you can drop the sortbycolumns as it becomes irrelevant. 

Secondly, the person column field in SPO is actually a record containing many fields - if 'Subscriber' here is a person column in SharePoint Online, then you're trying to compare an entire record to a single value, which you simply cannot do.

You need to select a field from that record to compare User().Email to - which would likely look something like this;

LookUp(
    SortByColumns(Subscribers, "Created", SortOrder.Descending),
    'Subscriber'.Email = User().Email
)

Just make sure that the User().Email and the 'Subscriber'.Email actually do match.  (You can test this by adding a text label and setting its Text: property to User().Email then finding the corresponding record in your source manually by looking at the list and comparing the email addresses.  They need to match exactly.

If your Form is set to Edit or View and the source is Subscribers then the above should fetch that record and populate the relevant fields for you.

Hope this helps,

RT

[WG3]

@RusselThomas, thanks for taking the time! The Sort by isn't needed indeed. The 'subscriber' column is unique. So every person is only listed once. So correct, 

LookUp(Subscribers,Subscriber.Email=User().Email)

 
Should suffice I believe. However when I use this or different forms

LookUp(Subscribers,Subscriber=User().Email)
LookUp(Subscribers,'Subscriber'.Email=User().Email)
LookUp(Subscribers,'Subscriber'.Email="abc@foo.com")
LookUp(Subscribers,Subscriber.Email="abc@foo.com")

all do not work. I get an error: Incompatible types for comparison. These types can't be compared: Error, text. Which I find really strange.

[RusselThomas]

Hi @WG3 

The types being compared are on either side of your comparison eg: A = B

"These types can't be compared: Error, Text" is saying A is resulting in an Error while B is a Text value, and it can't compare the two.  All of which points to Subscriber.Email being the troublemaker - so you need to validate that 'Subscriber' is

• A valid column name in your list 'Subscribers'
• Is a person column type 

You can also see how PowerApps views this by adding a button and setting its OnSelect: property to 

Set(varSubscriberRecord, First(Subscribers))

then go to your view menu, variables, then view the global variable varSubscriberRecord to see how the data is constructed.  If you see a little excel table icon in a field, it means you can click into it and see the underlying record data within the field.  In theory, the 'Subscriber' field would look like that if it was a person column.

If it just has an email address in it, then it's probably just a flat text field.

Let me know how it goes,

RT

[WG3]

Thanks @RusselThomas for your support.

It really is weird all the fields are there. See the image below. An condition on any of the other fields work, like displayname. However, displayname isn't unique for all employees, e-mail is. I could use the claims field which also has the email in it however, it frustrates me now that the email field doesn't work correctly for some reason.

LookUp(Subscribers,Subscriber.DisplayName="<name>") // works
LookUp(Subscribers,Subscriber.Email="<name>@company.com") // doesn't work
LookUp(Subscribers,User().Email in Subscriber.Claims) // works -_-', but performance wise really a bad solution

Also checked if I shouldn't use lowercase or x002 etc kind of stuff. All not the case.

[RusselThomas]

Hi @WG3 ,

So, just to be clear, using this on a button;

Set(varSubscriberRecord, LookUp(Subscribers,Subscriber.Email="<name>@company.com"))

throws the condition typing error?  I'm assuming the error indicator is lying under the = sign on that formula then?

If so, then yes, that is pretty weird...

RT

[WG3]

@RusselThomas 

Not exactly on the = but on the '.Email=' it says "Name isn't valid. 'Email' is isn't recognized". But other fields like Claims, DisplayName are OK

[RusselThomas]

Can you share a screen snip?

Also retype the formula, and when you get to "LookUp(Subscribers, Subscriber." you should get some prompts as to valid properties below that level - do you see them and what are they?