Conditional sum?

[Fred]

FMP6, MacOSX.2

I've created a calc field using the aggregate Sum(score) to give me a "total
points scored" for all related records in a self-join. I then created a
checkbox that, if checked, I want the score from that particular record
omitted from the Sum calc (but I want the score preserved so I can't have
the checkbox zero out the score field). Is this possible?

The only way I can think of seems clunky: create scoreForSum calc =
Case(omit = "true","",score)

I would then use Sum(scoreForSum) to obtain "total points scored".


As I said, it seems clunky with the extra field, so I was wondering if there
is a better way.

TIA,

Fred

[John Weinshel]

That's pretty much it.

If the checkbox is formatted with a VL whose contents are 1 and 0 (or even
1), and you squish it to show only the 1 part, and it's a number field, you
can shorten to:

omit*score

--
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634
Associate Member, Filemaker Solutions Alliance


"Fred" <fredr1@cox.net> wrote in message
news:BBC322C8.C65B%fredr1@cox.net...

> FMP6, MacOSX.2
>
> I've created a calc field using the aggregate Sum(score) to give me a

"total

> points scored" for all related records in a self-join. I then created a
> checkbox that, if checked, I want the score from that particular record
> omitted from the Sum calc (but I want the score preserved so I can't have
> the checkbox zero out the score field). Is this possible?
>
> The only way I can think of seems clunky: create scoreForSum calc =
> Case(omit = "true","",score)
>
> I would then use Sum(scoreForSum) to obtain "total points scored".
>
>
> As I said, it seems clunky with the extra field, so I was wondering if

there

> is a better way.
>
> TIA,
>
> Fred
>

[Glenn Schwandt]

I think that should be:

(not omit) * score

assuming that checking the box means omit = 1 and you don't want to count it
in your sum.

"John Weinshel" <john@datagrace.biz> wrote in message
news:vprr5q496i3u42@news.supernews.com...

> That's pretty much it.
>
> If the checkbox is formatted with a VL whose contents are 1 and 0 (or even
> 1), and you squish it to show only the 1 part, and it's a number field,

you

> can shorten to:
>
> omit*score
>
> --
> John Weinshel
> Datagrace
> Vashon Island, WA
> (206) 463-1634
> Associate Member, Filemaker Solutions Alliance
>
>
> "Fred" <fredr1@cox.net> wrote in message
> news:BBC322C8.C65B%fredr1@cox.net...

> > FMP6, MacOSX.2
> >
> > I've created a calc field using the aggregate Sum(score) to give me a

> "total

> > points scored" for all related records in a self-join. I then created a
> > checkbox that, if checked, I want the score from that particular record
> > omitted from the Sum calc (but I want the score preserved so I can't

have

> > the checkbox zero out the score field). Is this possible?
> >
> > The only way I can think of seems clunky: create scoreForSum calc =
> > Case(omit = "true","",score)
> >
> > I would then use Sum(scoreForSum) to obtain "total points scored".
> >
> >
> > As I said, it seems clunky with the extra field, so I was wondering if

> there

> > is a better way.
> >
> > TIA,
> >
> > Fred
> >

>
>