How would you approach storing rating data of multiple users to 1 or many (at the moment around 100) skills?

Hey Community,

i am currently building an internal app for my company, where employees can find another employee by skills based on their experience level in respective skills. For that i need to make every users rating publicly available. So far i could only do this by adding a column for each user in the skills table but that way, i can’t automate the process, can i?

So i’ve set up the following glide tables all with ROW IDs and relations:

  • Users
  • “Public” Profile (Thank you @Robert_Petitto for your VERY helpful videos, almost watched them all now)
  • Skills
  • SkillCategory

A User Specific Column (for e.g. userRating) does not work here, as i can’t make that accessible by other people. So my set up right now is petersRating, timsRating, laurasRating etc.

Any ideas on a smarter way to approach this?

Does each skill have a numerical rating?

1 Like

if you wanna keep track of who rated it… you need to open a new sheet and record each rating, then average ratings for each user by relation to the users sheet

1 Like

Yes, from 0 to 4. Edit: I tried putting it into an array but i can’t figure out how to match the entries (numbers from 0 to 4) in this array, with anothr array (all skills). Every employee can rate all the skills over time, if a skill is not rated yet, i set it to 0 by default.

Hey Uzo, thanks for your reply!

Yes i want to keep track of who rated it (i have that sorted out through relations), what their rating (0-4) for each skill is and have that available for all in an inline list (i’ve set that up already too, but i can’t wrap my head around a smart way to get that data except for having each users rating for a skill (in the skills table, where each skill is a row) in an extra column.

So whenever a new employee starts, i have to set up a lot manually (creating columns in the skill table for the new employees rating for each skill).

I am not sure if i wholefully understand your solution but will try to wrap my head around it. :melting_face: :grin:

How is that rating determined? Is it manually entered or calculated?

All i had was an excel sheet from a workshop we did a couple of weeks ago where there have been around 100 skills to be rated by everyone.

I created the database layout in glide and prefilled everything i had so far. Users can edit their rating for each skill now if they are in the app and are the signed in user visiting the respective detail page of the skill (i used pizzas :smiley:). Also built a form where employees can suggest new skills (they’ll be added to the skills table which has an “approved?” boolean column and a trigger to send a mail to me whenever someone suggests a new skill.

If approved any employee can “claim” the skill and rate it. If they won’t it will not sho up in the overview.

Edit: Attached are 3 Screenshots. The first image shows the Skills-Overview Tab (“7 können das” means “7 people got this skill”. The second image shows the detail page of a skill. In the blue circles i would like to show the skill-rating.

The third one shows the beginning of the skills table. As you can see i have a column for each user there and also a user specific rating column (a leftover for now)

you have a wrong data structure, thats why is so hard to make it right… you need these skills names be columns not rows, and place them in the users sheet…
now there are two ways to set average rating in the columns…

  1. everytime someone give a new rating, you calculate average and overwrite previous record, or…
  2. make these columns dynamic, using rollup column.

the first method is better if you wanna print it, or make PDF report… or see in the Google Sheets

Hey @Uzo,

thanks for your time to dive into this. So if i understood you correctly, there wouldn’t be a more elegant way than adding ±100 skill-columns (that number could change often) to the users table to record the ratings of each user for each skill and make it viewable to anyone in an inline list for example?

I mean the way i’ve set it up is that every user automatically has every skill from the skills table attached to them. That way i can make sure that the total number of skills is always relatable to the users rating (i did a rating column for each user in the skills table, which i also don’t think is the nicest way) and in sync if a new skill is added or a skill is removed (i hope) ;D.

Also i do not need average ratings. I only need to have the rating of a skill by anyone visible to anyone else in the company. 0 = No skill in that area ; 1 = Interested to learn this skill ; … up to 4 = can teach at expert level.

I’ve stored the 0-4 rating and their ‘description’ in an extra table.

Sorry if my ideas sound stupid, i am still learning! :slight_smile:

you have a very interesting problem… both Users and skills are not fixed… both can be added or taken out? by users not by hard design?

The users would be kind of variable (depending on fluctuation ofc). As it’s an internal app only, i could leave non employed users in the table to reduce complexity and and leave out this variable. The skills could change for sure tho.

there is nothing to build stable relations… I need to think about it lol

1 Like

it would be possible in google sheets… but in Glide tables… probably not, or with some extreme workarounds… is your app based on GS?

Can you join arrays in google sheets / excel? Would that be a direction to shift my thinking towards to, to get closer to a concept?

in google sheets, pretty much everything is possible, and if not… it can be forced by scripts… so sky is the limit

1 Like

I am thinking like relating values (rating number from 0-4) from an array (which updates it’s length automatically as it is looking up the skills table row count) with the row number in the skills table to match it and relate this to the user. :open_mouth: Does that make any sense? :smiley:

let’s start from the beginning…

  1. users can create new skills and rate other users’ skills
  2. users can delete skills?

New skills can be suggested by tapping the + button (floating) but not edited or deleted. Suggested skill will be send to the skills table. I got a boolean column in there that won’t be turned true by submitting the form.

I have to change it manually to true.

even more complicated lol…

now… how do you compute skills? sum… average… totals or by specific users?

I personally do not need the approval function but the “stakeholders”… you know the way. :slight_smile:

On computing the skills:

  • no average needed.
  • the only viable information for other employees is: to see what the other employees skill level at a certain skill, for example photoshop, is. All skills are listed and tappable on the “skills tab” together with the information on how many employees own that skill (filtedred by: if skill rating is higher than 0)