Identify Microsoft-Username in Excel after submitting Forms

[Anonymous]

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.

I deleted other functions to solve the problem with the remaining daysThe Excel-File with names and their vacation days

[edgonzales]

@Anonymous 

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.

[Anonymous]

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!

[edgonzales]

@Anonymous 

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-

[edgonzales · Accepted solution]

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:

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.

[v-alzhan-msft]

Hi @Anonymous ,

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.

[Anonymous]

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.

[edgonzales]

@Anonymous 

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.

[Anonymous]

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!

[Anonymous]

@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?

[edgonzales]

@Anonymous 

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!  🙂

-Ed-