Unanswered

How to populate email id in new sharepoint column from another column which has office365 user ID?

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi,

I already have a Sharepoint database with a column that has Office365 user ID saved. How can I populate the users' email addresses using that ID in another column? I have 2500+ entries. I think I need to use ForAll in a button. Can somebody help me write a formula?

Regards,

Aakash

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (0)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

Can you explain more about what you are trying to do? Your person column in your list will already have the email address in it.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @RandyHayes ,

Thanks for replying. No there is no such column with email address. I have captured the person's office365 ID and display name but not email addresses. Now my client have asked me to introduce another column and populate all the email addresses for the historical data.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

Got you. So, if you have the ID in the column, you can use the UserProfileV2 action on the Office 365 Users Connector to get any other details you want.

So, Office365Users.UserProfileV2(ResourceO365ID).email will give you the email for the user with that ID.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
@RandyHayes
Yes, that I can do for the new entries. Can you tell me how to use the same formula to populate the email address for the existing entries. This will be a one time exercise. I tried using ForAll but couldn't manage to build the working formula.

ForAll( EffortHoursDatabase, Patch( EffortHoursDatabase, Defaults(EffortHoursDatabase),{ ResourceEmail:Office365Users.UserProfile(ResourceID).email } ) ) //Efforthoursdatabase is the main sharepoint list in which to want to populate all the email address in ResourceEmail column. //ResourceID has the O365 ID

Was this reply helpful? Yes No
joeinmay 205 on at

Like
a
(0)

Report
Copy link

Link copied!

Since you are saying this is a one time activity, try the below:
Remember since you have 2500+ records, you decide how you split the records. 500 each.
Get EffortHoursDatabase into a collection (only few columns)
Maybe ClearCollect(Temp1, ShowColumns( EffortHoursDatabase,"ID","ResourceO365ID","ResourceEmail"))
Like i said break the number of records so use filters to the above.
Next display the collection into a Gallery.
In the gallery inlcude a Label1 and Label2 and include:
Label1: ThisItem.ID
Labdl2: Office365Users.UserProfile(ThisItem.ResourceID).email
You should see all the email id's in the respective rows of the gallery.
Insert a button outside the gallery.
Button on select property:
ForAll(Gallery1.AllItems,Patch(EffortHoursDatabase,LookUp(EffortHoursDatabase,ID=Value(Label1.Text),{ResourceEmail:Label2.Text}))

note: Make sure you turn off all related flows for the SP list .

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@Anonymous

Okay, no problem. This is pretty straightforward except for the fact that you are over the 2000 record limit.

So, the Basic formula will be:

ForAll( EffortHoursDatabase As edb, UpdateIf( EffortHoursDatabase, ID = edb.ID, {ResourceEmail: Office365Users.UserProfile(edb.ResourceID).email} ) )

But now we need to figure a way to split your use of this formula over a smaller set of delegable filters to the database. Not only for the factor of delegation, but also because this formula will hit your API limit on the connector very quick and then PowerApps kind of goes into a holding pattern and takes forever to recover.

So, is there anything in the database that we can delegate a filter statement on?

Looking for things like a sequential numeric value (not ID) or a Date range.

@joeinmay ShowColumns is not delegable,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@RandyHayes I think there is only 1 column CreatedDate. The first entry was created on june 17, 2020.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

Do you have a general idea about how many entries per month there are?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@RandyHayes No. Can we not consider an average of 300-400?

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@Anonymous

So then using the created date would be a fairly viable option.

Here is what I would do...

Add a Timer control to your screen and set the Duration to 120000 (2 minutes). I went with 2 minutes per set to avoid hitting the API limit and also to allow for the Updates to complete.

Set the OnSelect action of the timer to : UpdateContext({filterMonth:-1})

Set the Start action of the timer to: Coalesce(filterMonth, 0) < 7

Set the OnStart action of the timer to the following:

With({startRange: DateAdd(Date(2020,6,1), Coalesce(filterMonth, 0), Months), endRange: DateAdd(Date(2020,6,1), Coalesce(filterMonth, 0) + 1, Months)}, With({records: Filter(EffortHoursDatabase, Created>=startRange && Created < endRange)}, ForAll( records As edb, UpdateIf( EffortHoursDatabase, ID = edb.ID, {ResourceEmail: Office365Users.UserProfile(edb.ResourceID).email} ) ) ) )

Let's see how that goes for you.

Was this reply helpful? Yes No