f



Formatting number fields within a calculated text field

I have a calculated text field which is part of an invoice printout.
The definition is as follows:

Case ( WeeklyRental > 0 ; "Includes " & Weeks & " weeks rental. After "
& Weeks & " weeks rental will be charged at �" & Round (WeeklyRental;2
) & " per week"; "" )

This shows in an invoice to specify rental amounts after a certain
number of weeks. I want this statement to completely disappear in other
invoices where no rental is to be charged. As it is it works fine
except - the weekly rental does not format properly as currency. So for
example �30.20 comes out as �30.2. Is there any simple way to make this
format properly as currency? The rental is a percentage and can have
more that 2 decimal places.
-- 
Dan Fretwell
Using FMP7.03 on WinXP SP2
0
Dan
7/9/2005 11:47:17 PM
comp.databases.filemaker 11053 articles. 0 followers. amosw01 (46) is leader. Post Follow

13 Replies
869 Views

Similar Articles

[PageSpeed] 57

In article <xn0e4kf6voahov000@news.freenetname.co.uk>, dan@owlsnet.co.uk=20
says...
>=20
> I have a calculated text field which is part of an invoice printout.
> The definition is as follows:
>=20
> Case ( WeeklyRental > 0 ; "Includes " & Weeks & " weeks rental. After "
> & Weeks & " weeks rental will be charged at =A3" & Round (WeeklyRental;2
> ) & " per week"; "" )
>=20
> This shows in an invoice to specify rental amounts after a certain
> number of weeks. I want this statement to completely disappear in other
> invoices where no rental is to be charged.=20

So far so good.

> As it is it works fine
> except - the weekly rental does not format properly as currency. So for
> example =A330.20 comes out as =A330.2. Is there any simple way to make th=
is
> format properly as currency?

Simple? Depends on what you mean by simple.

> The rental is a percentage and can have more that 2 decimal places.

Anyhow... rewriting it a bit... using if instead of case... (Why would=20
you use a "case" here?)

There are about a million ways to acheive what you want to do. A simple=20
'hack' would be:


If ( WeeklyRental > 0 ;=20

Let (
=09[hack =3D Round(WeeklyRental;2)+0.005;
=09formattedrate =3D left(hack,length(hack)-1)];

=09"Includes " & Weeks & " weeks rental. " &
=09"After " & Weeks & " weeks rental will be charged at =A3" &
=09formattedrate &=20
=09" per week"
);

"")

The hack turns what ever your number is... e.g. 30.2 to 30.201, and then=20
returns all the characters except the last one... e.g. 30.20. Because=20
the 0.005 is added on after the rounding is finished, you can rest=20
assured it won't alter the value.

If your doing this a lot, FM7 Developer's custom definable functions=20
make this much cleaner, and less prone to typos and other errors.

And If you don't have FM7 at all you can do it equivalently without let,=20
its just a little less readable:

If ( WeeklyRental > 0 ;=20

"Includes " & Weeks & " weeks rental. " &
"After " & Weeks & " weeks rental will be charged at =A3" &
left(
=09Round(WeeklyRental;2)+0.005,
=09length(Round(WeeklyRental;2)+0.005)-1) &=20
" per week";

"")

-regards,
Dave
0
42
7/10/2005 5:35:14 AM
In article <MPG.1d3a53757cf20aac989bdf@shawnews.vf.shawcable.net>, 42
<nospam@nospam.com> wrote:

> And If you don't have FM7 at all you can do it equivalently without let, 
> its just a little less readable:
> 
> If ( WeeklyRental > 0 ; 
> 
> "Includes " & Weeks & " weeks rental. " &
> "After " & Weeks & " weeks rental will be charged at �" &
> left(
>         Round(WeeklyRental;2)+0.005,
>         length(Round(WeeklyRental;2)+0.005)-1) & 
> " per week";
> 
> "")

