Tally scores for several items across many categories

I have a small/fixed number of items that I’d like to rate across a large/expanding number of categories and display a weighted average of those ratings in a separate view. I have this mostly working, but the number of kludges and hidden fields is expanding so rapidly that I suspect my basic architecture is wrong. Is there a better way than this?

This is how I have things set up:

  • each item has its own table
  • categories are records with ratings (as single select of -3 ... +3)
  • to use the same set of categories for another item, I duplicate the table and rename it
  • weights are a separate table of decimal values 0...1 (keyed by category name), I link the categories to these by adding a hidden formula column ={Name} then coercing it into a link to the weights table (this already feels pretty kludge-y)
  • bring weights back into the item table with a lookup field, calculate weighted value of the rating as another field
  • in a Summary table, link all the category records (manually, ugh) to a summary record, then use a rollup with AVERAGE
  • now I’m stuck, since a field can only link to one table and my items each have their own

Conceptually I think the big issue is that I want to represent a comprehensive “all to all” relationship. I can solve some of my issues by putting all the item-category pairings into a single table and creating views for each item, but manually creating every relationship (and updating them as I add more categories) is quite daunting.

Is there a better way?

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.