7 Replies Latest reply on May 23, 2012 8:40 AM by philmodjunk

    Many-to-Many Reporting - HELP!

    Sealander

      Title

      Many-to-Many Reporting - HELP!

      Post

      Thanks to anyone reading this. I have a many-to-many relationship that I have no idea how to report on. I'll use a simpler example than what I'm really doing that has the essence of my problem. 

       

      I have users evaluate songs. Each evaluation is a record in an Evaluation Table, with an auto-serialized EvaluationID primary key. In the entry layout, I use two portals. The first portal allows users to categorize the song (e.g. Country), with possibly multiple categories (e.g., Country, Western) with each category its own record in a Categories Table related to the Evaluation Table by the EvaluationID. The second portal allows users to give feedback on the song (e.g. Too Twangy, Awful Lyrics), with also possibly many feedbacks with each its own record in a Feedbacks Table related to the Evaluation Table by the EvaluationID field. 

      What I need to do is calculate things like "For each Category, such-and-such feedback was given x% of the time". For example, "For 'Country' songs, 'too twangy' was given 70% of the time and 'awful lyrics' was given 100% of the time" and maybe make a bar chart or something. 

       

      What I was trying to do is report on the "Feedbacks" table, but if I try to include the category field from the "Categories" table, it only picks the first category associated with the EvaluationID its related by (and there are possibly many). Any ideas? I think if could somehow create a new table that somehow joins the Categories Table and the Feedbacks Table I could just report on that. Is that something I can do as a table occurence? If that's what I should be doing, I have no idea how. THANK YOU!

      Mike

       

       

        • 1. Re: Many-to-Many Reporting - HELP!
          philmodjunk

          YOu appear to be describing these relationships:   (--< means one to many )

          categories>-----Evaluations-----<Feedback

          This would indicate that you may have "country" in many different records in the categories table each of which link to only one evaluation record.

          You could script the process by performing a find on the categories table for "country", then use go to related records with the match found set option to pull up all feedback for all evaluations in that category. These could then be sorted by feedback with summary fields and sub summary layout parts giving you a breakdown as to how frequently each feedback item was selected for that category.

          This could produce a report that looks like this:

          Category: Country

              Too twangy 20%
              Awful Lyrics 100%

          but it would only be for one category at a time as the same feedback record could be linked to more than one category and this method doesn't allow you to count the same record twice.

          • 2. Re: Many-to-Many Reporting - HELP!
            Sealander

            Phil, 

            That is exactly my situation: Categories>-----Evaluations-----<Feedbacks

            Thanks for responding. I think I followed you and will give it a try. Is there any way for this to happen to each category automatically in one layout? For example, is there a way to get all Categories and Feedbacks listed in one "table" (layout?) that I could sort and report on by category? 

            Thanks again so much. 

            • 3. Re: Many-to-Many Reporting - HELP!
              philmodjunk

              Not easily given the structure that you have--at least not as a summary report as that would require that the same feedback record appear in more than one place in your report if it is associated with more than one category.

              • 4. Re: Many-to-Many Reporting - HELP!
                Sealander

                THAT'S what I want.

                "that would require that the same feedback record appear in more than one place in your report if it is associated with more than one category"

                There are a BUNCH of categories and so I would really rather not have to perform a find on each category first, if it's possible. Can it be done with a table occurrence? I'm not sure I really understand them and could therefore be way off. (Also, what is the "X" operator when defining a relationship?)

                Thanks!

                Mike

                • 5. Re: Many-to-Many Reporting - HELP!
                  philmodjunk

                  There are a BUNCH of categories and so I would really rather not have to perform a find on each category first

                  A single find script from a layout based on your categories table or a single find script that uses a value list of categories in a global field where the user selects a Category or Comment can make this happen very simply for the user.

                  I have to point this out here as the alternative is far from simple. I can't think of a way, (though maybe someone else can) where just adding another table occurrence or two or three will solve this. It looks to me like you need a true join table between categories and comments.

                  That table would look like this:

                  category
                  Comment
                  EvaluationID

                  Everytime you'd add a category in the category portal, a script would need to create one such record in this join table with the selected Category, the current Evaluation ID and there'd need to be one such record for every comment. In our example, you'd need 4 records:

                  Country  | Too twangy  | Eval #1
                  Country  | awful lyrics   | Eval #1
                  Western | Too Twangy  | Eval #1
                  Western | Awful lyrics   | Eval #1

                  From such a table, you no longer have the problem of putting on record in more than one place in the report and now you can sort and group to get the counts you need.

                  What's tricky, is setting up the scripts that correctly maintain this table as data changes in the two portals. You have to handle all of the following editing actions with scripts to keep things up to date:

                  Deleting a portal row (matching records in join table must be deleted)
                  Adding a portal row (see above)
                  Changing a category or comment (Delete records for old value, add records for new value...)

                  • 6. Re: Many-to-Many Reporting - HELP!
                    Sealander

                    Here's what I'm thinking of doing:

                    There are fewer 'feedbacks' than 'categories' so in the Feedbacks table I added calculation fields for each feedback that calculated 1 or 0 (e.g. 'TooTwangyCalc' = 1 if 'too twangy' was the feedback. Then in the Categories table I added a calculation field for each feedback that summed all of the related records in the Feedbacks table for each feedback and compared it to 0 (e.g. 'TooTwangyCalc' = (Sum(Feedbacks::TooTwangyCalc)>0)  ). Then if you run a report on the Categories Table, you can include the TooTwangyCalc field from that table in the report and sort on it. 

                    All of that works, but now I have a different problem. I can use the Categories::TooTwangyCalc field to calculate the percent of each category that contains that feedback. But I also need to resort based on those percentages (e.g.highest percentage to lowest) and to do that the 'resort' field needs to be a Summary Field, right? If so, I need to use the 'Fraction of Total' Summary Field, but I can't make that work. It is calculating for each record 1 or 0 / (number of records in given category). Any ideas? 

                    THANK YOU. 

                    • 7. Re: Many-to-Many Reporting - HELP!
                      philmodjunk

                      Fraction of total should calculate a value for either the whole found set or a specific group (if you put it in a sub summary part and sort to group by the sub summary's specified "break" field).

                      If you want to sort on that field but also arrange the groups in ascending or descending order by the summary field's value, use the option: "Reorder based on summary field" found in the bottom of the Sort dialog.