12 Replies Latest reply on May 27, 2011 9:14 AM by philmodjunk

    reporting on value list choices

    denno

      Title

      reporting on value list choices

      Post

      [this is an update to the original thread at http://forums.filemaker.com/posts/fb19124823]

      PhilModJunk had presented 2 options-

      A couple of options come to mind. The simplest is a series of calculation fields. Say conclusion 1 is the text "Outstanding".

      Not IsEmpty ( FilterValues ( YourTable::YourCheckBoxFIeld ; "Outstanding ) )

      will return a 1 in all records where "Outstanding" was selected.

      You could one such field for each value and summary fields could be used to sum up the numbers for each value selected.

      A more sophisticated option, is to replace your checkbox field with a portal with a single value check box in each portal row. You'd need to script a process for creating the portal records with each new record created in your Review table, but with that working, you could generate reports and graphs on layouts based on this portal table where you could sort the records to group them by conclusion--which can make for more flexible reporting and charting.

      ---

      I am trying the 2nd option and am able to get the portal and script set up but I can't figure out how to put the single value check box in each portal row. Each row in the portal is showing the value list items from the related table but when I try to change it to a checkbox set, I only have the option to select a value list.

      Thanks-

        • 1. Re: reporting on value list choices
          philmodjunk
          1. Define a custom value list with 1 as it's only value.
          2. Make this value list the value list you select for your check box field.
          3. Resize the checkbox field so that only the check box is visible.
          4. A text field in the same record is used to display the value that the user is selecting when the click this check box.
          • 2. Re: reporting on value list choices
            denno

            Thanks. Just need some guidance now on setting up the report. I'm experimenting with a value list that has 2 choices--ChoiceA and ChoiceB.

            I created 3 records in the "review" table and ran the script for each and made selections. In the related table with the value list entries, I now see 2 records for each entry in the Review table. So there's a total of 6 records in the values table--ChoiceA and ChoiceB for each ID.

            If I select the "1" for ChoiceA in Review, the related record shows as "ChoiceA 1". Is that correct? 

            I tried creating a field to sum up the value list field but was unsuccessful--If ( value name = "ChoiceA" ; Sum ( value name ) ; 0 )

            • 3. Re: reporting on value list choices
              philmodjunk

              All you need is a summary field that computes the "total of" your check box field.

              In a report based on your portal's table, you can add sub summary parts "when sorted by" the value name field and you can put the value name field and the summary field in this sub summary part.

              You can remove the body part from this layout as you don't need it.

              Now sort your records by the value name field and you'll see a report with a total for choice A and a total for Choice B.

              • 4. Re: reporting on value list choices
                denno

                Something's not right--

                I created the summary field and it is displaying the correct sum for all selections when it's on the regular layout with the value list records. (It displays 4 which is the total of the combined ChoiceA and ChoiceB selections.)

                I created the report [sorted by value name] with a [sub-summary by value name] that includes the value name and summary fields. The report only shows "ChoiceB 1" and "4" though.

                The value name records show as "ChoiceA" or "ChoiceB" when no selection was made in the Review table and as "ChoiceA 1" or "ChoiceB 1" if they were selected. Is that right?

                Thanks again for the help.

                • 5. Re: reporting on value list choices
                  philmodjunk

                  Are the records sorted?

                  Sub Summary layout parts are will not be visible unless you sort the records by the "sort by" field selected when you created the sub summary part

                  You need two fields in your records, one for the check box field, where a 1 is entered or cleared when you click the check box and the other is a text field where you enter the name of that value. From your last few posts, I'm not sure if that's what you set up or not.

                  • 6. Re: reporting on value list choices
                    denno

                    Got it now--didn't have 2 separate fields. Thanks so much!

                    • 7. Re: reporting on value list choices
                      denno

                      One last thing I'm stuck on--I'm creating a chart to display the results and can't get the sorting right.

                      The fields I have are value name, counter (populated with 1 when selected), counter sum (=total of counter) and counter summary (=GetSummary ( counter sum ; value name ).

                      I created a bar chart that displays counter summary on the X-axis using data from current found set with data points shown as groups. When I sort by value name the chart displays the data correctly.

                      I'd like to display the chart with the counts displayed in descending order. I've tried putting the chart in a subsummary part but that doesn't work.

                      Thanks-

                      • 8. Re: reporting on value list choices
                        philmodjunk

                        No need for the calculation field with GetSummary.

                        When you sort your records, use the "Reorder based on summary field" option and select your Counter Sum field.

                        • 9. Re: reporting on value list choices
                          denno

                          beautiful--thanks!

                          • 10. Re: reporting on value list choices
                            denno

                            One thing I forgot about...when I had the value lists in a checkbox field, I had an email that sent out the values that had been selected. I need to be able to do the same thing with this new structure (i.e., values in their own table.) Is there a way to list the values that were selected for each record? I was thinking I could use an if statement looking for a value of 1 in the counter field but I haven't been able to set it up.

                            • 11. Re: reporting on value list choices
                              philmodjunk

                              You can use the List Function to return a list of value names:

                              List ( relatedtable::valueName )    (Listed value names are listed with returns separating each name)

                              or

                              Substitute ( List ( RelatedTable::valueName ) ; ¶ ; ", " )  (turns the "vertical" list into a horizontal list with names separated by commas.)

                              • 12. Re: reporting on value list choices
                                philmodjunk

                                Hmm let's rework that idea a bit. After posting, I realized that this would list all values, not just the selected values.

                                You can either define a relationship that links by ID and by the value 1 to get the above calculation to work or you can define this calculation in the related values table:

                                If ( checkBoxField ; ValueName )

                                And use this calculation field's name in the List function.