views:

303

answers:

2

Hello

I have the following in my formula field:

toText(((Sum ({DataTable1.Ending_Value}, {DataTable1.Name})) - (Sum ({DataTable1.Starting_Value}, {DataTable1.Name})) - (Sum ({DataTable1.Investments}, {DataTable1.Name})) + (Sum ({DataTable1.Proceeds}, {DataTable1.Name})) + (Sum ({DataTable1.Interest_and_Dividends}, {DataTable1.Name})))/(Sum ({DataTable1.Starting_Value}, {DataTable1.Name})) * 100) + " %"

The output is something like: 6.32 %

Instead of having 2 numbers after the decimal, is there any way to only have 1? I tried the following to no luck:

toText(((Sum ({DataTable1.Ending_Value}, {DataTable1.Name})) - (Sum ({DataTable1.Starting_Value}, {DataTable1.Name})) - (Sum ({DataTable1.Investments}, {DataTable1.Name})) + (Sum ({DataTable1.Proceeds}, {DataTable1.Name})) + (Sum ({DataTable1.Interest_and_Dividends}, {DataTable1.Name})))/((Sum ({DataTable1.Starting_Value}, {DataTable1.Name})) * 100),1) + " %"

b/c the output kept showing me 0.0 % for everything

Next I tried this:

(toText(((Sum ({DataTable1.Ending_Value}, {DataTable1.Name})) - (Sum ({DataTable1.Starting_Value}, {DataTable1.Name})) - (Sum ({DataTable1.Investments}, {DataTable1.Name})) + (Sum ({DataTable1.Proceeds}, {DataTable1.Name})) + (Sum ({DataTable1.Interest_and_Dividends}, {DataTable1.Name})))/(Sum ({DataTable1.Starting_Value}, {DataTable1.Name})) * 100) + " %"),1)

No luck with that either.

Anybody have any ideas?

A: 

Have you tried using the number formatting options you get when you right-click on the field and select 'format object'? It is way easier than trying to get ToText() to do what you want.

Edit: If you want to use ToText(), try using a format string instead of 1 as your second parameter - I think this will do it:

ToText(sum(...) + sum(...), "0.0")
Ray
A: 

I like Ray's solution, but for completeness there is also this hacky method; which quite obviously is the wrong way to go if you also need a currency symbol:

  1. Right-click on the numeric field and select Format.
  2. Go to the Number tab and click Customize.
  3. Click the Currency Symbol tab.
  4. Click Enable Currency Symbol.
  5. Change the Currency Symbol to '%'
  6. Change Position combobox to '-123%'
  7. Click OK, OK to exit and save.
Adam Bernier