Salesforce and other SMB Solutions are coming soon. ×

# formatting numeric text

## Recommended Posts

Customers pledge and sometimes an amount is not specified. I need to send a reminder letter. In the letter is a field that is a calculation that specifies the dollar amount or if it is one that is a codeword to put "an unspecified amount".

If I make the calculation result in number, the text does not display. If I make it text, "\$12.50" shows up as "\$12.5". I want the text to show up or "\$12.50"

my calculation is If (BillingAmount > 1; "\$" & Round (BillingAmount;2);"An unspecified amount")

I tried SetPrecision but it didn't work, perhaps only because it's text.

I've tried a number (an unspecified one ) of combinations of text and number functions with no good results so if you think you've got it, could you be explicit.

Thank you!

##### Share on other sites

I had this exact same problem at work recently for a client and found a nice custom function @ brian dunnings website to solve the problem:

http://www.briandunning.com/cf/196

Pass it a number (ie your number dollar field) and it will spit out the dollar as text with 2 decimal places.

##### Share on other sites

This works too.

Num - Mod ( Num;1 ) &

Case( Mod ( Num;1 )=0;".00";Left(Mod ( Num;1 ) & "00";3))

##### Share on other sites

Thanks Weetbix, I'm sure that would work but I haven't used CFs so I tried aaa's code and shoot dang! if it didn't work the first try!

funny thing is i do a similar thing to force year abbreviations, I just couldn't wrap my head around how to get it to work for this.

Thank you!

##### Share on other sites

This one gives the flexibility of using any currency and the thousand separator.

Amt = Number

Currency = Text ( So that you can have any currency)

Million Calculation (Number) Unstored, = Int(Amt/1000000)

Thousand Calculation (Number) Unstored, = Int((Amt-(Million * 1000000 ))/1000)

Hundred Calculation (Number) = Int(Mod(Amt;1000))

Cents Calculation (Number) = (Amt-Int(Amt))*100+.00005

Million Number Calculation (Text) Unstored, = If(Million=0;"";GetAsText ( Million ))

Thousand Number Calculation (Text) Unstored, = Case(Million=0 and Thousand=0;"";Million ≥ 1 and Thousand=0;"000";Million ≥ 1 and Thousand>0 and Thousand ≤ 9;"00" & GetAsText ( Thousand );

Million ≥ 1 and Thousand>9 and Thousand ≤ 99;"0" & GetAsText ( Thousand );

Million ≥ 1 and Thousand>99;GetAsText ( Thousand );Million=0;GetAsText( Thousand ))

Hundred Number Calculation (Text) Unstored, = Case(Million=0 and Thousand=0 and Hundred=0;"";Int(Amt) ≤ 999;

GetAsText ( Hundred );Int(Amt) > 999 and Hundred = 0;"000";Int(Amt) > 999 andHundred>0 and Hundred ≤ 9;"00" & GetAsText ( Hundred );Int(Amt) > 999 andHundred>9 and Hundred≤ 99;"0" & GetAsText ( Hundred );Int(Amt) > 999 andHundred>99;GetAsText ( Hundred ))

Cent Number Calculation (Text) Unstored, = Case(Int(Cents)=0;"00";Int(Cents) ≥ 1 and Int(Cents) ≤ 9;"0" & GetAsText

( Int(Cents) );Int(Cents) > 9;GetAsText ( Int(Cents) ))

Million Comma Calculation (Text) Unstored, = If(Million>0;",";"")

Thousand Comma Calculation (Text) = If(Int(Amt) > 999 ;",";"")

Amt Number Calculation (Text) Unstored, = Case(Int(Amt)=0;"0." & Cent Number;Int(Amt) ≥ 1;Million Number &

Million Comma & Thousand Number & Thousand Comma & Hundred Number &"."

& Cent Number)

Amt Number FINAL Calculation (Text) Unstored, = Currency & " " & Amt Number

##### Share on other sites

• 4 weeks later...

Hi,

thanks for the pointer to this thread. Still, I am confused! The script, I am afraid, is still beyond my knowledge of Filemaker. (new to this!)

I am also not sure if I am explaining things clearly enough, maybe I am clouding the issue. Here's what I am trying to achieve.

I have a solution, and I use it in the U.S. It contains Zip Codes, which I need to change to Post Codes for the U.K. copy. In this case, I suppose all I need change are the labels.

