Creating a report that lists records only if there are multiple records according to a particular field

  • 1
  • 2
  • Question
  • Updated 2 years ago
  • Answered
I have a field called Validation ID which is a text field. I am trying to create a report that will display a list of all records where there are more than 1 record with the matching Validation ID's.

Example:


List all would look like this...

Record 1 Validation ID = A
Record 2 Validation ID = B
Record 3 Validation ID = C
Record 4 Validation ID = D
Record 5 Validation ID = D
Record 6 Validation ID = E
Record 7 Validation ID = F
Record 8 Validation ID = F
Record 9 Validation ID = F

New Report would look like this...

Record 4 Validation ID = D
Record 5 Validation ID = D
Record 7 Validation ID = F
Record 8 Validation ID = F
Record 9 Validation ID = F
Photo of Dan Park

Dan Park

  • 410 Points 250 badge 2x thumb

Posted 2 years ago

  • 1
  • 2
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
I don't know of a direct way to do this (maybe Dan D. does).

A temporary work-around is to make a summary report.
Set the rows to summarize "# of X" grouped by 'validation ID' and then set a custom sort "# of X" high-to-low.

This will put the number of Validation D's and Number of validation E's.  Then you can click on that row to see the details.

It will get your end goal, but will only show each validation ID each time you click the drilldown.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,322 Points 20k badge 2x thumb
UPDATED: I am short time to create a demo of this but you have to use script and it is not that complicated. If nothing below makes sense to you feel free to contact me off-world and I can help you get this working.


You have to use script. Here is the essence of the code:
var data = [
  [1,"A"],
  [2,"B"],
  [3,"C"],
  [4,"D"],
  [5,"D"],
  [6,"E"],
  [7,"F"],
  [8,"F"],
  [9,"F"]
];
var result = _.chain(data) .groupBy(function(item) { return item[1]; }).filter(function(item) { return item.length > 1; }).flatten() .groupBy(function(item, index) { return Math.floor(index / 2); }).values();
console.log(JSON.stringify(result, null, ""));
>[[4,"D"],[5,"D"],[7,"F"],[8,"F"],[9,"F"]]
Basically you call the API and get a representation of the data in a JavaScript variable (data). Then you call a few Underscore methods to group, filter, flatten, and group again. Then you  either template the result to HTML (perhaps using Mustaache or Underscore's template() method) or run some further Underscore to turn the above result into a long query like this:
var data = [  [1,"A"],
  [2,"B"],
  [3,"C"],
  [4,"D"],
  [5,"D"],
  [6,"E"],
  [7,"F"],
  [8,"F"],
  [9,"F"]
];
var result = _.chain(data) .groupBy(function(item) { return item[1]; }).filter(function(item) { return item.length > 1; }).flatten() .groupBy(function(item, index) { return Math.floor(index / 2); }).values() .pluck(0); var query = "{3.EX." + result.join("}OR{3.EX.") + "}";
console.log(query);
>{3.EX.4}OR{3.EX.5}OR{3.EX.7}OR{3.EX.8}OR{3.EX.9}
What could be easier?
(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,322 Points 20k badge 2x thumb
I created a demo:

Duplicates Only ~ Tasks Table
https://haversineconsulting.quickbase.com/db/bmi6z3yee?a=td

Click the green button labeled "GiveMeDuplicates.js".

There appears to be a small problem with the version of Underscore QuickBase is using so this is not 100% complete. I am still working with the hardcoded data rather than drawing the data with the API using API_DoQuery.

I have to go to my beloved JavaScript meetup so I will finish this tomorrow.l ENjoy.
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 2,256 Points 2k badge 2x thumb
Another solution would be to create a table with the unique values and make that value the Key Field.  Then create a relationship from that new table to the table where the data exists.  Create a Summary Count field counting all the records assigned to the Parent.  Then set the Related Reference field between the new table and the child to a formula-Text field.  Populate the formula with the [Field Name] that contains the validation ID.   Then your table will be connected automatically to "parents" equal to the validation ID.  You can then easily do a report showing the Validation IDs with more than 1 child record.

You could embed the list of children on the Parent-validation Form Layout to show you the duplicate records more easily than the Summary Table-solution (although that would have been my first suggestion too).
(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,322 Points 20k badge 2x thumb
I completed the demo to draw data from the table Records and added an extra set of duplicates for testing purposes:

Duplicates Only ~ Tasks Table ~ GiveMeDuplicates.js
https://haversineconsulting.quickbase.com/db/bmi6z3yee?a=td

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

If you need further help implementing this solution feel free to contact me off-world using the informatmion in my profile:

https://getsatisfaction.com/people/dandiebolt