How to format list item number as currency?

[Rbovenkamp]

I want to transform the listitems Stukprijs en AanschafTotaal to a currency number for example € 12,98

I thought this can be done by placing the fields Stukprijs a formatnumber expression, but I cannot select Stukprijs to the formatnumber expression.

Any ideas? 

[efialttes]

Hi!

There is a new action block to simplify format currency...

https://flow.microsoft.com/es-es/blog/simplified-number-formatting/

... but your flow is based on a 'Get items' output right? Not 100% sure, anyway, your topic should be a good reason for me to start learning Dutch 😅

If my assumption is right, and this new action block is a valid workaround, then you will probably need to use an Apply to Each to  iterate all over the items. Or, use a expression based on its equivalent WDL function in your Select mapping table

Hope this helps

[Pstork1 · Accepted solution]

You should be able to use something like this

formatNumber(variables('TargetNumber'),'$#.00')

 Just substitute your dynamic content and the appropriate currency symbol

[Rbovenkamp]

Unfortunately 

[Rbovenkamp]

Yes it's based on a get items output. But isn't there a way to transform the data in the select block directly with an expression? 

[efialttes]

Oops!

The error you just shared is because of using a variable in your expression that does not exist called 'Stukprijs'.

Following @Pstork1 recommendation, I would use instead

formatNumber(item('Stukprijs'),'$#.00')

If this also fails, please hover the mouse over the first SP dynamic content you assigned at the beginning to your Select 'price' column, and share the screenshot so we can identify the Stukprijs internal name in your Sharepoint list ... since sometimes the SP column internal name does not match with the one shown by the Flow User Interface

Thanx!

[Rbovenkamp]

Thanks Efialttes,

 

[efialttes]

Hi!

Following @Pstork1 recommendation, I would replace currentassigned value

item()?['Stukprijs']

and use this expression instead

formatNumber(item()['Stukprijs'],'$#.00')

Hope this helps

[Rbovenkamp]

[efialttes · Accepted solution]

Hi again
There is a typo on the expression
formatNumber(item()?['Stukprijs'],'$#.00')
TRy now

[Rbovenkamp]

Thank you very much! That worked!

[efialttes]

@Rbovenkamp 

I've seen you marked this topic as solved. Thanks for your kindness!

This way other community members facing this same problem in the future have the chance to find a solution faster.

Please note you can mark more than one answer as Solution, even if they come from different users. Why I am mentioning this?

Cause in my opinion @Pstork1  deserves most of the credit for the solution, since 95% of the design comes from him. I just suggested how to adapt it to your current implementation

Thanx for making this community so great!

[Rbovenkamp]

You're right @Pstork1 is also mentioned as accepted solution.

Now I've got another question. I would like to do the same trick for column totals. But it says that this column is retrieved as string instead as float. The column in Sharepoint is a calculated column, which multiplies the amount with the price per item. The column is added as a number column with 2 decimals. How is it possible that this data is retrieved as string in power automate? And how to overcome this? I tried to convert the string data with float(). Unfortunately without result. 

[Rbovenkamp]

And... @Pstork1 really appreciate your solution. Can you give met some clues about the syntax? I try to understand it, but don't understand this part: 

item()?['Stukprijs'] 

in 

formatNumber(item()?['Stukprijs'],'$#.00')

🙄

[efialttes]

@Rbovenkamp 

Select iterates through all elements from you input array. Since you assigned 'Get items' output value as Select's input, you are iterating through all items from your Sharepoint List, each SP List item represented as an element of the input array.

item() represents current element under evaluation

item()?['Stukprijs'] represents current element Stukprijs column value

Select output is also an array.

You can also use item() inside 'Apply to each' and 'Filter array' function blocks

Hope this makes sense

[Rbovenkamp]

Nice thank you very much, with your hints I came with this solution to transform the string format into a float:

formatNumber(float(item()?['Totaal']),'$#.00')

So I understand it correct item()?['Totaal']      item() is the function which puts the column data with a for each. The ['Totaal'] is the coumn selector. But what is the ? and is it possible to give the item() function extra option in between the ()?

[efialttes]

Hi again

IN this case the element represented by item() is of type object.

Objects have properties.

And one way in WDL language (which is the programming language behind the graphic editor) to refer to an object's property is by means of the ? operator.

You will understand better if you implement the following dummy logic after SElect action block: add 'Apply to each' action block just after your Select action block, assign Select output as its input. Inside Apply to each:

1.- add a dummy Compose action block, assign it as input the following expression:

item()

2.- add a second dummy Compose action block, assign it as input the following expression:

item()?['Totaal']

REexecute the flow and inspect content displayed inside 'Apply to each'

In my example 'Apply to each' performed 92 iterations (one per element from the input array), the browser is currently displaying Dummy Compose outputs from iteration#1, you can click 'Next' to see the particular Dummy compose outputs in iteration #2... and so and so.

Please note Dummy Compose#1 will show... an object! representing the current iteration element from Select array.

Dummy Compose#2 shows value of requested property, also from current iteration element from Select array.

If you enjoyed the explanations from my last two replies, you can click on 'Thumbs up' in the one you prefer most, or even in both of them 😉.

Hope this makes sense