 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / FileMaker Topics / May 2004FMP7 - Display number field with "+" and "-" signs
Is there a calculation field I can use to display a number field with the correct signs and two decimal places. example: I enter +6 or 6 and a display field shows +6.00 I enter -1.5 and I get -1.50 (in red) The second example will of course work from using the format number field, but I need both examples to work. I've been able to do this using a script, but I don't want my users to have to remember to button press. Thanx in advance for any help offered. Mike. > Is there a calculation field I can use to display a number field with > the correct signs and two decimal places. [quoted text clipped - 10 lines] > > Mike. Make the displayed field be a calculated result where the appropriate formatting is computed and applied.  SignatureTom Stiller PGP fingerprint = 5108 DDB2 9761 EDE5 E7E3 7BDA 71ED 6496 99C0 C7CF Support the 2004 Million Mom March on Washington DC Mother's Day, May 9, 2004 visit <http://www.mmm2004.org/> > > Is there a calculation field I can use to display a number field with > > the correct signs and two decimal places. [quoted text clipped - 13 lines] > Make the displayed field be a calculated result where the appropriate > formatting is computed and applied. That's what I've been trying to do with little success. I need help! > > > Is there a calculation field I can use to display a number field with > > > the correct signs and two decimal places. [quoted text clipped - 15 lines] > > > That's what I've been trying to do with little success. I need help! FileMaker will display a negative number with a minus sign or in parentheses, optionally in red, according to number formatting of the field on Layout. With none of the previous, a positive number is assumed, with no sign indicated. You'll have to do a calculation field returned as text, along the lines of NumberDisplay = Case( Number > 0; "+" & GetAsText ( Number ); Number = 0; "0"; Number < 0; "-" & GetAsText ( Number ) ) Matt >"Mike" <mike@promaestro.com> wrote in message >> > > [quoted text clipped - 10 lines] > Number < 0; "-" & GetAsText ( Number ) >) As Tom pointed out, this will not deal with the needed trailing zeros. How about something like this: Left ((GetAsText (Number) & "00"), Position ((GetAsText (Number) & "00"), ".", 2) (Copy & paste this into the Case statement above in the two places needed) That is, tack two zeros (as text) on to the end of every number, then use the Left/Position combo to truncate everything but two digits to the right of decimal point. This may not be exactly riht. I get messed up with Position functions all the time. Besides, this is an FM 7 request and I'm responding with an FM 6 answer. Steve Brown In article <eyebrown-2604040750550001@sdn-ap-024tnnashp0022.dialsprint.net>, > >"Mike" <mike@promaestro.com> wrote in message > >> > > [quoted text clipped - 24 lines] > Position functions all the time. Besides, this is an FM 7 request and I'm > responding with an FM 6 answer. It takes a little more hacking. If the number is an integer, you need to include the decimal point as well as the two zeros. Only the OP can say if forcing the leading plus sign is worth all the additional testing and formatting, but it it is... SignatureTom Stiller PGP fingerprint = 5108 DDB2 9761 EDE5 E7E3 7BDA 71ED 6496 99C0 C7CF Support the 2004 Million Mom March on Washington DC Mother's Day, May 9, 2004 visit <http://www.mmm2004.org/> > > Tom Stiller <tomstiller@comcast.net> wrote in message > news:<tomstiller-1DFB10.11545125042004@comcast.ash.giganews.com>... [quoted text clipped - 35 lines] > > Matt Thanks for the effort, but I tried something like that and it doesn't work. Still searching for solution??? Mike. This may seem overly simplistic, but why not create an additional field, "PosNeg?" (calc field with text result) Case( YourNumber >= 0, "+", "-" ) and place this as a merge field preceeding your number field as needed. Now, number formatting will take care of your decimal place, etc... Also, if your field is required in a mathmatical calc at some later time, it will still be a number. Michael Myett >>>Tom Stiller <tomstiller@comcast.net> wrote in message >> [quoted text clipped - 42 lines] > > Mike. Shouldn't PosNeg give a "+" if the number is positive, but give nothing if it is negative, because if the number is negative, it will already display with a negative sign in front of it? Greg >This may seem overly simplistic, but why not create an additional field, >"PosNeg?" (calc field with text result) [quoted text clipped - 56 lines] >> >> Mike. Signature> >This may seem overly simplistic, but why not create an additional field, > >"PosNeg?" (calc field with text result) [quoted text clipped - 9 lines] > > > >Michael Myett This was the answer I've been looking for, thanks to all for giving there best, but my hat goes off to Michael Myett. One thing, Case( YourNumber > 0, "+") was sufficient. > > > Is there a calculation field I can use to display a number field with > > > the correct signs and two decimal places. [quoted text clipped - 15 lines] > > > That's what I've been trying to do with little success. I need help! I spoke too soon. I mistakenly thought there was a number function that would format a number with a specified number of decimal places as text; there doesn't seem to be. I can force the leading "+" sign or the two (possibly zero) decimal places, but not both. SignatureTom Stiller PGP fingerprint = 5108 DDB2 9761 EDE5 E7E3 7BDA 71ED 6496 99C0 C7CF Support the 2004 Million Mom March on Washington DC Mother's Day, May 9, 2004 visit <http://www.mmm2004.org/> > Is there a calculation field I can use to display a number field with > the correct signs and two decimal places. [quoted text clipped - 10 lines] > > Mike. OK, three problems to solve. The result of this calculation should be text. First, you want "+" or "-" at the beginning. Not too hard... Second, you want exactly two decimal places. A little more difficult, but not impossible... Case( YourNumber < 0, "-", YourNumber > 0, "+", "") & Left( GetAsText(YourNumber + .000000001), Position( GetAsText( YourNumber + .000000001), ".", 1, 1) + 2) Last, you want the result to be red if less than 0. This part I can't help with, because my version 7 trial expired :-( , but it should be easy enough to do. If you can't figure it out, maybe someone else can help. > > Is there a calculation field I can use to display a number field with > > the correct signs and two decimal places. [quoted text clipped - 24 lines] > with, because my version 7 trial expired :-( , but it should be easy enough > to do. If you can't figure it out, maybe someone else can help. As Greg Dember just pointed out, you don't need to add a "-" for a negative number, so you can drop the first test and result from the Case statement... Case( YourNumber > 0, "+", "") & Left( GetAsText( YourNumber + .000000001), Position( GetAsText( YourNumber + .000000001), ".", 1, 1) + 2) And now I see there is a "rounding" problem with negative numbers with my formula... > And now I see there is a "rounding" problem with negative numbers with my > formula... Try this instead... Case( YourNumber > 0, "+", "") & Left( Case( PatternCount( YourNumber, "."), YourNumber & "0", YourNumber & ".00"), Position( Case( PatternCount( YourNumber, "."), YourNumber & "0", YourNumber & ".00"), ".", 1, 1) + 2) Mike, Couldn't you set the field to Auto-Evaluate a calculation and always update the field's contents. See options for any text or number field in FM version 7. I hope this helps. Denis On 4/25/04 11:25 AM, in article 9a98e8ff.0404250725.1a17e9be@posting.google.com, "Mike" <mike@promaestro.com> wrote: > Is there a calculation field I can use to display a number field with > the correct signs and two decimal places. [quoted text clipped - 10 lines] > > Mike. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.