Create ranks based on collection ID's

Ask for Help
1

Hey there,

I’m building a tool where users can submit speed runs for Lego builds.

I’ve got a Set table, a user table and a time table (for submitting times).

What I want to achieve is a ranked leaderboard for each Lego set. I followed a tutorial from @Robert_Petitto but it didn’t show how to create a rank exclusive for each collection row ID.

I’ve attached a screenshot to show where I’m currently up to. What I’m missing is the Rank/Position for the users. It’s currently showing Time taken, User name and date of submission.

Many thanks,

Mark

Screenshot 2025-09-26 at 01.36.45
Screenshot 2025-09-26 at 01.36.45618×1298 139 KB

2

Hi mark, a few questions:

  1. are you using big tables or normal tables?
  2. do you have the time taken value? is it in minutes or seconds?
  3. If you do use a glide table, why not just sort your colleciton via the time taken (A-Z)? fastets comes first on the leaderboard this way
3

Thanks for your reply.

it’s a standard Glide table.

I’m storing it as seconds at the moment.

it is currently ordered by time A-Z.

What I’m looking to achieve is showing a rank for the order. 1st, 2nd, 3rd etc.

The trouble I’m having at the moment is that I don’t know how to structure the database to provide ranks filtered by the set ID. If I build an array of ranks it does it across all set ID’s

4

I can show you a way to do this. Can you show me a screen shot of your data table, so I can refer to that?
Do you need to handle ties?

5

Ah fantastic.

Screenshot 2025-09-26 at 11.08.00
Screenshot 2025-09-26 at 11.08.002318×806 225 KB

Here’s the the table for the submitted times. I basically want to create a rank for each set ID based on ascending time. There will be multiple submissions per set by multiple people.

6

Sorry I missed the question about ‘ties’. I’m not entirely sure what that means.

7

Another question: how do you want to present this in the Layout? Would have a single collection grouped by SetID, or would you have the user select a set and then present the rankings for that set?

Regarding Ties: lets say you have have 2 users equal 1st in one set. Would you want to indicate they are tied (eg. T1), or just show them both as number 1?

Also, I noticed that you mentioned 1st, 2nd, 3rd - is it important to include the suffixes, or would 1, 2, 3 be okay?

8

In the UI I was hoping to display 1st, 2nd, 3rd for a collection grouped by set ID.

Ah ties! That would be a nice stretch goal yeah, I think once I’ve got the basic logic setup I’d like to include logic for ties.

9

Okay, you need 4 columns:

  • Query column. Target the same table, filter by “Set ID is This row->Set ID”. Sort by Time ascending. Ensure multiple match is selected.
  • Lookup column. Target the Query column and select Time. This will give you an array of Time values.
  • Find Element Index column. “Values” should be the Lookup column, and “To find” will be the Time column.
  • Javascript column: Pass the output of the previous column as p1, and use the following code:
let m = (p1+1) % 10;
let suffix = m === 1 ? "st" : m === 2 ? "nd" : m === 3 ? "rd" : "th";
return `${m}${suffix}`;

Those 4 columns should look something like the below:

CleanShot 2025-09-26 at 19.55.13@2x
CleanShot 2025-09-26 at 19.55.13@2x900×612 70.4 KB

In the Layout, group your collection by Set, and Sort by Time.

2 Likes
10

Thanks! I’ll give that a bash and let you know how I get on!

1 Like