Expand all | Collapse all

Can I use grouped field totals to filter report?

  • 1.  Can I use grouped field totals to filter report?

    Posted 07-07-2017 20:26
    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!

  • 2.  RE: Can I use grouped field totals to filter report?

    Posted 07-10-2017 11:20
    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.

  • 3.  RE: Can I use grouped field totals to filter report?

    Posted 07-12-2017 04:21
    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.