7 Replies Latest reply on Feb 9, 2017 5:55 PM by user19752

# Running total for common field

Hello. First of all I am a beginner to filemaker. I hope you guys can help me. I am trying to create a field that will keep a running total on the amount of another field. For example:

Database with 1000 records. 200 records are type A, 200 are type B etc.....

Product name: X  (name field)

Product type:  A    (name field)

Product price:10 (variable)   (number field)

Running total:  (would keep a running total of the amount all product A types are worth)

Product name: X  (name field)

Product type:  B    (name field)

Product price:10 (variable)   (number field)

Running total:  (would keep a running total of the amount all product B types are worth)

I hope I have explained this well enough. Could anyone give me a calculation (or something else) to make running total work? Thank you

• ###### 1. Re: Running total for common field

Do you want to see just one row for type B and one row for Type A or do you need to list all the individual records with a total for each type?

• ###### 2. Re: Running total for common field

I guess you want something like

TypePriceRunningTotal
A1010
A1020
B1111
B1021
A1232
B1233
• ###### 3. Re: Running total for common field

Thank you for your prompt nreplies. Maybe using the term running total was wrong. I would like for all records that are type A to show the total worth of all As. All records that are type B to show the total worth of all type Bs. Cs, Ds etc.....

so it would be

Type    Price    Total

A         10         32

A         12         32

A         11         32

B         10         25

B         15         25

C         10         30

C         10         30

C         10         30

I hope I explained it better this time.

• ###### 4. Re: Running total for common field

Define a self join relationship (uses a second occurrence of the same table) that matches by type. Then the Sum Function can total Price From the related table occurrence. Note that this sums all records of a given type in the table--which might not work for you.

And unstored calculation field using ExecuteSQL might be also used.

• ###### 5. Re: Running total for common field

I am just starting to use filemaker. Maybe you could give me more detailed instructions or a sample database where I can see what was done? Thank you kindly. I use fmp14

• ###### 6. Re: Running total for common field

See if the attached gives you the idea. The key steps are:

1.     Set up the relationship using Type field

2.     Make the layout based on the related TO (table occurrence)

3.     Set up the running total based on Type sort

4.     Sort the file

• ###### 7. Re: Running total for common field

For your result that sorted by type, you can use old style summary field.

1. make a summary field

total of price (don't need "running total" option. and this field is not need in layout)

2. make a calculation field

GetSummary ( totalOfPrice ; type )

3. sort records by type.

This sums on found set.