How to use information from 'Get user profile (V2)' that is in an Apply to each loop (and write it to different columns in SP List)?

(1) Share

Report

Posted on by Femke1

348

Hi smart people,

Hope you can help me with the following situation (I start a new topic, because I gave myself replies in my last post):

- One person uploads document(s) using Forms and fills in other email addresses (up to 6)

- I send an 'Approval' to all the email addresses the person filled in on the Form.

- In an Apply to each loop I gather User Information (Get user Profile (V2)).

- I have it working until here

But then, after everyone approved, I want to use the 'Get user profile (V2)' information and store that per approver in different columns in a SP List (for example: DisplayName, givenName, surname, employeeId, mail). So my columns are named mail1, mail2, mail3, mail4, mail5 and mail6 (and also for the other values: like DisplayName1 up to 6, etc).

I've tried the steps below, but what happens, is that now only the last of the 'apply to each-loop' appears in de column in the list:

- I sort of hoped that every name would appear in the column (comma seperated - to later separate them to different columns)

- But I actually want every name to appear in different columns of the list

Then I added a Compose action, right after the 'Apply to each' loop, that joins the information gathered in the Compose action that is in the loop:

The output is this (so it DOES gather the information that I want):

{"@odata.context":"https://graph.microsoft.com/.../...(displayName,employeeId,mail,givenName,surname)/$entity ",
"displayName":"Name of Person 1","employeeId":"ID of Person 1","mail":"email of Person 1","givenName":"Given name of Person 1"},
{"@odata.context":"https://graph.microsoft.com/.../...(displayName,employeeId,mail,givenName,surname)/$entity ",
"displayName":"Name of Person 2","employeeId":"ID of Person 2","mail":"mail of Person 2","givenName":"Given name of Person 2","surname":"Surname of Person 2"}

But still... how can I use or split the info and put it in different columns in a SP List?

Another idea that I had was to initialize a variable (array) and get all the profiles in an apply to each (after 'Start and wait for an approval' with an append to array variable), but I'm in doubt on how to split the information afterwards per approver? Or maybe something with filtering an array? But then, I first have to built an array with the information?

Anybody an idea on how to do this?

Help is much appreciated! Thanks in advance 🙏

Femke

Categories:

Building flows

I have the same question (0)

All responses (17)

Answers (3)

abm abm 32,856 Most Valuable Professional on at

Like (1)

Report

Re: How to use information from 'Get user profile (V2)' that is in an Apply to each loop (and write it to different columns in SP List)?

Hi @Femke1

Looks like you mapped the while output body. See here I can see the dynamic properties.

If you want to store this in an array then declare a variable type as array just after the trigger step. Then above where I added the compose you could use Append Array to store the variable.

The screenshot you provided looks like you are updating after Get Profile 2. Looks like thats not correct. Place the update sharepoint action step after all the approvals are done. Finally you can reference the array values using the index against each fields for the update.

Thanks

Thanks

Was this reply helpful? Yes No
Femke1 348 on at

Like (0)

Report

Re: How to use information from 'Get user profile (V2)' that is in an Apply to each loop (and write it to different columns in SP List)?

Ah, so I need to initialize a variable - array for each value (1 for mail, 1 for DisplayName, 1 for Given name, etc)?

And then in the Apply to each an append to the array variable?

Was this reply helpful? Yes No
Femke1 348 on at

Like (0)

Report

Re: How to use information from 'Get user profile (V2)' that is in an Apply to each loop (and write it to different columns in SP List)?

Hi abm,

Do you perhaps mean that I initialize a ColumnIndex (value 1), increment that value in each loop and then put in an 'if statement' to set the columnname where it should put the value?

Thanks again 👍

Was this reply helpful? Yes No
Femke1 348 on at

Like (1)

Report

Re: How to use information from 'Get user profile (V2)' that is in an Apply to each loop (and write it to different columns in SP List)?

Oké, my post keeps disappearing... maybe because I used screenshots in it? Sorry for that...

I now have it working almost...

- I first did an 'Initialize variable' of type 'Integer' with a value of 1 - I called it ColumnIndex.
- Then, in the Apply to each loop, directly after 'Get user profile', I did an 'Increment variable' (increment by 1).
- Then, in the 'Update item', I used an if-statement in every column.
- The if-statement I used in column 'DisplayName2' is:
if(equals(variables('ColumnIndex'), 2), outputs('Get_user_profile_(V2)_2')?['body/displayName'], '')
- The if-statement I used in column 'DisplayName3' is:
if(equals(variables('ColumnIndex'), 3), outputs('Get_user_profile_(V2)_2')?['body/displayName'], '')
etc.

