Format Number bizare behaviour when output in email

[GuyBoswell]

Dear All,

I am seeing very bizare behaviour when I try to use formatted numbers in an email.

My Power Automate flow is triggered by change or addition to a SharePoint list. The flow reads values from the list, does a bunch of arithmetic and outputs the results to an email. For example I calculate a total monthly income as the sum of monthly Gift Aided income and monthly not Gift Aided income as varMonthlyNewTotal = add(colMonthlyNewGA,colMonthlyNewNotGA). I then calculate annual income as varYearlyNewTotal = mul(varMonthlyNewTotal,12).

This all works fine and delivers the correct values in an email to my inbox. However the numbers are mostly currency and they are not formatted as such. So I added a list of Format Number steps.

In outline my flow is:
SharePoint list modified
Initialize variables (17 steps)
Condition statement - proceed if colReport = true
Do the arithmatic as a series of Set Variables (16 steps)
Format the result of the variables and some other numbers read straight from the list (15 steps)
Condition statement - check if this is real data or a test and set the recipient email and the email subject appropriately
Send an email (V2)

If I include the numbers in the emails as the raw variables and column values it runs OK but delivers unformated numbers
If I also add in the formatted numbers it runs OK but obviously looks odd. For example a line in the email "Annual total: varYearlyNewTotal Formatted Number(varYearlyNewTotal)", returns somethng like, "Annual total: 9876.123 £9,876.12"

This is where it starts to get strange!
If I now delete varAnnualTotal from the email (just from the email, the initialise and set steps still exist exactly the same outside the email) the email step fails. I can see in the run history that all the variables are still calculating correctly and all the Format Number steps are still formatting correctly but there is an error in the email.

This is where it gets really weird!
If I put the variable back in to the email, so the whole flow looks to be exactly the same as before, the email step still fails to run. In fact I have to delete all of the formated numbers to coax the email to run again. I can then add them back and it still runs OK. But still not giving what I want because it still includes the variables and the formated number. And as soon as I delete any of the variables the email stops working.

This is where it gets really, really bizare.

The error message relates to a totally different formated variable than the one I deleted. For example I deleted varYearlyNewTotal but the error message refers to varPercentageNewPartners. I can see in the run history that varPercentageNewPartners still calculated and formatted OK (Input 0.825 - format P2 - output 82.50%)

The whole error message is...
Unable to process template language expressions in action 'Send_an_email_(V2)' inputs at line '1' and column '2923': 'The template language expression 'body('Format_varPercentageNewPartners')?['body']' cannot be evaluated because property 'body' cannot be selected. Property selection is not supported on values of type 'String'. Please see https://aka.ms/logicexpressions for usage details.'

Can anybody suggest why this is going wrong? I am going to try solving it by building the email step again from scratch but even if that solves it, it doesn't explain it and I would like to know what is happening.

Thank you very much for all your wonderful help,

Guy

[v-litu-msft]

Hi @GuyBoswell,

This is a potential bug that appears when editing dynamic content and variables in Email body. As a workaround, you can put these contents in a compose action, to edit and adjust, and then put its output into this Send Email body.

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

[GuyBoswell]

Thank you @v-litu-msft but no, it fails on the compose step with the same error message except the column number changed to 2914

It also means I can't format the text as I want (bold, underline, font, etc)

Guy

[GuyBoswell · Accepted solution]

I have fixed this for now by replacing all the Format Number steps with Compose steps containing FormatNumber() functions.

All works fine now.

Format Number generates the correctly formated numbers but you can't use them - not much use then!!!