Skip to main content

Filtering Grouped Records

  • November 13, 2019
  • 1 reply
  • 15 views
B

I’m trying to figure out if there’s a way to filter groups of records. Each month, our volunteers send in a progress report for the student they’re working with. I currently have the progress reports grouped by the student’s name, so all of the student’s reports appear together.
I’m trying to filter the view to show only students whose volunteers have NOT filled out the report for that month so that we can easily see which volunteers we need to follow up with.

I’ve been able to filter for folks who have reported by filtering for the month the report was sent in, but I haven’t been able to do the opposite. Is there any way to apply a label to a group of records that I could filter for?

    1 reply

    Justin_Barrett
    Forum|alt.badge.img+21

    Welcome to the community, @Beth_Watkins! :grinning_face_with_big_eyes: From your description so far, it sounds like you have a [Students] table for tracking students, and something like a [Reports] table for tracking progress reports, with the latter containing one record for each monthly report submitted by a volunteer for a given student (linked from [Students]). If that’s correct, finding the reports for a given month is easy (as you’ve noted) because the report records exist. However, to track missing reports for a given month, you’ll actually need to do some work in the [Students] table based on those linked reports.

    Add a {Latest Report} rollup field to the [Students] table, using the links coming from [Reports], targeting the field that lists the report date, and using the MAX(values) aggregation formula to find the most recent date. Then add a formula field named something like {Current Month Reported} using the following formula:

    DATETIME_FORMAT({Latest Report}, "M/YY") = DATETIME_FORMAT(TODAY(), "M/YY")

    That will fill those fields with either a 1 or 0, with 1 meaning that the month and year of the latest report match the current month. Now add a filter for that field to only show records where that value is 0, and you’ll know which students have volunteers who haven’t submitted reports this month.

    Terms & ConditionsAccessibility statement