How To Filter The Summary Column

Question

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?

quickbasecoachd · Answer

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.

quickbasecoachd · Answer

It's not natively possible.&nbsp; 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.&nbsp; 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.

_anomdiebolt_ · Answer

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

_anomdiebolt_ · Answer

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.

_anomdiebolt_ · Answer

&gt;But they would like it to be delivered as an exception subscription report&nbsp;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

Forum Discussion

How To Filter The Summary Column

Related Content

Summary Reports

Default column widths on embedded reports obscuring data

Rearrange Fields/Columns in table

Maximum Summary field not working

Report Column labels not saving