A coworker came to with this problem:

He has a database where a user is entering in a dollar amount. He wants to format that field to have 2 decimal places and have a leading C$ to denote canadian dollars. He would like the user to only have to enter in the number and not have to enter in the extra decimal places if the amount is a whole dollar. So this is what he wants:

User Entered amount: 24

Auto formatted amount: C$24.00

I can't seem to get it to work correctly with a calculation field. We did think of a way to script it but it seems messy. We thought we could do a pattern count on the user entered amount and look for a decimal point. If we find one, then we check to see if there are 2 digits after it. If there are 2 then we do nothing. If there is only 1 digit after the decimal point, we would append a 0. We haven't tested this yet but it seems like it would work. We are hesitant to do this way because we then don't know how to make sure that the script is run. We probably could live with setting it up as an exit script. We were hoping that there was a better way of doing it through defining fields. Does anyone have any suggestions as to what we may try???

John

Hi,

Can you just format the number for on screen purposes, from the format menu in layout mode.

Choose number formatting.

Format as decimal, set 2 decimal places and use notation of currency leading and enter C$

HTH

Andy,

That does seem to work but I failed to mention one thing. They sometimes need to change the C$ to US$. The default would be C$ but if I format as you suggested, I don't believe they can change it on the fly.

Thanks for the suggestion though. I forgot that you can change the leading currency symbol.

John

Hi John,

You are correct, they wont be able to change it on the fly. I almost answered your original post, when it struck me that we may be talking about something more then just formatting a field on screen. I was in the process of responding when Andy answered, so I figured I would wait until you responded to his post.

Perhaps it would be helpfull for us to know how the field is used. For instance, are you also wanting to convert the currency along with the formatting?

Lee

Lee,

No, we aren't converting the currency. The amount entered will either be in canadian or us currency. That's why we need to be able to switch from C$ to US$. We are trying to keep it simple and are only dealing with the format. They use this for year end receipts so all they do is print out the forms and mail it to our members.

Thanks,

John

If it is only for format, create a value list called currency with both $ and c$ in it.

Then enter the amount in the field "amount"

Then, use a calculation with currencywithamount = valuelist&amount with number result.

If it is just for a layout, you can get rid of this calc...

I tried doing this but I couldn't get the valuelist to showup in along with the amount. What syntax do I need to use to call the value list up? Here is the calculation that I used: [color:"red"] ValueListItems( test, Currency) & Amount

I have never referenced a value list item before so I don't know if I did this correctly or not. How can the users set which item in the value list to use with this technique?

John

Sorry! Doesn't know even how to do this.

I meant :

1. create the value list "Currencysymbols" with both currency symbols

2. create a text field choiceCurrency

In layout mode, define the field to be formated as a list using the Value list

3. create a calculation "choiceCurrency"&"Amount"

4. Place this field just on the layout on your field "amount"

5. In layout mode, change the color of "amount" field to invisible.

This is only a format solution.

This works except that I don't get the two decimal places. The result of the calculation needs to be a text because of the currency symbols. The text result does not allow me to specify that it have 2 decimal places. We need to show the 2 decimal places. Any ideas how I can get those to show up?

Thanks,

John

Modify this calculation of your number field to fit your needs.

"US $ " & Int(Round(Numberfield,2)) & "." & Right(Round(Numberfield,2) * 100,2)

The quote of US $ could just as well be another field using the two choices mentioned (i.e. US $ or C $ ).

HTH

Lee

That did it! I took this to my coworker and he was thrilled with it. Thank you for all your help.

Thanks,

John

Just in case you'll need also thousands separator

Case( NUMBER < 0 , "-")

&

Choose( Abs( Round( NUMBER , PRECISION )) < 10^9 ,

Right( Abs(Int( Round( NUMBER , PRECISION ) / 10^9 )) , 3 ) & "," )

&

Choose( Abs( Round( NUMBER , PRECISION )) < 10^6 ,

Right( Abs(Int( Round( NUMBER , PRECISION ) / 10^6 )) , 3 ) & "," )

&

Choose( Abs( Round( NUMBER , PRECISION )) < 10^3 ,

Right( Abs(Int( Round( NUMBER , PRECISION ) / 10^3 )) , 3 ) & "," )

&

Right( Abs( Int( Round( NUMBER , PRECISION ))) , 3 )

&

Choose( PRECISION = 0 , "." &

Right( 10^PRECISION & Abs( Round( NUMBER , PRECISION ) )

* ( 10^PRECISION ) , PRECISION ))

Dj