6 Replies Latest reply on Dec 4, 2014 7:49 AM by philmodjunk

# Help with calculating a sum of lengths for a specific grouping of records within a database

### Title

Help with calculating a sum of lengths for a specific grouping of records within a database

### Post

Hi,

I cannot figure out how to calcuate a total_length field that can be stored so I can use it in another calculations.

I need to find a percentage of something within a length of transect I walked. The transect is broken into multiple units. Each unit has its own number and date. There are multiple records in my database with different units/dates, but I need to calculate a stored data field for the entire unit length of my individual transects so I can determine the total percentage of substrates, etc, along only that transect- not others.

The first thing I tried to do was when I created the cumulative length field, I clicked "summary" and then "fraction of the total of." The issue is that I am only allowed to click one subtotal field to link those records to my particular result. I really need to click two fields (date AND unit name) to make the database pull the correct records to be totaled.

How do I do this??? Thanks so much.

• ###### 1. Re: Help with calculating a sum of lengths for a specific grouping of records within a database

Why would you need to refer to a date field?

A Summary field will compute different totals when used in different contexts. The most common use--especially by beginners is for it to compute a total for all records in your found set. If you performed find, for all records on a given date or range of dates, the summary field would then show a total for just those records.

And in a sub summary layout part with "when sorted by date" specified, the same summary field can compute a total for just those records with the same value in the date field--provided that you sort your records by this date field.

And in other contexts, the same summary field can compute a total based on a related set of records or on all the related records displayed in a filtered portal.

• ###### 2. Re: Help with calculating a sum of lengths for a specific grouping of records within a database

I understand that I can use the summary function for that, but unfortunately it doesn't work when I want to use it in a calculation. The calculation turns up at "unstored" because, I think, of this summary field.

These are the calculations that I want, but aren't working:

Sum area silt_organics_sand / (Total percentage silt_sand * Total Reach Length)

It shows up "unstored" and can't give me a result.

Thanks again for your help! Any ideas?

• ###### 3. Re: Help with calculating a sum of lengths for a specific grouping of records within a database

"unstored" is not why it can't give you a result. Unstored is normal and expected for calculations that refer to global fields, summary fields or fields in related tables. When you use a summary field in a calculation, you should see that it evaluates from a "grand total" context. In other words, it should supply a total based on all records in the current found set to be used in the calculation.

You haven't explained why you need to refer to the date field for your summary total.

If you want the summary fields to return subtotals based on all records in your found set with the same date, you can sort your records by Date and then the getSummary function can use the summary and date fields as parameter to return the same sub total as would appear in a sub summary layout part if records were sorted by this same "break" field.

• ###### 4. Re: Help with calculating a sum of lengths for a specific grouping of records within a database

I just chose date for the sake of sorting data out. I could also use another field like Stream Name, etc. My calculations that say "unstored" are not working. I must be setting them up incorrectly.

• ###### 5. Re: Help with calculating a sum of lengths for a specific grouping of records within a database

It looks like I need to figure out how to use the "getSummary" function.

• ###### 6. Re: Help with calculating a sum of lengths for a specific grouping of records within a database

If you know how to set up a summary report with subtotals in sub summary layout parts, it's nearly identical. The "sorted by" field you'd specify for a sub summary layout part is the "break" field you'd use with GetSummary. The only difference is that a sub summary layout part can specify a field from a related table as the break field where getsummary can only use a break field defined in the same table as the summary field.