YOu appear to be describing these relationships: (--< means one to many )
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:
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.
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.
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.
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?)
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:
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...)
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?
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.