Efficient ways to manually rank records

Question

I've received a request for staff to manually rank their top prospects for soliciting donations (a mix of Contacts and Accounts) so that when they meet periodically with their supervisor they can together look at these top prospects.

It makes sense to me that I need to have a new object that combines both Contacts and Accounts in one list so that a list view can be created for ranking. My initial thought was a checkbox on both Contact and Account, that when checked will automatically create a mapping record that has a lookup to either an Account or Contact and some formula fields to pull in name and some other key identifying fields. Let's call this a Prospect Ranking record. Staff could go through any of their Contacts or Accounts and check this box. Next they would go to a list view of their Prospect Ranking records and could enter a numeric value of 1-20. It would be awesome if I could find a way to do drag and drop to rank these but so far have not found anything. I've been explicitly told that the ranking numbers will not be sequential within one object, meaning 1 could be a Contact, 2 an Account, 3 an Account, 4 a Contact, etc. and that ordering across object needs to be retained.

If I can't drag and drop, users could enter in numbers but it would be unpleasant when they want to shift a whole bunch of records up or down by one to insert a record into the middle of the rankings. I could add a bit of automation that could shift the ranking value for records with a higher rank than a number being inserted, but I imagine this would cause problems if someone edited multiple values in one batch within a list view. Does anyone have other ideas of how I could proceed?

Answer 1

TL;DR

Ideas to consider:

• Use Campaigns, enabling Account membership in Campaigns
• Use a numeric field for ranking, use coarse values for ranking (e.g. in the 100s)
• Add Flow/Apex code to rebase the scores

Full answer

Consider using Campaigns (which can be enabled for Accounts) to be able to inspect Accounts and Contacts (and Leads, if you need/want) in the same list view.

Think whether the ranking be user-specific, occasion-specific, or universal. Here "universal" means the same for all users, with a single score for each record, regardless of the marketing/sales action that is planned.

• If the score is universal, create a Score field in Account, Contact and Lead, and create a formula in Campaign Member to look up the value of Score based on what record the Campaign Member lookups point to.

• If the score can be campaign-specific (which can mean user- or occasion-specific), create a Score field in Campaign Member. You should be able to edit the ranking scores from the table of campaign members.

To make ranking easier, make the score relevant for the purposes of ordering and give the users the ability to assign any arbitrarily large number, so that the scores are initially 100, 200, 300, so that if we need to add someone to be ranked second we can give them a score of 150, for instance. You could also throw decimals into the mix to achieve a similar effect.

If you want to be really fancy and your stakeholders have a certain degree of OCD-ness about the score, write some Apex or Flow to change the scores of all the affected records so that the ordering is preserved but the scoring is evenly spaced.