Jump to content

Newbie Calculation Question


This topic is 5759 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

I have a problem trying to use a number that needs to be formatted as currency in a new calculated text field.

The new field I'm creating will be a Text field containing the values of the Account, Date, Amount, Memo.

The Amount is in a field which is defined as a number (i.e. 1000 or 10.64). In the new calculated field, I need to add the currency sign, the commas and the decimal points.

I can't figure out how to do that. Whan I create my calculation in the new field, it formats the field just as it is stored. My calc is: Donor & "|" & Date & " - " & Amt & " - " & Memo.

Any help would be appricaited.

Thanks - kevin

Link to comment
Share on other sites

Maybe something like this:

Donor & "|" & GetAsText ( Date) & " - " & "$ " & GetAsText ( Amt ) & " - " & Memo

which would return

Jim|12/12/2005 - $ 12.40 - this and that

HTH

Lee

Edited by Guest
showed result
Link to comment
Share on other sites

Ah shoot. I didn't test for more than 1000.

So, if the thousand separator is important, then the amended calculation would be:

Donor & "|" & GetAsText ( Date) & " - " & "$ " &

Case ( Div ( Amt ; 1000 ) ; Div ( Amt ; 1000 ) & "," )

&

Right ( Int ( Amt ) ; 3 )

& "." &

Right ( "00" & Amt * 100 ; 2 )

& " - " & Memo

I was excited to see when FileMaker changed the TextToNumber (v6) to GetasText(Number) in (v7), that they also changed it so that the decimals show (provided they were entered in the number field). Too bad the thousand separators don't carry over and we can stop having to use a workaround case statement.

oh well, maybe in version 9 or 10.

Lee

Link to comment
Share on other sites

the decimals show (provided they were entered in the number field)

I find this a very confusing "feature". I would expect a NUMBER to ignore any redundant digits that the user have chosen to input. You certainly cannot rely on it, because it doesn't work when the number comes from a calculation.

Link to comment
Share on other sites

Maybe - just maybe - a calc isn't even needed. If this is for display, merge fields would work a treat for you (and save adding another field to your field definitions, as well as saving a bit of resource).

If you use merge fields, each piece can be formatted as its specific type, ie, the date could be written out or the number could be selected and formatted as dollars etc. It's surprising how many times I've created a calculation when it's sole purpose was to display. :wink2:

Link to comment
Share on other sites

This topic is 5759 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.