5 Replies Latest reply on Dec 23, 2014 1:25 PM by philmodjunk

# Plot problems

### Title

Plot problems

### Post

Hi,

I collect a lot of data in woodlands. I set up a plot in which I measure 7 to 20 tree girths and one tree height. I have any plots therefore. I need a list view of all plots showing in one record (or line) only the (one) tree height, and the average of the girths.

I confess I'm a bit stumped by this.

I've attached a screenshot of the data for one plot. What I want is one record showing fields Strata_ID; Plot_ID; AV_DBH; Zero_Ht

Can anyone point me in the right direction?

Cheers

Mark

• ###### 1. Re: Plot problems

I would put the tree girths in a related table linked to your plot record. That related table allows you to record a flexible number of tree girths by adding just as many records as you need to record your data--probably by using a portal. You can then use: Average ( TreeGirths::Girth ) to compute the average girth from your 7 to 20 records recording tree girths.

• ###### 2. Re: Plot problems

PhilModJunk,

Thanks for this. I can't really create a separate table as this data is collected (in one table) by electronic callipers, and imported as such. I'd thought of some sort of self-join. Is that worth proceeding with?

Cheers

Mark

• ###### 3. Re: Plot problems

I would still use the related table. Even if you have to import the data as a batch and then, after import, parse the data into the individual related records via a script.

But just for comparison, you can also have 27 fields in your records and the same average function can compute the average of those fields. Average ( field1 ; field2 ; field3 ... etc.) Empty fields will be ignored so if you have 7 measurements, you get a total divided by 7 for the mean and if you have 27, you get a total divided by 27.

But this is much more limiting and it will be more difficult to do additional analysis of your data from within FileMaker if you choose this option.

• ###### 4. Re: Plot problems

PhilModJunk,

What I'm looking for is the 'Group by' facility in MS Access...

It seems odd to me that Filemaker (so much better than MS Access in so many ways) doesn't have an equivalent to MS Access 'Group by' command. When I do this in Access I have the result in the twinkling of an eye.

Mark

• ###### 5. Re: Plot problems

I do that all the time in FileMaker and in more than one way.

To repeat from my last post:

If you have
MainTable----<RelatedTable

You can define a calculation field in MainTable as: Average ( RelatedTable::Field )

and it will compute the average of field over all fields in RelatedTable that are linked to the current record in MainTable.

Set up a list view or table view layout based on Main table and your calculation field will show this average for each record in MainTable.

In a summary report, you can base your layout on RelatedTable and use summary fields inside a sub summary layout part to get the same result as "group by" in a SQL query. You can get one row of data for each record in MainTable with an aggregate value (sum, average, max, min, standard deviation...) based on the group of records all related to the same record in MainTable.

See this tutorial on summary reports: Creating Filemaker Pro summary reports--Tutorial