Need a formula
Author Message
Need a formula
I need help with a calculation.  I need a formula to display thirds as
either .1 or .2 (1/3, or 2/3).  Is there a place I can post my question and

Calling Filemaker technical support is pretty expensive.

Al

Sun, 21 Jan 2001 03:00:00 GMT
Need a formula

Hi Al,

I haven't had much time to think this one over as I'm in a hurry, so if I am
way off, please go easy on me...

Couldn't you just multiply your decimal number by 3?  Then run the Int()
function on that result to get values of either 0,1, or 2.  Then add 1 to
this result to get 1,2, or 3.  Then to a NumToText() and concatenate a
period in front of the number.

I feel like this will NOT work depending on how you want to "round" your
numbers.  For example, should .015 round to zero or to one third?  If you
use Round() instead of Int() you will get results of 0,1,2,or 3 which might
be ok for you too.  Then just don't add the extra one I guess...

Perhaps this will get you started.  The entire calc might look like:
resultField (calc, text) = ["." & NumToText(Int(sourceDecimal * 3) + 1)]

If there is a much simpler method, forgive me - my mind works in mysterious
ways when I am in a hurry. :)

Good luck,

Andrew LeCates
--------------

Quote:

>I need help with a calculation.  I need a formula to display thirds as
>either .1 or .2 (1/3, or 2/3).  Is there a place I can post my question and

>Calling Filemaker technical support is pretty expensive.

>Al

Sun, 21 Jan 2001 03:00:00 GMT
Need a formula

Quote:

>For example, should .015 round to zero or to one third?

Ok, I should slow down. Sorry.  To clarify:

Mean to ask if decimals < one-third should round to zero or one-third.
Should decimals > five-sixths round to one or to two-thirds?

Good luck :)

Andrew

Sun, 21 Jan 2001 03:00:00 GMT
Need a formula
Thanks for responding to my posting.

Ive created a database to track career stats for players in a baseball
league.  Im nearly finished except for displaying the total innings a
pitcher has completed.  I tracks the stats by year and then use the
sub-summary function to get grand totals for each player in the league.  Im
modeling my database after the Sporting News Baseball Register.

I use a field to input whole innings and a second field to input one-third
of an inning completed.  The thirds are input as a single digit.  The
display shows the whole innings, a period, then the thirds.

So a line item of 11 2/3 innings pitched, displays as 11.2 for a one season.
Another line might read 12.2.  Using sub-summary I get a total of 23.4
innings.  On paper I can equate that to 24.1.  My goal is to have the
database display 24.1.

The formula Im using is

Int(GetSummary(Total innings,Name)) +
Round(Mod(GetSummary(TotThrds,Name),1)/.3,1)

Ive gotten help from one fellow, but the formula did not account for the
thirds field.  I tweaked it enough so it now rounds the total innings.  Its
acceptable for now, but Im looking for perfection.   The formula is
rounding to the total number of innings, whether there are .1 or .2 innings.

I can forward formulas if you need to see where Im stuck if youd like to
help.  Hope this hasnt been too confusing.

Quote:

>>For example, should .015 round to zero or to one third?

>Ok, I should slow down. Sorry.  To clarify:

>Mean to ask if decimals < one-third should round to zero or one-third.
>Should decimals > five-sixths round to one or to two-thirds?

>Good luck :)

>Andrew

Tue, 23 Jan 2001 03:00:00 GMT
Need a formula
Alan, I hope you're using FMPro 3.0 or higher. I would die a thousand deaths
before using the GetSummary function. I used it when it was first introduced,
but found it to be so -ssssllllooooowwwwww- that it made my database solutions
unusable.

The upgrade to v3 or v4 is *well* worth the price of admission.

There, that said, here's a slightly less cumbersome solution to your problem:

In your database, create a RELATIONSHIP call 'NameMatch' which matchs WITHIN
the current file, name=name.
This will allow you to get rid of the GetSummary Functions and use the MUCH
faster Aggregate functions.

In my example, I have 5 fields as follows:

Name   Text - Indexed

Innings  Number - Validated by Calc     = (Innings = Int(Innings)) or
(Mod(Innings,1) = .1)
or (Mod(innings,1) = .2

calc.InningsWhole   Calculation, number = Int(Innings)

calc.InningsPartial Calculation, number = Round(Mod(Innings,1) * 10, 0)

sum.Innings         Calculation, number = Sum(NameMatch::calc.InningsWhole) +
Int(Sum(NameMatch::InningsWhole)/3) +

(Mod(Sum(NameMatch::InningsPartial),3)/10)

This works every time, and the validation rule on the innings field keeps the
innings from being entered as 12.25.
One caveat, sometimes the sum.Innings field won't get updated *immediately*
when an Innings value changes. Simply clicking in the sum.Innings field causes
it to update. This solution also eliminates the Partial Inning data entry
field. All innings and partial innings are entered in the Innings field.

Give it a spin, let me know if it works for you.

Quote:

> Ive created a database to track career stats for players in a baseball
> league.  Im nearly finished except for displaying the total innings a
> pitcher has completed.  I tracks the stats by year and then use the
> sub-summary function to get grand totals for each player in the league.  Im
> modeling my database after the Sporting News Baseball Register.

> I use a field to input whole innings and a second field to input one-third
> of an inning completed.  The thirds are input as a single digit.  The
> display shows the whole innings, a period, then the thirds.

> So a line item of 11 2/3 innings pitched, displays as 11.2 for a one season.
> Another line might read 12.2.  Using sub-summary I get a total of 23.4
> innings.  On paper I can equate that to 24.1.  My goal is to have the
> database display 24.1.

><snip>

--
==========================================================
Michael Little