formatting numeric text

[donwolfkonecny]

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!

[Weetbicks]

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.

[aaa]

This works too.

Num - Mod ( Num;1 ) &

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

[donwolfkonecny]

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!

[sujat]

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

[hugh71158]

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

[HBMarlowe]

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" )

[hugh71158]

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

[HBMarlowe]

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.

[sujat]

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 )

[sujat]

 

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

[hugh71158]

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

[sujat]

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

 

sujatpat@gmail.com

[Techphan]

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.