How To Filter The Summary Column

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
  • (Edited)
Before posting here, I read through QB help on Summary reports and filtering and searched the Community and then the Internet. Here is the problem.

We have a Summary Report that groups a field by equal record values. In the report the count of equal values is displayed as a column named "# of <fieldname>".  How do we filter this field if it is less than or equal to 1?

The goal is to create a report that shows all duplicate records, which we have done. Now we would like to filter the non-duplicate records to reduce the results.

Is this possible?
Photo of Jim Harrison

Jim Harrison

  • 1,770 Points 1k badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,762 Points 50k badge 2x thumb
It's not natively possible.  What is typically done is to make that report and click the column heading for the count to sort from lowest to highest and then click again to sport from highest to lowest and save the report.  Then the dups are at the top.

Unfortunately, that means that you cannot have a true exception report and someone will need to just look at the top of the report to clear the 2's.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 28,304 Points 20k badge 2x thumb
I can think of several ways to do this with script. Here is one way which simply hides groups which only contain one record where I applied the script manually from the console:

Before applying script:



After applying script:




Note that groups for Solyent and Massive Dynamic are hidden as they have only one record per group.

Here is the one line script manually applied from the console:
$("span.TableGroupHeader:contains((1 Record))").closest("tr.ss").nextUntil("tr.ss").andSelf().hide();
There are many ways to do this - I can probably come up with a dozen uniquely different ways to accomplish this using script.

Feel free to contact me using the information in my profile if you wants a shrink wrapped solution:

https://getsatisfaction.com/people/dandiebolt
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 28,304 Points 20k badge 2x thumb
You can paste that code into my test application and see it in action:

All the Dups
https://haversineconsulting.quickbase.com/db/bm6zyc45w?a=td

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=610

There is nothing in the script that references my application so you can use it in your application unchanged and introduce it through BOL or SW.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,762 Points 50k badge 2x thumb
Dan,
Typically what people are looking for is a report of just the duplicates so they can go in and scrub their data. But they would like it to be delivered as an exception subscription report so that if there are no dupes then there is no email received. That is the real challenge.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 28,304 Points 20k badge 2x thumb
>But they would like it to be delivered as an exception subscription report 

Maybe - I would like to hear various opinions. My experience is that it is better to clean up data before it enters a new system. So the ideal place to deal with dups would be to prevent the entry of duplicates during the initial form entry or import. But in reality I have found that it often takes human judgement to reconcile which of two "duplicate" records has the more recent information or which specific fields in the two records are stale.

In any case if users can clarify what they want I am sure there are easy script solutions for detecting duplicate records including accounting for typos.

There is an algorithm called the Levenshtein Distance which can compare two strings to see of far apart they are and which has a very simple implementation. Here are some examples that could easily be applied to duplicate detection of records:

Examples:

(1) "there" and "their" are two letters apart
(2) "general motors" and "GeneralMotors" are three letters apart
(3) "Mark" and "Dan" are three letters apart


Photo of Jim Harrison

Jim Harrison

  • 1,770 Points 1k badge 2x thumb
Well I wrote a really great response but wasn't logged in because I opened the link from email and lost the response when I hit submit and was asked to log on and it's been so long since I have run into this conundrum that I didn't select all - copy, so it's gone. Here's the gist.

I'm using it to remove duplicates from a table before making the field unique. 
Nice ideas from Dan, many thanks!

For the record QB does not offer native a solution to filter a Summary report using the Summary field generate by the report. I will submit a user request to the page.