is there a way to colour rows in a report if they have the same value in one field?

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • Answered

I have a report that show all the claims. I want rows with same Claim ID number to get colorized so users are aware there is a duplication.  any ideas how to achieve that?

Photo of EH


  • 282 Points 250 badge 2x thumb

Posted 5 months ago

  • 0
  • 1
This is not easy to do.

If you want to prevent duplicates, then you can set the field for claim number to be Unique. But that will block the Save, which may not be your intention.

I have one app where I force the user to indicate in another field that they know it’s a duplicate, then use a formula field to be unique. The formula evaluates to null if that checkbox is checked, this allowing the Save.

If you really want to achieve what you asked, you would initially populate a new table of Unique Claim IDs where the key field is set to Claim ID, using a summary report and copy to other table.

Then set a field in that table to be a formula checkbox field called [Claim ID exists?] set to the value

Then lookup to the records based in a relationship on Claim ID. Then fire an Automation to add a Claim ID when the record is added or modified and the lookup of [Claim IDexists?] is unchecked.

Then finally, a summary count if the #of records and lookup down to records.

Then colorize based in that count > 1