Text / Number Function

[dbanbury]

Hi, does anyone know if there is a simple function to return a number with a fixed number of decimal places? I'm having trouble working with mail.it in getting a text string together with price numbers properly lined up. $2.10 for example is coming out $2.1. I know how to set the number of decimals from the formatting menu but need to work with a text string. Help appreciated. Cheers, Dave

[Lee Smith]

Use the Merge Field function for the number, and insert that that in your text, and you can format the number as you describe.

Lee

Edited June 4, 2006 by Guest
wasn't very clear

[dbanbury]

I'm sorry but I don't follow. I can't find a merge field function. I know about merge fields but I don't follow how that could be relevant to working on a variable. If I concatenate a string variable to a number field, the result is text but with the number improperly formatted for my needs. I need to force a decimal point and two decimal places, since $200 won't line up in a list of prices such as $4.99, since there won't even be a decimal place added for an integer. Hope this clarifies. Cheers, Dave.

[Lee Smith]

Your first post led me to believe you wanted to format a number from a number field, in some text. You do this using the Insert >> Merge fields in Layout Mode.

I think what you are now asking is how to format a number field in a calculation. It can't be done. A search of the Forum for Format Number Field for export would turn up many treads on this subject. Basically, FileMaker drops any leading or trailing zeros (after the decimal) in calculations. You might use a Text Field Intead of number, and type in your format.

Maybe if you tell us what you want your result to look like we can give you some options.

Lee

[LaRetta]

I believe this should work. It doesn't include the thousands separator but I don't think you want that. If you need thousands separator or dollar signs, let me know and I can direct you to some great threads on the subject. Calculation (result is text):

Let ( [

num = Round ( numberField ; 2 ) ;

whole = Int ( num ) ;

cent = Round ( Mod ( num ; whole ) * 100 ; 2 )

] ;

whole & "." & Right ( "00" & cent ; 2 )

)

Note: The data can't have more than 2 decimal places.

UPDATE: I thought I should just include that so I've changed the calc...

LaRetta :wink2:

Edited June 5, 2006 by Guest
Added update

[Lee Smith]

Oh dang it. My brain hickup back to v6 again. :

I like the calculation that [color:red]comment posted. here

HTH

Lee

[LaRetta]

Yep, that's the one! Thanks for finding it for us, Lee. :wink2:

[comment]

Perhaps a simpler calc might suffice in this case.

[dbanbury]

Thanks everyone, particularly LaRetta. I'm still finding my way through the functions and had a similar technique to yours in mind but based on detecting the presence of a period within the text string. I just thought there might be a function already built in to achive this, a bit like the old Basic language instructions for formatting numbers by supplying a template. Anyway, I now use your calc with adjustments to the multiplier, zeros and rounding to achieve what I wanted for 0.1c accounting. Thanks again all. Best, Dave.