sum with if statement (newbie)

[emmade]

Hi!

I have the following problem

1 have 1 database

with the following fields art, code, & number

I would liek to have a calculated field with the following

If ( code = "aaa" ; sum ( number) ; 0 )

but his works only for the current record and I want it done on all records

can someone give me a direction because I am stuck

thanks!!

emma

[ESpringer]

Emma,

I'm not sure exactly what you mean, but: is it that you wan the sum of the numbers in ALL the number fields (in "aaa" records), instead of the sum of the current record's number field (namely, a value identical to what that record shows as its own number except when it's not an "aaa" record)?

You do know about summary fields, right? They are defined as calculations that show sums, averages, etc., for all records. It's possible that's what you want... It will show the total for a given field, across records, but it always restricts itself to the found set. So, *if* you use the field you already set up, which shows the number or a zero (depending on whether this is a "aaa"-type record) *then* a Summary Total field will sum up all the aaa-record numbers *in the found set*.

My best guess is that you want a place to show the TOTAL of the number values FOR the set of records tagged "aaa" (whether or net they're in the found set). Is that right? If so, in FM6, I'd create a relationship using a Constant field and a Boolean "tag" for the "aaa" code:

Create a field called Constant with the number 1 in every record (You can set it to auto-enter 1 after you fill in your existing records.)

Create a field called aaa? which just does this

Code="aaa"

This will put 1 in the field if the above statement is true.

Now define a relationship (called AllAAAs) so that any record in this file is related to any other records in the same file where Constant in the "home" record is the same value as aaa? in the "away" record.

Now, you can create a calc field which looks like this:

Sum(AllAAAs::number)

No matter which record you're in (unless you have zero records in the found set), that field will show the total for the "aaa"-coded numbers.

Let me pause and ask whether this is useful or whether you want to clarify your needs further...

[emmade]

that perfect (and thanks for your reply!)

but is there nogt a sollution with some code like:

If ( code = "aaa" ; sum ( number) ; 0 )

so that if I want to sum the field number if the code = "bbb"

I only have to change the code to If ( code = "bbb" ; sum ( number) ; 0 )

the same as the sum funktion but selective (sum only if code = "bbb"

again thanks for your time!

emma

[MoonShadow]

Hi Emma,

Create a global text field called gCode. Create a Value List based upon values from field and select your Code field. Attach this Value List to the gCode field as a popup (or even radio button). Good thing about radio buttons is that only one selection is possible; but it would depend upon how many codes you have and how much space you have.

Create a relationship (self-join) from gCode (left) to Code (right). Create a calculation (number) with: Sum(selfjoin::number) and place this calculation on your form layout.

When you choose from the popup, it establishes the unique relationship from Code xxx to Code xxx and sums the number field. As you change the Code, your calculation will change.

If you wish to instead have a calculation display on every record, just join Code to Code and then Sum(selfjoin::number). If you are viewing an aaa record, only the sum of aaa will display; same with bbb, etc.

[emmade]

Thank You for your help!

emma