1 2 3 Previous Next 30 Replies Latest reply on Jan 12, 2012 4:37 PM by denno

    reporting on Yes/No field

    denno

      Title

      reporting on Yes/No field

      Post

      I'd like to report on a field that records responses as Yes or No (radio button set). I'd like to be able to display the percentage of responses answered Yes/No and am drawing a blank on how to do that.

       

      Thanks–

        • 1. Re: reporting on Yes/No field
          philmodjunk

          Do you want something like this for your report?

          25% Yes
          75% No

          If so, use the "fraction of total of" type summary field and format it as a percentage

          Place the radio button field and this summary in a sub summary part "When sorted by" your radio button field.

          Remove the body layout part.

          Find the records you want in your report and sort them by your radio button field to group into groups of yes and no responses.

          This can also be done via a relationship using a related table with one record for each radio button field.

          • 2. Re: reporting on Yes/No field
            mgores

            Show all records

            Set var $$total = get (found count)

            Find Answer = "Yes"

            Set Var $$yes = get (found count)

             

            Then % Yes = ($$total/$$yes) * 100

            % No  = ($$total/($$total - $$yes)) * 100  or 100 - % Yes

            • 3. Re: reporting on Yes/No field
              LaRetta_1

              Hi Denno,

              First the calculations.  Go to field definitions and add a new field called sCountYesNo (type is summary and count the yes/no field).  Also create a calculation called cPercentYesNo with calculation (result is number):

              GetSummary ( sCountYesNo ; YesNo field ) / sCountYesNo

              Now create a report layout.  Include sub-totals.  Select your yesno field and any other standard fields you wish in the report.  Next, organize records by category and select your yesno field.  Next, it is important that the report be sorted on this field.  Add any other fields you wish to sort in the body of the report as well.

              Specify Subtotals:  Specify your summary field, then determine whether you want it above or below your detail records, then click Add Subtotal.  Specify grand totals if you wish.  Then choose your theme and headers/footers.

              Almost done

              Create a script ... FM will ask if you want it to create a script for you.  Say yes. 

              When done, you will see a report which groups the yes records together and counts them.  Now, place your cPercentageYesNo field in the trailing sub-summary.  In Inspector > Data and below in Data Formatting section, specify Percent as the format and fixed number of decimals (0 produces full percents).

              • 4. Re: reporting on Yes/No field
                denno

                @Phil

                 

                use the "fraction of total of" type summary field


                I tried this but my Yes/No field isn't selectable--I'm assuming that's because it's a text field??




                This can also be done via a relationship using a related table with one record for each radio button field


                Sorry--I'm not fully understanding this approach. How would i set that up?


                • 5. Re: reporting on Yes/No field
                  philmodjunk

                  I tried this but my Yes/No field isn't selectable--I'm assuming that's because it's a text field??

                  Yeah, I realized that after LaRetta posted her suggestion. Her method should work for you. To use Fraction of total, you'd need to add a number field with a 1 in it for every record or a calculation field that returns the number 1 and refer to it with the fraction of total summary field. Then the fraction of total method can be used as I described.

                  Given the extra stuff you have to create, I wouldn't use the relationship method unless I had a specific reason to do so. You'd create a table with two records and a text field, ButtonValue, one with Yes in its button value field and one with No in the field. Then you can define this relationship:

                  YesNoTable::ButtonValue = YourTable::YourRadioButtonField

                  Now you can use a summary field from YourTable to count the records and place this in a portal or layout that is based on the YesNoTable. A count function in a calculation field defined in YesNoTable can also compute the counts.

                  • 6. Re: reporting on Yes/No field
                    denno

                    Thanks for all the help!

                     

                    If I want to include other yes/no fields in this report layout, can I just create sCount/cPercent fields for each of them, then add additional trailing subsummary parts for each question and sort by all of them?

                    • 7. Re: reporting on Yes/No field
                      philmodjunk

                      I think you need to describe what you are doing in a bit more detail.

                      Can you post an exammple?

                      What does the report need to look like?--as you can't just sort on a single field anymore to group them by yes and no values.

                      • 8. Re: reporting on Yes/No field
                        LaRetta_1

                        Denno said, "If I want to include other yes/no fields in this report layout,"

                        I may be wrong but, whenever I see indications of several yes/no fields, it smacks of incorrect structure.  Are those yes/no fields questions?  If so, it is best if they are records in a related table.  If left as is, reporting will be nightmare (which I believe you are already experiencing). 

                        • 9. Re: reporting on Yes/No field
                          denno

                          The database is set up to capture responses to a patient satisfaction questionnaire that is completed by an interviewer in FileMaker Go. There are 7 different yes/no questions. Each of those fields is set to display a radio button set with values from a list that contains Yes and No.

                           

                          Should I use a different structure for this?

                          • 10. Re: reporting on Yes/No field
                            philmodjunk

                            Yes, each response to a question should be recorded as a separate record. Then you can group response questions by Question, then by response to report a count (or percentage) of responses for that question/answer combination.

                            • 11. Re: reporting on Yes/No field
                              denno

                              Ah--so instead of creating a layout that looks like a form with all the responses in the same table, it should be set up so that each question is in its own table?

                              • 12. Re: reporting on Yes/No field
                                philmodjunk

                                A typical Survey Database looks something like this:

                                Respondants----<Responses>------Questions>---------Surveys    (>---- means "many to one")

                                and the Surveys table may or may not be needed, it allows you to manage multiple surveys--each with a different set of questions--from the same database.

                                When a Respondant takes the survey, the system creates one new record in Respondants to collect data about that one respondant. This could be nothing more than a serial ID or any number of fields to collect contact and/or demographic data about that Respondant. The system then pulls up the set of Question records in Questions and creates one Responses record for each, linked by RespondantID to Respondants and linked to questions by QuestionsID. The person taking the survery sees these records as a list with a question associated from Questions with each. They record their responses there (This list can be in a portal or can be a list view layout.) And you create your summary report on the Responses Layout where the separate record structure allows you to group responses first by Question, then by response to get tabulated results.

                                • 13. Re: reporting on Yes/No field
                                  denno

                                  Thanks so much for the help. I found an example file but I'm still a little unclear on the details of how to set this up.

                                   

                                  I created 3 tables with the following fields in each:

                                   

                                  Respondents...RespondentID

                                  Questions...QuestionID, Question

                                  Responses...RespondentID, Question ID, Response

                                   

                                  I then created a relationship between Respondents and Responses based on RespondentID and between Questions and Responses based on QuestionID.

                                  I created a portal in Respondents that displays the questions. When I create a new record in Respondents, the questions don't display unless I manually go to Responses and enter respondent ID & question ID. The sample file has a "create new respondent" script that adds a new record but I don't see where it creates the responses. What am I missing here? 

                                  • 14. Re: reporting on Yes/No field
                                    philmodjunk

                                    All you are missing is a script to generate the response records automatically. See this thread for an example script:  Need aid on generating a report from a survey layout.

                                    1 2 3 Previous Next