cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RTW
Level: Powered On

Expression Troubleshooting - Displaying a number as Currency or Decimal

Hello everyone! I'm having a bit of trouble with an Flow I am working on. The idea is that our company's sales metrics are uploaded to a SharePoint list, along with news and so on, which is then sent out into Teams as an Adaptive Card. I have everything working out except getting the numbers to display as currency instead of a number. I have it set up so that the numbers that are fed in are whole numbers, even though as far as I can tell there is no way to set the SharePoint to integer instead of float.

 

Also, as a note, the sales figured entered ranges in from the thousands to the million. I do not expect to see numbers under 1,000 and will have bigger things than this Flow to worry about if I do. I also don't expect to see numbers in excess of (or even approaching 999,999,999. If I did I'd probably be able to retire off my profit sharing!

 

Anyway, this is the equation that is giving me trouble.

if(greater(length(string(triggerBody()?['Money'])),6),
concat('$',substring(string(triggerBody()?['Money']),0,add(length(string(triggerBody()?['Money'])),-6)),',',substring(string(triggerBody()?['Money']),add(length(string(triggerBody()?['Money'])),-6),3),',',substring(string(triggerBody()?['Money']),add(length(string(triggerBody()?['Money'])),-3),3)),
concat('$',substring(string(triggerbody()?['Money']),0,add(length(string(triggerbody()?['Money'])),-3)),',',substring(string(triggerbody()?['Money']),add(length(string(triggerbody()?['Money'])),-3),3))
)

To make it a bit easier to parse, I'll explain what the each section does.

 

The first determines how to split up the number. It determines the length. If it is greater than 6 digits long, it sends it to the first expression to split it up and format as a million dollar value, otherwise it sends it to the thousand dollar values.

 

The second uses a series of substring expressions to split the equation into three sections, add comma separators and a dollar sign. It would turn 123456789 into $123,456,789.

 

The third equation does the same thing, only for shorter strings. It turns 123456 into $123,456

 

Now, the way it fails seem to be only affecting strings that are 4 characters lon. I get the following error message.

'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.'.

These are the steps I have done to rectify the issues.

1) I have tested the expression with strings that range from 9 to 4 characters long, which is the range I will be feeding into this equation. Only 4 character strings run into any issues

2) I then fed 6, 4 and 5 character strings through only the last part of the equation and got the expected result.

3) I created an simple equation

if(greater(length(string(triggerBody()?['Money'])),6),'Mil','Thou')

This retunred the expected result, 4 digit strings returning "Thou"

4) I ran a 4 digit string through the Millions part of the original equation, and got the same error message.

 

My inital thought that, for whatever reason, TRUE statement instead of the FALSE statement, so I switched them and it still didn't work.

 

Honestly I'm at my wits end and its the last thing before I can wrap up this project. Any idea whats happening or what I'm doing wrong?

 

Thank you in advance!

 

**Minor redits for clarity**

 

1 ACCEPTED SOLUTION

Accepted Solutions
SamPo
Level 8

Re: Expression Troubleshooting - Displaying a number as Currency or Decimal

@RTW Hi,

 

I've tried to recreate your scenario and seem to be having a similar issue.

I've solved it like this:

Screenshot 2019-04-12 at 20.42.25.png

 

First I wrapped the 'Money' in 'string' to make the formulas shorter then used a condition for their length.

 

The formulas I used for each are as follows:

 

Millions:

concat
('$',
substring(outputs('Money'),0,sub(length(outputs('Money')),6)),
',',
substring(outputs('Money'),sub(length(outputs('Money')),6),3),
',',
substring(outputs('Money'),sub(length(outputs('Money')),3),3))

Thousands:

concat
('$',
substring(outputs('Money'),0,sub(length(outputs('Money')),3)),
',',
substring(outputs('Money'),sub(length(outputs('Money')),3),3))

Hope this helps,

Sam

5 REPLIES 5
SamPo
Level 8

Re: Expression Troubleshooting - Displaying a number as Currency or Decimal

@RTW Hi,

 

I've tried to recreate your scenario and seem to be having a similar issue.

I've solved it like this:

Screenshot 2019-04-12 at 20.42.25.png

 

First I wrapped the 'Money' in 'string' to make the formulas shorter then used a condition for their length.

 

The formulas I used for each are as follows:

 

Millions:

concat
('$',
substring(outputs('Money'),0,sub(length(outputs('Money')),6)),
',',
substring(outputs('Money'),sub(length(outputs('Money')),6),3),
',',
substring(outputs('Money'),sub(length(outputs('Money')),3),3))

Thousands:

concat
('$',
substring(outputs('Money'),0,sub(length(outputs('Money')),3)),
',',
substring(outputs('Money'),sub(length(outputs('Money')),3),3))

Hope this helps,

Sam

RTW
Level: Powered On

Re: Expression Troubleshooting - Displaying a number as Currency or Decimal

@SamPo Yes, that's helpful in that it works, but I was hoping to be able to get it done in just an expression. I have quite a few feilds to update, and it would be a lot easier if it was just changing one line.

 

Do you have any reason what is causing this error? Is there something wrong with the expressions or is this some sort of glitch or limitation?

SamPo
Level 8

Re: Expression Troubleshooting - Displaying a number as Currency or Decimal

@RTW I've tried a number of different tests and it seems like it's always falling on the first half of the expression.

Seems to me like a bug.

 

It's a bit far-fetched by the cleanest solution I can think of is as follows:

Create a separate flow and call it 'Currency Formatting'. Set up the new flow with the same condition as above but with an HTTP trigger to receive the 'money' input and a response action with the result.

 

Then whenever you need to convert a currency amount you can call this flow via HTTP and the 'body' response will be your result. 

 

It's a bit of extra work but it will give you the ability to do this conversion whenever you need only using one action in all your other flows.

RTW
Level: Powered On

Re: Expression Troubleshooting - Displaying a number as Currency or Decimal

@SamPo 

 

Thanks for the investigation. Its not as elegant as I would like, but it works well enough for now. I might try that HTTP request idea later down the road, as I do know that I'm going to have to move more of our manual reports into this system. Might save some time in the long run.

Highlighted
Kenneth
Level: Powered On

Re: Expression Troubleshooting - Displaying a number as Currency or Decimal


@RTW wrote:

@SamPo 

 

Thanks for the investigation. Its not as elegant as I would like, but it works well enough for now. I might try that HTTP request idea later down the road, as I do know that I'm going to have to move more of our manual reports into this system. Might save some time in the long run.



I hope this helps you guys out, I manage to make it work, the problem is that the evaluation of the should be made in cascading order, and always evaluating if it less than the other, Iyou try to evaluate first if it is less and then if it is greater, and you second if true value if for the greater evaluation it will fail.  It's kind of tricky to catch and explain, but here you go.  I used your logic and Sampo's logic to create it.  Thanks for the challenge I was looking for these results too. 

if(less(length(variables('Money')),4),
concat('$',variables('Money'))
,
if(less(length(variables('Money')),7)
,
concat
('$',
substring(variables('Money'),0,sub(length(variables('Money')),3)),
',',
substring(variables('Money'),sub(length(variables('Money')),3),3))
,
concat
('$',
substring(variables('Money'),0,sub(length(variables('Money')),6)),
',',
substring(variables('Money'),sub(length(variables('Money')),6),3),
',',
substring(variables('Money'),sub(length(variables('Money')),3),3))
)
)

image.pngMIllionsimage.pngDollarimage.pngThousandsimage.pngHundreds

>