Rank Users' Multiple Abilities

[El_mV]

Hi all,

Right now, I have one base with three tables.

Table 1, Column 1: Person’s name
Table 1, Column 2: Ability

I was able to link Ability and and Name in Table 2 and came out with

Table 2, Column 1: Ability
Table 2, Column 2: All people that have the ability in Column 1

Now, I would like to rank each individual’s ability on a rating system from 1-5. There doesn’t seem to be able to transfer data from the other tables in order to individually rank each skill a person has. I’ve tried grouping by abilities in Table 1, but that just gives me multiple skills within a group instead of individual.

For example:

Ashleigh is has skills in knitting and gardening. Her knitting skill is a 3, her gardening is a 5.
Ryan also has skills in gardening but also in martial arts. Their gardening skill is a 2, their martial arts at a 4.

I enter both their name and talents in Table 1, Column 1 and 2 respectively and then link Abilities to Table 2. I can now see that both Ryan and Ashleigh have skills in gardening. Now, I want to rank them individually on each skill; here is where I am getting stuck. If it’s one skill, it’s fine, I just add a Column in Table 1 for ratings. But what if they have multiple skills that I want to rank individually?

I try to group by ability in Table one and the groups turn out to be “gardening” and “gardening and martial arts” instead of having one ability per group.

It seems as though I have to create a Table 3 and manually enter all the abilities, followed by manually the names and then rank them. Is this the workaround or is there another way to configure the table so I just have to add rankings and not have to be redundant in names/abilities?

Thanks!

Photos are attached for visuals.

TL;DR: How can I enter multiple abilities for a user and then individually rank each ability that user has?

[Justin_Barrett · Accepted solution]

Welcome to the community, @El_mV! :grinning_face_with_big_eyes: Your setup is pretty close to what you’re seeking. I would rename your [Master] table to [People] and not put their skills in that table. Only include any other personal information you’d like to track (phone number, email, etc.).

The [Ranking] table would be set up slightly differently, making it into a junction table that connects the other two. Add a {Person} link field between the primary field and the {Ability} field. Make the primary field a formula that combines the name of the person with the ability that you’re ranking.

With those changes, your tables look like this. First the [People] table:

Next the [Abilities] table:

And finally the [Ranking] table:

On the [People] table, if you want to see a more condensed list of just their abilities without the name cluttering things up, add an {Abilities} formula field:

SUBSTITUTE(ARRAYJOIN(Ranking), Name & " - ", "")

[El_mV]

Hi @Justin_Barrett - thanks so much for your response.

I’m just stuck on something - for the [Ranking] table, I cannot seem to get the [People] table to link. Do I have to enter their names manually first or is there a step I’m missing?

EDIT: I linked them, but had to manually go in and select which person for which ability - is that how it is supposed to work? Also, when I go to join Person and Ability with Concatenate, it appears as “Name” - Ability

[Justin_Barrett]

El_mV:

EDIT: I linked them, but had to manually go in and select which person for which ability - is that how it is supposed to work?

Yes, that’s how a junction table works. You’re making a connection between data stored in other tables. Rather than manually entering someone’s info on both the [People] and [Rating] tables, you enter it once on [People], then use links to pull it in anywhere else it’s needed.

El_mV:

Also, when I go to join Person and Ability with Concatenate, it appears as “Name” - Ability

You’re getting quotes around the name? That’s odd. Here’s the formula I used:

Person & " - " & Ability

The ampersand symbol is a shortcut way of connecting pieces without using the CONCATENATE function.