Also, all amounts are reported preceded with the \$ symbol. So, if I create some sales, all the amounts are shown in the format - \$250.

If I want to give someone in the U.K. a copy of the same solution, they need the amount fields, and calculations, to show as, for example, Â£125.

Now, it takes me a little while to go from layout to layout changing everything individually, not to much of an issue. But, when I make improvements to the solution I have to repeat the process all over again to send to the U.K.

I am just trying to figure out if there is a better way, a more automated way, preferably contained within the one "universal" solution instead of using two versions.

Any help would be GREATLY appreciated with this.

Regards,

Hugh

##### Share on other sites

I have a solution, and I use it in the U.S. It contains Zip Codes, which I need to change to Post Codes for the U.K. copy. In this case, I suppose all I need change are the labels.

Hi Hugh,

I don't know if this technique will cover all of your needs, but you could use a calculation field as a label. Something along the lines of

If ( SomeCondition ; "Zip Code" ; "Post Code" )

##### Share on other sites

Hi,

thanks for that. I could use a drop down to select USA or UK and apply that IF statement.

My bigger issue is the currency notation. Do you know if it could be formatted as a number only, with whatever notation is required scripted as an IF?

But that wont work in the script, right?

Confused!!!! :eek:

Hugh

##### Share on other sites

You could have a number field where the data is entered, and it would be used for all calculations, etc. Then have a calculation field, returning text, like this:

If ( SomeCondition ; "Â£" & NumberField ; "\$" & NumberField )

I've not personally dealt with the issues you are describing, and it may well be that others have better ways of dealing with them.

##### Share on other sites

Hi,

Also, all amounts are reported preceded with the \$ symbol. So, if I create some sales, all the amounts are shown in the format - \$250.

If I want to give someone in the U.K. a copy of the same solution, they need the amount fields, and calculations, to show as, for example, Â£125.

Hugh

Without number format ( that is no comma thousand separator )

Country = Text ( to store USA or UK or any other country )

CurrencySymbol = Calculation ( Text ) GLOBAL > Case(Country ="USA","\$",Country ="UK","Â£")

Currency Conveter > Number>GLOBAL

Amount New = Calculation > CurrencySymbol & your amount * Currency Conveter

For USA Currency Conveter value = 1

This will display something like this \$250 , \$2365 ( no thousand separator )

##### Share on other sites

I have a solution, and I use it in the U.S. It contains Zip Codes, which I need to change to Post Codes for the U.K. copy. In this case, I suppose all I need change are the labels.

Hugh

Postal Code = Calculation ( Text )> Case(Country ="USA","Zip code",Country ="UK","Post Code")

And use Postal Code in place of Zip code

##### Share on other sites

Hi sujat,

thank you for the info. As a newbie, I am struggling a little with the application of this, especially how I tie it to the price field that I have in my solution.

Can I ask, what do you mean? "For USA Currency Conveter value = 1"

Am I on the right track with the attached file?

Is it not possible to have a thousand separator?

Sorry to be a pain!

Regards,

Hugh

##### Share on other sites

I cannot download the file as I am not a cafe member. You can send the file directly to me .

sujatpat@gmail.com

##### Share on other sites

Hi sujat,

thank you for the info. As a newbie, I am struggling a little with the application of this, especially how I tie it to the price field that I have in my solution.

Can I ask, what do you mean? "For USA Currency Conveter value = 1"

Am I on the right track with the attached file?

Is it not possible to have a thousand separator?

Sorry to be a pain!

Regards,

Hugh

Add a 5th field called Amount (entry field, numeric) and a 6th field (calculated, global, numeric) called converted amount and define the calc as ConvertedAmount= Amount*CurrencyConvertor.

On your layout right click the numeric fields and define the number formatting as you desire (number of digits, thousands separator, etc).

Finally, change your AmountNew field to text and change the calculation to

AmountNew=CurrencySymbol & ConvertedAmount.

Make sure the CurrencySymbol logic is correct so that if you input dollars the output is pounds and vice-versa.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

• ### Images

• By Soliant Consulting,
• By Soliant Consulting,
• By Soliant Consulting,
• ### Forum Statistics

• Total Topics
33.7k
• Total Posts
141.6k
×
×
• Create New...