Query Design and Results FAQ

Why doesn't this 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. 

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) 

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.