WOW!! There IS another person on the planet who uses the 'If' statement
when there's only one or two possible result ... everyone else seems
stuck on using 'Case' all the time. Poor old 'If' will become a
nostalgic memory soon.  :o(


Anyway, there's two problems with your solution which may or may not
bother the original person:

A.  Negative numbers get rounded towards zero rather then the 
    correct way.
    eg.    -9.9965 becomes -9.99 instead of -10.00
    In fact, "-0.9" does somthing funny and displays as "-.89"
    (probably because it's adding 0.005 rather than subtracting 
     for negative numbers).

B.  Numbers between 1 and 0 won't display the leading 0.
    eg.     0.9865 displays as .99

We did go through something similar to this a little while back for
displaying numbers in scientific format (eg. 1.95E+04) and came to the
conclusion that it's not as easy as it looks to round and format
numbers within a calculation. We did come up with a couple of different
formulas that appeared to work, but were very complicated.




Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)
0
Helpful
7/10/2005 6:05:31 AM
Thanks to both 42 and HH

42 - as you say it depends what "simple" means and your solution is
much simpler than the way I was going - thanks.

HH - The reason for the Case is that I have been experimenting with
"dynamic" instructions to the user when entering data and this needs a
Case. I implemented this one as a Case as I may what I want to change
the text according to other situations (i.e. replace with something
else under certain extra conditions and nothing under others).
Regarding your comments on negatives, that will not happen here as the
WeeklyRental, if not zero, is a percentage of a positive amount and I
think the smallest non-zero it is likely to have is of the order of 2.5.

 <SNIP>
> else seems stuck on using 'Case' all the time. Poor old 'If' will
> become a nostalgic memory soon.  :o(
> 
> 
> Anyway, there's two problems with your solution which may or may not
> bother the original person:
> 
> A.  Negative numbers get rounded towards zero rather then the 
>     correct way.
>     eg.    -9.9965 becomes -9.99 instead of -10.00
>     In fact, "-0.9" does somthing funny and displays as "-.89"
>     (probably because it's adding 0.005 rather than subtracting 
>      for negative numbers).
> 
> B.  Numbers between 1 and 0 won't display the leading 0.
>     eg.     0.9865 displays as .99
> 
> We did go through something similar to this a little while back for
> displaying numbers in scientific format (eg. 1.95E+04) and came to the
> conclusion that it's not as easy as it looks to round and format
> numbers within a calculation. We did come up with a couple of
> different formulas that appeared to work, but were very complicated.
> 
> 
> 
> 
> Helpful Harry                   
> Hopefully helping harassed humans happily handle handiwork hardships
> ;o)
=======



-- 
Dan Fretwell
Using FMP7.03 on WinXP SP2
0
Dan
7/10/2005 7:33:08 AM
In article <100720051805316195%helpful_harry@nom.de.plume.com>,=20
helpful_harry@nom.de.plume.com says...
> In article <MPG.1d3a53757cf20aac989bdf@shawnews.vf.shawcable.net>, 42
> <nospam@nospam.com> wrote:
>=20
> > And If you don't have FM7 at all you can do it equivalently without let=
,=20
> > its just a little less readable:
> >=20
> > If ( WeeklyRental > 0 ;=20
> >=20
> > "Includes " & Weeks & " weeks rental. " &
> > "After " & Weeks & " weeks rental will be charged at =A3" &
> > left(
> >         Round(WeeklyRental;2)+0.005,
> >         length(Round(WeeklyRental;2)+0.005)-1) &=20
> > " per week";
> >=20
> > "")
>=20
> WOW!! There IS another person on the planet who uses the 'If' statement
> when there's only one or two possible result ... everyone else seems
> stuck on using 'Case' all the time. Poor old 'If' will become a
> nostalgic memory soon.  :o(

It is essentially a shortcut for case. And with FM syntax its only a=20
shortcut by two characters. But still when reading someone elses code if=20
tells me there is only one case to look at, while case doesn't.
=20
>=20
> Anyway, there's two problems with your solution which may or may not
> bother the original person:
>=20
> A.  Negative numbers get rounded towards zero rather then the=20
>     correct way.
>     eg.    -9.9965 becomes -9.99 instead of -10.00
>     In fact, "-0.9" does somthing funny and displays as "-.89"
>     (probably because it's adding 0.005 rather than subtracting=20
>      for negative numbers).

Yeah, I called it a 'hack' for a reason. :)

