Unanswered

Commas in number fields

(1) Share

Report

Posted on by Community Power Pla...

I read data in from a sharepoint list, and use that data to make an html table, which then sends an automated email. My issue is the read in numeric data from sharepoint no longer has commas in it. So 10,000 becomes 10000. I took a look at this link: https://powerusers.microsoft.com/t5/Using-Flows/Format-number-with-thousands-separator-in-Flow-email/td-p/176369. Which works for a single value, only I am using an array of values, and if I do an apply for each loop, then I end up with an array output that won't properly translate into my html table. Is there another way to translate my output in a way that can be used by the create html action? Or is there a better way to get commas in altogether? Thanks.

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (0)

v-yamao-msft on at

Like (2)

Report
Hi @Anonymous ,

Do you want to format the numeric data of all the items got from the list?

Please check the following configuration for a reference.

After action Get items, add action Create HTML table.

And insert the following code for total column:

Concat( substring(string(item()?['OTHours']),0,sub(length(string(item()?['OTHours'])),3)), ',', substring(string(item()?['OTHours']),sub(length(string(item()?['OTHours'])),3),3))

Best regards,

Mabel

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

That works perfect for whole numbers thank you! I don't suppose there's anything I can do about decimals? The problem is sometimes the data has decimals, and sometimes it doesn't.

Was this reply helpful? Yes No
v-yamao-msft on at

Like (0)

Report
Hi @Anonymous ,

Do you also want to apply to data which has decimals?

Please try with the following code:

if(contains(string(item()?['OTHours']),'.'), concat(Concat( substring(first(split(string(item()?['OTHours']),'.')),0,sub(length(first(split(string(item()?['OTHours']),'.'))),3)), ',', substring(first(split(string(item()?['OTHours']),'.')),sub(length(first(split(string(item()?['OTHours']),'.'))),3),3)) ,'.', Concat( substring(last(split(string(item()?['OTHours']),'.')),0,sub(length(last(split(string(item()?['OTHours']),'.'))),3)), ',', substring(last(split(string(item()?['OTHours']),'.')),sub(length(last(split(string(item()?['OTHours']),'.'))),3),3)) ), concat(Concat( substring(first(split(string(item()?['OTHours']),'.')),0,sub(length(first(split(string(item()?['OTHours']),'.'))),3)), ',', substring(first(split(string(item()?['OTHours']),'.')),sub(length(first(split(string(item()?['OTHours']),'.'))),3),3))) )

Best regards,

Mabel

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

I can't get that to work, I've thrown it in and redone it multiple times. Could this be becuase the decimal values I'm reading in are sometimes to the tenth or the hundreth place? And sometimes the values are whole numbers. Also, some of the values read in are null or 0, but I've put if statements in to check for that, and still getting the same issue, it reads like this

InvalidTemplate. The execution of template action 'Create_HTML_table_2' failed. The column values could not be evaluated: 'The template language function 'substring' parameters are out of range: 'start index' and 'length' must be non-negative integers and their sum must be no larger than the length of the string. Please see https://aka.ms/logicexpressions#substring for usage details.'.

Something tells me it's because your code is taking in only 3 digit decimals? I'm not sure though, and I admit it could be an issue with the way I am implementing it.

Was this reply helpful? Yes No