Highligting duplicate records in a grid view

I have an object called CLIENTS, with 2 fields CLIENT NUMBER and CLIENT NAME.

For various reasons, we cannot legally prevent the same person from creating multiple CLIENT records, which means I cannot require the CLIENT NAME to be unique. This leads to the circumstance where JIM SMITH might have 3 CLIENT records numbered 101, 443 and 876, for example

I am looking for a way to highlight the existence of duplicated names, so the operational staff are aware the person has more than one client account.

I was hoping that I could just provide a grid of all clients, grouped by NAME, and then find a way to remove any group that has less than 2 rows.

Does anyone know a way to achieve this?

You could link the table to itself and get a count of the “child” table. Then filter on Count>1

I am confused, client , person, staff? person = user ? clients = person creating clients? Staff = Admin?

Hey @LeighBerre96926,

You could use a pivot table to group by client name, but Knack doesn’t let you sort highest to lowest count, which makes it hard to determine the duplicates in a sea of clients.

Combined with a pivot table, you can use this small snippet of code to remove all pivot table rows that have a count of less than 2.

Replace view_xx with your pivot table’s view.

$(document).on(`knack-view-render.view_xx`, function (event, view, data) {

  // For each row in the pivot table...
  $(`#${view.key} tbody tr`).each(function() { 
    const calc = parseFloat($(this).find('.kn-pivot-calc').text().trim()); // Get the count field value
    if (calc < 2) {
      $(this).remove(); // Remove the row if the count is less than 2
    }
  });

});

This turns this:


Into this:

Hope that helps!

1 Like

Thanks Stephen, I’ll give it a shot tonight.