Unanswered

How to format number fields to currency, but then also Sum them?

(0) Share

Report

Posted on by DanavanCampen

Hi all!

I have some calculated number fields in a Gallery that i'd like to be formatted to currency. This works fine, however as it converts the number to Text, then I can't seem to sum those fields to create a Total Field. The only work around I can currently find is to duplicate the fields and display one with Text formatting, and Sum the other field that I will hide. This seems a bit silly, as surely there is a better way!

My calculated field is called 'Total Sell by Area calc' and the Gallery is called 'AreaSummaryGal'.

The calculated fields listed in the gallery return values of eg: 4485.888555. This is messy, so i used the Text (Value,"[$-en-US]$#.##"). However, as this converts it to a text, my Total field doesn't show anything!

The formula i used for the Total field is:
Text(Sum(AreaSummaryGal.AllItems,'Total Sell by Area calc'),"[$-en-US]$#.##").

This Sum only works when i take the formatting off the 'Total Sell by Area calc' field.

Any ideas?

I have the same question (0)

All responses (9)

Answers (0)

WarrenBelz 151,881 Most Valuable Professional on at

Like (0)

Report

Re: How to format number fields to currency, but then also Sum them?

Hi @DanaBananaNZ ,
You should be able to reverse the process using Value( text field).

Please click and accept as Solution if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a Thumbs Up.

Was this reply helpful? Yes No
DanavanCampen on at

Like (0)

Report

Re: How to format number fields to currency, but then also Sum them?

It is already a value, so I'm not sure how this would format my field?

Was this reply helpful? Yes No
PowerAddict 7,314 Most Valuable Professional on at

Like (1)

Report

Re: How to format number fields to currency, but then also Sum them?

Are you using this just to display your calculated field?
Text (Value,"[$-en-US]$#.##")

Then are you using this calculated field to get the sum using this formula?
Text(Sum(AreaSummaryGal.AllItems,'Total Sell by Area calc'),"[$-en-US]$#.##")

Also, are you saying when you keep the calculated field to Value, the sum doesn't work?

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

Thanks!
Hardit Bhatia
The Power Addict
https://thepoweraddict.com

Was this reply helpful? Yes No
DanavanCampen on at

Like (0)

Report

Re: How to format number fields to currency, but then also Sum them?

Thanks for your reply! In answer to your q's:

I am using the Text format of what I put in the posted in my first post.. not sure why i can't see it now. It looks like you have copied it correctly though..

When i format the calculated field to Text (ie to make it look tidy and give it a $ symbol), the sum doesn't work. If i take off the formatting completely (ie and leave it as the original format which is a number), the Sum does work!

Was this reply helpful? Yes No
PowerAddict 7,314 Most Valuable Professional on at

Like (1)

Report

Re: How to format number fields to currency, but then also Sum them?

Have you tried this?

Text(Sum(AreaSummaryGal.AllItems,Value('Total Sell by Area calc')),"[$-en-US]$#.##")

Let me know if this helps.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

Thanks!
Hardit Bhatia
The Power Addict
https://thepoweraddict.com

Was this reply helpful? Yes No
DanavanCampen on at

Like (0)

Report

Re: How to format number fields to currency, but then also Sum them?

Yes i have tried that but as it is already a value, i don't think that it can convert a value to a value!!

(Error message says expected text here)

Was this reply helpful? Yes No
PowerAddict 7,314 Most Valuable Professional on at

Like (0)

Report

Re: How to format number fields to currency, but then also Sum them?

And what error message do you get with the original formula for sum?

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

Thanks!
Hardit Bhatia
The Power Addict
https://thepoweraddict.com

Was this reply helpful? Yes No
PowerAddict 7,314 Most Valuable Professional on at

Like (0)

Report
Re: How to format number fields to currency, but then also Sum them?

Let me know your use case is different than my example.

I have a collection which is defined as:
ClearCollect(ColTest, 124.343, 23543.23, 223.23123)

I then have a label (instead of using a gallery, just for a quick example) that displays the first record of the collection in a formatted format. The Text property of the label is set to:
Text(First(ColTest).Value, "[$-en-US]$#.##")
The result is: $124.34

I have another label to display the sum of all values in the collection. The Text property of this label is set to:
Text(Sum(ColTest, Value), "[$-en-US]$#.##")
The result is: $23890.8

Which is what is expected. So let me know whats diff in your case, and we can figure this out.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.
Thanks!
Hardit Bhatia
https://thepoweraddict.com

Was this reply helpful? Yes No
DanavanCampen on at

Like (0)

Report

Re: How to format number fields to currency, but then also Sum them?

Thank you for trialling it for me and for trying to help. Unfortunately this code is exactly the same as I have used, which I have mentioned in my first post.

I don't get an error message, the Sum field is just blank.

Was this reply helpful? Yes No