Rank records based on field

Ask the Community Get Answers
1

I am looking to get an object and rank all the records based on a field (there will be 4 rankings across 4 fields). I need it recorded in the record though so that when the client logs in I can show them their ranking without showing any other records.

Since I only have 14 records I am currently doing this manually but it would be nice to have some equation or process where I can automate this.

I have seen the previous question for this Ranking Based on Results but it is 4 years ago and also seems to indicate you show all results.

Thanks in advance.

2

I may not understand correctly here, but can you create a field in one of the objects that uses conditional rules based on the other records to create your desired rankings?

3

Hi @AndrewSein56834,

Just had a look into that. Looks like conditional would work in a case where you are checking against another value in the same record, not the same value in other records. If that makes any sense.
Maybe it can but I am not seeing it.
Couldn’t see any way for it to give it a rank 1 if it was the highest number in the field, 2 if second highest etc down to 14 for the lowest value.

Thanks for the advice, will keep trying.

4

Hi @Adrian_vD

I have a question - is it a single rating you are looking to rank (say an overall score based on the total of the 4) or are you looking for each rating to be ranked separately?

In order to do this with Knack (or any database really) you will need to have a calculated value in each record with the ranking - and for rankings this is a comparison against all other records in the object. This means that if any one record is edited ALL the records must have their rankings recalculated.

I have a potential solution but it would be dependant on the scale of the system you will be running - probably NOT workable if there are hundreds of records!!!

This would be to trigger a Make (formerly Integromat) process when a record is updated which would:

  1. Use a Search for Records module to read all the records sorted by the field you want to rank.
  2. Have an Update Record module update each record found with their Ranking number (you could use the “Bundle Order Position” value from the list of searched for records for this).

The viability of this approach would depend on the number of updates and the number of records.

If the numbers are higher or the frequency of updates to the records is high then a slight modification would be to run this periodically (rather than a triggered update each time a record is updated). This could be timed according to how up to date you need the rankings to be.

If you have 4 separate fields that you need to rank separately, then you will need to calculate them all separately.

Julian

5

Hi @JulianKirkness,

Thank you for the response. I thought Make might be the solution, I had never used it before but did sign up yesterday thinking the answer is there. With so many options, I wasn’t sure what my next move was. Thanks for giving me a place to look.

Yes, there are 4 different rankings for each record but there are only ever going to be 10-15 records.