Query fields

Query fields help you display quick reports of information alongside your records, such as the number of Assets linked to a Shot, the total time Bid for all Shots in a Project, or the latest Version of a Shot.

Creating and configuring a Query field

Using query fields, you can roll-up systemwide information using a single field, displaying the results by:

  • Count. Display the number of records that match the query (e.g., "45" Assets in a Shot).
  • Summary. Display a summary of record values (e.g., "45 days," the total Estimate for Tasks on an Asset).
  • Single record. Display a single record's value (e.g., "100_010_anm_v001" for the latest Version of a Shot).

Here's how to create one:

  1. To create a Query field, first add a new field to your record (e.g., "Asset," "Shot," etc.). Click on the plus sign in the column header in List view and choose "Manage [...] Fields…."
    Manage Asset Fields
  2. Select “Add a new field.”
    Add a new field
  3. Select "Query" as the field type, and give the field a name.
    Characters
  4. Choose an "Entity." The information displayed in the Query field will be based on the entity you choose. For example, if you want to display the number of Assets in each Shot, select "Asset."
  5. Define the Query. You'll be able to query fields on the entity you specified. Choose the filters that return the desired records to be summarized.
  6. Choose a Display method: Query fields can display the results of the query in the following ways:
    • Entity count. Display the total number of records returned by the query.
    • Summarize by Calculation. Choose a field and calculation method to summarize the records returned by the query.
    • Single Record. Display the value of one field on one record.
      • Define a sort order. Pick a field and method for sorting the results of the query. Tip: To display the "latest" of something, choose “Date Created” and “Descending.”
      • Pick a field to display.
      • Choose whether to display the cell as a link to the detail page.
  7. Select “Next” and then "Create field."
    Create field

Keep in mind:

  • Query fields need at least one active filter to be saved.
  • Query fields can be modified after being created. Just bring up the configure field dialog and modify the filters. Note that query fields can't be converted into other types of fields such as "Text" or "Number" fields.
  • Sorting and grouping is disabled on query fields. This is because we only calculate what is required by the page when the page loads for performance reasons.
  • The Query Builder cannot (yet) query on query fields.
  • Query fields can't be added to email notifications (doing this won't produce errors, but their summary values will not evaluate - they'll always show up as blank).
  • Query fields do not have an implicit project filter, so if you're creating filters that are not based on the “Current entity,” the filters will return results regardless of project association.

Interacting with Query fields

These fields are like short reports that calculate a value when the page loads, and then presents the answer for you to view. You can then interact with the information in various ways, depending on how the field is configured.

Selecting a Query field to display a Focus Window

For Query fields displaying the Count or Summary of a query, you'll be able to click on the field to launch a focus window.

As an example, below is a Query field named “Total Bid,” which is displaying a sum of the Bid field on all Tasks linked to an Asset.

Total Bid

Notice that when you hover over the field, it displays an (i) icon along with a “Click to view details” tooltip.

Details

Clicking the field launches a focus window that shows you exactly which records the field is summarizing. In the example below, the focus window shows you all Tasks linked to the Asset. When you look at the “Bid” field value of each of these Tasks, you'll see that they total up to 9 days—exactly what the query field displayed on the Shots page. You can even edit the records in the focus window.

Bid

Selecting a value within a Query field to display its detail page

For Query fields displaying a single record, you'll be able to click on the field to jump into the record's detail page (if your Admin has configured the field to do so, otherwise the record will display as "read only" text).

Latest Version

Example Query fields

Total Bid (on Asset)

"Bid" is a duration field on a Task. Below is an example that shows how to set up a Query field that will find all Tasks linked to a given Asset, and then add up all the Bid values for each of those linked Tasks.

Total Bid on Asset

% Final (on Project)

Below is an example that shows how to create a Query field called "% Complete" on a Project that will display the percentage of all Tasks for a given Project that are final (where the status of a Task is final).

% Complete

Latest Version (on an Asset)

Below is an example that shows how to display the latest Version of an Asset alongside each Asset record. You can modify this example to show the latest Version for a single department by making the filters specific to one department.

Version

More example Query fields

  • Total number of finaled Shots on a project
  • Total time bid for a Project
  • Total time logged for a Project
  • Count of all character Assets in a Shot
  • Latest Reply to a Note
  • Have others you want to create but can't? Email us!

Filtering for the "Current Entity" and "Current User"

In order to make the results of the query unique to each record, you can filter for things such as "Current Shot," "Current Task," that is, the record type on which you're creating the Query field. You can also filter on the current logged-in user, which is useful when querying for Notes or Tasks written by or assigned to the person logged in.

For example, if you're creating a Query field on Playlists that lists Notes written by you (the current user) for each Playlist, you'd define the following two filters:

  • "Links includes Current Playlist"
  • "Author is Me"

When running this query, Shotgun will look to each record on the page (i.e., each Playlist) and find Notes that have it as a link, and then Shotgun will run through that list of Notes and only return the Notes where you (the current user, or "Me") is the Author.

Playlist

Follow

4 Comments

  • 0
    Avatar
    Tom Stratton

    Is there a way to concatenate two text fields together - or better yet, if i have two fields and one is blank, use the value of the non-blank field?

  • 0
    Avatar
    Andrew Richmond

    Nevermind - I got this to work - It was a mistake in the any/all settings for the search. I had to search "any" on two sets of outside criteria that matched "all" of the inside criteria.

  • 0
    Avatar
    Andrew Richmond

    Is there a way to query all the blank fields - for instance, I want to find out all the assets that are blank in the %Complete column. But using the query %Complete>is>0 - doesn't give me the blank fields. I tried tying %Complete>is>Empty & %Complete>is>Blank but neither of those worked.

    Thanks!

  • 0
    Avatar
    Nate Barnard

    Hi.

    Looking to group statuses into a summary percentage as the floor uses more statuses than production wants to report individually.

    Referencing Ex.  % Complete from above:

    Floor has 3 statuses in use, Final, Complete, Locked each with varying meaning.  In prod report % complete should include all 3 tasks with any of these statuses.  Currently can only see a way to create a percent based on one status.  Request multi-select status for percent summary.

    Floor: 20 shots with an anim task.

    3 final

    3 complete

    4 locked

    10 in progress

    Need percentage field to return 50%

    Any ideas?

    Thanks

Please sign in to leave a comment.