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.
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:
- 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…."
- Select “Add a new field.”
- Select "Query" as the field type, and give the field a name.
- 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."
- 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.
- 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.
- Select “Next” and then "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.
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.
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.
Notice that when you hover over the field, it displays an (i) icon along with a “Click to view details” tooltip.
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.
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).
"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.
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).
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.
- 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!
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.