The thing is; the first round it works fine, but because I set the 'false' value on '' (empty), the next round it overwrites the value that is put in there (the name of the person that was put in there in de first round) and it perfectly writes down the name of the person of that next round... What I want is that it does not overwrite anything if the statement is false, but leaves the value that is in there, as it is 🙂

Any solution for adjusting my if-statement?

Was this reply helpful? Yes No
Femke1 348 on at

Like (1)

Report

Re: How to use information from 'Get user profile (V2)' that is in an Apply to each loop (and write it to different columns in SP List)?

I have it working now, but I used conditions for it (if ColumnIndex is equal to 2 - if yes: update item with the content from the Get user profile - if no, do nothing) - same conditions for if ColumnIndex is equal to 3, etc.

It works for now, but if anyone has a better solution - you're welcome to share 😉

Was this reply helpful? Yes No
abm abm 32,856 Most Valuable Professional on at

Like (0)

Report

Re: How to use information from 'Get user profile (V2)' that is in an Apply to each loop (and write it to different columns in SP List)?

Hi @Femke1

What is the below condition for?

if(equals(variables('ColumnIndex'), 2), outputs('Get_user_profile_(V2)_2')?['body/displayName'], '')

Thanks

Was this reply helpful? Yes No
Femke1 348 on at

Like (0)

Report

Re: How to use information from 'Get user profile (V2)' that is in an Apply to each loop (and write it to different columns in SP List)?

Yeah, because my replies are missing, the story/my timeline of thinking isn't complete anymore and I can understand that it doesn't make sense anymore 😉

After my first reply to you, I wrote this (I now skip the images and I hope my reply does not disappear):

Thank you so much for your help, I really hope I can pull this one off 🙂

First I initialized 2 values (later, if this works, I'm going to initialize all my values such as Given Name, employeeId, etc):
Image of the two 'Initialize variable type Array' missing
Then, after everyone approved/rejected, I gathered the information with 'Get user profile' and then 'Append to array variable' for the 2 values that I initialized earlier:
Image of the steps:
- Start and wait for an approval
- Apply to each
- Get user profile (V2)
- Append to array variable
- Append to array variable
- Update item
But then you say: "Finally you can reference the array values using the index against each fields for the update". I'm afraid I don't understand how I can do that - I also don't see dynamic content in the 'Update item' action that rings a bell...

Can you please break that down to Power Automate steps or maybe share a screenshot?
Thank you very much again for your time and effort!

I did this reply twice, because the first time it disappeared, but it disappeared again... So then I went on and kept thinking about what you had said ("Finally you can reference the array values using the index against each fields for the update"). That inspired me to initialize a variable at the beginning of my flow, type Integer, named 'ColumnIndex'. In the apply to each loop, I increment the value of ColumnIndex by 1. So that gave me something to reference the value of each round in the loop, whoopwhoop!

With that, I could use an if statement to fill the columns in the action 'Update item':
if(equals(variables('ColumnIndex'), 2), outputs('Get_user_profile_(V2)_2')?['body/displayName'], '')
Meaning: if the value of ColumnIndex = 2, place the display Name of Get user profile_2, else: do nothing.
This caused 1 problem: it deleted the value in column 2 when it ran through round 3 (I hope I make sense with this sentence).

I found a way around this, by using 'Conditions', 'if yes' gets an 'update item (only fill in the columns that are part of number 2 in round 2, etc)' and I leave the 'If no' path empty. This way (when it doesn't meat the condition) it does nothing and it doesn't overwrite the value that is already in the column.

Now I don't need the 'initialize variable, type array' anymore.

I still think there might be a cleaner way to do this (by putting in better/more effective expressions)? But on the other hand: the way I did it might be more simple to understand in case somebody else looks at my flow 😉

Thank you so much for your help!

Was this reply helpful? Yes No
Verified answer

Femke1 348 on at

Like (1)

Report

Re: How to use information from 'Get user profile (V2)' that is in an Apply to each loop (and write it to different columns in SP List)?

I'll wrap it up, to make my solution clear. Maybe it helps others that are looking for a solution.

So first, initialize a variable, type integer and give it a value (in my case 1, but you can choose 0 if you want to start your Apply to each loop with the value 1):

Then, after the 'start and wait for an approval', get the user profile of each 'approver', increment the value of the ColumnIndex by 1 and use conditions for each value of the ColumnIndex:

In the update item action, only fill in the columns corresponding to the ColumnIndex number:

So, for condition 2 (ColumnIndex = 2) I only fill in the columns for person 2 (yellow highlight), for condition 3 (ColumnIndex = 3) I only fill in the columns for person 3 (blue highlight), for condition 4 (ColumnIndex = 4) I only fill in the columns for person 4 (green highlight), etcetera.

Thanks again @abm for your help!

Was this reply helpful? Yes No