As of July 12, 2021, this "Learn Veracross" site has been deprecated. It will remain live at least through December 23, 2021, but will no longer be updated. Q&A articles are only visible to authenticated users in the Veracross Community; please speak with your implementation or account manager for additional information.
Yes! You can filter summarized queries based on record count by using the ‘Apply Criteria to Record Count’ field.
One example of where this might be useful is if you want to create a query to only show students with five or more absences in a given period of time.
Here are the steps you can take to create that query using this feature:
- Starting with a ‘Find Master Attendance’ query, pull in the necessary fields needed to filter your query. You should be sure to also pull the ‘Record Count’ field into your query design.
- Set the necessary criteria on each field to limit your query results as needed. In this example, that would include setting the ‘Attendance Category’ to ‘Absence’ and specifying that record count ‘is greater than 5.’
- Summarize on the field(s) by which you want to ‘count.’ In this case, you want to count master attendance records for each individual student, so you should summarize by the ‘Person’ field.
- Click on the ‘Record Count’ field and in the properties pane, expand the ‘Summarization’ section. Select ‘Sum’ from the ‘Summarization Method’ dropdown (because you want to filter your query based on the sum of the records), and set the ‘Apply criteria to sum value’ to ‘yes,’ as seen here:
- Since the system will automatically include a ‘Record Count’ column in query results when you summarize on any field, in order to prevent two record count columns from showing, the last step is to go to the ‘Style’ tab of the properties pane for the record count field and set the ‘Column Options’ dropdown to ‘Suppress from Results,’ as seen below. This hides the record count field you added to the query design.
Once you’ve followed these steps, the resulting query should return only those students who have more than 5 master attendance records that meet your query criteria.