Struggling to get excel formula cell into email body using MS Flow

[Anonymous]

Hi,

I'm trying to build out a form which then creates a row (or multiple rows) in excel and then sends out an email containing key elements from the form. 

One of the cells in excel "Total Revenue" multiplies two of the values that are submitted on the form. The intention is for that value to be a part of the body of the email.

The issue i'm having is that each time I run the flow, that value always comes out blank in the email and i'm not sure why. I need to build out the complexity of the excel sheet to include additional formulas which i'd then want to include in the body of the email, but need to figure out first how to include any cell which has a formula into the body of the email! Any help would be appreciated!

[edgonzales]

@Anonymous 

Hey there.  Before I dig too deep into this, have you already tried wrapping your dynamic values from the form with the functions Float()?

I'm wondering if it's getting cranky because it doesn't recognize it as a number.  Can you post what the previous runs look like (the output data from that and the previous step)?

Thanks,

-Ed-

[Anonymous]

Thanks for the reply @edgonzales 

So, I did try float and it's encompassed in the multiply expression that's in the body of the email (and it does pull through the value) but I don't think it will work for what i'm trying to achieve. I have a condition on the form that allows for more than one row to be created in Excel if the user answers yes for a question. (Currently upto 3 rows can be created, if I build it out correctly, it would be upto 6 rows).

Essentially, I need the "Total Revenue" to be in the body of the email from each row thats created. So in the body you can see i'm pulling "Hedge Date?" and two more below it. The 2nd and 3rd hedge dates are only populated if the user answers yes to a prior question, otherwise only hedge date is - and a row is created for each relevant hedge date. I'd need the Total Revenue value thats created in excel from each row.

I've attached a screen of the excel rows that gets pulled - in this instance I filled in 3 'hedge dates', pulled 3 rows from excel, and would need the 3 values that are pulled in the "Total Revenue" column. With float being used as it is in the multiply bit in the email body, it only pulls one value of 1500, whereas i'd need that 3 times. As you can see in the email attachment, there's just a blank space where i'd expect the excel value to pull.

Hope that makes sense!

Excel tableEmail body

So, I did try float and it's encompassed in the multiply expression that's in the body of the email (and it does pull through the value) but I don't think it will work for what i'm trying to achieve. I have a condition on the form that allows for more than one row to be created in Excel if the user answers yes for a question. (Currently upto 3 rows can be created, if I build it out correctly, it would be upto 6 rows).

Essentially, I need the "Total Revenue" to be in the body of the email from each row thats created. So in the body you can see i'm pulling "Hedge Date?" and two more below it. The 2nd and 3rd hedge dates are only populated if the user answers yes to a prior question, otherwise only hedge date is - and a row is created for each relevant hedge date. I'd need the Total Revenue value thats created in excel from each row.

I've attached a screen of the excel rows that gets pulled - in this instance I filled in 3 'hedge dates', pulled 3 rows from excel, and would need the 3 values that are pulled in the "Total Revenue" column. With float being used as it is in the multiply bit in the email body, it only pulls one value of 1500, whereas i'd need that 3 times. As you can see in the email attachment, there's just a blank space where i'd expect the excel value to pull.

Hope that makes sense!

[Anonymous]

Added some output images too, hopefully they were what you meant? (sorry - first flow i've attempted to build!) 

[edgonzales]

@Anonymous 

In your Send eMail step in the Flow, it looks like it is pulling the Hedge Date values from the Form and not the spreadsheet, and there is no dynamic value for the corresponding data.  You are very close, we just need to clarify what it is you want to present, right?  And then compose the eMail elements in the right way.

Since that portion of your email (Hedge Dates and values) could be multiple rows, maybe we add an action step: List Rows Present in a Table.  And filter on dynamic dates somehow.  The weird part is going to be if they want multiple dates (hard to automate something that sometimes happens).  Another option might be to try a Select or something to only grab the info you want, and then work off of that.

Another solution might be to store the data in an Array (not as hard as it sounds) as you're adding the rows to Excel, and then just presenting the Array data in the email (this would limit the email to just the stuff entered on the Form).

Point is, we have options.  Which direction you go really depends on your comfort level and the scope of the project.  

We'll get through this 🙂

-Ed-

If you liked this reply, please give it a thumbs up! 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]

Thanks again @edgonzales.

So the ultimate aim of the form is for someone who requires a currency hedge to be able to input the date they want the hedge to be executed (i.e. a date in the future) and the option of selecting multiple future dates. The hedge value will be based on a trade price and trade volume which is submitted via the form, and then the actual hedge value is calculated in excel. It needs to be calculated in excel because I need to add extra layers to the current basic "trade price x trade volume" formula - which is why I can't just use the float function.

The hedge value will be different each month because of the number of days in the month etc, so this needs to be factored in. (I will need to essentially create a formula in excel which takes the month value from the hedge date, minus one month and then multiply the number of days in that month to the "trade price x trade volume" - e.g. hedge date is 20th Nov, therefore need trade price x trade volume x no of days in October, if that makes sense!)

Certain values from the form are fine to present in the email body, as they provide an instruction to the recipient to action. So the goal is to have the hedge date, with the total revenue next to it. If there are multiple hedge dates, then each one has the corresponding total revenue alongside it.

With regards to the List Rows Present in a table - I think I added this step after the first add a row into a table step, hoping that it would help recognise the 'total revenue' cell in the email body but it didn't seem to work, so I didn't bother adding it again after the table 2 and table 3 conditions!

How would sorting the data into an Array work?

Thanks again for the help! 

[Anonymous]

Hi @edgonzales  - would you have any further thoughts on this?

Thanks!

[edgonzales]

@Anonymous 

Hi there. I'm not great with building Arrays, and there may be a more creative solution.  Tagging @yashag2255 to see if he has some ideas.

Thanks!

-Ed-

[Anonymous]

Hello @yashag2255 - is my issue something that you would be able to help with?

Thanks!

[yashag2255]

Hey @Anonymous 

I can see that you are adding an apply to each loop that is iterating over each row retrieved from Excel and so, you might not get all the rows with the Total Revenue values at the same place. 

I would suggest: 

Move the email action outside of the apply to each loop (as most responses are from the Forms only). Initialize a variable of type String before the Apply to each action and inside of the loop, add an append to string variable action and select the Total revenue for value here. Later on, use that string variable outside of the apply to each action. What is the multiply expression that you are using? can you share that expression? 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[v-alzhan-msft · Accepted solution]

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!

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.

[nihalwani_raj]

i am facing the same issue but i don't think solution is in the current reply. please help if u got the solution

[Anonymous]

Hi Raj,

Unfortunately not. For the time being i've had to abandon the form & flow that I had built as it was not fit for purpose