3 Replies Latest reply on Apr 25, 2013 9:54 PM by philmodjunk

    Need help with table view

    ReidCamp

      Title

      Need help with table view

      Post

           Hello all,

           I would like to have a table in my database that shows ALL the data. The tables in my DB are hierarchal, so I would like to group the data by the highest level table's ID ("SurveyInfo_ID").

           In access, I would simply create a totals query and group by "SurveyInfo_ID." However, I'm not sure how to do this in FM.

           In other words...

           Here is what I want my table to look like:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                          SurveyID                     Crew                     Year                     Location                     StationID                     Date                     Attempt                     Control
                          1                     RC                     2013                     Here                     45                     4/25/2013                     1                      
                          1                     RC                     2013                     Here                     45                     4/25/2013                     2                      
                          1                     RC                     2013                     Here                     45                     4/25/2013                     3                      
                          1                     RC                     2013                     Here                     45                     4/25/2013                     4                      
                          1                     RC                     2013                     Here                     45                     4/25/2013                                           1
                          1                     RC                     2013                     Here                     45                     4/25/2013                                           2
                          1                     RC                     2013                     Here                     46                     4/25/2013                     1                      
                          1                     RC                     2013                     Here                     46                     4/25/2013                     2                      
                          1                     RC                     2013                     Here                     46                     4/25/2013                                           1
                          2                     RC                     2013                     There                     47                     4/25/2013                     1                      
                          2                     RC                     2013                     There                     47                     4/25/2013                     2                      
                          2                     RC                     2013                     There                     47                     4/25/2013                     3                      
                          2                     RC                     2013                     There                     47                     4/25/2013                                           1
                          2                     RC                     2013                     There                     47                     4/25/2013                                           2
                          2                     RC                     2013                     There                     47                     4/25/2013                                           3

           Here is what it currently looks like:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
                          SurveyID                     Crew                     Year                     Location                     StationID                     Date                     Attempt                     Control
                          1                     RC                     2013                     Here                     45                     4/25/2013                     1                     1
                          1                     RC                     2013                     Here                     46                     4/25/2013                     1                     1
                          2                     RC                     2013                     There                     47                     4/25/2013                     1                     1

           FYI, there are many more fields, this is just an abridged example of what I want to accomplish.

           Thanks!

        • 1. Re: Need help with table view
          philmodjunk

               It would help to have a more complete understanding of how your data is structured. Please explain the differences in the last two columns and how they are produced in your first example.

               Are you grouping your data by the first 5 or 6 columns? are the values in the two right columns running totals?

               If so, this can easily be produced with a List View, not Table View layout using summary fields set to compute running totals and the correct sort order.

          • 2. Re: Need help with table view
            ReidCamp

                 I have tables structured like this:

                   
            1.           SurveyInfo           
                               
              1.                            StationInfo                     
                                           
                1.                                           Attempts (portal)                               
                                                       
                  1.                                                          Attempts information
                  2.                               
                                           
                2.                          
                3.                                           Controls (portal)                               
                                                       
                  1.                                                          Controls information
                  2.                               
                                           
                4.                     
                               
              2.           
                   

                 The attempts and controls information is filled in using portals in the StationInfo layout.

                 When a person uses this form they will:

                   
            1.           Fill out the survey info (name, date, place)
            2.      
            3.           Fill out station info (will be multiple stations per survey)           
                               
              1.                            Fill out attempts and controls (will be multiple attempts and controls per station)
              2.           
                   

                 I would like to be able to group the data by individual surveys which each have their own unique ID.

                 The examples above are conceptual - I made them in excel.

                 Sorry for the poor formatting above - the bullets and numbers are not working properly...

            • 3. Re: Need help with table view
              philmodjunk

                   I would like to be able to group the data by individual surveys which each have their own unique ID.

                   Then I'm still not understanding what you want here. Both tables appear to be grouped by survey ID--the first column in both tables. This does not explain what is supposed to be different between your two examples.

                   I think you should investigate summary reports which are created in List View: Creating Filemaker Pro summary reports--Tutorial

                   But I could easily be misunderstanding your.