Query Design and Results FAQ

Why doesn't the student group query sort properly?

You can adjust the sort key for student groups. To do this:

  1. Access the Student Group Category via System Homepage > Configuration/Classification/Student Groups.
  2. Update the Sort Key in the query results screen and click Update Records to save your changes.

Additional Information

Sort Keys are used to group/sort similar named values together and/or to order values in a particular way. Whereas often just sorting alphabetically doesn't keep similar values together and/or in a particular order that makes sense to the user. This is the first level of sorting applied to a field in the query results. The next level is applying the sort within the query itself.

How do I format the "TODAY" field in a query?

The date field defaults to the MM/DD/YYYY format in a query but can be adjusted based on user preference.

Note: this field is currently only available in the Donation and Pledge queries.

To do this:

  1. In the Query Design, select the "Today" field.

  2. Click on the Style tab in the third column. 

  3. Use the Override Display Format field to set the format as desired.

Use a combination of the formats below separated by "/" or "," to set the date format in the query.

mMonth as a number with no leading zeros
mmMonth as a number with leading zeros
mmmThree letter abbreviation of the month (e.g. Dec)
mmmmFull name of the month (e.g. December)
dDay as a number with no leading zeros
ddDay as a number with leading zeros
dddThree letter abbreviation of the day of the week (e.g. Wed)
ddddFull name of the day of the week (e.g. Wednesday)
yyTwo digit year
yyyyFour digit year

How do I rename a query?

When building custom queries for later use, it's helpful to rename the query to be something indicative of what you are using the query for. To do this, perform the following steps:

You can adjust the name of a query on the Query tab in the query design screen.

  1. From a query design page, click on the Query tab in the center panel.
  2. Type the name you would like for this query on the Query Description line.
  3. Click on the Organize (+) menu and select either Save to Workspace... or Overwrite Query.
    • Save to Workspace... should be used if this is a brand new custom query that you're saving.
    • Overwrite Query should be used if you are modifying an existing query that you use.
      • WARNING: Overwriting a query will change this query for anyone looking to access it, so, be sure you use this option deliberately!

You may also find these articles helpful:

What's the difference between the Classifications Exist (by Date) and Classifications List (by Date) function fields?

Here are some tips on the information you can get using these two helpful function fields:

Classifications Exist (by Date)

  • Displays a yes/no flag to indicate whether or not a record has any or all of the classifications set in the parameters for the time frame specified.
  • It is not connected to the Classifications field. 
  • If both the function and classification fields, the results will display as "yes" for all records regardless of the start/end dates on the classification record itself.

Classifications List (by Date)

  • Displays the classifications selected within the time frame specified within the parameter settings.
  • Does not require the Classification field to be included in the query results.
  • Use the parameters to set the classifications you are querying on
  • Set the criterion filter field to set the filter to view only records with those classifications indicated in the parameters

How can I change the name of a column in my query results?

Query columns will use the underlying field name as the header on the results page. To change the name of a column in your query results, first navigate to the query design screen. Select the field you would like to rename, and in the properties pane on the right of the screen, click the field name to edit the description. 

As you change the field description, you will see the original field name appear above for your reference:

Once you've renamed the appropriate fields, run your query and you'll see that the columns are now labeled with the new field name descriptions. This trick can be particularly useful when creating data package exports that must have specific column names.

How do I add static text or placeholder columns in my data package queries?

In some cases, when creating a query for use in data packages you may need to include static text or placeholder columns in order to match an import template from a third party vendor. Each column in a query result must be tied to an underlying field. In cases where you need to include static text or a blank 'placeholder' column, you can do so in one of two ways:

  1. Use Person Reference Numbers
    If there is static information that you want to store and you are querying person records, you can use the Veracross person reference number functionality to create a reference number and then batch-add that static value for all of the applicable people. Once you have added those reference numbers, you can include them in your query using the reference number function field. You can read more about using person reference numbers in this documentation article.

    Similarly, you can create placeholder reference numbers without a value, and when including the reference number function field in your query design it will return a blank column.

  2. Repurpose an Unused Field in the Database
    If you are not querying person records or do not want to use reference numbers, you can repurpose an unused field in your database, such as a notes field, to either store static information or include as a blank placeholder column.

With either solution, be sure to rename the field description in your query design in order to give your column the correct header.

What does the "a provided value is too large to be processed by the database" error mean?

If you receive the error "a provided value is too large to be processed by the database" when trying to save a query, the most likely reason this happened is that one of your fields has criteria that is over the maximum character limit for being saved. 

  • For example, if you are copying many person ID values into the Person ID field.
  • The character limit for query criteria is 2000 characters. 

Suggested workaround: If you are going by the copy and paste method, a best practice is to save both queries in a workspace, one for the copy and one for the paste, and then add instructions on how to reproduce the desired query that cannot be saved. You can add directions on the "Query" tab of the query design.

What are function fields?

Function fields provide a way to display customizable aggregate data in Axiom. Conceptually, they are similar to calculated fields, though with the added ability to change the input of the calculation directly in the field’s parameters. Function fields exist within certain query templates and are visually set apart from regular fields with the orange “Fn” prefix. When a function field is highlighted, parameters are displayed in the Properties tab on the right side of the query design.

To learn more about function fields, we recommend you review our documentation on the topic.

How do we make nametags?

Nametags can be constructed within Axiom's query design pulling whichever fields you'd like. To set up nametags, follow these steps:Working from a find person query, take the following steps:

  1. Beginning on a Find Person query design screen, in the center section of included fields, select the fields to include on the nametag by clicking the "Displayed" checkbox to the left of the field name.
    •  If you want to include a picture to your nametag, search for the Photo field in the field library on the left and add Photo to your design.
  2. Click the Query tab located near the top center of the included fields section.
  3. Under the first header "Visualization" select the "Cards" layout.
  4. On the right hand side of the screen, the Visualization Configuration options will then appear.
  5. If you've included a photo, be sure to select Photo Card from the Card Format dropdown menu. Then select photo from the card fields.
  6. Run the query to see your result. You may need to modify the order of the included fields to achieve the desired result.

Note: The cards visualization only allows for printing on the follow parameters:

  • Page Size - 8.5 ×11in
  • Card Size - 2.6×2in
  • Number of Columns - 3
  • Number of Rows - 5
  • Total Cards per Page - 15

What is that yellow bar that appears at the bottom of some query design pages?

The yellow bar at the bottom of a query indicates the presence of a query comment. Query comments can be edited from the query design screen by clicking on the Query tab near the top of the included fields section of the screen. After you've added a query comment, be sure to save the query to a workspace by clicking the "+" Organize button in the upper right corner of the screen.

Query Comments are a great way to preserve instructions for queries that are not looked at frequently. All users with access to queries have the ability to add query comments.

Can a query that has subtotal headers be automatically collapsed when the query is run?

Ctrl+Click or Command+Clicking these arrows will collapse all subtotal headers on that specific tier of subtotal headers.

There’s no setting to have a subtotaled header be automatically collapsed when a query is initially run; it always shows each header as expanded, initially. However, to quickly collapse these headers, you can use the following hotkeys to collapse all headers:

  • Ctrl+Click (PC)
  • Command+Click (Mac) 

Can I filter a query based on record count?

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:

  1. 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.
  2. 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.’
  3. 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.
  4. 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:
  5. 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.

How is the ‘My Student’ field used?

The ‘My Student’ field can be pulled into a query and allows you to filter the query results to only show records related to students that the logged in person teaches.

The field returns results based on active classes in which the student is currently enrolled, where the logged in user is the primary or additional teacher on the class permissions.

This field can be particularly useful when sharing queries with teachers by publishing them in the teacher portal, as it allows you to create and publish one single query that will only show the results relevant to the teacher viewing it.

How do I exclude pledge payments from a Gift or Donation query?

When in a gift or donation query, you can exclude pledge payments from the query results by adding the "Apply To Pledge" field with the criteria set to “No”. This will only display donations that are not linked to any pledges.

How do I see student birthdays in a query?

To see a query where all student birthdays are shown, peform the following steps:

  1. Navigate to the Find Student query from the Main homepage.
  2. From the query design screen, go to the top left and type in "Birthday" in the search field.  Then, add the "Birthday" (Classifications) field into your query design.
  3.  When you run the query you will see all students and their birthdays.

To see all the Student Birthdays by Grade Level you can use the same query as above and, on the Current Grade field, select the "Subtotal by" option on the Properties. Running this query displays all Student Birthdays by Grade Level. 

The Birthday Calendar query on the Main homepage displays all birthdays for the current school year.

What does “P1”, “P2”, “P3” and “P4” mean in reference to attendance in a Class Enrollment query?

It can be helpful to report on student class attendances for a grading period. When pulling attendance data into a Class Enrollment query, you will see the fields to the right as options in the Attendance Section.

P1 is referring to the first “minor” grading period. P2 would be the second minor period, and so on. Similarly, T1 is referring to the first major grading period. You can see how your grading periods are configured (mid, minor, or major) via the Grading Periods query located in the Configuration section of the System Homepage.

You might also find these documentation articles helpful:

Why is my query returning incorrect results when I use the Classification Exists (by date) function field?

The system looks at the high level date range when returning results for this function field. If someone has an actual Classification Start/End Date and the School Year Start/End Date fields are both populated, the system uses the School Year Start/End date instead of the date range (or vice versa if the date range falls outside the school year).

When building a query that actively updates based on the Active Grading Period, which Grading Period parameter should I use?

There are multiple parameters you can use to build your query to actively update based on the current or active grading period. These options are:

  • {Current_Reporting_Period} — the mid period associated with the active minor grading period of the applicable school level's primary grading group, as long as "today" is between two weeks after the beginning and end dates of the mid period of the same name
  • {All_Active_Grading_Periods} —  all non-obsolete minor grading periods that are currently marked as "Active"
  • {All_Current_Grading_Periods} — all non-obsolete minor grading periods for which today is between the begin and end dates

You can see the configuration of your Grading Periods (mid, major, minor) through the Grading Periods query on the System Homepage. For clarification on what mid, major, and minor grading periods are, please see this documentation.

What records are returned if I filter a query on the "most_recent_school_year"?

If you filter the School Year field in your query design by 'most_recent_school_year', your query results return records from the current school year; however, in the summer, the query results yield records from the preceding school year.

We recently moved from a custom GPA to a 'Veracross Standard' GPA. Do we need to edit any of the function fields we've been using related to GPA within our queries?

No — as query values are generated dynamically every time you open a query, you do not need to make any adjustments to the function fields and can use your queries as normal.