6 Replies Latest reply on Aug 16, 2010 5:03 PM by vmtc

Mixed number formatting in calculation

Title

Mixed number formatting in calculation

Post

I'm trying to create a calculated field that gives a statement involving three sets of numbers, but I can't find a way to format it properly. The specific problem is with a dollar figure which I want to display with two decimal places. However, if the last digit, or last two digits are O, it drops off. So instead of \$45.70, I get \$45.7

The sentence should read something like either

4 sales. Royalty of 10% on \$25.50

Or, if it's the case,

No Sales.

Is there any way I can structure this statement so that it always displays with two decimal places and a \$ sign, without affecting the other figures? Rounding doesn't stop it dropping off.

The best I can think of is to do it as a merge field and put the \$ and % symbols in manually, and format it, but then I'll get 4.00 sales and 10.00%. Also, this only works properly when there are sales.

I'm using FM Pro 11 Advanced on Mac OSX (DB also used on Windows XP, Vista and 7)

• 1. Re: Mixed number formatting in calculation

For the sake of explanation, I'll call the data field with the currency value "Amount".

When you enter a value into the "Amount" field, you'll have to actually enter the last one (or two) zeroes if you want the calculation field to "see" (and therefore display) those zeroes, even if the "Amount" field is formatted to display the value as currency with two decimal places.

The calculation field reads the number data as entered into the "Amount" field. The currency formatting which I assume you've applied to the "Amount" field only affects the *display* of the data in the "Amount" field, not the underlying *actual* number value that has been entered into the field.

So, if you want the calculation field to display \$45.70, the value in the "Amount" field must be entered as 45.70 (with the zero actually typed in), not 45.7.

Nick

• 2. Re: Mixed number formatting in calculation

Hi

given 3 number fields: Number of sales, Royalty and Amount ( never negative and with max 4 decimals )
a cSentence ( result text ) may be:

Case(
IsEmpty ( Number of sales ) or Number of sales  ≤ 0 ; "No Sales" ;
Number of sales & " sales. Royalty of " & Royalty & "% on \$" & Int ( Amount)  & Left ( Mod ( Amount ; 1 ) + 10 ^ - 5  ; 3 )
)

• 3. Re: Mixed number formatting in calculation

Nice, Daniele.  :^)

If someone wants two decimal to display then I would think they would also want thousands separator to show.  You can handle the thousands separation by incorporating something like this:

NumToJText ( Int ( Amount ) ; 1 ; 0 ) &
Left ( Round ( Mod ( Amount; 1 ) ; 2 ) + 10 ^ - 5  ; 3 )

I also rounded it to two places.

Indeed I did!  Thanks for the correction! I fixed my response.

• 4. Re: Mixed number formatting in calculation

Thank you, LaRetta.

For the thousands separator, do you forget the NumToJText (  ) function ?

• 5. Re: Mixed number formatting in calculation

I also wanted to point out, since your on vs. 11 Pumpkin, that you don't need to even create a calculation to handle this.

How many calculations do we end up with throughout our solutions when, in trut, we really only want to DISPLAY the calculation?  With vs. 11, you can take advatage of layout-level variables.  This helps keep down the number of fields in our field definitions and layout-level variables are faster.

Here is an example using the above calculation:  http://www.4shared.com/file/mw-u1Tly/VarDisplay.html

Notice the 'I declare variables.'  This is where we use any text object to declare many layout-level variables, including complex calculations and references to other layout-level variables.  We have one of these text fields on every layout to use for various purposes (learned from Comment).  By having all of our variables attached to one object (which only shows in layout mode), we have complete control (instead of attachming variables in multiple places).

Then the script trigger refreshes when the value has changed (if variable isn't based upon data being changed immediately on current layout, there is no need to force a refresh at all). The number of calculations in one solution alone has dropped by a third.

• 6. Re: Mixed number formatting in calculation

Thanks for your help raybaudi and LaRetta, which has solved my problem nicely.  And I've learnt something.

I'm actually using v10 (don't know why I said 11), so haven't looked up your other suggestion, but as I said, am very happy with the way it is all working.

Cheers