cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Power Automate - Form to Excel to Email using vlookup to collect fields

I'm fairly new to Power Automate.

I am collecting a registration through forms and using a flow to move the data into Excel.  I am collecting name, email address, and date of training.  I am transferring this information in a flow to Excel.  From there, there are two vlookups to bring in the time and URL of the training.  I then want to email the individual the date, time, and URL of the training.  Everything works except the two vlookup fields going into the email - they come in blank.  I even tried to put in a 30 second delay between the data entry into the Excel file and the email.  The email is based off of the Excel file fields, not the form. 

Any ideas?  Or is there a better way to match up the fields to send confirmation emails?  I'm not married to using Excel.

1 ACCEPTED SOLUTION

Accepted Solutions
Responsive Resident
Responsive Resident

Another way of explaining the Key/Value in the Get row action is that it will search your table, and return the first row it encounters where the Key (the column) matches the value that you supply.

 

So if you know the timestamp of the row you want to return, select that column in your "Get row" action as the Key, and put the timestamp in the Value.

View solution in original post

7 REPLIES 7
Responsive Resident
Responsive Resident

Can you post your flow?

 

This might or might not have anything to do with your situation, but your Excel data will not refresh once you have fetched it - so if you fetch the data at the beginning of your flow and then send an email at the end, that data will not have refreshed.  Even if it has changed in the meantime, even if you have built in a delay.  You'll have to fetch it again.

New Member

Here it is.  Is there a better way to post it?  Again, I'm new to Power Automate.  Thank you!

jamieannemiller_0-1610469939355.png

 

Responsive Resident
Responsive Resident

Yes, the issue is that Excel is adding the dates in the vlookup fields AFTER your "Add a row into a table" action.  In order to include those values in an email, you need to retrieve fresh data.

 

Use a "Get a row" action to get data from the row you just entered.  You can add this action after your "Add a row into a table" action (I don't think you'll need the Delay).  Then make sure your email is using data from the "Get a row" action, not the "Add a row" action.

Thank you.  Does Key Column and Key Value determine which row I choose?  If so, I want the latest row so I can send an email confirmation based on that row.  How can I determine that or can this not be done this way?

Yes, Key Column and Key Value determine the row that is returned.  Do you have a column in your table that will contain a unique value (like an ID column or a timestamp)?  If so, use that.  If not, you'll need to return all of the rows instead of just one and then use the last() expression to access the last row.  That's a bit more complex.

New Member

I do have a timestamp.  How could I use the last row entered based on time? 

Responsive Resident
Responsive Resident

Another way of explaining the Key/Value in the Get row action is that it will search your table, and return the first row it encounters where the Key (the column) matches the value that you supply.

 

So if you know the timestamp of the row you want to return, select that column in your "Get row" action as the Key, and put the timestamp in the Value.

View solution in original post

Helpful resources

Announcements
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Microsoft Ignite

Microsoft Power Platform: 2021 Release Wave 1 Plan

Power Platform release plan for the 2021 release wave 1 describes all new features releasing from April through September 2021.

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (12,669)