I assumed rental rate would never be less than zero.
Tweaking it to add/subtract for sign though is as simple as making it:

Round(WeeklyRental;2)+(0.005*if(sign(weeklyrental)<0),-1,1))

> B.  Numbers between 1 and 0 won't display the leading 0.
>     eg.     0.9865 displays as .99
>=20

The leading 0 is less a bug and more a separate part of the initial=20
problem I didn't tackle. And again the range 0-1 might not even be in=20
the problem space. At any rate adding it back is fairly simple: detect=20
if weeklyrental is between 0 and 1 and add a leading 0, or between 0 and=20
-1 and insert a zero at the second character (or strip off the first=20
character and add "-0").

> We did go through something similar to this a little while back for
> displaying numbers in scientific format (eg. 1.95E+04) and came to the
> conclusion that it's not as easy as it looks to round and format
> numbers within a calculation. We did come up with a couple of different
> formulas that appeared to work, but were very complicated.

I remember it; I was actually involved in that one too. (or at least=20
i've been involved in one recently here... im sure its come up more than=20
once)

-Dave
0
42
7/10/2005 6:14:09 PM
On Sun, 10 Jul 2005 18:14:09 GMT, 42 wrote:
> > WOW!! There IS another person on the planet who uses the 'If' statement
> > when there's only one or two possible result ... everyone else seems
> > stuck on using 'Case' all the time. Poor old 'If' will become a
> > nostalgic memory soon.  :o(
> 
>  It is essentially a shortcut for case. And with FM syntax its only a 
>  shortcut by two characters. But still when reading someone elses code if 
>  tells me there is only one case to look at, while case doesn't.

Case is always ok and extendable.

I mainly prefer 'if' whenever there's no else:

  if ( a=b, c)

Otherwise, the case lines up better - and there's "always" yet another
case to be verified ;-)

  case(
    a=b, c,
    d=e, f,
    g)

- Martin
0
Martin
7/12/2005 2:05:25 PM
In article <slrndd7jh5.pdp.t-use@ID-685.user.individual.de>, t-
use@gmx.net says...
> On Sun, 10 Jul 2005 18:14:09 GMT, 42 wrote:
> > > WOW!! There IS another person on the planet who uses the 'If' statement
> > > when there's only one or two possible result ... everyone else seems
> > > stuck on using 'Case' all the time. Poor old 'If' will become a
> > > nostalgic memory soon.  :o(
> > 
> >  It is essentially a shortcut for case. And with FM syntax its only a 
> >  shortcut by two characters. But still when reading someone elses code if 
> >  tells me there is only one case to look at, while case doesn't.
> 
> Case is always ok and extendable.
> 
> I mainly prefer 'if' whenever there's no else:
> 
>   if ( a=b, c)
> 
> Otherwise, the case lines up better - and there's "always" yet another
> case to be verified ;-)
> 
>   case(
>     a=b, c,
>     d=e, f,
>     g)

For a single case:

case (
 a=b, c
 d)

if (
 a=b, c
 d)

How does the case line up better? 
0
42
7/12/2005 4:46:07 PM
42 wrote:
> In article <slrndd7jh5.pdp.t-use@ID-685.user.individual.de>, t-
> use@gmx.net says...
>>I mainly prefer 'if' whenever there's no else:
>>
>>  if ( a=b, c)
>>
>>Otherwise, the case lines up better - and there's "always" yet another
>>case to be verified ;-)
>>
>>  case(
>>    a=b, c,
>>    d=e, f,
>>    g)
> 
> How does the case line up better? 

