10 Replies Latest reply on Apr 14, 2011 9:50 PM by JamesWong

# SUM exactly same value as the field

### Title

SUM exactly same value as the field

### Post

Hi forum!! Am trying to get the total sum of my 'Amount' column, instead of adding all the values from every row it seems FileMaker is only mirroring the 'Amount' value of each row. The formula am using is Sum(Expenses::Amount). I got it working when I used a summary field but it wouldn't let me relate it to other databases.

• ###### 1. Re: SUM exactly same value as the field

I believe you must use the Sum function from another context (table or TO). Otherwise the field using Sum is one field of one record and will, of course, only "sum" itself.

RW

• ###### 2. Re: SUM exactly same value as the field

Sum adds up the list of values located inside the parenthesis. Sum ( Expenses::amount ) only gets one value to sum up and thus produces the same result as if you just used Amount in its place.

A summary field, as you've found will add up all the values in yoru found count and can even compute a running total if that should be useful.

In order for Sum ( ) to work for you, it'd need to refer to all the records being summed. That requires a relationship to another table or table occurrence. You can use that to relate a table to itself so that you can compute the sum, but the resulting total is an unstored calculation and this also cannot be used in many relationships. (Unstored fields can only be used on the Parent side of a relationship.)

Say you want sum to compute the sum of all the records in your table.

Define this relationship:

Expenses::anyfield X Expenses2::Anyfield

Create Expenses2 by selecting expenses, then clicking the button with two green plus signs. Now Sum ( Expenses2::Amount ) will compute the total for all records in your table. If you only want sum to compute a total for a smaller subset of the total records, you'll need to modify the relationship to link only to those records.

• ###### 3. Re: SUM exactly same value as the field

@Rick Whitelaw: Makes sense to me now!

@PhilModJunk: I did exactly what you said, however it is not returning anything now. Is it because my field type 'number'? does it needs to be text?

• ###### 4. Re: SUM exactly same value as the field

Amount should be of type number, but even a text field containing a number should sum correctly.

Your total field should be of type calculation set to return number. Don't try this with a number field and an auto-entered calculation.

• ###### 5. Re: SUM exactly same value as the field

Hmmm I can't seem to get it to work... It is still returning blank.

• ###### 6. Re: SUM exactly same value as the field

On the tables tab, do you see one table: Expenses with two occurrences: Expenses, Expenses2?

• ###### 7. Re: SUM exactly same value as the field

Yes I do.

• ###### 8. Re: SUM exactly same value as the field

Here's a demo file that does work. Compare it to yours and see if you can spot what's different. (Click blue down load button located below the file description text.)

http://www.4shared.com/file/ANIZrWn8/SumFunctTest.html

• ###### 9. Re: SUM exactly same value as the field

-----

Opened the file and cValueSum is showing 6 on every row. Is that normal?

• ###### 10. Re: SUM exactly same value as the field

Am giving up, spent the whole day and night figuring how this sum works and am still stuck... Thanks a lot for you help but I guess am off to easier alternatives.