6 Replies Latest reply on May 26, 2010 1:26 PM by therin

    Report for displaying number of records with a certain value

    therin

      Title

      Report for displaying number of records with a certain value

      Post

      Hi,

       

      I'm trying to create a report that displays the count of the records whose field contains a specific value. The field that I am trying to get this data from is a radio button set where the user chooses a number from 1 to 6. I want Filemaker to display the count of each option, from 1 to 6, in a report. However, I can't seem to figure out how to do this without creating six new fields, one for counting the totals of each option. Any suggestions?

       

      Thanks,

        • 1. Re: Report for displaying number of records with a certain value
          philmodjunk

          Try a "Count of" summary field in a subsummary part on a layout designed to display a summary report. You can sort by your radio button field to group your records by response and then use the count of summary field to compute the totals in each category.

           

          Here's a link to a simple tutorial on setting up summary reports that you may find useful:

          Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Report for displaying number of records with a certain value
            therin

            Yes, a summary field works fine for counting up the individual answers, but it also requires me to split the sub-summary into a different section for each individual option. I was hoping that I could display the count of every option on the same horizontal line, because in the report I want to be able to print several of these types of fields one after the other (13 to be exact).

            • 3. Re: Report for displaying number of records with a certain value
              philmodjunk

              Hmm, it really helps to spell out all the details of what you want so we don't waste time with suggestions that don't do what you want...

               

              It's possible to do this with a related table, a calculation field with a count () function and a "horizontal portal" of 6 columns.

              In your case, you'd have 6 records in this related table--one for each of the values 1...6 in your value list. Then a relationship such as

               

              RadioValues::Value = DataTable::RadioButtonFIeld can be used with a calculation field in RadioValues defined as Count ( DataTable::RadioButtonField) will compute a different sub-total in each of the 6 records. A horizontal portal is just a series of copies of the same portal set up to each display one row of the portal records(Column 1, displays the 1st row, column 2 displays the 2nd and so forth.) You'd need a second table occurrence and a different relationship so that all 6 records in RadioValues match to your parent record.

               

              "I want to be able to print several of these types of fields one after the other (13 to be exact)."

              Sounds like a survey. In any case, I"d put each of these into separate records in a table and refine the above technique so that RadioValues has 78 records (assuming responses of 1...6 for each) and an additional pair of fields in the "count" relationship to match each set of 6 records to a different one of these 13 records.

               

              RadioValues::Value = Responses::RadioButtonField AND

              RadioValuesQuestID = Responses::QuestID

              • 4. Re: Report for displaying number of records with a certain value
                therin

                I've created a table and related the value and radio button field, but I can't seem to get the portal to display more than one row. I created six records, each with a radio button value. If I select List view, the count of all of the values are displayed properly. Any suggestions?

                 

                Thanks for all the help,

                • 5. Re: Report for displaying number of records with a certain value
                  philmodjunk

                  You need two relationships for thi--one that links your 6 values by value and one that will match to all 6.

                   

                  You use one relationship for your count function and the other for your portal.

                   

                  DataTable::RadioButtonFIeld X AllRadioValues::Value (AllRadioValues is a 2nd table occurrence of RadioValues)

                   

                  is one such possibility. The "X" (cartesian join) operator will match all records in RadioValues to all records in DataTable. Refer to this new table occurrence, AllRadioValues in portal setup... and you'll get a portal that lists all the records in the RadioValues table.

                  • 6. Re: Report for displaying number of records with a certain value
                    therin

                    Thanks Phil, I was able to get the relationships working. Going to try to get multiple field functionality working now.