Just to throw my two cents in.  I used to use If() whenever I could, but 
have since made the effort to always use Case().  Why?  Because Case() 
always works and If() doesn't -- consistency.  Also because If() 
requires two (and only two) results.  With Case(), I can say: Case(A, B) 
and I find it to be cleaner and easier to read then If(A, B, "")

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg              (818) 883-2846
FM Pro Solutions       Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance
0
Howard
7/12/2005 6:48:45 PM
On Sun, 10 Jul 2005 18:14:09 GMT, 42 wrote:
> >
> > WOW!! There IS another person on the planet who uses the 'If' statement
> > when there's only one or two possible result ... everyone else seems
> > stuck on using 'Case' all the time. Poor old 'If' will become a
> > nostalgic memory soon.  :o(
> 
>  It is essentially a shortcut for case. And with FM syntax its only a 
>  shortcut by two characters. But still when reading someone elses code if 
>  tells me there is only one case to look at, while case doesn't.

Technically, in the world of programming "If" came long before "Case".
So "Case" is really a shortcut for multiple "If"s.    :o)

Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)
0
Helpful
7/12/2005 8:33:23 PM
On Tue, 12 Jul 2005 16:46:07 GMT, 42 wrote:
>  For a single case:
> 
>  case (
>   a=b, c
>   d)
> 
>  if (
>   a=b, c
>   d)
> 
>  How does the case line up better? 

It does not line up better - not yet.
But maybe, as soon as another condition will be added. 
0
Martin
7/13/2005 8:52:25 AM
On Tue, 12 Jul 2005 11:48:45 -0700, Howard Schlossberg wrote:
>  Also because If() 
>  requires two (and only two) results.  With Case(), I can say: Case(A, B) 
>  and I find it to be cleaner and easier to read then If(A, B, "")

Did you ever try If(A, B)!?
0
Martin
7/13/2005 8:53:26 AM
In article <slrndd9lk6.pth.t-use@ID-685.user.individual.de>, Martin
Trautmann <t-use@gmx.net> wrote:

> On Tue, 12 Jul 2005 11:48:45 -0700, Howard Schlossberg wrote:
> >  Also because If() 
> >  requires two (and only two) results.  With Case(), I can say: Case(A, B) 
> >  and I find it to be cleaner and easier to read then If(A, B, "")
> 
> Did you ever try If(A, B)!?

That won't work in older versions of FileMaker Pro, but I'm not sure
which version added the ability to not use the "else" / "otherwise"
part of the If statement. 

I've just tried it in a FileMaker 5.5 Calculation field and you get the
"two few separators" error. 

'If' I get to the Mac with FileMaker 6 later today, 'then' I might try
it on that version, 'otherwise' I won't.   ;o)



Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)
0
Helpful
7/13/2005 9:02:00 PM
In article <140720050902002529%helpful_harry@nom.de.plume.com>, 
helpful_harry@nom.de.plume.com says...
> In article <slrndd9lk6.pth.t-use@ID-685.user.individual.de>, Martin
> Trautmann <t-use@gmx.net> wrote:
> 
> > On Tue, 12 Jul 2005 11:48:45 -0700, Howard Schlossberg wrote:
> > >  Also because If() 
> > >  requires two (and only two) results.  With Case(), I can say: Case(A, B) 
> > >  and I find it to be cleaner and easier to read then If(A, B, "")
> > 
> > Did you ever try If(A, B)!?
> 
> That won't work in older versions of FileMaker Pro, but I'm not sure
> which version added the ability to not use the "else" / "otherwise"
> part of the If statement. 
> 
> I've just tried it in a FileMaker 5.5 Calculation field and you get the
> "two few separators" error. 
> 
> 'If' I get to the Mac with FileMaker 6 later today, 'then' I might try
> it on that version, 'otherwise' I won't.   ;o)

That was his point. You -can't- do if(a,b), but you can case(a,b)

His thesis is that this difference makes case better than if. I'm not 
entirely convinced myself but I see where he's coming from.
0
42
7/13/2005 11:48:13 PM
In article <slrndd9li9.pth.t-use@ID-685.user.individual.de>, t-
use@gmx.net says...
> On Tue, 12 Jul 2005 16:46:07 GMT, 42 wrote:
> >  For a single case:
> > 
> >  case (
> >   a=b, c
> >   d)
> > 
> >  if (
> >   a=b, c
> >   d)
> > 
> >  How does the case line up better? 
> 
> It does not line up better - not yet.
> But maybe, as soon as another condition will be added. 

And on that faraway day I'll change the word 'if' to 'case'. 

I probably won't heave great sighs of regret that I lacked the forsight 
to set it up that way initially though. 

:p
0
42
7/13/2005 11:53:44 PM
Reply: