Can I use grouped field totals to filter report?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • In Progress
  • (Edited)
I have a report that is using two fields to group and sort the data and my only problem is, from that point on, I only want to show the entries where there is more than one record matching my grouping criteria.

In this particular case, we are using employees who are given assignments on event days. The purpose of the report is supposed to be to show when someone has been booked for more than one position (or job) on a single day. I am grouping and sorting the report by related employee and then by event date and that works perfectly but I would like to be able to say "if a group has only 1 record, do not show it in the report". I've tried using a summary table but did not like it because the grouping, sorting, and display options are not as helpful in displaying the information we would like in the report.

Does anyone know if we can use the totals from our grouped report to filter the table as well? If not, anyone have any other ideas either native or with code? Thanks!
Photo of rhuffman

rhuffman

  • 328 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
In order to filter the report, you will need to have a table populated with a Key field which concatenates the employee and the date and which contains all the possible combinations.  If you had that, then you could create a relationships back to your details schedule table and make a summary of the # of entries for that Employee for that date.

There are a variety of ways to maintain that summary table.  One way to get it started is to create a summary report off your schedule table and have it summarize the unique values for that field, which would be in the format 

List("-", totext([Employee #],totext(date]))


 and then use the "More .." button to copy it across to the summary table.  Then the data could be maintained in that summary table using an Action to create the additional entries as they are required.
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
There are a few other options.  Can you describe your tables and their relationships?  Then we can do it with some summaries and lookup logic.