8 Replies Latest reply on Sep 17, 2013 10:06 AM by philmodjunk

    Number of problems related to counting records based on relationships

    ShaneB

      Title

      Number of problems related to counting records based on relationships

      Post

           I'd like to be able to display information about the data in the database in a number of ways for my users. For example:

           - Show each unique value in the datapoint_source field, and count the number of records in the table that include that value.

           - Show each unique value in the datapoint_source field, but count the number of Observations on the Observations table are linked to Datapoints that include that value.

           - For a found set of observations, show the different datapoint_source values that are linked to the found set and a count of how many times each unique datapoint_source value is matched to the found set.

           I am not able to determine how these relationships or calculations should be defined. My database is below:

        • 1. Re: Number of problems related to counting records based on relationships
          philmodjunk
               

                    - Show each unique value in the datapoint_source field, and count the number of records in the table that include that value.

               Set up a list view layout based on the dataPoint table. Remove the body layout part and replace it with a sub summary layout part "when sorted by" datapoint_source.

               Place a "count of" summary field in this sub summary layout part along with the datapoint_source field.

               Show All Records

               Sort your records by the datapoint_source field.

               Done.

               

                    -Show each unique value in the datapoint_source field, but count the number of Observations on the Observations table are linked to Datapoints that include that value

               Do the same as the above, but now do one of the following:

                 
          1.           Define a calculation field in your Datatpoint table to return the Count ( Observations::_observation_id_pk ) and add this field to the sub summary layout part.
          2.      
          3.           Define a "count of summary field in Observations. Put that field in the sub summary layout part.

                

               

                    - For a found set of observations, show the different datapoint_source values that are linked to the found set and a count of how many times each unique datapoint_source value is matched to the found set.

               Not sure that I have a clear picture of what you have in mind for the output here. I think the above report method can be used if you first:

               A) Perform a find on a layout based on Observations (do NOT do this on a layout based on a different occurrence of observations).

               B) Use Go to Related records, specifying the Datapoint table occurrence, but with the match found sets option to pull up the matching data point records on the above described layout.

               C) Sort the records by datapoint_source

          • 2. Re: Number of problems related to counting records based on relationships
            ShaneB
                 

                      Set up a list view layout based on the dataPoint table. Remove the body layout part and replace it with a sub summary layout part "when sorted by" datapoint_source.

                 

                      Place a "count of" summary field in this sub summary layout part along with the datapoint_source field.

                 

                      Show All Records

                 

                      Sort your records by the datapoint_source field.

                 

                      Done.

                 

                      Do the same as the above, but now do one of the following:

                 
                        
            1.                Define a calculation field in your Datatpoint table to return the Count ( Observations::_observation_id_pk ) and add this field to the sub summary layout part.
            2.           
            3.                Define a "count of summary field in Observations. Put that field in the sub summary layout part.
            4.      

                  

                 Both of these things seem to be doing what I want. Thank you. Is there a way I can create a portal to these results that can be displayed on the main user layouts? Additionally, can I take the calculated/counted results and use them in a chart created with the chart tool? Many of the results eventually are used in graphic reports so if those could be created using FMP for quick visualization that would be handy.

                 

                      Not sure that I have a clear picture of what you have in mind for the output here. I think the above report method can be used if you first:

                 

                      A) Perform a find on a layout based on Observations (do NOT do this on a layout based on a different occurrence of observations).

                 

                      B) Use Go to Related records, specifying the Datapoint table occurrence, but with the match found sets option to pull up the matching data point records on the above described layout.

                 

                      C) Sort the records by datapoint_source

                 My use-case here is where users are looking at a "cluster" of observation records and want to see a count of the unique "source" of the observations AND details about the source of the observations. For example, if the sources were tagged with demographic data or something like that (this happens through a table of tags and a join table, they would want to be able to see how many occurrences there were of various categories within the cluster. So if there were 10 records in a cluster, how many males/females were there, how many people from each state in the USA, etc.

            • 3. Re: Number of problems related to counting records based on relationships
              philmodjunk
                   

                        Both of these things seem to be doing what I want. Thank you. Is there a way I can create a portal to these results that can be displayed on the main user layouts?

                   Nope. That would require a different approach. You can't put sub summary layout parts in a portal. You could, however, set up a script to do this that pops it up in a new window when a button is clicked on that layout.

                   For a "different approach" see this thread: FMP 12 Tip: Summary Recaps (Portal Subtotals)

                   

                        My use-case here is...

                   And so we have even MORE data in MORE related tables--that you didn't tell me about...

                   I can still read that description more than one way. I suggest posting an example of what this report might look like.

              • 4. Re: Number of problems related to counting records based on relationships
                ShaneB

                     I attached the database photo above, which is still current, if that helps.

                     Here are some types of things I imagine being in the report:

                     What I am looking for in the report is for all the different unique values in the field to be displayed, and then a count of the number of times the value is used, but for the currently displayed found set. Users want to be able to see information about groups of records.

                     What would be even MORE ideal is to be able to print a report of this data for all "cluster" observation records without having to do it one-by-one. So if there are 10 cluster records in the database, the report would break down each cluster into the example data above. I can currently use GTRR to show the found set of observations linked in a cluster, but doing this for reports might prove tedious.

                • 5. Re: Number of problems related to counting records based on relationships
                  ShaneB

                       I've been trying to figure this out, to no avail. Any assistance would definitely be appreciated.

                  • 6. Re: Number of problems related to counting records based on relationships
                    philmodjunk

                         Presumably, your report example shows data from 6 records. Correct? and that would be 6 records from Datapoints?

                         And is the number in the far right field the count of all records in Observations that link to that specific DataPoints record?

                         If so, you can base your layout on DataPoints and a calculation field: Count ( Observations::__observation_id_pk )

                         would return that count.

                         If it's possible that the join table links up the same datapoint and observation record pair more than once, then you would either need to take steps to prevent that data entry error or use an ExecuteSQL calculation fields with the "DISTINCT" keyword as part of the SQL query.

                    • 7. Re: Number of problems related to counting records based on relationships
                      ShaneB

                           I meant there would be any number of records being displayed. I would want to be able to "look into" and summarize fields such as Datapoints::datapoint_source_detail1 and Datapoints::datapoint_source_detail2 while displaying a found group of X number of observations that are joined to datapoints via the join table ObservationsDatapoints.

                           So, in a real life example, Datapoints have fields such as Datapoints::datapoint_source_detail1, which might store the values Male or Female, and Datapoints::datapoint_source_detail2 might store the values High, Medium, or Low. I'd like to be able to show a count of how many times High, Medium, or Low appears in a found set of Observations, or Male or Female occurs in a found set of observations. This will allow users to see important details about the data that makes up found sets of Observations.

                      • 8. Re: Number of problems related to counting records based on relationships
                        philmodjunk

                             But your example shows one datapoint value per row. If your report lists observation records instead of datapoint records, this will not be possible, especially with one field recording Male or Female and another recording "high, medium or low". Once your relationships are in place and from the context of datapoints, you can reference summary data pulled from the related groups of observations records linked to each data point. Count was my explanation that fits what you showed in your example, but any number of different totals, counts, averages, max/min values,standard deviation values, etc. are possible with either an aggregate function defined in DataPoints or as a summary field defined in observations.

                             

                                  So, in a real life example, Datapoints have fields such as Datapoints::datapoint_source_detail1, which might store the values Male or Female, and Datapoints::datapoint_source_detail2 might store the values High, Medium, or Low.

                             That doesn't sound like a workable structure for your datapoints table. I recommend splitting them up into individual records so that each datapoint record is of a specific "type" (Male, Female, High, Medium, Low, etc.). Then the report you are describing becomes much easier to produce.