cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
breater
Level: Powered On

Identify Microsoft-Username in Excel after submitting Forms

Hello to all,

 

i'm working on a Holiday-Flow, where you can request you prefered Days in Microsoft Forms. To make it easier for the supervisor, i would like to send the Request with the Name of the worker(Microsoft-Username), the prefered date, a comment and the remaining days of vacation. These days are saved in an Excel-File in One-Drive.

 

I tried many things and all things are working but i can't realise the remaining days of vacation. I used the function "Get Rows", but then he request X-row times because he applies this to all rows.

 

I'm not sure that i can solve this in Excel. I hope some of you can give me some advises or maybe some alternatives to solve my problem.

 

 

urlaubfflow.JPGI deleted other functions to solve the problem with the remaining daysurlaubflow.JPGThe Excel-File with names and their vacation days

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Identify Microsoft-Username in Excel after submitting Forms

Ok, here's what I did...

Rather than getting all of the rows, I'll focus on just the row that has the user I want.  In my example, I just entered an email in a text box, then used that to get some info from O365 profiles, then got the data in Excel, then sent an email with that information:20190821a.PNG

 

Does that help get you closer?  Let me know where I'm off and we'll adjust.

 

Thanks!

-Ed-

 

 

If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

10 REPLIES 10
Super User
Super User

Re: Identify Microsoft-Username in Excel after submitting Forms

@breater 

Hi there!   I think there are a few different options (or more) depending on your comfort level:

  1. Add in an OData filter on your "List all existing lines" step to only get the line with your intended user.  The syntax will be something like Name eq <<dynamic value for the person's name>> (without the brackets)
  2. Make sure the "Send email" bit is only on the Yes part of your condition path.  If there's not a match on the name, it shouldn't do anything (You may already have this).
  3. I think it's the "Get line" action that's triggering the extraneous emails, right?  Try moving that inside the "Apply to Each" loop, just before your "append to variable" step.

Hope that helps.  Keep us posted.

 

-Ed-

 

If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

breater
Level: Powered On

Re: Identify Microsoft-Username in Excel after submitting Forms

Hello Ed,

 

I'm new with the flows so i really don't know how to use OData.

 

But in the end I want to find out the remaining vacation days and not find the requester himself in the Excel. I thought finding the requesters name in the Excel could help me find out his remaining vacation days but when i have found the name in the Excel-File i don't know how to find out the Number of free days in this row.

 

But thank you for your help Ed!

Super User
Super User

Re: Identify Microsoft-Username in Excel after submitting Forms

@breater 

No worries at all.  It helps to know your comfort level so I don't make things too detailed or not detailed enough.  What you want to do is totally possible, so don't lose hope.  I'll build something quick in a bit and we'll figure this out.  It's a great idea and should be totally doable.  

 

-Ed-

Super User
Super User

Re: Identify Microsoft-Username in Excel after submitting Forms

Ok, here's what I did...

Rather than getting all of the rows, I'll focus on just the row that has the user I want.  In my example, I just entered an email in a text box, then used that to get some info from O365 profiles, then got the data in Excel, then sent an email with that information:20190821a.PNG

 

Does that help get you closer?  Let me know where I'm off and we'll adjust.

 

Thanks!

-Ed-

 

 

If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

Community Support Team
Community Support Team

Re: Identify Microsoft-Username in Excel after submitting Forms

Hi @breater ,

 

Have your problem been solved?  If yes, please go ahead and mark the post as solved by clicking “Accept as Solution” so that this thread will be marked for other users to easily identify! If not, please share more details so we could better undertand your issue and provide a proper workaround for you.

 

Best regards,

Alice       

 

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

breater
Level: Powered On

Re: Identify Microsoft-Username in Excel after submitting Forms

Hello Ed,

 

thank you for your help & time, this works fine!

 

In the next step, there is an approval and when this approval get accepted by the supervisor I want to update the remaining days in the Excel-File. Now I'm not sure how to solve this and find the right row to update. In the function "Update Rows" I'm not sure what to type in. The next problem is, i need to subtract the dates to get the lenght of the vacation, but they are saved as "String-Variable".

 

I just want to say thank you to you Ed for your help to this point, maybe you have some fast solution, but I don't want to waste your time.

Super User
Super User

Re: Identify Microsoft-Username in Excel after submitting Forms

@breater 

No trouble at all, I'm here to help where I can.

So the whole Key Column, Key Value thing:  Basically, in order for Flow to know which row to update, it needs to know how to find a unique identifier for that row.  In our example, we're using the username/email, right?  So the Key Column would be that, and then the Key Value would still be the same dynamic value we used for the Get Row step.

 

This way, Flow looks at the username column until it finds a match from the previous step and then updates whatever column you tell it to in the subsequent area.  

 

Things will get weird if you have multiple rows for a user, so pay attention to that if you have any troubles.  If that happens, then we just create a new column that concatenates a time or something to the username to make it unique.

 

Keep at it, you're getting close and building something awesome!

 

-Ed-

 

 

If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

breater
Level: Powered On

Re: Identify Microsoft-Username in Excel after submitting Forms

Okay I'll take your answer above as solution. Tomorrow I'll try to realise the remaining function but if I get some questions I update the thread.

 

Thank you Ed!

breater
Level: Powered On

Re: Identify Microsoft-Username in Excel after submitting Forms

@edgonzales 

 

It's me again. It's working to count the days between the vacation start and end. But there is another problem:

 

When I edit in the Excel-File above the "Days Taken" for the requester (for example 5 days) with the flow and the same worker request another vacation (for example 3 days), the second request overwrites the first request. And then there are only 3 days at the "Days Taken" field and not 8. Is there any possibilty that the requests don't override each other?

Super User
Super User

Re: Identify Microsoft-Username in Excel after submitting Forms

@breater 

No problem.  I'd suggest starting a new topic for this one if it goes much further to help other users facing the same issue.

What you're describing is really more of a "Process" thing, than a "System" thing, right?  We can make Flow do the things you want to do, but it sounds like you might want to consider having the spreadsheet updated after the supervisor approves the time off.  Then, whichever one is approved first will be decremented from the total.

 

Another consideration (a little more complex) is to add a "Pending Approval" column.  Here, you would have your "8" (from your example), and then when the "3" was approved, the days remaining would be reduced, and the "Pending" would reflect the remaining "5".

 

Sometimes, it helps to map out the complete journey to help account for everything...but you are definitely on the right path and everything you want to do is possible here.  Keep at it!  Smiley Happy

 

-Ed-

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 72 members 4,357 guests
Please welcome our newest community members: