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

    Plot problems

    MarkPage

      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

       

       

       

      Forum_Av_DBH.png

        • 1. Re: Plot problems
          philmodjunk

          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
            MarkPage

            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
              philmodjunk

              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
                MarkPage

                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.

                Thanks for your help.

                Mark

                 

                 

                 

                • 5. Re: Plot problems
                  philmodjunk

                  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